synapse pyspark write dataframe to sql azure

Simon Zhang 26 Microsoft Employee

I want to use Pyspark datawrite.write method to write dataframe to sql azure database with linked service. How can I achieve it?

Vinodh247 23,426 Reputation points MVP

2024-07-27T11:26:05.6+00:00
Hi Simon Zhang,

Thanks for reaching out to Microsoft Q&A.

The below should work, let me know if you have any questions.

Pre-requisites:

Azure Synapse Analytics workspace with Apache Spark and Synapse SQL configured.

Azure SQL Database and a linked service set up in your Synapse workspace to connect to it.

Steps:

Create a Pyspark notebook in your Synapse workspace.

Generate or load your dataframe that you want to write to the SQL database.

Specify the connection details for the linked service using the spark.conf.set method:

spark.conf.set("spark.sql.parquet.writeLegacyFormat", "true") spark.conf.set("spark.sql.hive.convertMetastoreParquet", "false") spark.conf.set("spark.sql.hive.convertMetastoreOrc", "false") spark.conf.set("spark.datasource.writeDataSourceOptions", "true") spark.conf.set("spark.sql.legacy.allowCreatingManagedTableUsingNonemptyLocation","true")

Write the dataframe to the SQL database using the write.format method:

df.write.format("com.databricks.spark.sqldw") \ .option("url", "jdbc:sqlserver://<server_name>.database.windows.net:1433;database=<database_name>") \ .option("dbtable", "<schema>.<table_name>") \ .option("user", "<username>") \ .option("password", "<password>") \ .option("tempDir", "abfss://<container_name>@<storage_account_name>.dfs.core.windows.net/temp") \ .mode("overwrite") \ .save()

5. Run the notebook to execute the write operation

You can monitor the write progress in the Synapse Studio by going to the "Monitor" hub and checking the "Pipeline runs" or "Spark job" details.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Simon Zhang 26 Reputation points Microsoft Employee

2024-07-27T12:58:34.9333333+00:00
Thanks for your answer. This won't work. You are using user name and password. I am using appid/secret. User name and password are forbidden to use. appid/secret is also forbidden that's why I am trying to use linked services.

My current code is like this.

df.write\ .format("jdbc")\ .mode("overwrite")\ .option("truncae","true")\ .option("url",sqlurl)\ .option("dbtable",dbtable)\ .option("accessToken",sql_access_token)\ .option("encrypt","true")\ .option("hostNameInCertificate","*.database.windows.net")\ .save()

Vinodh247 23,426 MVP

If you are using synapse with linked services and prefer to authenticate using Azure Active Directory (AAD) with an MI, you can achieve this without explicitly providing username and password.

# Load your DataFrame (ex:)
df = spark.createDataFrame([(1, "example")], ["id", "name"])
# Write DataFrame to azuresqldb using the linked service
df.write \
  .format("com.microsoft.azure.sqldw") \
  .option("url", "jdbc:sqlserver://<your_sql_server>.database.windows.net:1433;database=<your_database_name>") \
  .option("dbtable", "<schema>.<table_name>") \
  .option("tempDir", "abfss://<container_name>@<storage_account_name>.dfs.core.windows.net/temp") \
  .option("authentication", "ActiveDirectoryManagedIdentity") \  
# Use Managed Identity
  .mode("overwrite") \
  .save()

The authentication option can be set to ActiveDirectoryManagedIdentity if your synapse workspace is configured with a managed identity. This allows you to authenticate without needing to provide credentials in your code.

Amira Bedhiafi 26,266

To write a PySpark DataFrame to an Azure SQL Database using a linked service in Azure Synapse, you need to set up the following steps:

Set Up Linked Service in Azure Synapse:
- Go to your Azure Synapse workspace.
- In the Management hub, navigate to Linked Services.
- Create a new linked service and select "Azure SQL Database" as the connection type.
- Configure the connection details and test the connection to ensure it's working.
Configure Azure Synapse Notebook:
- In the Azure Synapse workspace, create or open a PySpark notebook.
- Add the necessary libraries for JDBC if they are not already available in your environment.
Write DataFrame to Azure SQL Database:
- Use the Spark DataFrame write method with the JDBC option to write data to the SQL database via the linked service.


# Import necessary libraries

from pyspark.sql import SparkSession

# Create Spark session

spark = SparkSession.builder \

    .appName("WriteToAzureSQL") \

    .getOrCreate()

# Assuming df is your DataFrame you want to write to Azure SQL Database

df = spark.createDataFrame([

    (1, 'Alice', 29),

    (2, 'Bob', 31),

    (3, 'Cathy', 23)

], ['id', 'name', 'age'])

# JDBC connection properties

jdbc_url = "jdbc:sqlserver://<your-server-name>.database.windows.net:1433;database=<your-database-name>"

jdbc_properties = {

    "user": "<your-username>",

    "password": "<your-password>",

    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"

}

# Write DataFrame to Azure SQL Database

