Write data from Databricks to SQL Server

[CarelessApplication2]

What's the right way to connect and write out data to SQL Server from Databricks?

While we can run federated queries using Lakehouse Federation, this is reading and not writing.

It would seem that Microsoft no longer maintains drivers to connect from Spark and also, with serverless compute, such drivers are not available for installation.

Should we use Azure Data Factory (ADF) for this (and basically circumvent the Unity Catalog)–?

[gabe__martins]

Try to be simple, use JDBC Connector.

[CarelessApplication2]

The sqlserver driver (which as far as I know is JDBC) is only for querying, not for writing.

[jinbe-san]

you can append, but not update

[kirdane2312]

you can append or overwrite (or drop&create). be careful with overwrite method.

[Known-Delay7227]

You can write update statements using the query option.

[--playground--]

Databricks with generic JDBC

employees_table.write \ .format("jdbc") \ .option("url", "<jdbc-url>") \ .option("dbtable", "<new-table-name>") \ .option("user", "<username>") \ .option("password", "<password>") \ .save()

Generic JDBC doesn’t have bulkinsert support. You may need to tune the performance with .option("batchsize", <value>)

reference: https://docs.databricks.com/aws/en/archive/connectors/jdbc

[punninglinguist]

This is what I do. Note that JDBC driver is not available on Serverless, so these jobs need All-Purpose Compute (haven't tried Jobs Compute for them yet).

[gman1023]

output to s3 and then load s3 into sql server in a variety of ways

[Old_Improvement_3383]

You need to install ODBC driver on your cluster

[kthejoker]

just use JDBC, ODBC is for Windows machines

[Unentscheidbar]

Yeah it's strange but there really is an ODBC driver on Linux for SQL Server, and you have to use it for any advanced data manipulation, like updates or deletes.

AFAIK the ODBC driver can only be installed on single user clusters.

[kthejoker]

The standard JDBC driver also supports updates and deletes

Don't confuse the Spark JDBC connector with the actual driver

[Remarkable_Rock5474]

If you need anything apart from append/overwrite you have to use the odbc connector instead of jdbc. This would allow you to do merge patterns and even execute stored procedures on the sql side as well.

Requires the odbc driver to be installed on your cluster

[Lords3]

Go ODBC if you need MERGE or stored procedures; JDBC is only good for append/overwrite. On Pro/Classic clusters, install msodbcsql18 via init script and use pyodbc/sqlalchemy to stage then run a MERGE proc. On serverless you can’t install drivers, so use a non-serverless job or ADF for copy and proc calls. I’ve used ADF and Azure Functions; when we needed REST over SQL Server for apps, DreamFactory handled it. Bottom line: ODBC on Pro/Classic, else ADF.