How do I connect Spark to JDBC driver in Zeppelin?

user_7053813 0
Viewed 2929

I am trying to pull in data from a SQL server to a Hive table using Spark in a Zeppelin notebook.

I am trying to run the following code:

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

spark = SparkSession.builder \
.appName('sample') \
.getOrCreate()

#set url, table, etc.

df = spark.read.format('jdbc') \
.option('url', url) \
.option('driver', 'com.microsoft.sqlserver.jdbc.SQLServerDriver') \
.option('dbtable', table) \
.option('user', user) \
.option('password', password) \
.load()

However, I keep getting the exception:

...
Py4JJavaError: An error occurred while calling o81.load.
: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
...

I have been trying to figure this out all day and I believe something is wrong with how I am trying to set up the driver. I have a driver under /tmp/sqljdbc42.jar on the instance. Can you please explain how I can let Spark know where this driver is? I have tried many different ways both through the shell and through the interpreter editor.

Thanks!

EDIT

I also should note that I loaded the jar to my instance throug Zeppelin's shell (%sh) using

curl -o /tmp/sqljdbc42.jar http://central.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/6.4.0.jre8/mssql-jdbc-6.4.0.jre8.jar
pyspark --driver-class-path /tmp/sqljdbc42.jar --jars /tmp/sqljdbc42.jar
apache-zeppelin amazon-emr pyspark apache-spark
3 Answers

Here is how I fixed this:

  1. scp driver jar onto the cluster driver node

  2. Go to Zeppelin interpreter and scroll to the Spark section then click edit.

  3. Write the complete path to the jar under artifacts e.g. /home/Hadoop/mssql-jdbc.jar and nothing else.

  4. Click save.

Then you should be good!

user_70538130

You can add it through Web UI in Interpreter settings as follow:

  • Click Interpreter in menu

  • Click 'edit' button in the Spark interpreter

  • Add the path for the jar in the artifact field

  • Then just save and restart interpreter.

user_80487500

Similar to Tomas, you can add the driver (or any library) using maven in the interpreter:

  • Click Interpreter in menu
  • Click 'edit' button in the Spark interpreter
  • Add the path for the jar in the artifact field
  • Add the groupId:artifactId:version

For example, in your case, you can use com.microsoft.sqlserver:mssql-jdbc:jar:8.4.1.jre8 in artifact field.

When you restart the interpreter, it will download and add the dependency for you.

user_18165360
Related