Fabric notebook - Oracle connection error: java.lang.ClassNotFoundException

[Rodrivieiras]

I am trying to connect to an Oracle database, but the oracle driver is not being found by Pyspark.

First I uploaded the oracle jdbc driver v.11 in the builtin folder:

Code:

!java -version

 Output:

openjdk version "11.0.22" 2024-01-16 LTS

OpenJDK Runtime Environment Microsoft-8909545 (build 11.0.22+7-LTS)

OpenJDK 64-Bit Server VM Microsoft-8909545 (build 11.0.22+7-LTS, mixed mode, sharing)

from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("Iniciando com Spark") \
    .config("spark.driver.extraClassPath", "/builtin/ojdbc11.jar") \
    .getOrCreate()

properties = {
    'driver': 'oracle.jdbc.driver.OracleDriver',
    'url': "jdbc:oracle:thin://hostname:1521/db_name",
    'user': "username",
    'password': "password",
    'dbtable': '(select id from dt.users) as t'
}

df1 = spark.read.format('jdbc') \
    .option('driver', properties['driver']) \
    .option('url', properties['url']) \
    .option('user', properties['user']) \
    .option('password', properties['password']) \
    .option('dbtable', properties['dbtable']) \
     .load()

Output:

File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/java_gateway.py:1322, in JavaMember.__call__(self, *args)

1316 command = proto.CALL_COMMAND_NAME +\

1317 self.command_header +\

1318 args_command +\

1319 proto.END_COMMAND_PART

1321 answer = self.gateway_client.send_command(command) ->

1322 return_value = get_return_value(

1323 answer, self.gateway_client, self.target_id, self.name)

1325 for temp_arg in temp_args:

1326 if hasattr(temp_arg, "_detach"

File /opt/spark/python/lib/pyspark.zip/pyspark/errors/exceptions/captured.py:169, in capture_sql_exception.<locals>.deco(*a, **kw) 167 def deco(*a: Any, **kw: Any) -> Any:

168 try: -->

169 return f(*a, **kw)

170 except Py4JJavaError as e:

171 converted = convert_exception(e.java_exception)

File ~/cluster-env/trident_env/lib/python3.10/site-packages/py4j/protocol.py:326, in get_return_value(answer, gateway_client, target_id, name)

324 value = OUTPUT_CONVERTER[type](answer[2:], gateway_client) 325 if answer[1] == REFERENCE_TYPE: -->

326 raise Py4JJavaError(

327 "An error occurred while calling {0}{1}{2}.\n".

328 format(target_id, ".", name), value) 329 else:

330 raise Py4JError( 331 "An error occurred while calling {0}{1}{2}. Trace:\n{3}\n".

332 format(target_id, ".", name, value))

Py4JJavaError: An error occurred while calling o4395.load.

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

at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:476)

at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:594) at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:527) at org.apache.spark.sql.execution.datasources.jdbc.DriverRegistry$.register(DriverRegistry.scala:46)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1(JDBCOptions.scala:103)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.$anonfun$driverClass$1$adapted(JDBCOptions.scala:103)

at scala.Option.foreach(Option.scala:407)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:103)

at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:41)

at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:34)

at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:346)

at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:236)

at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:219) at scala.Option.getOrElse(Option.scala:189) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:219)

at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:174)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)

at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374) at py4j.Gateway.invoke(Gateway.java:282)

at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)

at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238)

at java.base/java.lang.Thread.run(Thread.java:829)

I have also tried to use this code below, but the error persists:

spark = SparkSession.builder \
    .appName("Iniciando com Spark") \
    .config("spark.jars", "/builtin/ojdbc11.jar") \
    .getOrCreate()

What is wrong?

Thanks!

[amolt]

Hi @Rodrivieiras , 

Hello,

You should take a look at this post: 

Solved: Re: Trying to connect to an Oracle database - Microsoft Fabric Community

[Rodrivieiras]

Thanks for sharing @amolt. I replied there.

[Anonymous]

Hello @Rodrivieiras ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Rodrivieiras]

Ok, I'll wait for your next reply.

[Anonymous]

Hello @Rodrivieiras ,

Apologies for the delay in reply from our side.

Please try to load the jar via the following magic: 

# Load jar directly to the Scala interpreter

%load_new_custom_jar {mssparkutils.nbResPath}/builtin/Animation1.jar

Starting a new SparkSession doesn't work here. You can also drag and drop a jar file from notebook filesystem. Here's the doc: How to use notebooks - Microsoft Fabric | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.

[Rodrivieiras]

Thanks for the suggestions.

I'll try them out and let you know if they work.

[Rodrivieiras]

I tested the code using the suggested syntax:

%load_new_custom_jar {mssparkutils.nbResPath}/builtin/ojdbc11.jar

However the error persists.

Py4JJavaError: An error occurred while calling o4395.load.

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

I suppose this could be related to JDBC, JDK and Oracle interop versions.

I'm trying to access an Oracle 11.2.0.4 database, but the JDK defined in Fabric is JDK 11:

openjdk version "11.0.22" 2024-01-16 LTS

OpenJDK Runtime Environment Microsoft-8909545 (build 11.0.22+7-LTS)

OpenJDK 64-Bit Server VM Microsoft-8909545 (build 11.0.22+7-LTS, mixed mode, sharing)

It seems that to access Oracle DB 11.2, the correct JDK to use is not JDK11, but JDK 6, 7 or 8, according to the Oracle website:

Is there a way to downgrade Fabric JDK11 to JDK8, for example?

Thanks!

[Anonymous]

Hello @Rodrivieiras ,

At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Rodrivieiras]

Hi, all right.

[Anonymous]

Hi @Rodrivieiras ,

We got an information from internal team that we cannot downgrade the JDK version.

[Anonymous]

Hello @Rodrivieiras ,

We haven’t heard from you on the last response and was just checking back to see if we answered your query.
Otherwise, will respond back with the more details and we will try to help.

[Rodrivieiras]

Hi @Anonymous, could you ask the internal team if there is a solution in Fabric Spark to connect to an Oracle 11 database using JDBC driver?

Or do I need to update the Oracle database to a later version compatible with JDK11, which is the default JDK version installed on Fabric?

[Anonymous]

Hi @Rodrivieiras ,

At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Rodrivieiras]

Ok!

[Anonymous]

Hi @Rodrivieiras ,

Apologies for the issue you have been facing.

The best course of action is to open a support ticket.

Please go ahead and raise a support ticket to reach our support team: Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.

[Rodrivieiras]

Hi @Anonymous ,

I opened a ticket and the team is already looking at this issue.

Thanks!

[Anonymous]

Hi @Rodrivieiras ,

We haven’t heard from you on the last response and was just checking back to see if we answered your query.
Otherwise, will respond back with the more details and we will try to help.