"java.sql.SQLSyntaxErrorException: ORA-00911: invalid character" with Transformer JDBC query Origin

  • 14 December 2021
  • 0 replies
  • 756 views

Userlevel 4
Badge
  • StreamSets Employee
  • 96 replies
Transformer JDBC query origin with oracle database fails with below error when the underlying Spark version is < 2.4.4Transformer version 4.x Spark version < 2.4.4
Error message stack trace: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:537) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:765) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:921) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640) at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:61) 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:317) at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:225) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:213) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:169) at com.streamsets.pipeline.spark.origin.jdbc.query.JdbcQueryOrigin.generate(JdbcQueryOrigin.scala:77) at com.streamsets.datatransformer.api.operator.Origin.lambda$getOrCreate$0(Origin.java:29) at com.streamsets.datatransformer.api.operator.Operator.generateDF(Operator.java:103) at com.streamsets.datatransformer.api.operator.Operator.lambda$getOrCreate$0(Operator.java:99) at java.util.Optional.orElseGet(Optional.java:267) at com.streamsets.datatransformer.api.operator.Operator.getOrCreate(Operator.java:99) at com.streamsets.datatransformer.api.operator.Origin.getOrCreate(Origin.java:27) at com.streamsets.datatransformer.api.spark.SparkOrigin.getOrCreate(SparkOrigin.java:39) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1$$anonfun$10.apply(BaseBatchDAGRunner.scala:620) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1$$anonfun$10.apply(BaseBatchDAGRunner.scala:614) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234) at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48) at scala.collection.TraversableLike$class.map(TraversableLike.scala:234) at scala.collection.AbstractTraversable.map(Traversable.scala:104) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1.apply(BaseBatchDAGRunner.scala:614) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner$$anonfun$generateDataRefs$1.apply(BaseBatchDAGRunner.scala:609) at scala.collection.mutable.LinkedHashMap.foreach(LinkedHashMap.scala:141) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner.generateDataRefs(BaseBatchDAGRunner.scala:609) at com.streamsets.datatransformer.dag.BaseBatchDAGRunner.run(BaseBatchDAGRunner.scala:564) at com.streamsets.pipeline.spark.dag.SparkBatchDAGRunner.run(SparkBatchDAGRunner.scala:71) at com.streamsets.pipeline.spark.dag.SparkBatchDAGRunner.run(SparkBatchDAGRunner.scala:55) at com.streamsets.datatransformer.dag.runner.DataTransformerRunner.startDataTransformerDagRunner(DataTransformerRunner.java:494) at com.streamsets.datatransformer.dag.runner.DataTransformerRunner.start(DataTransformerRunner.java:274) at com.streamsets.datacollector.execution.runner.common.AsyncRunner.lambda$start$3(AsyncRunner.java:145) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:214) at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:22) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:210) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.lambda$call$0(SafeScheduledExecutorService.java:214) at com.streamsets.datacollector.security.GroupsInScope.execute(GroupsInScope.java:22) at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:210) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at com.streamsets.datacollector.metrics.MetricSafeScheduledExecutorService$MetricsTask.run(MetricSafeScheduledExecutorService.java:88) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: Error : 911, Position : 60, Sql = SELECT * FROM (SELECT * FROM CAMPTAB.CHEETAH_UNSUBSR_EMAIL) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0, OriginalSql = SELECT * FROM (SELECT * FROM $DB.$TABLE) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0, Error Msg = ORA-00911: invalid character
With spark < 2.4.4 it generates the SQL query as below which has incorrect syntax incompatible with oracle
SELECT * FROM (select * from sdc.dbo) __SPARK_GEN_JDBC_SUBQUERY_NAME_0 WHERE 1=0


With Spark 2.4.4+ version with correct syntax.

SELECT * FROM (select * from (select * from sdc.dbo)) SPARK_GEN_SUBQ_0 WHERE 1=0,


Solution: Please use spark version 2.4.4 or later to read from Oracle databases with the JDBC Query origin.
 

Note: To read from most database vendors, the origin requires that Apache Spark version 2.4.0 or later is installed on the Transformer machine and on each node in the cluster. To read from Oracle databases only, the JDBC Query origin requires Spark version 2.4.4 or later.


0 replies

Be the first to reply!

Reply