Hi Maryann, Could you please explain what is the complete test case is?
I just try to run the query from tpc-h query #5, which I provided in an earlier email. I just wanted to make sure the inner join works on phoenix. Thank you so much, David On Mon, Feb 24, 2014 at 7:52 PM, Maryann Xue <[email protected]> wrote: > Could you please give the complete test case so that I can repo the issue? > > > On Mon, Feb 24, 2014 at 7:48 PM, David Wang <[email protected]>wrote: > >> Hi Maryann, >> >> I tried moving the lineitem table (the largest table) to the first place >> in the query below: >> select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from >> lineitem inner join orders on l_orderkey = o_orderkey inner join supplier >> on l_suppkey = s_suppkey inner join customer on c_nationkey = s_nationkey >> and c_custkey = o_custkey inner join nation on s_nationkey = n_nationkey >> inner join region on n_regionkey = r_regionkey where r_name = 'AMERICA' and >> o_orderdate >= '1993-01-01' and o_orderdate < '1994-01-01' group by n_name >> order by revenue desc >> >> >> But when I execute I get the following error: >> >> java.lang.RuntimeException: >> com.salesforce.phoenix.exception.PhoenixIOException: >> com.salesforce.phoenix.exception.PhoenixIOException: Failed after >> attempts=14, exceptions: >> Mon Feb 24 19:36:50 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:36:51 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:36:52 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:36:54 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:36:56 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:37:00 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:37:04 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:37:12 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:37:28 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: �@2[] >> Mon Feb 24 19:38:00 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: i?�0�� >> Mon Feb 24 19:39:05 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: i?�0�� >> Mon Feb 24 19:40:09 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: i?�0�� >> Mon Feb 24 19:41:13 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: i?�0�� >> Mon Feb 24 19:42:18 EST 2014, >> org.apache.hadoop.hbase.client.ScannerCallable@53f5fcb6, >> java.io.IOException: java.io.IOException: Could not find hash cache for >> joinId: i?�0�� >> >> at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440) >> at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074) >> at sqlline.SqlLine.print(SqlLine.java:1735) >> at sqlline.SqlLine$Commands.execute(SqlLine.java:3683) >> at sqlline.SqlLine$Commands.sql(SqlLine.java:3584) >> at sqlline.SqlLine.dispatch(SqlLine.java:821) >> at sqlline.SqlLine.begin(SqlLine.java:699) >> at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441) >> at sqlline.SqlLine.main(SqlLine.java:424) >> >> >> Do you know of any way I can fix this? >> >> Thank you so much, >> >> David >> >> >> On Mon, Feb 24, 2014 at 7:02 PM, Maryann Xue <[email protected]>wrote: >> >>> Hi David, >>> >>> Since join is implemented through hash-join and table stats are >>> currently unavailable yet to help better decide the join order (and which >>> tables should be small enough to go into memory), users may sometimes have >>> to be careful about the table order in the query. >>> In your case, you can try moving the lineitem table (which is the >>> largest here) to the first place in the query. Think that should work. >>> >>> >>> Thanks, >>> Maryann >>> >>> >>> >>> On Mon, Feb 24, 2014 at 6:08 PM, David Wang <[email protected]>wrote: >>> >>>> Hi Maryann, >>>> >>>> The sizes of each table in my query are as follows: >>>> customer - 24 MB >>>> orders - 164 MB >>>> lineitem - 725 MB >>>> supplier - 1.4 MB >>>> nation - 2.2 KB >>>> region - 400 B >>>> >>>> The heap size of my region servers is 4 GB. >>>> >>>> Thank you, >>>> David >>>> >>>> >>>> On Mon, Feb 24, 2014 at 5:58 PM, Maryann Xue <[email protected]>wrote: >>>> >>>>> Hi David, >>>>> >>>>> What are sizes of each table in your query? And what is the heap size >>>>> of your region server? >>>>> >>>>> >>>>> Thanks, >>>>> Maryann >>>>> >>>>> >>>>> On Mon, Feb 24, 2014 at 5:50 PM, David Wang <[email protected]>wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> I had a question about running queries containing inner joins on >>>>>> phoenix, but wasn't sure where to post. >>>>>> I downloaded the phoenix-master (3.0.0), and inserted 10 MB of data >>>>>> through psql.sh. I found I was able to run a query, >>>>>> which contains an inner join in sqlline. >>>>>> >>>>>> The query I tried: >>>>>> >>>>>> select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue >>>>>> from customer inner join orders on c_custkey = o_custkey inner join >>>>>> lineitem on l_orderkey = o_orderkey inner join supplier on l_suppkey = >>>>>> s_suppkey and c_nationkey = s_nationkey inner join nation on s_nationkey >>>>>> = >>>>>> n_nationkey inner join region on n_regionkey = r_regionkey where r_name = >>>>>> 'AMERICA' and o_orderdate >= '1993-01-01' and o_orderdate < '1994-01-01' >>>>>> group by n_name order by revenue desc; >>>>>> >>>>>> But when I increased the data size from 10 MB to 1 GB, and try to run >>>>>> the same query, I get the following error: >>>>>> Error: Encountered exception in hash plan execution. (state=,code=0) >>>>>> >>>>>> I have included the path to snappy-java-1.0.3.2.jar in sqlline.sh >>>>>> like the following: >>>>>> java -cp >>>>>> ".:$phoenix_client_jar:/usr/local/hbase-0.94.8/lib/snappy-java-1.0.3.2.jar" >>>>>> -Dlog4j.configuration=file:$current_dir/log4j.properties >>>>>> sqlline.SqlLine >>>>>> -d com.salesforce.phoenix.jdbc.PhoenixDriver -u jdbc:phoenix:$1 -n >>>>>> none -p >>>>>> none --color=true --fastConnect=false --verbose=true >>>>>> --isolation=TRANSACTION_READ_COMMITTED $sqlfile >>>>>> >>>>>> Do you have any idea where the problem might be? >>>>>> >>>>>> Thank you so much, >>>>>> >>>>>> David >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Thanks, >>>>> Maryann >>>>> >>>> >>>> >>> >>> >>> -- >>> Thanks, >>> Maryann >>> >> >> > > > -- > Thanks, > Maryann >
