In this article, I will show how to connect Azure SQL Database from Azure Databricks, read a SQL table or query result, and write a Spark DataFrame back to Azure SQL Database.
We will use PySpark because Azure Databricks is built for distributed data processing. PySpark can read data from external databases into Spark DataFrames, transform the data at scale, and write the result back to Azure SQL Database. Microsoft also provides Spark connector support for SQL Server and Azure SQL as input data sources and output sinks for Spark jobs.
Connect the Azure SQK Database
define the Azure SQL Database connection settings.
Read data from Azure SQL Database
Read a full table and Read a SQL query
To read a full table, use the dbtable option. To run a SQL query against Azure SQL Database and load the result into Databricks, use the query option.
Databricks also supports JDBC-style reads and writes with Spark Data Source APIs, including reading from a table or pushing down a source SQL query.
Write Data to Azure SQL Database
In this example, we first create a small pandas DataFrame and convert it to a Spark DataFrame.
import pandas as pd
data = {
"Product": [
"Laptop", "Mouse", "Keyboard", "Monitor", "Headset",
"Webcam", "USB Hub", "Laptop", "Mouse", "Monitor"
],
"Region": [
"North", "South", "East", "West", "North",
"East", "South", "West", "North", "East"
],
"Units_Sold": [15, 42, 30, 8, 25, 17, 60, 22, 38, 10],
"Revenue": [22500, 1260, 2400, 3200, 1875, 850, 1800, 33000, 1140, 4000]
}
pdf = pd.DataFrame(data)
df = spark.createDataFrame(pdf)
display(df)
In Azure Databricks notebooks, spark is usually available automatically. If you run the code outside Databricks, you may need to create a SparkSession manually.
Use .write.format("sqlserver") and provide the connection options.
df.write
.format("sqlserver")
.options(**SQL_OPTIONS)
.option("dbtable", "dbo.test_table")
.mode("overwrite") #or "append"
.save()
Explore more
Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.
Top comments (0)