intermediate
SQL Server
8 min read
Overview
Connect Spark to Microsoft SQL Server databases to query tables and write outputs using the SQL Server JDBC driver.
What You Will Learn
In this lesson, you will learn:
- SQL Server Connection: Setting connection URLs and ports.
- SQL Server Driver: Identifying the SQL Server driver class.
- Data Integration: Reading and writing SQL Server tables.
Detailed Concept Explanation
To connect Spark to Microsoft SQL Server, you use the SQL Server JDBC driver (com.microsoft.sqlserver.jdbc.SQLServerDriver).
The standard URL structure is:
jdbc:sqlserver://<host>:<port>;databaseName=<database> (default port is 1433).
Code Examples
Python (PySpark) Implementation
python
from pyspark.sql import SparkSession
spark = SparkSession.builder \
.appName("SQLServerTest") \
.config("spark.jars.packages", "com.microsoft.sqlserver:mssql-jdbc:9.4.1.jre8") \
.getOrCreate()
# Read from SQL Server table
sqlserver_df = spark.read.format("jdbc") \
.option("url", "jdbc:sqlserver://localhost:1433;databaseName=mydb") \
.option("dbtable", "orders") \
.option("user", "sa") \
.option("password", "StrongPassword123") \
.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
.load()
sqlserver_df.show()
Expected Output
text
+-------+------+
|orderId|amount|
+-------+------+
| 5001| 99.9|
+-------+------+
Execution Plan Diagram (Python & Scala)
Execution Plan Diagram
SparkSession.builder
read.format(jdbc)
option(url
jdbc:sqlserver)
load()
show()
Scala Implementation
scala
import org.apache.spark.sql.SparkSession
val spark = SparkSession.builder().appName("SQLServerScala").getOrCreate()
val sqlserverDF = spark.read.format("jdbc")
.option("url", "jdbc:sqlserver://localhost:1433;databaseName=mydb")
.option("dbtable", "orders")
.option("user", "sa")
.option("password", "StrongPassword123")
.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
.load()
sqlserverDF.show()
Expected Output
text
+-------+------+
|orderId|amount|
+-------+------+
| 5001| 99.9|
+-------+------+