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

Phoenix OFFSET LIMIT on BIGINT field erratic behaviour


Phoenix OFFSET LIMIT on BIGINT field erratic behaviour

New Contributor


I am trying to use NiFi GenerateTableFetch on a BIGINT field in a large 10M+ Apache Phoenix table however I have encountered problems with how Phoenix deals with the OFFSET. I am working on HDP 2.5.3 and these are my findings:

So basically the GenerateTableFetch was sending the SQL like below, however, it wasn't getting the correct data.:

  1. SELECT event_date_time,
  2. driver_id,
  3. truck_id,
  4. 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:

  1. 10:35:05[SELECT -0 rows,13.518 secs]Empty result set fetched
  2. ...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.

  2. 2014-10-2309:00:00 D-0762 T-081910657530
  3. 2014-10-2309:00:00 D-0729 T-091310657531
  4. 2014-10-2309:00:00 D-0711 T-067310657532
  5. 2014-10-2309:00:00 D-0684 T-096510657533
  6. 2014-10-2309:00:00 D-0633 T-071410657534
  7. 2014-10-2309:00:00 D-0555 T-078210657535
  8. 2014-10-2309:00:00 D-0504 T-080410657536
  9. 2014-10-2309:00:00 D-0477 T-029310657537
  10. 2014-10-2309:00:00 D-0426 T-041510657538
  11. 2014-10-2309:00:00 D-0381 T-016910657540

If I run the same query with OFFSET 0 I get the Min sequence as the first row (=min event_id), if I increase the OFFSET 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