apex-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bhupesh Chawda <bhup...@datatorrent.com>
Subject Re: JDBCtoJDBC Example
Date Wed, 21 Dec 2016 10:35:02 GMT
Hi,

To solve the issue of LIMIT clause, can you try to replace the
JdbcPOJOInputOperator
by JdbcPOJOPollInputOperator? The new operator uses a different way of
limiting the returned tuples and does not depend on LIMIT clause. This
migtht solve the issue.

The second problem seems to be because the NUMERIC type is not currently
handled in the JdbcPOJOInsertOutputOperator. I have created a JIRA
<https://issues.apache.org/jira/browse/APEXMALHAR-2373> to add the same.
Meanwhile a workaround can be to change the type of your database column to
a INTEGER type.

~ Bhupesh

On Wed, Dec 21, 2016 at 2:43 AM, JOHN, BIBIN <bj9306@att.com> wrote:

> Anyone have idea on this?
>
>
>
> *From:* JOHN, BIBIN
> *Sent:* Monday, December 19, 2016 7:05 PM
> *To:* 'users@apex.apache.org' <users@apex.apache.org>
> *Subject:* RE: JDBCtoJDBC Example
>
>
>
> 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
> <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> 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 <(770)%20235-5614> | Cell: (469) 648-9858
> <(469)%20648-9858>*
>
> *Email: *bj9306@att.com
>
>
>
> *From:* Devendra Tagare [mailto:devendrat@datatorrent.com]
> *Sent:* Monday, December 19, 2016 1:05 PM
> *To:* 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> 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
>
> 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.AbstractTransactionableStoreOu
> tputOperator.setup(AbstractTransactionableStoreOutputOperator.java:97)
>
>         at com.datatorrent.lib.db.jdbc.AbstractJdbcTransactionableOut
> putOperator.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