Connect Azure Synapse notebook to Azure SQL Database

Question

Connect Azure Synapse notebook to Azure SQL Database

KNP 31

I am not using SQL pools in Synapse instead I use only Azure SQL Database. My requirement is to read data from Azure SQL DB through Notebook do some transformations and save the output into Blob Storage.
The pipeline in synapse studio is working well and I am able to read data from Azure SQL DB through Integration datasets similar to datafactory. I am not able to read into my Notebook, I cant find any documentation arounds this online.

Is this supported in synapse?
Any help would be great.

PRADEEPCHEEKATLA 91,946 Reputation points

2021-09-27T10:12:10.913+00:00
Hello @KNP ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.

Answer accepted by question author

PRADEEPCHEEKATLA 91,946

Hello @KNP ,

Welcome to the Microsoft Q&A platform.

Here are steps to Connect Azure Synapse notebook to Azure SQL Database:

Step1: From Azure Portal get the Azure SQL Database JDBC connection string.

Step2: You can use the following Python Code to connect and read from Azure SQL Databases using JDBC using Python:

jdbcHostname = "chepra.database.windows.net"  
jdbcDatabase = "chepra"  
jdbcPort = "1433"  
username = "chepra"  
password = "XXXXXXXXXX"  
jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2}".format(jdbcHostname, jdbcPort, jdbcDatabase)  
connectionProperties = {  
  "user" : username,  
  "password" : password,  
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
}  
pushdown_query = "(Select * from citydata where ID > 0) CustomerID"  
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)  
display(df)

Step3: Writing data frame to Azure Storage Accounts.

Write to Azure Blob Storage use: wasbs://<Container>@<StorageName>.blob.core.windows.net/<Folder>/
Write to Azure Data Lake Gen2 use: abfss://<Container>@<StorageName>.dfs.core.windows.net/<Folder>/

I had written the data frame to Azure Data Lake Gen2:

Checkout the output written to ADLS Gen2 account:

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

KNP 31 Reputation points

2021-09-27T11:01:29.477+00:00

Thanks @PRADEEPCHEEKATLA @Thomas Boersma ,

Both of your answers helped me solve this issue..
Happy week....!

1 additional answer

Your answer

PRADEEPCHEEKATLA 91,946 Reputation points

2021-09-27T10:12:10.913+00:00

Hello @KNP ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
KNP 31 Reputation points

2021-09-27T11:01:29.477+00:00

Thanks @PRADEEPCHEEKATLA @Thomas Boersma ,

Both of your answers helped me solve this issue..
Happy week....!

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

3 deleted comments

Comments have been turned off. Learn more