topic Re: Fabric notebook - Oracle connection error: java.lang.ClassNotFoundException in Fabric platform

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

Rodrivieiras — Sun, 03 Mar 2024 06:44:45 GMT

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!

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

Anonymous — Mon, 04 Mar 2024 11:52:48 GMT

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.

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

Rodrivieiras — Mon, 04 Mar 2024 14:07:02 GMT

Ok, I'll wait for your next reply.

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

Anonymous — Thu, 07 Mar 2024 11:04:57 GMT

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.

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

Rodrivieiras — Thu, 07 Mar 2024 23:52:19 GMT

Thanks for the suggestions.

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

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

Rodrivieiras — Fri, 08 Mar 2024 20:56:08 GMT

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!

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

Anonymous — Tue, 12 Mar 2024 09:53:32 GMT

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.

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

Rodrivieiras — Wed, 13 Mar 2024 06:52:28 GMT

Hi, all right.

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

Anonymous — Thu, 14 Mar 2024 08:16:28 GMT

Hi Rodrivieiras ,

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

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

Anonymous — Thu, 14 Mar 2024 08:16:00 GMT

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.

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

Anonymous — Fri, 15 Mar 2024 06:04:44 GMT

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.

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

Rodrivieiras — Fri, 15 Mar 2024 23:13:45 GMT

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?

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

Anonymous — Mon, 18 Mar 2024 07:05:06 GMT

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.

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

Rodrivieiras — Tue, 19 Mar 2024 04:15:18 GMT

Ok!

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

Anonymous — Mon, 25 Mar 2024 10:43:24 GMT

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.

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

Rodrivieiras — Mon, 01 Apr 2024 05:57:35 GMT

Hi Anonymous ,

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

Thanks!

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

amolt — Mon, 12 Aug 2024 08:41:59 GMT

Hi Rodrivieiras ,

Hello,

You should take a look at this post:

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

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

Rodrivieiras — Thu, 15 Aug 2024 00:57:55 GMT

Thanks for sharing amolt. I replied there.