How to install the package com.microsoft.sqlserver.jdbc.spark in Azure synapse analytics

Question

Greetings!!!

I am trying to connect to an Azure SQL Database from Azure Synapse notebook using the below code. I am not sure how to install the Apache spark SQL connector com.microsoft.sqlserver.jdbc.spark

Code:

servername = "<< server name >>"
dbname = "<< database name >>"
url = servername + ";" + "databaseName=" + dbname + ";"
dbtable = "<< table name >> "
user = "<< username >>" 
password = mssparkutils.credentials.getSecret('azure key vault name','secret name')
try:
  df.write \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .mode("overwrite") \
    .option("url", url) \
    .option("dbtable", dbtable) \
    .option("user", user) \
    .option("password", password) \
    .save()
except ValueError as error :
    print("MSSQL Connector write failed", error)

print("MSSQL Connector write(overwrite) succeeded  ")

And running into the below error.

Error:

Py4JJavaError: An error occurred while calling o289.save.
: java.sql.SQLException: No suitable driver
    at java.sql.DriverManager.getDriver(DriverManager.java:315)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$6.apply(JDBCOptions.scala:105)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$6.apply(JDBCOptions.scala:105)
    at scala.Option.getOrElse(Option.scala:121)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.

Answer 1

Hello @Raj D ,

Thanks for the question and using MS Q&A platform.

As per the repro, when I tried to read the data using the cmdlets available in the document, I had experienced the same issue - Py4JJavaError: An error occurred while calling o289.save: java.sql.SQLException: No suitable driver.

As per the conversation with PG:

You don’t need to add the connector Apache Spark connector Jar or any package com.microsoft.sqlserver.jdbc.spark to your Synapse Spark pool. The connector is there out of the box for Spark 2.4 and for Spark 3.1 it will be in production most likely in upcoming weeks.

For now you can use this method:

Read from Azure SQL Table

servername = "jdbc:sqlserver://<AzureSQLServerName>.database.windows.net:1433"  
dbname = "<DBName>"  
url = servername + ";" + "databaseName=" + dbname + ";"  
user = "<UserName>"   
password = "<Password>"   
dbtable = "<TableName>"  
#Read from SQL table using MS SQL Connector  
print("read data from SQL server table  ")  
jdbcDF = spark.read \  
        .format("com.microsoft.sqlserver.jdbc.spark") \  
        .option("url", url) \  
        .option("dbtable", dbtable) \  
        .option("user", user) \  
        .option("password", password).load()  
jdbcDF.show(5)  

Write to a new Azure SQL Table

#Write from Spark to SQL table using MSSQL Spark Connector  
print("Use MSSQL connector to write to master SQL instance ")  
  
servername = "jdbc:sqlserver://<AzureSQLServerName>.database.windows.net:1433"  
dbname = "<DBName>"  
url = servername + ";" + "databaseName=" + dbname + ";"  
  
dbtable = "<NewTableName>"  
user = "<UserName>"  
password = "<Password>" # Please specify password here  
  
#com.microsoft.sqlserver.jdbc.spark  
  
try:  
  jdbcDF.write \  
    .format("com.microsoft.sqlserver.jdbc.spark") \  
    .mode("overwrite") \  
    .option("url", url) \  
    .option("dbtable", dbtable) \  
    .option("user", user) \  
    .option("password", password) \  
    .save()  
except ValueError as error :  
    print("MSSQL Connector write failed", error)  
  
print("MSSQL Connector write(overwrite) succeeded  ")  

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

Answer 2

please check the following link to the same question which was asked before :-)

https://learn.microsoft.com/en-us/answers/questions/564284/connect-azure-synapse-notebook-to-azure-sql-databa.html