How to Write, Read & Append Data to Dedicated SQL Pool Table Using Notebook in Azure Synapse | Azure Synapse Analytics Tutorial

How to Write, Read & Append Data to Dedicated SQL Pool Table Using Notebook in Azure Synapse

📘 Overview

Azure Synapse Notebooks allow seamless interaction with Dedicated SQL Pools using Spark. You can create and manage SQL tables from PySpark DataFrames, perform bulk writes, and query them using Spark SQL or Synapse SQL on-demand.

🛠️ Step-by-Step Examples

✅ 1. Create DataFrame

%%pyspark
data = [(1, "Aamir", 5000), (2, "Lisa", 6000)]
columns = ["id", "name", "salary"]
df = spark.createDataFrame(data, columns)

✅ 2. Write Data to Dedicated SQL Pool

Use the write.synapsesql format:

df.write \
  .format("com.databricks.spark.sqldw") \
  .option("url", "jdbc:sqlserver://<server>.database.windows.net:1433;database=<dbname>") \
  .option("dbtable", "dbo.Employee") \
  .option("user", "<username>") \
  .option("password", "<password>") \
  .mode("overwrite") \
  .save()

✅ 3. Read Data from SQL Pool Table

df_read = spark.read \
  .format("com.databricks.spark.sqldw") \
  .option("url", "jdbc:sqlserver://...") \
  .option("dbtable", "dbo.Employee") \
  .option("user", "...") \
  .option("password", "...") \
  .load()

df_read.show()

✅ 4. Append Data to Existing Table

df_append = spark.createDataFrame([(3, "John", 7000)], ["id", "name", "salary"])
df_append.write \
  .format("com.databricks.spark.sqldw") \
  .option("url", "jdbc:...") \
  .option("dbtable", "dbo.Employee") \
  .option("user", "...") \
  .option("password", "...") \
  .mode("append") \
  .save()

📌 Tips

• Use overwrite to replace and append to add rows
• Validate schema compatibility before appending
• Check Synapse firewall rules for access

🎯 Use Cases

• ETL workflows from Spark to Dedicated SQL Pool
• Reading warehouse data for ML or transformation
• Bulk appending logs or batch jobs

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.