Synapse can't find data source com.microsoft.sqlserver.jdbc.spark on apache spark pool 3.3

Question

I am trying to pull data from Azure SQL Database using spark notebook on Azure synapse spark pool. Apache Spark version 3.3

df = spark.read.format("com.microsoft.sqlserver.jdbc.spark") \
                            .option("url", Url) \
                            .option("query", query) \
                            .option("user", User) \
                            .option("password", Password) \
                            .load()

Here is the error:

Py4JJavaError: An error occurred while calling o3897.load. : java.lang.ClassNotFoundException: Failed to find data source: com.microsoft.sqlserver.jdbc.spark. Please find packages at https://spark.apache.org/third-party-projects.html

Above command works perfectly fine in Apache Spark 3.1 and 3.2.

Answer 1

@heta desai

Welcome to the MS Q&A platform.

This issue has been discussed here

Root cause:
This connector jar is still in beta, and there is no official stable version yet. So due to this reason, you are seeing the error "spark-mssql-connector jar missing from spark 3.3"

Resolution:

The Resolution is to add this jar https://repo1.maven.org/maven2/com/microsoft/azure/spark-mssql-connector_2.12/1.3.0-BETA/spark-mssql-connector_2.12-1.3.0-BETA.jar   to their spark pools using package management feature

https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-manage-pool-packages

https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-azure-portal-add-libraries

This issue should be resolved once you add the jar.

Please see this video tutorial for how to add the jar file to the Spark pool.

I hope this helps. Please let me know if you have any further questions.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.