apex-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "JOHN, BIBIN" <bj9...@att.com>
Subject RE: JDBCtoJDBC Example
Date Tue, 20 Dec 2016 03:04:52 GMT
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:devendrat@datatorrent.com]
Sent: Monday, December 19, 2016 4:38 PM
To: users@apex.apache.org
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 <bj9306@att.com<mailto:bj9306@att.com>>
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: bj9306@att.com<mailto:bj9306@att.com>

From: Devendra Tagare [mailto:devendrat@datatorrent.com<mailto:devendrat@datatorrent.com>]
Sent: Monday, December 19, 2016 1:05 PM
To: users@apex.apache.org<mailto:users@apex.apache.org>
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 <bj9306@att.com<mailto:bj9306@att.com>>
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--


Mime
View raw message