Connecting Oracle DB from PySpark 3.1.2 - fails with Py4JJavaError

Question

I am trying out PySpark3.2.1 with Oracle 11G. It fails with the following error:

Py4JJavaError: An error occurred while calling o44.load.
: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)

My code:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("PySpark_Oracle_Connection").getOrCreate()

driver = 'oracle.jdbc.OracleDriver'
url = 'jdbc:oracle:thin:@hostname:port/dbTEST'
user = 'myname'
password = 'mypswd'
table = 'mytable'

SPARK_CLASS_PATH = "C:\Oracle_Client\jdbc\lib\ojdbc8.jar"

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

I'd appreciate a quick help, please. I have gone through previous posts, but still doesn't work.

while submitting spark app through spark-submit are you passing these parameters - --driver-class-path C:\Oracle_Client\jdbc\lib\ojdbc8.jar --jars C:\Oracle_Client\jdbc\lib\ojdbc8.jar — Vikramsinh Shinde
– Vikramsinh Shinde, Commented Aug 19, 2021 at 9:14
I am using Spyder to execute. I am not a developer. I am statistics guy, just trying PySpark as a layman, by learning from google. How do I pass this in python code in Spyder pls? — Ranjit Mishra
– Ranjit Mishra, Commented Aug 20, 2021 at 6:11
Not sure about Spyder, but if you have spark installed and configured properly, then you will be able to run spark applications using spark-submit command. Have a look at this, there is an info for the python app as well - spark.apache.org/docs/latest/submitting-applications.html — Vikramsinh Shinde
– Vikramsinh Shinde, Commented Aug 20, 2021 at 6:54
these as well - tutorialkart.com/apache-spark/… AND sparkbyexamples.com/spark/spark-submit-command — Vikramsinh Shinde
– Vikramsinh Shinde, Commented Aug 20, 2021 at 6:55
All these links are talking about how to use Python on Spark. I need how to run PySpark package with jodbs.jar in windows10. I have not installed Spark spark separately, but my Spark is part of PySpark package in Anaconda environment - ~Anaconda3\Lib\site-packages\pyspark – — Ranjit Mishra
– Ranjit Mishra, Commented Aug 22, 2021 at 4:52

Rishabh Sairawat · Accepted Answer · 2021-08-19 09:49:32Z

1

Py4JJavaError: An error occurred while calling o44.load.
: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

Error itself suggests the problem root cause i.e. oracle.jdbc.OracleDriver class was not found when spark tried to read from your oracle db table.

So now, you just have to tell the spark to find your jar. This can be done by changing spark-defaults.conf file which should be present in the $SPARK_HOME/conf/ directory. If not present, then add it yourself with the following config:

spark.driver.extraClassPath C:\Oracle_Client\jdbc\lib\ojdbc8.jar
spark.executor.extraClassPath C:\Oracle_Client\jdbc\lib\ojdbc8.jar

Or just use the --jars option while submitting the job.

answered Aug 19, 2021 at 9:49

Rishabh Sairawat

4606 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ranjit Mishra Over a year ago

I am sorry for unable to find SPARK_HOME in my windows10. Tried various things from google search, nothing seems to work for me. I dont know anything in Java or jar. I am a statistics guy trying out PySpark. Pls let me know how do I find/set SPARK_HOME in Windows10 machine.

Rishabh Sairawat Over a year ago

In Windows command prompt, try echo %SPARK_HOME%

Ranjit Mishra Over a year ago

When I submit "echo %SPARK_HOME%" on CMD, it returns "%SPARK_HOME%" only, nothing else. I tried to set %SPARK_HOME% in windows configuration, then PySpark itself fails. I have got a conf file at "~Anaconda3\Lib\site-packages\pyspark\conf.py" , but there is no way I can specify jdbc jars in this. Thanks

rams Over a year ago

@Rishab - Even after providing the ojdbc8.jar path in spark.driver.extraClassPath and spark.executor.extraClassPath in the config while creating SparkSession I still get the exception : java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver. I tried with both oracle.jdbc.driver.OracleDriver and oracle.jdbc.OracleDriver

buddemat · Accepted Answer · 2022-08-09 12:52:03Z

0

driver = 'oracle.jdbc.OracleDriver'

is the old version with ojdbc8.jar you must use

driver= 'oracle.jdbc.driver.OracleDriver'

edited Aug 9, 2022 at 12:52

buddemat

5,32816 gold badges37 silver badges61 bronze badges

answered Aug 6, 2022 at 11:07

MHancer

1

Collectives™ on Stack Overflow

Connecting Oracle DB from PySpark 3.1.2 - fails with Py4JJavaError

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related