Azure SQL Data Warehouse / Synapse

Set up Azure SQL DW connection parameters

1
dwDatabase = "<DATABASENAME>" ## The Azure SQL Data Warehouse database name
2
dwServer = "<DWNAME>.database.windows.net" ## The Azure SQL Server
3
dwUser = "<USERNAME>" ## The dedicated loading user login
4
dwPass = dbutils.secrets.get(scope = "<SECRETNAME>", key = "<KEYNAME>") ## The dediciated loading user login password
5
dwJdbcPort = "1433"
6
sqlDwUrlSmall = "jdbc:sqlserver://" + dwServer + ":" + dwJdbcPort + ";database=" + dwDatabase + ";user=" + dwUser+";password=" + dwPass
Copied!

Define a query

1
sqlQuery = """
2
SELECT *, 'AzureSqlDw' AS SourceSystem
3
FROM dbo.<TABLENAME>
4
"""
Copied!

Create a Spark DataFrame using the SQL DW data

1
data = spark.read \
2
.format("com.databricks.spark.sqldw") \
3
.option("url", sqlDwUrlSmall) \
4
.option("tempDir", tempDir) \
5
.option("forwardSparkAzureStorageCredentials", "true") \
6
.option("query", sqlQuery) \
7
.load() \
8
.createOrReplaceTempView("<TEMPVIEWNAME>")
9
#.write.saveAsTable("<TABLENAME>")
Copied!