question

sakuraime avatar image
0 Votes"
sakuraime asked PRADEEPCHEEKATLA-MSFT commented

Databricks connect to Azure synapse dedicated pool

I have the following questions about connecting to Azure synapse dedicated pool from databricks

  1. How to connect to Azure synapse dedicated pool using private link?

  2. If I am going to use Service principal from databricks ( I have a key vault to store the service principal secret , I have set spark.conf.set

spark.databricks.sqldw.jdbc.service.principal.client.id, spark.databricks.sqldw.jdbc.service.principal.client.secret,
what should be the url for df.write.format("com.databricks.spark.sqldw")? Should I need to use option(username)...??

  1. And I have issue during write dataframe to Azure synapse
    80271-image.png


azure-synapse-analyticsazure-databricks
image.png (55.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

anyone has the idea ?

0 Votes 0 ·

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @sakuraime,

Connecting Azure Synapse using OAuth 2.0 with a service principal for authentication is available from Databricks Runtime 8.1 and above.

Below are the steps to connect Azure Synapse using OAuth 2.0 with a service principal for authentication:

Step1: Provide service Principal – permissions to Azure Synapse Analytics and storage account.

Azure Synapse Analytics: Go to workspace => Under settings => SQL Active Directory admin => Click on Set admin => Add registered application => Click on save.

81441-image.png

Azure Storage temp account: Go to Storage account => Access Control (IAM) => Add role assignment => Select Role: Storage Blob Data Contributor Select: register application => Click on save.

81318-image.png

Step2: Define Service Principal credentials for the Azure Synapse Analytics and storage account.

Python Code:

 # Defining the service principal credentials for the Azure storage account
 spark.conf.set("fs.azure.account.auth.type", "OAuth")
 spark.conf.set("fs.azure.account.oauth.provider.type",  "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")
 spark.conf.set("fs.azure.account.oauth2.client.id", "<application-id>")
 spark.conf.set("fs.azure.account.oauth2.client.secret", "<service-credential>")
 spark.conf.set("fs.azure.account.oauth2.client.endpoint", "https://login.microsoftonline.com/<directory-id>/oauth2/token")
    
 # Defining a separate set of service principal credentials for Azure Synapse Analytics (If not defined, the connector will use the Azure storage account credentials)
 spark.conf.set("spark.databricks.sqldw.jdbc.service.principal.client.id", "<application-id>")
 spark.conf.set("spark.databricks.sqldw.jdbc.service.principal.client.secret", "<service-credential>")

81402-image.png

Step3: Set the enableServicePrincipalAuth option in the connection configuration.

Sample URL: "url", "jdbc:sqlserver://<workspacename>.sql.azuresynapse.net:1433;database=<databasename>;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30"

Load data from an Azure Synapse query.

Python Code:

 df = spark.read \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("tempDir", "abfss://<your-container-name>@<your-storage-account-name>.dfs.core.windows.net/<your-directory-name>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("dbTable", "<your-table-name>") \
   .load()

81422-image.png

Write data to the table in Azure Synapse.

Python Code:

 df.write \
   .format("com.databricks.spark.sqldw") \
   .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
   .option("forwardSparkAzureStorageCredentials", "true") \
   .option("dbTable", "<your-table-name>") \
   .option("tempDir", "abfss://<your-container-name>@<your-storage-account-name>.dfs.core.windows.net/<your-directory-name>") \
   .save()

81397-image.png

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


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



image.png (160.8 KiB)
image.png (121.0 KiB)
image.png (140.4 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @sakuraime,
Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hello @sakuraime,
Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Take care & stay safe!

0 Votes 0 ·
Paula-3438 avatar image Paula-3438 PRADEEPCHEEKATLA-MSFT ·

Hello, is it necessary to set up a different app/SPN for Synapse?
Why would you and if you don't what's the issue with using the same app/SPN configured for the DLG3 storage acct?


Thank you!

0 Votes 0 ·
Show more comments

Hi
This data seems good to me. I have a doubt that how can we extract data from synapse tables using databricks.could you please explain this part separately.
Thank you.

0 Votes 0 ·

Hello @Revathi-9060,

Since this thread is too old, I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.

0 Votes 0 ·