Write Data to SQL DW from Apache Spark in Azure Synapse

Solution 1:

If you are writing to a dedicated SQL pool within the same Synapse workspace as your notebook, then it's as simple as calling the synapsesql method. A simple parameterised example in Scala, using the parameter cell feature of Synapse notebooks.

// Read the table
val df = spark.read.synapsesql(s"${pDatabaseName}.${pSchemaName}.${pTableName}")

// do some processing ...

// Write it back with _processed suffixed to the table name
df.write.synapsesql(s"${pDatabaseName}.${pSchemaName}.${pTableName}_processed", Constants.INTERNAL)

If you are trying to write from your notebook to a different dedicated SQL pool, or old Azure SQL Data Warehouse then it's a bit different but there some great examples here.

UPDATE: The items in curly brackets with the dollar-sign (eg ${pDatabaseName}) are parameters. You can designate a parameter cell in your notebook so parameters can be passed in externally eg from Azure Data Factory (ADF) or Synapse Pipelines using the Execute Notebook activity, and reused in the notebook, as per my example above. Find out more about Synapse Notebook parameters here.