Connecting SQLserver jdbc driver to Dataproc cluster

Question

I am working on PySpark application on analyzing Aviation Data. The Database is a MS SQLServer DB. While connecting to the database on the server. I get an error of "No suitable driver". However when I run on local machine with CLI and add JDBC driver jar file to driver-class-path, it runs and connects with DB. But when I try to run on Dataproc cluster, it throws an error of "No suitable driver".

The code snippet is as follows:

from pyspark import SparkContext
from pyspark.sql import SparkSession
from pyspark.sql.dataframe import DataFrame
from pyspark.sql.functions import *

spark = SparkSession.builder
    .appName('Test')
    .getOrCreate()

df = spark.read.format("jdbc").options(
        url="jdbc:sqlserver:XYXYXY",
        database="data1",
        user="YYYY", password="XXXX",
        dbtable="db")
    .load()

The Error was:

Py4JJavaError: An error occurred while calling o209.load.
: java.sql.SQLException: No suitable driver
    at java.sql.DriverManager.getDriver(DriverManager.java:315)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:84)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:84)
    at scala.Option.getOrElse(Option.scala:121)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:83)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:34)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:34)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:307)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
    at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
    at py4j.Gateway.invoke(Gateway.java:280)
    at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
    at py4j.commands.CallCommand.execute(CallCommand.java:79)
    at py4j.GatewayConnection.run(GatewayConnection.java:214)
    at java.lang.Thread.run(Thread.java:748)

Is there other way to add JDBC jar files to the Dataproc cluster?

If you can't show the code, maybe you can simplify it, change the variable names etc, so it has a similar behaviour but nothing secret any more. — alpereira7, Nov 26 '18 at 10:04
You can try adding the the jdbc jar under /usr/lib/spark/jars of the master node. — Dagang, Nov 27 '18 at 20:40

score 0 · Answer 1 · answered Dec 22 '18 at 19:16

Here is a very similar question and answer to it that shows how to add JDBC driver to Spark Driver classpath using gcloud command:

$ gcloud dataproc jobs submit spark ... \
    --jars=gs://<BUCKET>/<DIRECTORIES>/<JAR_NAME> \
    --properties=spark.driver.extraClassPath=<JAR_NAME>

Connecting SQLserver jdbc driver to Dataproc cluster

1 Answers1