Skip to content

Lab056.md

Latest commit

 

History

History
33 lines (20 loc) · 1.5 KB

Lab056.md

File metadata and controls

33 lines (20 loc) · 1.5 KB
Raw

Lab 056: Using Spark to write data into Synapse SQL Pools

TODO: solliancenet beginning of 02

Business Objective

We need to load data into our Synapse SQL Pool from data that we already transformed in the data lake. Let's assume we did all of the transform already in Spark and we'd like to leverage Spark to do the final insert into Synapse.

Implementation

We will use scala to do this by Python or SQL are other options.

This code writes the data retrieved from Blob Storage into a staging table in Azure Synapse Analytics using the SQL Analytics connector. Using the connector simplifies connecting to Azure Synapse Analytics because it uses AAD pass-through. There is no need to create a password, identity, external table, or format sources, as it is all managed by the connector.

As the cell runs you can look at the job execution status under the cell.

%%spark

// Import libraries for the SQL Analytics connector
import com.microsoft.spark.sqlanalytics.utils.Constants
import org.apache.spark.sql.SqlAnalyticsConnector._
import org.apache.spark.sql.SaveMode

// Set target table name
var tableName = s"SQLPool01.wwi_staging.Sale"

// Write the retrieved sales data into a staging table in Azure Synapse Analytics.
sales.limit(10000).write.mode(SaveMode.Append).sqlanalytics(tableName, Constants.INTERNAL)

After this completes, find the staging table in the Data pane and perform a SELECT TOP 100 rows to ensure the data was copied successfully.