java.sql.SQLException: Unrecognized SQL type -102 while connecting to Oracle Database from Apache Spark

Question

I'm trying to load a remote Oracle Database table on to Apache Spark shell.

This is how I started the spark-shell.

./spark-shell --driver-class-path ../jars/ojdbc6.jar --jars ../jars/ojdbc6.jar --master local

And I get a Scala prompt, where I try to load an Oracle database table like below. (I use a custom JDBC URL)

val jdbcDF = spark.read.format("jdbc").option("url", "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=WHATEVER)(HOST=myDummyHost.com)(PORT=xxxx)))(CONNECT_DATA=(SERVICE_NAME=dummy)(INSTANCE_NAME=dummyKaMummy)(UR=A)(SERVER=DEDICATED)))").option("dbtable", "THE_DUMMY_TABLE").option("user", "DUMMY_USER").option("password", "DUMMYPASSWORD").option("driver", "oracle.jdbc.driver.OracleDriver").load()

(Replaced employer data with dummy variables)

And then I get this error.

java.sql.SQLException: Unrecognized SQL type -102
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.org$apache$spark$sql$execution$datasources$jdbc$JdbcUtils$$getCatalystType(JdbcUtils.scala:246)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$8.apply(JdbcUtils.scala:316)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$8.apply(JdbcUtils.scala:316)
  at scala.Option.getOrElse(Option.scala:121)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.getSchema(JdbcUtils.scala:315)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:63)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:210)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:35)
  at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:318)
  at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:223)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:211)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:167)
  ... 49 elided

I tried to see if there is an issue with the quotes, but it's not that.

Can somebody save my life, please?

Consider trying a newer Oracle JDBC driver. ojdbc6 means it was written for Java 6, so it is pretty old. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 16, 2019 at 14:38
@MarkRotteveel From this https://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html, ojdbc6 is for Oracle 11g and that's what we use. — Aryan Venkat
– Aryan Venkat, Commented Jun 16, 2019 at 15:17
Also from Oracle JDBC FAQ, under section What are the Oracle JDBC releases Vs JDK versions?, ojdbc6.jar is supported with JDK6, JDK7 and JDK8. https://www.oracle.com/technetwork/topics/jdbc-faq-090281.html — Aryan Venkat
– Aryan Venkat, Commented Jun 16, 2019 at 15:43
ojdbc14 is for Java 1.4 and is even older than ojdbc6. If it also gives an error with the newer drivers, this could mean that the problem is with a non-standard Oracle datatype that Spark doesn't support (type code -102 is not defined in the JDBC standard). — Mark Rotteveel
– Mark Rotteveel, Commented Jun 16, 2019 at 17:43
Try creating a table with half the columns of the problematic tables, if the error doesn't occur, try creating one with the other half. Continue doing this until you have identified the column (or columns) that is the problem, then consider updating your question with a minimal reproducible example. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 16, 2019 at 18:36

quasipolynomial · Accepted Answer · 2019-07-03 13:21:12Z

1

The problem is an incompatible field in the database. If you cannot modify the database, but would still like to read it, the solution would be to ignore specific columns (in my case it's a field with type geography). With the help of How to select specific columns through Spack JDBC?, here's a solution in pyspark (scala solution would be similar):

df = spark.read.jdbc(url=connectionString, table="(select colName from Table) as CompatibleTable", properties=properties)

answered Jul 3, 2019 at 13:21

quasipolynomial

7467 silver badges12 bronze badges

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

1 Comment

William R Over a year ago

we can also use TO_CHAR() to convert the timestamp with local time zone column to string in the above sql query.

Collectives™ on Stack Overflow

java.sql.SQLException: Unrecognized SQL type -102 while connecting to Oracle Database from Apache Spark

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related