This query is not supported in oracle, so this is first failure. I am not sure
if here any way to suppress LIMIT keyword when we use Oracle as source database.
2016-12-19 18:42:25,697 DEBUG jdbc.JdbcStore
(JdbcTransactionalStore.java:connect(122)) - select dt_window from dt_meta
where dt_app_id = ? and dt_operator_id = ?
2016-12-19 18:42:25,734 DEBUG jdbc.JdbcPOJOInputOperator
(JdbcPOJOInputOperator.java:queryToRetrieveData(354)) - built query select
account_no, name, amount from test_event_table LIMIT 100 OFFSET ?;
2016-12-19 18:42:25,746 DEBUG jdbc.JdbcStore
(JdbcTransactionalStore.java:connect(127)) - insert into dt_meta (dt_app_id,
dt_operator_id, dt_window) values (?,?,?)
2016-12-19 18:42:25,746 DEBUG jdbc.JdbcStore
(JdbcTransactionalStore.java:connect(132)) - update dt_meta set dt_window = ?
where dt_app_id = ? and dt_operator_id = ?
2016-12-19 18:42:25,746 DEBUG jdbc.JdbcStore
(JdbcTransactionalStore.java:connect(137)) - delete from dt_meta where
dt_app_id = ? and dt_operator_id = ?
Second failure is here. Please also find mapping used.
2016-12-19 18:42:26,032 DEBUG jdbc.JdbcPOJOInsertOutputOperator
(JdbcPOJOInsertOutputOperator.java:populateColumnDataTypes(162)) - resultSet
MetaData column count 3
2016-12-19 18:42:26,033 DEBUG jdbc.JdbcPOJOInsertOutputOperator
(JdbcPOJOInsertOutputOperator.java:populateColumnDataTypes(170)) - column name
ACCOUNT_NO type 2
2016-12-19 18:42:26,033 DEBUG jdbc.JdbcPOJOInsertOutputOperator
(JdbcPOJOInsertOutputOperator.java:populateColumnDataTypes(170)) - column name
NAME type 12
2016-12-19 18:42:26,033 DEBUG jdbc.JdbcPOJOInsertOutputOperator
(JdbcPOJOInsertOutputOperator.java:populateColumnDataTypes(170)) - column name
AMOUNT type 2
2016-12-19 18:42:26,033 DEBUG engine.StreamingContainer
(StreamingContainer.java:setupNode(1333)) - activating 2 in container
container_e15_1482167280022_0017_01_000002
2016-12-19 18:42:26,033 DEBUG jdbc.JdbcPOJOInsertOutputOperator
(JdbcPOJOInsertOutputOperator.java:activate(134)) - insert statement is INSERT
INTO test_output_event_table (ACCOUNT_NO,NAME,AMOUNT) VALUES (?,?,?)
2016-12-19 18:42:26,034 ERROR engine.StreamingContainer
(StreamingContainer.java:run(1431)) - Abandoning deployment of operator
OperatorDeployInfo[id=2,name=JdbcOutput,type=GENERIC,checkpoint={ffffffffffffffff,
0,
0},inputs=[OperatorDeployInfo.InputDeployInfo[portName=input,streamId=POJO's,sourceNodeId=1,sourcePortName=outputPort,locality=CONTAINER_LOCAL,partitionMask=0,partitionKeys=<null>]],outputs=[]]
due to setup failure.
java.lang.RuntimeException: unsupported data type 2
at
com.datatorrent.lib.db.jdbc.AbstractJdbcPOJOOutputOperator.handleUnknownDataType(AbstractJdbcPOJOOutputOperator.java:177)
at
com.datatorrent.lib.db.jdbc.AbstractJdbcPOJOOutputOperator.activate(AbstractJdbcPOJOOutputOperator.java:292)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInsertOutputOperator.activate(JdbcPOJOInsertOutputOperator.java:136)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInsertOutputOperator.activate(JdbcPOJOInsertOutputOperator.java:47)
at com.datatorrent.stram.engine.Node.activate(Node.java:619)
private int accountNumber;
private String name;
private int amount;
From: Devendra Tagare [mailto:[email protected]]
Sent: Monday, December 19, 2016 4:38 PM
To: [email protected]
Subject: Re: JDBCtoJDBC Example
Hi,
Could you please ping how you are providing the mapping between the POJO types
and sql types in your application.
you can also enable debug and log the value of columnDataTypes (see
populateColumnDataTypes method) in JdbcPOJOInputOperator and
JdbcPOJOInsertOutputOperator.
Thanks,
Dev
On Mon, Dec 19, 2016 at 2:50 PM, JOHN, BIBIN
<[email protected]<mailto:[email protected]>> wrote:
I could figure out issue with table doesn’t exist issue. but it still has below
2 exceptions.
DROP TABLE DMAAP_POC.DT_META CASCADE CONSTRAINTS;
CREATE TABLE DMAAP_POC.DT_META
(
DT_APP_ID VARCHAR2(100 BYTE) NOT NULL,
DT_OPERATOR_ID NUMBER NOT NULL,
DT_WINDOW NUMBER NOT NULL
)
NOCACHE
MONITORING;
CREATE TABLE DMAAP_POC.TEST_EVENT_TABLE
(
ACCOUNT_NO NUMBER(11) NOT NULL,
NAME VARCHAR2(255) DEFAULT NULL
NULL,
AMOUNT NUMBER(11) DEFAULT NULL
NULL
)
NOCACHE
MONITORING;
CREATE TABLE DMAAP_POC.TEST_OUTPUT_EVENT_TABLE
(
ACCOUNT_NO NUMBER(11) NOT NULL,
NAME VARCHAR2(255 BYTE) DEFAULT NULL
NULL,
AMOUNT NUMBER(11) DEFAULT NULL
NULL
)
NOCACHE
MONITORING;
2016-12-19 13:51:51,315 ERROR engine.StreamingContainer
(StreamingContainer.java:run(1431)) - Abandoning deployment of operator
OperatorDeployInfo[id=1,name=JdbcInput,type=INPUT,checkpoint={ffffffffffffffff,
0,
0},inputs=[],outputs=[OperatorDeployInfo.OutputDeployInfo[portName=outputPort,streamId=POJO's,bufferServer=<null>]]]
due to setup failure.
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00933: SQL
command not properly ended
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.setup(JdbcPOJOInputOperator.java:145)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.setup(JdbcPOJOInputOperator.java:67)
at com.datatorrent.stram.engine.Node.setup(Node.java:187)
at
com.datatorrent.stram.engine.StreamingContainer.setupNode(StreamingContainer.java:1309)
at
com.datatorrent.stram.engine.StreamingContainer.access$100(StreamingContainer.java:130)
at
com.datatorrent.stram.engine.StreamingContainer$2.run(StreamingContainer.java:1388)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not
properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.populateColumnDataTypes(JdbcPOJOInputOperator.java:158)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.setup(JdbcPOJOInputOperator.java:142)
... 5 more
2016-12-19 13:51:51,520 ERROR engine.StreamingContainer
(StreamingContainer.java:run(1431)) - Abandoning deployment of operator
OperatorDeployInfo[id=2,name=JdbcOutput,type=GENERIC,checkpoint={ffffffffffffffff,
0,
0},inputs=[OperatorDeployInfo.InputDeployInfo[portName=input,streamId=POJO's,sourceNodeId=1,sourcePortName=outputPort,locality=CONTAINER_LOCAL,partitionMask=0,partitionKeys=<null>]],outputs=[]]
due to setup failure.
java.lang.RuntimeException: unsupported data type 2
at
com.datatorrent.lib.db.jdbc.AbstractJdbcPOJOOutputOperator.handleUnknownDataType(AbstractJdbcPOJOOutputOperator.java:177)
at
com.datatorrent.lib.db.jdbc.AbstractJdbcPOJOOutputOperator.activate(AbstractJdbcPOJOOutputOperator.java:292)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInsertOutputOperator.activate(JdbcPOJOInsertOutputOperator.java:136)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInsertOutputOperator.activate(JdbcPOJOInsertOutputOperator.java:47)
at com.datatorrent.stram.engine.Node.activate(Node.java:619)
at com.datatorrent.stram.engine.GenericNode.activate(GenericNode.java:205)
at
com.datatorrent.stram.engine.StreamingContainer.setupNode(StreamingContainer.java:1336)
at
com.datatorrent.stram.engine.StreamingContainer.access$100(StreamingContainer.java:130)
at
com.datatorrent.stram.engine.StreamingContainer$2.run(StreamingContainer.java:1388)
2016-12-19 13:51:51,524 INFO engine.WindowGenerator
(WindowGenerator.java:activate(178)) - Catching up from 1482184295500 to
1482184311524
2016-12-19 13:51:53,548 INFO engine.StreamingContainer
(StreamingContainer.java:processHeartbeatResponse(799)) - Undeploy request: [1,
2]
2016-12-19 13:51:53,549 INFO engine.StreamingContainer
(StreamingContainer.java:undeploy(561)) - Undeploy complete.
Thanks and Regards,
Bibin John| Data Movement Technology Development
20205 North Creek Pkwy , Bothell, WA 98011 USA
• Office: (770) 235 5614<tel:(770)%20235-5614> | Cell: (469)
648-9858<tel:(469)%20648-9858>
Email: [email protected]<mailto:[email protected]>
From: Devendra Tagare
[mailto:[email protected]<mailto:[email protected]>]
Sent: Monday, December 19, 2016 1:05 PM
To: [email protected]<mailto:[email protected]>
Subject: Re: JDBCtoJDBC Example
Hi,
properties.xml seems to indicate that the databaseUrl is of the form
jdbc:oracle:thin:@//host:port/db
Can you try setting the databaseUrl to the below format which OracleDriver
recommends -
here<https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html>
jdbc:oracle:<drivertype>:<user>/<password>@<database>
where database is of the form - host:port:dbName instead of //host:port/dbName
eg :
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:scott/tiger@myhost:1521:orcl);
The JdbcStore used by this operator passes the databaseUrl as is to the
DriverManager to get a connection handle so any differences in the expected
syntax may not work.
Let me know if this helps.
Thanks,
Dev
On Mon, Dec 19, 2016 at 12:00 PM, JOHN, BIBIN
<[email protected]<mailto:[email protected]>> wrote:
All,
I am getting below exception for JDBC to JDBC example and hitting below error.
I have verified the table and it exist in DB. I have attached the property
file. Please help on this.
2016-12-19 11:07:32,186 INFO storage.DiskStorage (DiskStorage.java:<init>(53))
- using
/opt/data/data04/yarn/local/usercache/bj9306/appcache/application_1482167280022_0009/container_e15_1482167280
022_0009_01_000006/tmp as the basepath for spooling.
2016-12-19 11:07:32,190 INFO server.Server (Server.java:registered(110)) -
Server started listening at /0.0.0.0:41067<http://0.0.0.0:41067>
2016-12-19 11:07:33,240 INFO engine.StreamingContainer
(StreamingContainer.java:processHeartbeatResponse(812)) - Deploy request:
[OperatorDeployInfo[id=1,name=JdbcInput,type=INPUT,checkpoint={ffff
ffffffffffff, 0,
0},inputs=[],outputs=[OperatorDeployInfo.OutputDeployInfo[portName=outputPort,streamId=POJO's,bufferServer=<null>]]],
OperatorDeployInfo[id=2,name=JdbcOutput,type=GENERIC,checkpoin
t={ffffffffffffffff, 0,
0},inputs=[OperatorDeployInfo.InputDeployInfo[portName=input,streamId=POJO's,sourceNodeId=1,sourcePortName=outputPort,locality=CONTAINER_LOCAL,partitionMask=0,partitionKeys=
<null>]],outputs=[]]]
2016-12-19 11:07:34,266 ERROR engine.StreamingContainer
(StreamingContainer.java:run(1431)) - Abandoning deployment of operator
OperatorDeployInfo[id=2,name=JdbcOutput,type=GENERIC,checkpoint={ffff
ffffffffffff, 0,
0},inputs=[OperatorDeployInfo.InputDeployInfo[portName=input,streamId=POJO's,sourceNodeId=1,sourcePortName=outputPort,locality=CONTAINER_LOCAL,partitionMask=0,partitionKeys=<null>]
],outputs=[]] due to setup failure.
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942: table
or view does not exist
at
com.datatorrent.lib.db.jdbc.JdbcTransactionalStore.getCommittedWindowIdHelper(JdbcTransactionalStore.java:240)
at
com.datatorrent.lib.db.jdbc.JdbcTransactionalStore.getCommittedWindowId(JdbcTransactionalStore.java:196)
at
com.datatorrent.lib.db.AbstractTransactionableStoreOutputOperator.setup(AbstractTransactionableStoreOutputOperator.java:97)
at
com.datatorrent.lib.db.jdbc.AbstractJdbcTransactionableOutputOperator.setup(AbstractJdbcTransactionableOutputOperator.java:95)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInsertOutputOperator.setup(JdbcPOJOInsertOutputOperator.java:59)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInsertOutputOperator.setup(JdbcPOJOInsertOutputOperator.java:47)
at com.datatorrent.stram.engine.Node.setup(Node.java:187)
at
com.datatorrent.stram.engine.StreamingContainer.setupNode(StreamingContainer.java:1309)
at
com.datatorrent.stram.engine.StreamingContainer.access$100(StreamingContainer.java:130)
at
com.datatorrent.stram.engine.StreamingContainer$2.run(StreamingContainer.java:1388)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not
exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at
com.datatorrent.lib.db.jdbc.JdbcTransactionalStore.getCommittedWindowIdHelper(JdbcTransactionalStore.java:229)
... 9 more
2016-12-19 11:07:34,266 ERROR engine.StreamingContainer
(StreamingContainer.java:run(1431)) - Abandoning deployment of operator
OperatorDeployInfo[id=1,name=JdbcInput,type=INPUT,checkpoint={fffffff
fffffffff, 0,
0},inputs=[],outputs=[OperatorDeployInfo.OutputDeployInfo[portName=outputPort,streamId=POJO's,bufferServer=<null>]]]
due to setup failure.
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00933: SQL
command not properly ended
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.setup(JdbcPOJOInputOperator.java:145)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.setup(JdbcPOJOInputOperator.java:67)
at com.datatorrent.stram.engine.Node.setup(Node.java:187)
at
com.datatorrent.stram.engine.StreamingContainer.setupNode(StreamingContainer.java:1309)
at
com.datatorrent.stram.engine.StreamingContainer.access$100(StreamingContainer.java:130)
at
com.datatorrent.stram.engine.StreamingContainer$2.run(StreamingContainer.java:1388)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not
properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.populateColumnDataTypes(JdbcPOJOInputOperator.java:158)
at
com.datatorrent.lib.db.jdbc.JdbcPOJOInputOperator.setup(JdbcPOJOInputOperator.java:142)
... 5 more
2016-12-19 11:07:34,275 INFO engine.WindowGenerator
(WindowGenerator.java:activate(178)) - Catching up from 1482174389500 to
1482174454275
2016-12-19 11:07:36,297 INFO engine.StreamingContainer
(StreamingContainer.java:processHeartbeatResponse(799)) - Undeploy request: [1,
2]
2016-12-19 11:07:36,298 INFO engine.StreamingContainer
(StreamingContainer.java:undeploy(561)) - Undeploy complete.
End of LogType:dt.log
--More--
