spark importing data from oracle - java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

Question

While trying to read data from oracle database using spark on AWS EMR, I am getting this error message:

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver.

Can someone let me know if anyone faced this issue and how they resolved it?

pyspark --driver-class-path /home/hadoop/ojdbc7.jar --jars   /home/hadoop/ojdbc7.jar

from pyspark import SparkContext, HiveContext, SparkConf

from pyspark.sql import SQLContext

sqlContext = SQLContext(sc)

df = sqlContext.read.format("jdbc").options(url="jdbc:oracle:thin:user/pass@//10.200.100.142:1521/BMD", driver = "oracle.jdbc.driver.OracleDriver", 
dbtable="S_0COORDER_TEXT_D").load()

Answer 1

Although You haven't mentioned which version of spark you are using... you can try below....

import jars to both driver & executor. So, you need to edit conf/spark-defaults.conf adding both lines below.

spark.driver.extraClassPath /home/hadoop/ojdbc7.jar
spark.executor.extraClassPath /home/hadoop/ojdbc7.jar

or
you can try to pass while submitting job like below example :

--conf spark.driver.extraClassPath /home/hadoop/ojdbc7.jar
--conf spark.executor.extraClassPath /home/hadoop/ojdbc7.jar

Answer 2

add codes below to your_spark_home_path/conf/spark-defaults.conf,'/opt/modules/extraClass/' is dir where i put extra jars:

spark.driver.extraClassPath = /opt/modules/extraClass/jodbc7.jar
spark.executor.extraClassPath = /opt/modules/extraClass/jodbc7.jar

or you can simple add jodbc7.jar to your_spark_home_path/jars.