apache phoenix join issue

Question

when i do the query operation with "join" in my statement , i get the error information.Below are the error, environment and version details.

jdk-1.7.0_79
Phoenix-4.7.0
Hbase-1.1.2 with 7 region servers.

Caused by: java.sql.SQLException: Encountered exception in sub plan [0] execution. at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:193) at org.apache.phoenix.mapreduce.PhoenixInputFormat.getQueryPlan(PhoenixInputFormat.java:129) ... 11 more Caused by: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 1 at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:266) at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:84) at org.apache.phoenix.execute.HashJoinPlan$HashSubPlan.execute(HashJoinPlan.java:381) at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:162) at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:158) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at org.apache.phoenix.job.JobManager$InstrumentedJobFutureTask.run(JobManager.java:183) ... 3 more Caused by: java.lang.ArrayIndexOutOfBoundsException: 1 at org.apache.hadoop.io.FastByteComparisons$LexicographicalComparerHolder$UnsafeComparer.compareTo(FastByteComparisons.java:245) at org.apache.hadoop.io.FastByteComparisons$LexicographicalComparerHolder$UnsafeComparer.compareTo(FastByteComparisons.java:132) at org.apache.hadoop.io.FastByteComparisons.compareTo(FastByteComparisons.java:46) at org.apache.hadoop.io.WritableComparator.compareBytes(WritableComparator.java:188) at org.apache.phoenix.util.ScanUtil$2.compare(ScanUtil.java:484) at org.apache.phoenix.query.KeyRange.compareUpperToLowerBound(KeyRange.java:277) at org.apache.phoenix.query.KeyRange.compareUpperToLowerBound(KeyRange.java:222) at org.apache.phoenix.util.ScanUtil.searchClosestKeyRangeWithUpperHigherThanPtr(ScanUtil.java:506) at org.apache.phoenix.filter.SkipScanFilter.intersect(SkipScanFilter.java:220) at org.apache.phoenix.filter.SkipScanFilter.hasIntersect(SkipScanFilter.java:182) at org.apache.phoenix.compile.ScanRanges.intersectScan(ScanRanges.java:331) at org.apache.phoenix.compile.ScanRanges.intersects(ScanRanges.java:421) at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:175) ... 10 more

Below is the sql.

select a.MINITORDATE as MINITORDATE ,TEMPVAL, HUMVAL,PM25VAL ,NCPM25VAL from (
               ( select    "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as PM25VAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '00' AND SUBSTR(ROW,1,6) = '099812' AND SUBSTR(ROW,10,2) = '00' AND SUBSTR(ROW,12,2) = '00' 
                  AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') a 
                 INNER JOIN  
                ( select    "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as TEMPVAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '00' AND SUBSTR(ROW,1,6) = '099812' AND SUBSTR(ROW,10,2) = '02' AND SUBSTR(ROW,12,2) = '00' 
                   AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') b  on b.MINITORDATE = a.MINITORDATE  
                INNER JOIN 
                ( select    "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as HUMVAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '00' AND SUBSTR(ROW,1,6) = '099812' AND SUBSTR(ROW,10,2) = '03' AND SUBSTR(ROW,12,2) = '00' 
                   AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') c  on c.MINITORDATE = b.MINITORDATE  
                INNER JOIN 
                ( select    "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as NCPM25VAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '02' AND SUBSTR(ROW,1,6) = '023120' AND SUBSTR(ROW,10,2) = '00' AND SUBSTR(ROW,12,2) = '00' 
                   AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') d on d.MINITORDATE = c.MINITORDATE 
                )

The table ' AQM.AQMDATA_ALL' is created by Phoenix,and SALT_BUCKETS = 28. The number of rows in 'AQM.AQMDATA_ALL' is around 6 million.

With out SALT_BUCKETS or use the below sql,the query is fine!!!

 select a.MINITORDATE as MINITORDATE ,TEMPVAL, HUMVAL,PM25VAL ,NCPM25VAL from (
               ( select   "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as PM25VAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '00' AND SUBSTR(ROW,1,6) = '099812' AND SUBSTR(ROW,10,2) = '00' AND SUBSTR(ROW,12,2) = '00' 
                  AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') a 
                 INNER JOIN  
                ( select   "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as TEMPVAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '00' AND SUBSTR(ROW,1,6) = '099812' AND SUBSTR(ROW,10,2) = '02' AND SUBSTR(ROW,12,2) = '00' 
                   AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') b  on b.MINITORDATE = a.MINITORDATE  
                INNER JOIN 
                ( select    "Data_minitorDate" as MINITORDATE ,"Data_minitorVal" as HUMVAL from AQM.AQMDATA_ALL   where 1=1   AND SUBSTR(ROW,8,2) = '00' AND SUBSTR(ROW,1,6) = '099812' AND SUBSTR(ROW,10,2) = '03' AND SUBSTR(ROW,12,2) = '00' 
                   AND "Data_minitorDate" between '2016-08-22 00:00:00' and '2016-08-23 23:59:59') c  on c.MINITORDATE = b.MINITORDATE  )

The only difference between above two statemens is missing one "inner join".

Not only doing the query with 'SQuirreL' SQL Client,but also running the MapReduce job with phoenix-client , i meet the same problem.

Please help me on this.

Regards!

score 0 · Answer 1 · answered Aug 24 '16 at 13:44

At Splice Machine (Open Source) we ran a TPCH 1 Gig benchmark with the latest version of Phoenix and saw a lot of the sub plan execution exceptions. We did not have salted tables however.

I would file a bug directly via JIRA with just your schema. It looks like the query plan has a parse problem.

Be careful running a lot of joins in Phoenix, it does not scale (Join Performed on Client).

See slide 20 on this presentation from a member of the phoenix team.

http://www.slideshare.net/enissoz/apache-phoenix-past-present-and-future-of-sql-over-hbase

Good luck.

apache phoenix join issue

1 Answers1