Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.3.0, 3.3.0
    • Labels:
      None

      Description

      Enhance our join capabilities to support many-to-many joins where the size of both sides of the join are too big to fit into memory (and thus cannot use our hash join mechanism). One technique would be to order both sides of the join by their join key and merge sort the results on the client.

      1. 1179.patch
        83 kB
        Maryann Xue

        Issue Links

          Activity

          Hide
          maryannxue Maryann Xue added a comment -

          1. Add USE_SORT_MERGE_JOIN hint
          2. Otherwise queries that cannot be done with Hash-joins alone will take the sort-merge-join path instead. For example, a right-left join like:
          "SELECT ... FROM A RIGHT JOIN B ON ... LEFT JOIN C ON ... " the query plan will generate like: (A hash-join B) sort-merge-join C.

          Show
          maryannxue Maryann Xue added a comment - 1. Add USE_SORT_MERGE_JOIN hint 2. Otherwise queries that cannot be done with Hash-joins alone will take the sort-merge-join path instead. For example, a right-left join like: "SELECT ... FROM A RIGHT JOIN B ON ... LEFT JOIN C ON ... " the query plan will generate like: (A hash-join B) sort-merge-join C.
          Hide
          jamestaylor James Taylor added a comment -

          Thanks for the contribution, Maryann Xue. This is fantastic! First some high level questions before any detailed feedback:

          • how is it determined if a hash-join is possible?
          • is the hint USE_SORT_MERGE_JOIN an all-or-nothing hint, in that if more than two tables are joined together, they'd all use the the merge sort algorithm instead of a hash join? Should we have a way of hinting each part of the join (kind of like we have for our index order hint)?
          • what's the high level algorithm? That'd be good to capture in a code comment (sorry in advance if I missed it). Does it push an ORDER BY to the server based on the join keys? What happens if there's already an ORDER BY clause?
          • would be interesting to get your feedback on what we need in terms of statistics to drive the optimizer decisions: PHOENIX-1178, PHOENIX-1453, maybe others needed? Julian Hyde may have ideas.
          Show
          jamestaylor James Taylor added a comment - Thanks for the contribution, Maryann Xue . This is fantastic! First some high level questions before any detailed feedback: how is it determined if a hash-join is possible? is the hint USE_SORT_MERGE_JOIN an all-or-nothing hint, in that if more than two tables are joined together, they'd all use the the merge sort algorithm instead of a hash join? Should we have a way of hinting each part of the join (kind of like we have for our index order hint)? what's the high level algorithm? That'd be good to capture in a code comment (sorry in advance if I missed it). Does it push an ORDER BY to the server based on the join keys? What happens if there's already an ORDER BY clause? would be interesting to get your feedback on what we need in terms of statistics to drive the optimizer decisions: PHOENIX-1178 , PHOENIX-1453 , maybe others needed? Julian Hyde may have ideas.
          Hide
          maryannxue Maryann Xue added a comment -

          Basically, hash-join is found limited for 3 patterns in terms of functionality:
          1) Full joins, where both sides are the "OUTER" side
          2) "A RIGHT JOIN B LEFT JOIN C"
          3) "A RIGHT JOIN (complicated subquery or sub-join)" or "(complicated subquery or sub-join) LEFT JOIN A" or "(complicated subquery or sub-join) INNER JOIN A"
          complicated subquery means those subqueries that cannot be flattened into a simple scan plan and thus cannot be the LHS table (main table), such as a group-by, a limit, a sub-join, etc.

          Currently hash-join has the priority. So if no hint is provided, the compiler see if the top-level join can be done using hash-join, and recursively go through its sub-joins, and whenever we hit the above three patterns, the sort-merge-join plans are used. Again take the example (pattern 2) I wrote in my first comment:
          "A right join B left join C"
          First, it does not qualify for a star join (which should not contain any right join), so it should be compiled in a sequence like (A right join B) left join C, and now we find that the LHS table is not a flat scan, so we have to compile the left join as sort-merge join but we can still do hash-join for (A right join B).

          So far, we don't have a way to hint using certain join algorithms for a specific join in a query. But I think it might be worth adding such facilities.

          The orderby is pushed down to join tables in a query rewrite way, so order-by optimizations can be automatically used and of course will have server-side sorting. For sub-queries used as join tables that has order-by itself, the original order-by will be overridden by the sort-merge order by. The outmost order-by will just remain intact.

          would be interesting to get your feedback on what we need in terms of statistics to drive the optimizer decisions

          Sure, definitely. Already had some discussions with ramkrishna.s.vasudevan.

          Show
          maryannxue Maryann Xue added a comment - Basically, hash-join is found limited for 3 patterns in terms of functionality: 1) Full joins, where both sides are the "OUTER" side 2) "A RIGHT JOIN B LEFT JOIN C" 3) "A RIGHT JOIN (complicated subquery or sub-join)" or "(complicated subquery or sub-join) LEFT JOIN A" or "(complicated subquery or sub-join) INNER JOIN A" complicated subquery means those subqueries that cannot be flattened into a simple scan plan and thus cannot be the LHS table (main table), such as a group-by, a limit, a sub-join, etc. Currently hash-join has the priority. So if no hint is provided, the compiler see if the top-level join can be done using hash-join, and recursively go through its sub-joins, and whenever we hit the above three patterns, the sort-merge-join plans are used. Again take the example (pattern 2) I wrote in my first comment: "A right join B left join C" First, it does not qualify for a star join (which should not contain any right join), so it should be compiled in a sequence like (A right join B) left join C, and now we find that the LHS table is not a flat scan, so we have to compile the left join as sort-merge join but we can still do hash-join for (A right join B). So far, we don't have a way to hint using certain join algorithms for a specific join in a query. But I think it might be worth adding such facilities. The orderby is pushed down to join tables in a query rewrite way, so order-by optimizations can be automatically used and of course will have server-side sorting. For sub-queries used as join tables that has order-by itself, the original order-by will be overridden by the sort-merge order by. The outmost order-by will just remain intact. would be interesting to get your feedback on what we need in terms of statistics to drive the optimizer decisions Sure, definitely. Already had some discussions with ramkrishna.s.vasudevan .
          Hide
          jamestaylor James Taylor added a comment -

          +1. Adding the above as code comments at commit time would be good.

          Show
          jamestaylor James Taylor added a comment - +1. Adding the above as code comments at commit time would be good.
          Hide
          alexdl alex kamil added a comment - - edited

          getting an error when trying to execute these queries with phoenix 3.3 on table with 23M rows:

          query 1: SELECT A.ROWKEY,A.VS, A.COL1, A.COL2, A.COL3,A.COL4 FROM BT.MYTABLE AS A JOIN (SELECT B.ROWKEY, B.VS FROM BT.MYTABLE AS B WHERE ((COL3 = 'COMPLETED')) ) B ON A.ROWKEY=B.ROWKEY AND A.VS=B.VS WHERE COL1 = 'REFERENCES' AND COL2 = 'CAPTION' ORDER BY A.ROWKEY, A.VS DESC, A.COL1, A.COL2;

          query 2: SELECT C.ROWKEY AS RK, C.VS AS VS
          FROM BT."TABLE_WITH_23OK_ROWS" AS C JOIN (SELECT DISTINCT B.ROWKEY, B.VS FROM BT."TABLE_WITH_23M_ROWS" AS B LIMIT 1000000) B ON (C.ROWKEY=B.ROWKEY AND C.VS=B.VS);

          Error: Encountered exception in sub plan [0] execution. (state=,code=0)

          Caused by: java.sql.SQLException: Encountered exception in sub plan [0] execution.
          at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:159)
          at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:222)
          at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:213)
          at org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54)
          Caused by: org.apache.phoenix.join.MaxServerCacheSizeExceededException: Size of hash cache (104857690 bytes) exceeds the maximum allowed size (104857600 by
          tes)
          at org.apache.phoenix.join.HashCacheClient.serialize(HashCacheClient.java:104)
          at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78)
          at org.apache.phoenix.execute.HashJoinPlan$HashSubPlan.execute(HashJoinPlan.java:372)
          at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:138)

          Show
          alexdl alex kamil added a comment - - edited getting an error when trying to execute these queries with phoenix 3.3 on table with 23M rows: query 1: SELECT A.ROWKEY,A.VS, A.COL1, A.COL2, A.COL3,A.COL4 FROM BT.MYTABLE AS A JOIN (SELECT B.ROWKEY, B.VS FROM BT.MYTABLE AS B WHERE ((COL3 = 'COMPLETED')) ) B ON A.ROWKEY=B.ROWKEY AND A.VS=B.VS WHERE COL1 = 'REFERENCES' AND COL2 = 'CAPTION' ORDER BY A.ROWKEY, A.VS DESC, A.COL1, A.COL2; query 2: SELECT C.ROWKEY AS RK, C.VS AS VS FROM BT."TABLE_WITH_23OK_ROWS" AS C JOIN (SELECT DISTINCT B.ROWKEY, B.VS FROM BT."TABLE_WITH_23M_ROWS" AS B LIMIT 1000000) B ON (C.ROWKEY=B.ROWKEY AND C.VS=B.VS); Error: Encountered exception in sub plan [0] execution. (state=,code=0) Caused by: java.sql.SQLException: Encountered exception in sub plan [0] execution. at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:159) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:222) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:213) at org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54) Caused by: org.apache.phoenix.join.MaxServerCacheSizeExceededException: Size of hash cache (104857690 bytes) exceeds the maximum allowed size (104857600 by tes) at org.apache.phoenix.join.HashCacheClient.serialize(HashCacheClient.java:104) at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78) at org.apache.phoenix.execute.HashJoinPlan$HashSubPlan.execute(HashJoinPlan.java:372) at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:138)
          Hide
          jamestaylor James Taylor added a comment -

          You likely already saw this comment: https://issues.apache.org/jira/browse/PHOENIX-1179?focusedCommentId=14211790&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14211790

          I think you probably need either Phoenix to "know" when to use many-to-many join strategy (PHOENIX-1556), or a fallback mechanism when you encounter the InsufficientMemoryException (PHOENIX-1555), or a means of "telling" Phoenix to use many-to-many join strategy (PHOENIX-1557). I think currently, it'll use the hash join strategy if the compiler says it's able to (even if at runtime it will fail), so this isn't helping you much.

          The last one (PHOENIX-1557) may be doable as a small patch if you're up for it, alex kamil.

          Show
          jamestaylor James Taylor added a comment - You likely already saw this comment: https://issues.apache.org/jira/browse/PHOENIX-1179?focusedCommentId=14211790&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14211790 I think you probably need either Phoenix to "know" when to use many-to-many join strategy ( PHOENIX-1556 ), or a fallback mechanism when you encounter the InsufficientMemoryException ( PHOENIX-1555 ), or a means of "telling" Phoenix to use many-to-many join strategy ( PHOENIX-1557 ). I think currently, it'll use the hash join strategy if the compiler says it's able to (even if at runtime it will fail), so this isn't helping you much. The last one ( PHOENIX-1557 ) may be doable as a small patch if you're up for it, alex kamil .
          Hide
          alexdl alex kamil added a comment - - edited

          James Taylor actually this hint seems to work
          SELECT /*+ USE_SORT_MERGE_JOIN*/

          Show
          alexdl alex kamil added a comment - - edited James Taylor actually this hint seems to work SELECT /*+ USE_SORT_MERGE_JOIN*/
          Hide
          jamestaylor James Taylor added a comment -

          Ah, excellent. Didn't know that was available yet.

          Show
          jamestaylor James Taylor added a comment - Ah, excellent. Didn't know that was available yet.
          Hide
          sunnychen sunnychen added a comment -

          Dear J,
          I run the sql,
          select BIG.id from MAX_CT_STANDARD_TEST_TABLE1 as BIG JOIN CT_4 AS SMALL ON BIG.ID=SMALL.ID;

          as you can see above,the table MAX_CT_STANDARD_TEST_TABLE1 has 60 million which size is 120G,consists of 20 fields,the table CT_4 has 1 million which has 1 million same id as MAX_CT_STANDARD_TEST_TABLE1
          besides,i have 2 region server,they share the same configure files,,and I set the heap size to 10g,with 40% phoenix.query.maxGlobalMemoryPercentage
          then the sql runs,and it could kill one of my region server process random from time to time
          i am wondering if phoenix could not support lhs table over the memory too? cause if the lhs table's size changed to 10 million,after long time waiting, the results comes out correctly
          the problem is i need to join the big table,or two big tables together
          what i should do? could you please give some advice to me? thank you for your help!

          --java.sql.SQLException: Encountered exception in hash plan [0] execution.
          – at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:146)
          – at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:185)
          – at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:164)
          – at org.apache.phoenix.util.phoenixContextExecutor.call(phoenixContextExecutor.java:54)
          – at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:164)
          – at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:153)
          – at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:147)
          – at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:152)
          – at org.apache.phoenix.jdbc.phoenixConnection.executeStatements(phoenixConnection.java:220)
          – at org.apache.phoenix.util.phoenixRuntime.executeStatements(phoenixRuntime.java:193)
          – at org.apache.phoenix.util.phoenixRuntime.main(phoenixRuntime.java:140)
          --Caused by: java.sql.SQLException: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException
          – at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:199)
          – at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78)
          – at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:119)
          – at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:114)
          – at java.util.concurrent.FutureTask.run(FutureTask.java:262)
          – at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
          – at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
          – at java.lang.Thread.run(Thread.java:745)
          --Caused by: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException
          – at java.util.concurrent.FutureTask.report(FutureTask.java:122)
          – at java.util.concurrent.FutureTask.get(FutureTask.java:202)
          – at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:191)
          – ... 7 more
          --Caused by: java.lang.reflect.UndeclaredThrowableException
          – at com.sun.proxy.$Proxy10.addServerCache(Unknown Source)
          – at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:169)
          – at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:164)
          – ... 4 more
          --Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=2, exceptions:
          --Wed Jan 21 15:58:28 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4eb2d325, java.io.IOException: Call to nobida122/10.60.1.122:60020 failed on local exception: java.io.EOFException
          --Wed Jan 21 15:58:28 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4eb2d325, java.net.ConnectException: Connection refused

          -- at org.apache.hadoop.hbase.client.ServerCallable.withRetries(ServerCallable.java:187)
          – at org.apache.hadoop.hbase.ipc.ExecRPCInvoker.invoke(ExecRPCInvoker.java:79)

          Show
          sunnychen sunnychen added a comment - Dear J, I run the sql, select BIG.id from MAX_CT_STANDARD_TEST_TABLE1 as BIG JOIN CT_4 AS SMALL ON BIG.ID=SMALL.ID; as you can see above,the table MAX_CT_STANDARD_TEST_TABLE1 has 60 million which size is 120G,consists of 20 fields,the table CT_4 has 1 million which has 1 million same id as MAX_CT_STANDARD_TEST_TABLE1 besides,i have 2 region server,they share the same configure files,,and I set the heap size to 10g,with 40% phoenix.query.maxGlobalMemoryPercentage then the sql runs,and it could kill one of my region server process random from time to time i am wondering if phoenix could not support lhs table over the memory too? cause if the lhs table's size changed to 10 million,after long time waiting, the results comes out correctly the problem is i need to join the big table,or two big tables together what i should do? could you please give some advice to me? thank you for your help! --java.sql.SQLException: Encountered exception in hash plan [0] execution. – at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:146) – at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:185) – at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:164) – at org.apache.phoenix.util.phoenixContextExecutor.call(phoenixContextExecutor.java:54) – at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:164) – at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:153) – at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:147) – at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:152) – at org.apache.phoenix.jdbc.phoenixConnection.executeStatements(phoenixConnection.java:220) – at org.apache.phoenix.util.phoenixRuntime.executeStatements(phoenixRuntime.java:193) – at org.apache.phoenix.util.phoenixRuntime.main(phoenixRuntime.java:140) --Caused by: java.sql.SQLException: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException – at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:199) – at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78) – at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:119) – at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:114) – at java.util.concurrent.FutureTask.run(FutureTask.java:262) – at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) – at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) – at java.lang.Thread.run(Thread.java:745) --Caused by: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException – at java.util.concurrent.FutureTask.report(FutureTask.java:122) – at java.util.concurrent.FutureTask.get(FutureTask.java:202) – at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:191) – ... 7 more --Caused by: java.lang.reflect.UndeclaredThrowableException – at com.sun.proxy.$Proxy10.addServerCache(Unknown Source) – at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:169) – at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:164) – ... 4 more --Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=2, exceptions: --Wed Jan 21 15:58:28 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4eb2d325, java.io.IOException: Call to nobida122/10.60.1.122:60020 failed on local exception: java.io.EOFException --Wed Jan 21 15:58:28 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4eb2d325, java.net.ConnectException: Connection refused – -- at org.apache.hadoop.hbase.client.ServerCallable.withRetries(ServerCallable.java:187) – at org.apache.hadoop.hbase.ipc.ExecRPCInvoker.invoke(ExecRPCInvoker.java:79)
          Hide
          maryannxue Maryann Xue added a comment -

          sunnychen There're a few points to your problem:

          1. It should be the RHS that is put into region server memory. So I suppose in your case, the size of "MAX_CT_STANDARD_TEST_TABLE1" has nothing to do with the heap size whatsoever.

          2. But why did changing the LHS size actually make a difference?
          Very likely what happened was that the scan of LHS took so long that the client had timed out and initiated a retry, which once again sent the RHS to the region servers. After many times of such overlapped retries, different versions of RHS co-existed in the region server memory, which led to the exception or RS crash.

          So a general solution can be to increase the rpc timeout value for HBase client, in order to avoid these meaningless retries.

          3. Based on my assumption that you SMALL table ids are just a small part of your BIG table ids, a much better solution is to use hint "SKIP_SCAN_HASH_JOIN" for the query. If my assumption stands, you will see big difference in query running time.

          Show
          maryannxue Maryann Xue added a comment - sunnychen There're a few points to your problem: 1. It should be the RHS that is put into region server memory. So I suppose in your case, the size of "MAX_CT_STANDARD_TEST_TABLE1" has nothing to do with the heap size whatsoever. 2. But why did changing the LHS size actually make a difference? Very likely what happened was that the scan of LHS took so long that the client had timed out and initiated a retry, which once again sent the RHS to the region servers. After many times of such overlapped retries, different versions of RHS co-existed in the region server memory, which led to the exception or RS crash. So a general solution can be to increase the rpc timeout value for HBase client, in order to avoid these meaningless retries. 3. Based on my assumption that you SMALL table ids are just a small part of your BIG table ids, a much better solution is to use hint "SKIP_SCAN_HASH_JOIN" for the query. If my assumption stands, you will see big difference in query running time.
          Hide
          sunnychen sunnychen added a comment -

          Dear M,
          Thank you so much for solving my problem,the advice you gave is really helpful,i am very appreciated.
          I changed the hbase-site config:
          --<property>
          – <name>hbase.rpc.timeout</name>
          – <value>9000000</value>
          --</property>
          and run the sql i have mentioned ,
          select BIG.id from MAX_CT_STANDARD_TEST_TABLE1 as BIG JOIN CT_4 AS SMALL ON BIG.ID=SMALL.ID;
          it works,Time: 2040.769 sec(s), records:100000L

          it seems i have solved my problem with your kind help,but i run the other sql with the config below,
          select BIG.id from MAX_CT_STANDARD_TEST_TABLE1 as BIG JOIN TEST_YQW_1 AS SMALL ON BIG.ID=SMALL.ID;
          MAX_CT_STANDARD_TEST_TABLE1 has 60million rows which size is 120G,and TEST_YQW_1 has 10million rows which size is 20G,it still comes out with the rpc error,so the next step i should do is to still to increase the hbase.rpc.timeout value? but the time i had set is so larger, so i am wondering if the problem is the rhs table is too large to fit my memory?
          the phoenix version i am using is version3.1,so SKIP_SCAN_HASH_JOIN is not support in this version,so i am willing to improve my version to 4.2 to test your advice, if the phoenix3.1 do not support the rhs table exceed the memory?
          Again,Thank you for your help~ I am really appreciated with it~

          the error message,it's a little bit different~
          ava.sql.SQLException: Encountered exception in hash plan [0] execution.
          at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:146)
          at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:185)
          at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:164)
          at org.apache.phoenix.util.phoenixContextExecutor.call(phoenixContextExecutor.java:54)
          at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:164)
          at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:153)
          at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:147)
          at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:152)
          at org.apache.phoenix.jdbc.phoenixConnection.executeStatements(phoenixConnection.java:220)
          at org.apache.phoenix.util.phoenixRuntime.executeStatements(phoenixRuntime.java:193)
          at org.apache.phoenix.util.phoenixRuntime.main(phoenixRuntime.java:140)
          Caused by: java.sql.SQLException: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException
          at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:199)
          at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78)
          at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:119)
          at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:114)
          at java.util.concurrent.FutureTask.run(FutureTask.java:262)
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
          at java.lang.Thread.run(Thread.java:745)
          Caused by: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException
          at java.util.concurrent.FutureTask.report(FutureTask.java:122)
          at java.util.concurrent.FutureTask.get(FutureTask.java:202)
          at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:191)
          ... 7 more
          Caused by: java.lang.reflect.UndeclaredThrowableException
          at com.sun.proxy.$Proxy10.addServerCache(Unknown Source)
          at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:169)
          at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:164)
          ... 4 more
          Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=2, exceptions:
          Fri Jan 23 16:34:00 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4f7be11c, java.io.IOException: Call to nobida122/10.60.1.122:60020 failed on local exception: java.io.EOFException
          Fri Jan 23 16:34:00 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4f7be11c, org.apache.hadoop.hbase.ipc.HBaseClient$FailedServerException: This server is in the failed servers list: nobida122/10.60.1.122:60020

          at org.apache.hadoop.hbase.client.ServerCallable.withRetries(ServerCallable.java:187)
          at org.apache.hadoop.hbase.ipc.ExecRPCInvoker.invoke(ExecRPCInvoker.java:79)
          ... 7 more

          Show
          sunnychen sunnychen added a comment - Dear M, Thank you so much for solving my problem,the advice you gave is really helpful,i am very appreciated. I changed the hbase-site config: --<property> – <name>hbase.rpc.timeout</name> – <value>9000000</value> --</property> and run the sql i have mentioned , select BIG.id from MAX_CT_STANDARD_TEST_TABLE1 as BIG JOIN CT_4 AS SMALL ON BIG.ID=SMALL.ID; it works,Time: 2040.769 sec(s), records:100000L it seems i have solved my problem with your kind help,but i run the other sql with the config below, select BIG.id from MAX_CT_STANDARD_TEST_TABLE1 as BIG JOIN TEST_YQW_1 AS SMALL ON BIG.ID=SMALL.ID; MAX_CT_STANDARD_TEST_TABLE1 has 60million rows which size is 120G,and TEST_YQW_1 has 10million rows which size is 20G,it still comes out with the rpc error,so the next step i should do is to still to increase the hbase.rpc.timeout value? but the time i had set is so larger, so i am wondering if the problem is the rhs table is too large to fit my memory? the phoenix version i am using is version3.1,so SKIP_SCAN_HASH_JOIN is not support in this version,so i am willing to improve my version to 4.2 to test your advice, if the phoenix3.1 do not support the rhs table exceed the memory? Again,Thank you for your help~ I am really appreciated with it~ the error message,it's a little bit different~ ava.sql.SQLException: Encountered exception in hash plan [0] execution. at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:146) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:185) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:164) at org.apache.phoenix.util.phoenixContextExecutor.call(phoenixContextExecutor.java:54) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:164) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:153) at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:147) at org.apache.phoenix.jdbc.phoenixPreparedStatement.execute(phoenixPreparedStatement.java:152) at org.apache.phoenix.jdbc.phoenixConnection.executeStatements(phoenixConnection.java:220) at org.apache.phoenix.util.phoenixRuntime.executeStatements(phoenixRuntime.java:193) at org.apache.phoenix.util.phoenixRuntime.main(phoenixRuntime.java:140) Caused by: java.sql.SQLException: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:199) at org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78) at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:119) at org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:114) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.util.concurrent.ExecutionException: java.lang.reflect.UndeclaredThrowableException at java.util.concurrent.FutureTask.report(FutureTask.java:122) at java.util.concurrent.FutureTask.get(FutureTask.java:202) at org.apache.phoenix.cache.ServerCacheClient.addServerCache(ServerCacheClient.java:191) ... 7 more Caused by: java.lang.reflect.UndeclaredThrowableException at com.sun.proxy.$Proxy10.addServerCache(Unknown Source) at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:169) at org.apache.phoenix.cache.ServerCacheClient$1.call(ServerCacheClient.java:164) ... 4 more Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=2, exceptions: Fri Jan 23 16:34:00 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4f7be11c, java.io.IOException: Call to nobida122/10.60.1.122:60020 failed on local exception: java.io.EOFException Fri Jan 23 16:34:00 CST 2015, org.apache.hadoop.hbase.ipc.ExecRPCInvoker$1@4f7be11c, org.apache.hadoop.hbase.ipc.HBaseClient$FailedServerException: This server is in the failed servers list: nobida122/10.60.1.122:60020 at org.apache.hadoop.hbase.client.ServerCallable.withRetries(ServerCallable.java:187) at org.apache.hadoop.hbase.ipc.ExecRPCInvoker.invoke(ExecRPCInvoker.java:79) ... 7 more
          Hide
          maryannxue Maryann Xue added a comment -

          Yes, I do recommend you upgrade your Phoenix version. 3.2.2 / 4.2.2 includes the SKIP_SCAN_HASH_JOIN optimization, which might be able to improve your first query performance significantly. And 3.3 / 4.3 will be coming soon, and your second query may have to depend on that. Just remember to use hint "USE_SORT_MERGE_JOIN" if both LHS and RHS are too big to fit into memory.

          Show
          maryannxue Maryann Xue added a comment - Yes, I do recommend you upgrade your Phoenix version. 3.2.2 / 4.2.2 includes the SKIP_SCAN_HASH_JOIN optimization, which might be able to improve your first query performance significantly. And 3.3 / 4.3 will be coming soon, and your second query may have to depend on that. Just remember to use hint "USE_SORT_MERGE_JOIN" if both LHS and RHS are too big to fit into memory.
          Hide
          enis Enis Soztutar added a comment -

          Bulk close of all issues that has been resolved in a released version.

          Show
          enis Enis Soztutar added a comment - Bulk close of all issues that has been resolved in a released version.

            People

            • Assignee:
              maryannxue Maryann Xue
              Reporter:
              jamestaylor James Taylor
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development