Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

New Contributor

Hi,

I am trying to test the NiFi SQL processor capability against Apache Phoenix on a HDP 2.5.3 cluster both on thin and thick client jdbc connections, in a small flow that reads records from a 10mil record file, then SplitAvro-->ConverAvroToJSON-->MergeContent-->PutFile on local disk.

jdbc:phoenix:edm-c1-v-dev-master-01:2181:/hbase-unsecure
org.apache.phoenix.jdbc.PhoenixDriver
/hbase/phoenix-client.jar

jdbc:phoenix:thin:url=http://edm-c1-v-dev-web-01:8765;serialization=PROTOBUF
org.apache.phoenix.queryserver.client.Driver
/hbase/phoenix-thin-client.jar

Test 1: ExecuteSQL Processor - Successfully executed with both thin and thick client libraries.

Test 2: QueryDatamaseTable --> tried both thick and thin client and both Generic and Oracle Database Type.

13231-screen-shot-2017-03-03-at-114854-am.png

ERROR See below.

13230-screen-shot-2017-03-03-at-114336-am.png

Any thoughts that might help?

Thank you,

Adrian

6 REPLIES 6

Re: NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

Super Guru

Look in the NIFI error logs, there's often more details, can you post those.

Re: NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

Super Guru

make sure you do view state and clear that out

Re: NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

Super Guru

do you see an error like

Caused by: java.sql.SQLException: ERROR 1101 (XCL01): ResultSet is closed. at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:441) ~[na:na] at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145) ~[na:na] at org.apache.phoenix.jdbc.PhoenixResultSet.checkOpen(PhoenixResultSet.java:215) ~[na:na] at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:772) ~[na:na] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) ~[na:na] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) ~[na:na] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:102) ~[nifi-standard-processors-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2] at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$0(QueryDatabaseTable.java:253) ~[nifi-standard-processors-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2] ... 13 common frames omitted

Re: NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

New Contributor

@Timothy Spann

Hi Timothy,

This is what I have in the Log File:

2017-03-03 11:48:16,601 ERROR [Timer-Driven Process Thread-6] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=3de24353-627f-1d27-75aa-f72696b5c27e] Unable to execute SQL select query SELECT event_date_time, driver_id, truck_id, vehicle_class, location,driver_age, age_group,cost FROM TRUCK.T_SIM_03 due to org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.: org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro. 2017-03-03 11:48:16,605 ERROR [Timer-Driven Process Thread-6] o.a.n.p.standard.QueryDatabaseTable org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro. at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$0(QueryDatabaseTable.java:255) ~[nifi-standard-processors-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.processors.standard.QueryDatabaseTable$Lambda$139/1403151268.process(Unknown Source) ~[na:na] at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2329) ~[nifi-framework-core-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:249) ~[nifi-standard-processors-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_45] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_45] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_45] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_45] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_45] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_45] Caused by: java.sql.SQLTimeoutException: Operation timed out at org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:332) ~[na:na] at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145) ~[na:na] at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:532) ~[na:na] at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:510) ~[na:na] at org.apache.phoenix.iterate.RoundRobinResultIterator.getIterators(RoundRobinResultIterator.java:176) ~[na:na] at org.apache.phoenix.iterate.RoundRobinResultIterator.next(RoundRobinResultIterator.java:91) ~[na:na] at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:765) ~[na:na] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) ~[na:na] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) ~[na:na] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:102) ~[nifi-standard-processors-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$0(QueryDatabaseTable.java:253) ~[nifi-standard-processors-1.1.0.2.1.2.0-10.jar:1.1.0.2.1.2.0-10] ... 14 common frames omitted

Re: NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

Super Guru

does that table and database exist: TRUCK.T_SIM_03

is it really in TRUCK schema and you have read permissions

It may be data format.

Seems to be a timeout

] Caused by: java.sql.SQLTimeoutException: Operation timed out at

make sure you have permissions to Oracle.

Highlighted

Re: NiFi 1.1.0 QueryDatamaseTable processor against Apache Phoenix Table not working

New Contributor

@Timothy Spann

Hi Timothy,

Sorry for being late with my reply. I think I found the problem and it might have to do with how Phoenix deals with OFFSET clause (on a BIGINT field?).

The Phoenix Table has over 10Mil rows. In the table, I have an "event_id" BIGINT, sequence, indexed field with

Min = 10632544 and,

Max = 21265086.

These are my test scenarios:

Test1

ExecuteSQL processor: successfully processes the query on both Thin (PQS) and Thick Phoenix JDBC client connections. (The initial errors had to do with an older Phoenix JDBC Thick Client that I was referencing).

Test 2:

GenerateTableFetch processor: Viewstate = 21265086 (=max event_id). The next processor (SplitAvro) failed with:

13399-screen-shot-2017-03-08-at-95633-am.png

So basically the GenerateTableFetch was sending the SQL instead of the data file. The first statement looks like below with subsequent flowfiles having the OFFSET increased by 100:

SELECT event_date_time, 
driver_id,
truck_id,
event_id FROM TRUCK.T_SIM_03 ORDER BY event_id LIMIT 100 OFFSET 1000000

I ran the exact code in DbVisualiser and I get:

 10:35:05  [SELECT - 0 rows, 13.518 secs]  Empty result set fetched
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 13.518/0.000 sec  [1 successful, 0 errors]

I then reduce the OFFSET from 1000000 to 10000 and I get 100 rows of data, however, there is no logic on why the result set started at 10657530. The Min sequence is 10632544, if I subtract the two I get 24986??? instead of 10000.

EVENT_DATE_TIME DRIVER_ID TRUCK_ID EVENT_ID
2014-10-23 09:00:00 D-0762 T-0819 10657530
2014-10-23 09:00:00 D-0729 T-0913 10657531
2014-10-23 09:00:00 D-0711 T-0673 10657532
2014-10-23 09:00:00 D-0684 T-0965 10657533
2014-10-23 09:00:00 D-0633 T-0714 10657534
2014-10-23 09:00:00 D-0555 T-0782 10657535
2014-10-23 09:00:00 D-0504 T-0804 10657536
2014-10-23 09:00:00 D-0477 T-0293 10657537
2014-10-23 09:00:00 D-0426 T-0415 10657538
2014-10-23 09:00:00 D-0381 T-0169 10657540 

If I run the same query with OFFSET 0 I get the Min sequence as the first row (=min event_id), if I increase to 10 I get an event_id <> Min(event_id) + 10. I did some subsequent tests and it seems the Phoenix has a problem in dealing with OFFSETs or OFFSETS on BIGINT fields. Any Thoughts?

Thank you.

Don't have an account?
Coming from Hortonworks? Activate your account here