Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Error] [JvmBridge] java.sql.SQLException: No suitable driver #271

Closed
schinchanikar-rms opened this issue Sep 26, 2019 · 20 comments
Closed

[Error] [JvmBridge] java.sql.SQLException: No suitable driver #271

schinchanikar-rms opened this issue Sep 26, 2019 · 20 comments

Comments

@schinchanikar-rms
Copy link

Problem encountered on https://dotnet.microsoft.com/learn/data/spark-tutorial/install-spark
Operating System: windows

I am trying to read a dataframe from SQL database through spark session using spark.Read.Format("jdbc").
I installed the sql jdbc driver as specified in https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-2017. I still get error [Error] [JvmBridge] java.sql.SQLException: No suitable driver
@rapoth
Copy link
Contributor

rapoth commented Sep 27, 2019 •
edited

@schinchanikar-rms: Thank you for reporting this problem! Can you provide the full logs, the precise spark-submit command you are using and your complete C# app code please?

And btw, you have to supply the appropriate driver through your spark-submit. For instance,

spark-submit.cmd --jars path\to\sql-server\mssql-jdbc-7.4.1.jre8.jar .... rest of the command params ...

Note that this is just an example. You'd have to replace the JAR version with the correct version you installed.

@rapoth rapoth mentioned this issue Sep 27, 2019
Closed
@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Sep 27, 2019 via email

@rapoth
Copy link
Contributor

rapoth commented Sep 27, 2019

Thanks! Can you also provide the spark-submit command you are using to submit your job?

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Sep 27, 2019 via email

@rapoth
Copy link
Contributor

rapoth commented Sep 28, 2019

Can you supply the appropriate driver JAR through your spark-submit. For instance,

spark-submit.cmd --jars path\to\sql-server\mssql-jdbc-7.4.1.jre8.jar .... rest of the command params ...

Note that this is just an example. You'd have to replace the JAR version with the correct version you installed.

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Sep 30, 2019 via email

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Sep 30, 2019 via email

@GoEddie
Copy link
Contributor

GoEddie commented Sep 30, 2019

Hi,

The error you are getting "java.sql.SQLException: No suitable driver" is a jvm error that means that the driver you are asking for cannot be loaded.

The first thing is to make sure you have the sql server driver, which I think you do and it is here "file:///C:/Users/YOURUSERNAME/mySparkApp/bin/debug/netcoreapp2.2/mssql-jdbc-7.4.1.jre8.jar".

If the jar isn't actually there you will need to download it.

The second thing is the microsft jdbc driver is in the package "com.microsoft.sqlserver.jdbc" and is called "SQLServerDriver" so when you pass in the driver option to spark it needs to be like:

.Option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")

but it looks like you are using:

.Option("driver", "com.Microsoft.SqlServerDriver")

Because there is no com.Microsoft package in that jar, it won't be found and you will get this error.

I notice that in the connection string you are trying to use windows authentication, there is one more thing you will need to do. In the folder with the Microsoft jdbc jar there is a folder called "chs\auth\x64" which contains a dll called "sqljdbc_auth.dll", on windows you will need to add the folder that contains sqljdbc_auth.dll to your path set PATH=c:\folder\to\sqljdbc_auth.dll;%PATH% before starting spark-shell or spark-submit - if you are running it on a cluster i'm not sure if you can get windows authentication to work - i've always used sql auth form spark.

If you have the driver jar referenced using --jars pathToJar.jar and you use the right name, and you have sqljdbc_auth.dll in a folder on your windows path statement you will be able to connect to SQL Server from spark on your windows machine.

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Sep 30, 2019 via email

@GoEddie
Copy link
Contributor

GoEddie commented Oct 1, 2019

I wrote this up, in case it helps anyone else (I think it will be quite a common issue for people using spark dotnet!)

https://the.agilesql.club/2019/10/how-to-connect-spark-to-ms-sql-server-without-error-jvmbridge-java.sql.sqlexception-no-suitable-driver/

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Oct 1, 2019 via email

@MikeRys
Copy link
Contributor

MikeRys commented Oct 1, 2019 via email

@GoEddie
Copy link
Contributor

GoEddie commented Oct 1, 2019

You are right to check that the sql server is listening on tcp and port 1433 and to add a firewall rule to allow it.

You will definitely need connectivity from your databricks cluster through to your sql server - if you don't have that you won't be able to connect.

One thing though is that SocketFinder.findSocket uses InetAddress.getAllByName(hostName); to lookup the ip address to connect to and if it can't resolve the name then it throws an UnknownHostException which is an IOException and can also cause this error you got (it is a generic cannot connect error message):

https://github.com/microsoft/mssql-jdbc/blob/dev/src/main/java/com/microsoft/sqlserver/jdbc/IOBuffer.java#L2323

I would say that it is unlikely "CAWL113418" is going to resolvable to an ip address from a databricks cluster so try using the fqdn or the ip address of the SQL Server.

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Oct 1, 2019 via email

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Oct 1, 2019 via email

@GoEddie
Copy link
Contributor

GoEddie commented Oct 1, 2019

Connecting databricks (azure) to on-prem:

https://docs.azuredatabricks.net/administration-guide/cloud-configurations/azure/on-prem-network.html

Deploying databricks to an azure virtual network:

https://docs.azuredatabricks.net/administration-guide/cloud-configurations/azure/vnet-inject.html

Have you got your databricks cluster connected to any other SQL Servers? It sounds like you have some networking work to do :)

@GoEddie
Copy link
Contributor

GoEddie commented Oct 2, 2019

Just an additional thought really, if you are struggling to connect up your various networks you will likely find it easier to have a system where you push your data from your network with sql on to blob or adls and use databricks to read from that (adf can help).

Having adf read your batches, move them into adls and then trigger a databricks job is a pretty typical use of adf / databricks. I've used this when connecting to SQL Servers which we weren't allowed to open up externally (even to internal azure subscriptions)

@schinchanikar-rms
Copy link
Author

schinchanikar-rms commented Oct 7, 2019 via email

@GoEddie
Copy link
Contributor

GoEddie commented Oct 9, 2019

https://github.com/dotnet/spark/blob/master/examples/Microsoft.Spark.CSharp.Examples/Sql/VectorUdfs.cs

It is helpful though if you can close an issue if it is complete (the original question was about finding the sql server driver) otherwise it is hard for other people to find answers to the questions here.

@Niharikadutta
Copy link
Collaborator

Hi, we are going to close this issue as it has been inactive for a while and the original issue has been resolved. Please feel free to re-open it if the issue persists and/or there are any new updates. Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@GoEddie @Niharikadutta @rapoth @MikeRys @schinchanikar-rms