df.write.jdbc(

    url=jdbc_url,

    table="<your-table-name>",

    mode="overwrite",  # or "append" depending on your use case

    properties=jdbc_properties

)

Since using usernames and passwords is forbidden, you need to replace them with the linked service credentials. You can use Azure Key Vault to securely manage and retrieve credentials.

Set Up Azure Key Vault:
- Store your SQL database credentials (username and password) in Azure Key Vault.
Access Key Vault from Azure Synapse:
- Configure your Synapse workspace to access the Key Vault.
Modify the PySpark Code to Retrieve Credentials:


# Import necessary libraries

from pyspark.sql import SparkSession

from azure.identity import DefaultAzureCredential

from azure.keyvault.secrets import SecretClient

# Set up Key Vault client

credential = DefaultAzureCredential()

key_vault_url = "https://<your-key-vault-name>.vault.azure.net/"

client = SecretClient(vault_url=key_vault_url, credential=credential)

# Retrieve secrets

username = client.get_secret("<your-username-secret-name>").value

password = client.get_secret("<your-password-secret-name>").value

# Create Spark session

spark = SparkSession.builder \

    .appName("WriteToAzureSQL") \

    .getOrCreate()

# Assuming df is your DataFrame you want to write to Azure SQL Database

df = spark.createDataFrame([

    (1, 'Alice', 29),

    (2, 'Bob', 31),

    (3, 'Cathy', 23)

], ['id', 'name', 'age'])

# JDBC connection properties

jdbc_url = "jdbc:sqlserver://<your-server-name>.database.windows.net:1433;database=<your-database-name>"

jdbc_properties = {

    "user": username,

    "password": password,

    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"

}

# Write DataFrame to Azure SQL Database

df.write.jdbc(

    url=jdbc_url,

    table="<your-table-name>",

    mode="overwrite",  # or "append" depending on your use case

    properties=jdbc_properties

)

Simon Zhang 26 Reputation points Microsoft Employee

2024-07-28T16:17:23.0866667+00:00

Thanks for your reply. I see you setup a linked service. How do you use it?
Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-07-29T12:31:45.4566667+00:00

Hi @Simon Zhang

Thanks for the question and using MS Q&A platform.

Please refer to the similar thread which might help you.

Thread link: https://learn.microsoft.com/en-us/answers/questions/1194932/how-to-use-linked-service-in-notebook-with-pyspark

I hope this information helps, please do let us know if you have any further queries.
Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-07-30T12:41:48.25+00:00

Hi @Simon Zhang

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Simon Zhang 26 Reputation points Microsoft Employee

2024-07-30T16:16:08.07+00:00

I marked the wrong answer. My answer is the final answer. The marked one is not a right answer at all. Can you please change it for me?
Simon Zhang 26 Reputation points Microsoft Employee

2024-07-30T16:16:56.3333333+00:00

Please see my answer.

Accepted answer

Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-07-31T13:43:45.0033333+00:00
Hi @Simon Zhang

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue: I want to use Pyspark datawrite.write method to write dataframe to sql azure database with linked service. How can I achieve it?

Solution:

Here is the answer I got:

server = 'sql server name'

Port = 1433

Database = "databaseName"

jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"

sql_access_token=TokenLibrary.getConnectionString("linked service name")

conn_Prop = {

"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"accessToken" : sql_access_token

}

restapiJson.write\

.mode("overwrite")\ .option("truncae","true")\ .option("url",jdbcUrl)\ .option("dbtable",dbtable)\ .jdbc(jdbcUrl, dbtable, properties={"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver","accessToken" : sql_access_token})

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and **Yes **for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Simon Zhang 26 Reputation points Microsoft Employee

2024-07-30T16:11:44.3166667+00:00
My answer is here:

server = 'sql server name'

Port = 1433

Database = "databaseName"

jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"

sql_access_token=TokenLibrary.getConnectionString("linked service name")

conn_Prop = {

"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"accessToken" : sql_access_token

}

restapiJson.write\

.mode("overwrite")\ .option("truncae","true")\ .option("url",jdbcUrl)\ .option("dbtable",dbtable)\ .jdbc(jdbcUrl, dbtable, properties={"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver","accessToken" : sql_access_token})
Please sign in to rate this answer.
Simon Zhang 26 Reputation points Microsoft Employee

2024-07-30T16:09:45.5333333+00:00

Thank you all.

Here is the answer I got:

server = 'sql server name'

Port = 1433

Database = "databaseName"

jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"

sql_access_token=TokenLibrary.getConnectionString("linked service name")

conn_Prop = {

"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"accessToken" : sql_access_token

}

restapiJson.write\

.mode("overwrite")\ .option("truncae","true")\ .option("url",jdbcUrl)\ .option("dbtable",dbtable)\ .jdbc(jdbcUrl, dbtable, properties={"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver","accessToken" : sql_access_token})

Chandra Boorla 2,990 Reputation points Microsoft Vendor

2024-07-31T14:25:03.8133333+00:00

Hi @Simon Zhang

Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

synapse pyspark write dataframe to sql azure

1 additional answer

Your answer