Unable to read and write Azure Sql database. Getting exception java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

[bmorshed]

I have written a simple .net 8.0 apache spark code to connect to Azure sql database to read and write to the table but i am getting exceptions. I searched in google and followed some instruction but does not work. I debugged the code and got exception.

I read some info from different sites.
https://github.com/microsoft/sql-spark-connector/blob/master/README.md
https://stackoverflow.com/questions/76321485/py4jjavaerror-java-lang-classnotfoundexception-com-microsoft-sqlserver-jdbc-sp

sample code.

string url = "jdbc:sqlserver://testsqldb.database.windows.net:1433;database=testdb";
var tableName = "dbo.t_person";
var userName = "";
var password = "";
var driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //"com.microsoft.sqlserver.jdbc.spark";

var jarMssql = @"C:\BigData\spark\spark-3.2.1-bin-hadoop2.7\jars\spark-mssql-connector_2.12-1.2.0.jar";

var spark = SparkSession
.Builder()
.Master("local[*]")
.AppName("ReadCsvWriteMSSQL")
.Config("spark.driver.extraClassPath", jarMssql)
.GetOrCreate();

var df = spark.Read().Csv(filePath); // This is working perfectly
df.Show(); //working perfectly

//Error To Read
var dfTbl = spark.Read()
.Format("jdbc")
.Option("driver", driver)
.Option("url", url)
.Option("dbtable", tableName)
.Option("user", userName)
.Option("password", password)
.Load();

dfTbl.Show();

//Error To write data.
df.Write()
.Mode(SaveMode.Overwrite)
.Jdbc(url,"dbo.t_person",
new Dictionary<string, string>
{
{ "user", userName },
{ "password", password },
{ "driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver" }
});

// Stop the Spark session
spark.Stop();

code environment and setup

1. .Net 8.0 and Visual studion 2022
2. microsoft-spark-3-2_2.12-2.1.1.jar
3. spark version : spark-3.2.1-bin-hadoop2.7
4. spark-mssql-connector_2.12-1.2.0.jar -- copied from Maven repo and pasted to the spark->Jars folder
   5.Created Azure Sql database. -- I can connect database through SSMS and do query.

Exception details

System.Exception
HResult=0x80131500
Message=JVM method execution failed: Nonstatic method 'load' failed for class '62' when called with no arguments
Source=Microsoft.Spark
StackTrace:
at Microsoft.Spark.Interop.Ipc.JvmBridge.CallJavaMethod(Boolean isStatic, Object classNameOrJvmObjectReference, String methodName, Object[] args)
at Microsoft.Spark.Interop.Ipc.JvmBridge.CallNonStaticJavaMethod(JvmObjectReference jvmObject, String methodName, Object[] args)
at Microsoft.Spark.Sql.DataFrameReader.Load()
at HelloSpark.Program.readCSVFileAndWriteToAzurSql(String filePath) in C:\Azure\HelloSpark\HelloSpark\Program.cs:line 113
at HelloSpark.Program.Main(String[] args) in C:\Azure\HelloSpark\HelloSpark\Program.cs:line 15

Inner Exception 1:
JvmException: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:476)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:593)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:526)
at org.apache.spark.sql.execution.datasources.jdbc.DriverRegistry$.register(DriverRegistry.scala:46)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1(JDBCOptions.scala:101)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1$adapted(JDBCOptions.scala:101)
at scala.Option.foreach(Option.scala:407)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.(JDBCOptions.scala:101)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.(JDBCOptions.scala:39)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:33)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:350)
at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:274)
at org.apache.spark.sql.DataFrameReader.$anonfun$load$3(DataFrameReader.scala:245)
at scala.Option.getOrElse(Option.scala:189)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:245)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:174)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.spark.api.dotnet.DotnetBackendHandler.handleMethodCall(DotnetBackendHandler.scala:165)
at org.apache.spark.api.dotnet.DotnetBackendHandler.$anonfun$handleBackendRequest$2(DotnetBackendHandler.scala:105)
at org.apache.spark.api.dotnet.ThreadPool$$anon$1.run(ThreadPool.scala:34)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)

Any kind of hints and ideas would appreciate

[Sachin-Nand]

Hi,

Were you able to resolve the issue ?

Thanks

[wudanzy]

Looks like a missing dependency that SQLServerDriver is not found, can you try it in scala spark?