PySpark: Why do I get 'getLong not implemented for class oracle.jdbc.driver.T4CRowidAccessor' when creating a table in Oracle via JDBC?

user_7822853 0
Viewed 607

I am new to Pyspark and it's been a very long time since I saw anything Java so be gentle. I see similar questions on SO but they seem to all be in pure Java instead of Pyspark. I am attempting to write a Spark DataFrame to an Oracle table via JDBC. I am able to successfully connect and query the database but when I go to create a new table like this:

df.write.jdbc('jdbc:oracle:thin:@host:port/service', create_table,
              mode='overwrite',
              properties={'user': 'user', 'password': 'password']})

I get the error message java.sql.SQLException: Invalid column type: getLong not implemented for class oracle.jdbc.driver.T4CRowidAccessor

I suspect this has something to do with column ROW_ID that is df.dtypes bigint. The ROW_IDs look something like the table below, which doesn't seem to agree with the infered datatype.

ROW_ID
AABBVMAGRAAAJfsAAA
AABBVMAGRAAAJftAAA
AABBVMAGRAAAJfyAAB
AABBVMAGRAAAJfvAAB
AABBVMAGRAAAJfwAAB
AABBVMAGRAAAJf3AAI

EDIT:

I tried casting the datatype from bigint to string using:

from pyspark.sql.functions import col
from pyspark.sql.types import StringType
correct_dtypes = df.withColumn('ROW_ID', col('ROW_ID').cast(StringType()))
correct_dtypes.write.jdbc('jdbc:oracle:thin:@host:port/service', create_table,
                          mode='overwrite',
                          properties={'user': 'user', 'password': 'password'})

But I am still getting the same error.

pyspark apache-spark oracle java
1 Answers

One possible solution could be using createTableColumnTypes option during saving and cast the troublemaking bigint column to varchar2 on oracle dbs side:

(correct_dtypes.write.
.option("createTableColumnTypes", "ROW_ID VARCHAR2(18)")
.jdbc('jdbc:oracle:thin:@host:port/service',
                          create_table, mode='overwrite',
                          properties={'user': 'user',
                          'password': 'password'}))
user_119664210
Related