Ask Your Question
0

How to handle Null pointer exception in JDBC Query Consumer

asked 2018-08-07 08:29:43 -0600

girija gravatar image

updated 2018-08-13 03:33:45 -0600

I am using a JDBC Query Consumer to query a table in a DB. The output of JDBC Query Consumer is then sent to Kafka Producer. Only insert operations (in RDBMS) are done on the table that I am querying and insert are done almost on a daily basis. The workflow works fine until all the records have been consumed. Once, there are no new records to the fetched, the workflow errors out with "Null pointer Exception". I understand, this is an intended behavior, but is there any way to handle the Null Pointer Exception?


My expectation from the workflow is that, it should wait till new records arrive in the DB Table and then it should fetch the new records as they arrive. And it has to wait for the next batch of records to arrive with out throwing the exception.

I appreciate your help. Thanks!

Note: ORA-01830 appears just before Null Pointer Exception. Before Null Pointer Exception occurred, it ran successfully for 5 hours without any date format error

SDC version 3.3.0

SQL Query Used:

SELECT gir.* FROM GL.GL_IMPORT_REFERENCES gir WHERE gir.LAST_UPDATE_DATE > TO_DATE('${OFFSET}','YYYY-MM-DD HH:MI:SS') ORDER BY gir.LAST_UPDATE_DATE

Initial Offset:

2018-07-15 12:00:00

Offset Column:

LAST_UPDATE_DATE

Log:

Error Code: 1830

Message: ORA-01830: date format picture ends before converting entire input string

java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:762)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1309)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:422)
at com.zaxxer.hikari.proxy.StatementProxy.executeQuery(StatementProxy.java:110)
at com.zaxxer.hikari.proxy.HikariStatementProxy.executeQuery(HikariStatementProxy.java)
at com.streamsets.pipeline.stage.origin.jdbc.JdbcSource.produce(JdbcSource.java:387)
at com.streamsets.pipeline.api.base.configurablestage.DSource.produce(DSource.java:38)
at com.streamsets.datacollector.runner.StageRuntime.lambda$execute$2(StageRuntime.java:242)
at com.streamsets.pipeline.api.impl.CreateByRef.call(CreateByRef.java:40)
at com.streamsets.datacollector.runner.StageRuntime.execute(StageRuntime.java:195)
at com.streamsets.datacollector.runner.StageRuntime.execute(StageRuntime.java:257)
at com.streamsets.datacollector.runner.StagePipe.process(StagePipe.java:219)
at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.processPipe(ProductionPipelineRunner.java:801)
at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.runPollSource(ProductionPipelineRunner.java:548)
at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.run(ProductionPipelineRunner.java:383)
at com.streamsets.datacollector.runner.Pipeline.run(Pipeline.java:512)
at com.streamsets.datacollector.execution.runner ...
(more)
edit retag flag offensive close merge delete

Comments

Please edit your answer to include the full stack trace from sdc.log including any 'caused by' sections after the first trace. Also, what version of Data Collector are you running?

metadaddy gravatar imagemetadaddy ( 2018-08-07 09:37:16 -0600 )edit

Do you set an initial offset value?

Dean Han gravatar imageDean Han ( 2018-08-07 10:04:18 -0600 )edit

metadaddy, I have added the log.

girija gravatar imagegirija ( 2018-08-08 06:17:06 -0600 )edit

Dean Han, Yes I have set an initial offset value.

girija gravatar imagegirija ( 2018-08-08 06:17:37 -0600 )edit

I think the problem is where when finishing reading and set the last LAST_UPDATE_DATE to offset. Because LAST_UPDATE_DATE is a DATE type, then offset is set with DATE type as well, so there is no need to convert again, please try to just use WHERE gir.LAST_UPDATE_DATE > ${offset}

Dean Han gravatar imageDean Han ( 2018-08-08 07:57:49 -0600 )edit

1 Answer

Sort by ยป oldest newest most voted
1

answered 2018-08-13 03:29:35 -0600

girija gravatar image

I believe streamsets uses the below date format, YYYY-MM-DD HH24:MI:SS.FF I was able to resolve this by changing my query to convert offset value to Timestamp (TO_TIMESTAMP), instead of converting to date (TO_DATE) and making the respective changes to the Offset value.

Old Query:

SELECT gir.* FROM GL.GL_IMPORT_REFERENCES gir WHERE gir.LAST_UPDATE_DATE > TO_DATE('${OFFSET}','YYYY-MM-DD HH:MI:SS') ORDER BY gir.LAST_UPDATE_DATE

Old Offset Value:

2018-07-15 12:00:00

New Query

SELECT gir.* FROM GL.GL_IMPORT_REFERENCES gir WHERE gir.LAST_UPDATE_DATE > TO_TIMESTAMP('${OFFSET}','YYYY-MM-DD HH24:MI:SS.FF') ORDER BY gir.LAST_UPDATE_DATE

New Offset Value:

2018-07-15 12:00:00.0
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-07 08:29:43 -0600

Seen: 172 times

Last updated: Aug 13