topic How can I connect to an Azure SQL db from a Databricks notebook? in Data Engineering

How can I connect to an Azure SQL db from a Databricks notebook?

yopbibo — Tue, 07 Jun 2022 15:33:45 GMT

I know how to do it with spark, and read/write tables (like https://docs.microsoft.com/en-gb/azure/databricks/data/data-sources/sql-databases#python-example )

But this time, I need to only update a field of a specific row in a table. I do not think I can do that with spark jdbc (mode for append or overwrite)

Then, I tried to use jaydebeapi and pymssql, but no success.

jaydebeapi would: Class com.microsoft.sqlserver.jdbc.SQLServerDriver is not found

Also tried to use pyodbc, but failed to install mssql odbc driver.

what is the best practice for that usecase?

Re: How can I connect to an Azure SQL db from a Databricks notebook?

yopbibo — Thu, 09 Jun 2022 15:26:08 GMT

thanks for the link.

I am maybe wrong, but they describe how to connect with spark. They do not provide a connection engine that we could use directly (like with pyodbc) or an engine that we could use in pandas, for example.

Re: How can I connect to an Azure SQL db from a Databricks notebook?

yopbibo — Fri, 10 Jun 2022 14:36:26 GMT

thanks!

but here again, it goes through the spark interface.

Therefore, comes with spark limits. For example, when write, we can "overwrite" or "append".

What if we want to, only, update an existing row?

Could load all the table, update it in spark, and write it back. But if the table is large, and we need to do that in a very short time?

Re: How can I connect to an Azure SQL db from a Databricks notebook?

yopbibo — Wed, 22 Jun 2022 15:28:52 GMT

sure!

pip install jaydebeapi pymssql
 
import jaydebeapi
import pymssql
 
pymssql.connect(server='xxxxxxxxxxxxxxxxx.database.windows.net',
                       user='sqladmin@xxxxxxxxxxxxxxxxx',
                       password='1234',
                       database="yyyyyyyyyyyyyyyyy",
                       port='1433',
                      )
 
pymssql.connect(server='xxxxxxxxxxxxxxxxx.database.windows.net',
                       user='sqladmin',
                       password='1234',
                       database="yyyyyyyyyyyyyyyyy",
                       port='1433',
                      )
 
jaydebeapi.connect('com.microsoft.sqlserver.jdbc.SQLServerDriver',
                   'jdbc:sqlserver://xxxxxxxxxxxxxxxxx.database.windows.net:1433;database=yyyyyyyyyyyyyyyyy',
                   ['sqladmin', '1234'])

bellow, outputs:

OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (xxxxxxxxxxx.database.windows.net)\nNet-Lib error during Connection refused (111)\nDB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (xxxxxxxxxxxxx.database.windows.net)\nNet-Lib error during Connection refused (111)\n')

same for the second call.

and last, for jaydebeapi

TypeError: Class com.microsoft.sqlserver.jdbc.SQLServerDriver is not found

Re: How can I connect to an Azure SQL db from a Databricks notebook?

Abhiram_B — Fri, 23 Aug 2024 11:05:25 GMT

Hi @yopbibo - Did you find the answer that you were looking for? i.e. update individual rows in db tables?

Re: How can I connect to an Azure SQL db from a Databricks notebook?

raopheefah — Mon, 21 Apr 2025 12:15:57 GMT

Look at your compute configuration. Looks like this works perfectly on Dedicated (formerly: single user) or No isolation clusters, but not on Standard (formerly: Shared) ones.

Maybe you need a disposable one-time job cluster with these settings.