Hive
  1. Hive
  2. HIVE-1643

support range scans and non-key columns in HBase filter pushdown

    Details

    • Type: Improvement Improvement
    • Status: Patch Available
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.9.0
    • Fix Version/s: None
    • Component/s: HBase Handler
    • Labels:

      Description

      HIVE-1226 added support for WHERE rowkey=3. We would like to support WHERE rowkey BETWEEN 10 and 20, as well as predicates on non-rowkeys (plus conjunctions etc). Non-rowkey conditions can't be used to filter out entire ranges, but they can be used to push the per-row filter processing as far down as possible.

      1. hbase_handler.patch
        13 kB
        Sandy Pratt
      2. HIVE-1643.patch
        14 kB
        bharath v
      3. Hive-1643.2.patch
        25 kB
        bharath v

        Issue Links

        There are no Sub-Tasks for this issue.

          Activity

          Hide
          John Sichi added a comment -

          Notes for working on this:

          Background is in

          http://wiki.apache.org/hadoop/Hive/FilterPushdownDev

          • In HiveHBaseTableInputFormat, newIndexPredicateAnalyzer needs to add additional operators (and stop restricting the allowed column names). And then convertFilter needs to set up corresponding HBase filter conditions based on the predicates it finds. Note that for inequality conditions on the key, it's necessary to muck with startRow/stopRow (not just the filter evaluator).
          • See also the comment in HBaseStorageHandler.decomposePredicate. Currently, it can only accept a single predicate. If you want to be able to support AND of multiple predicates (using HBase's FilterList) then this will need to be relaxed.
          • Beware of the fact that until HIVE-1538 gets committed, it is more difficult to make sure that the HBase-level filtering is working as expected. The reason is that without HIVE-1538, a second copy of the filter gets applied within Hive (regardless of how the filter was decomposed when it was pushed down to HBase). So even if HBase doesn't filter out everything you're expecting it to, you won't notice in the results since Hive will do the filtering again.
          Show
          John Sichi added a comment - Notes for working on this: Background is in http://wiki.apache.org/hadoop/Hive/FilterPushdownDev In HiveHBaseTableInputFormat, newIndexPredicateAnalyzer needs to add additional operators (and stop restricting the allowed column names). And then convertFilter needs to set up corresponding HBase filter conditions based on the predicates it finds. Note that for inequality conditions on the key, it's necessary to muck with startRow/stopRow (not just the filter evaluator). See also the comment in HBaseStorageHandler.decomposePredicate. Currently, it can only accept a single predicate. If you want to be able to support AND of multiple predicates (using HBase's FilterList) then this will need to be relaxed. Beware of the fact that until HIVE-1538 gets committed, it is more difficult to make sure that the HBase-level filtering is working as expected. The reason is that without HIVE-1538 , a second copy of the filter gets applied within Hive (regardless of how the filter was decomposed when it was pushed down to HBase). So even if HBase doesn't filter out everything you're expecting it to, you won't notice in the results since Hive will do the filtering again.
          Hide
          Vaibhav Aggarwal added a comment -

          I would like to work on this if it not being worked on actively as of now.

          Show
          Vaibhav Aggarwal added a comment - I would like to work on this if it not being worked on actively as of now.
          Hide
          Otis Gospodnetic added a comment -

          Vaibhav - judging from the lack of response since you asked about this a month ago, you should push forward with this, yes!

          Show
          Otis Gospodnetic added a comment - Vaibhav - judging from the lack of response since you asked about this a month ago, you should push forward with this, yes!
          Hide
          Vaibhav Aggarwal added a comment -

          I have been looking into this since last 3 days.

          I would ideally like to break this into:

          1. Add support for range query on primary key
          2. Add support for filter pushdown on non primary key columns

          I will try to submit a patch for 1. soon.

          Show
          Vaibhav Aggarwal added a comment - I have been looking into this since last 3 days. I would ideally like to break this into: 1. Add support for range query on primary key 2. Add support for filter pushdown on non primary key columns I will try to submit a patch for 1. soon.
          Hide
          Sandy Pratt added a comment -

          I've been working on this issue myself the last few days, and I was hoping we could compare notes. I’m developing against the hive release we currently have deployed (hive-0.7.0-cdh3u0), so I’m not sure if a patch would make sense. But basically, here’s the gist:

          HBaseStorageHandler.java:

          • Strike the implementation of HivePredicateStorageHanlder, and implement HiveStorageHandler instead. We’ll still be passed a copy of the filter expression if we wish to optimize, but Hive will still do the filtering over top of us (which is fine).

          HiveHBaseTableInputFormat.java:

          • Change the IndexPredicateAnalyzer configuration to include a few more operations that are relevant to range scans (e.g. >=, <=, <, >).
          • Continue to set the start and stop row basically as before in convertFilter, while doing the right thing for range scans.

          My main concern with this approach is that the IndexPredicateAnalyzer might not be doing what I think. For example, if I write odd where clauses like “key >= b and (key < c or true)” is it smart enough to discard that vacuous second part? Also, there are HBase-specific edge cases to think about (e.g. if we get “key >= d and key <= b” then we shouldn’t set any startRow or stopRow).

          Does this sound like the right track?

          Show
          Sandy Pratt added a comment - I've been working on this issue myself the last few days, and I was hoping we could compare notes. I’m developing against the hive release we currently have deployed (hive-0.7.0-cdh3u0), so I’m not sure if a patch would make sense. But basically, here’s the gist: HBaseStorageHandler.java: Strike the implementation of HivePredicateStorageHanlder, and implement HiveStorageHandler instead. We’ll still be passed a copy of the filter expression if we wish to optimize, but Hive will still do the filtering over top of us (which is fine). HiveHBaseTableInputFormat.java: Change the IndexPredicateAnalyzer configuration to include a few more operations that are relevant to range scans (e.g. >=, <=, <, >). Continue to set the start and stop row basically as before in convertFilter, while doing the right thing for range scans. My main concern with this approach is that the IndexPredicateAnalyzer might not be doing what I think. For example, if I write odd where clauses like “key >= b and (key < c or true)” is it smart enough to discard that vacuous second part? Also, there are HBase-specific edge cases to think about (e.g. if we get “key >= d and key <= b” then we shouldn’t set any startRow or stopRow). Does this sound like the right track?
          Hide
          John Sichi added a comment -

          Sandy, it would be great if you and Vaibhav can push this one through together.

          I'm not sure what you mean by "striking" HiveStoragePredicateHandler. Its purpose is to allow handlers to tell Hive which portion of the predicate got pushed down, and which part needs to be handled by Hive during per-row evaluation. This helps reduce the mapper work, so we do not want to lose that.

          IndexPredicateAnalyzer currently only handles conjunction (AND), so for your example, it would only extract the condition for "key >= b" (and leave the right-hand side of the AND to be evaluated by Hive). Vacuous cases would be better handled generically inside of Hive's optimizer (as part of constant folding), rather than specifically in index predicate analysis.

          Note that for non-key columns, youll need to change newIndexPredicateAnalyzer to not call clearAllowedColumnNames.

          Also watch out for the comment "We'll need to handle this better later when we support more interesting predicates" in HBaseStorageHandler.decomposePredicate.

          Show
          John Sichi added a comment - Sandy, it would be great if you and Vaibhav can push this one through together. I'm not sure what you mean by "striking" HiveStoragePredicateHandler. Its purpose is to allow handlers to tell Hive which portion of the predicate got pushed down, and which part needs to be handled by Hive during per-row evaluation. This helps reduce the mapper work, so we do not want to lose that. IndexPredicateAnalyzer currently only handles conjunction (AND), so for your example, it would only extract the condition for "key >= b" (and leave the right-hand side of the AND to be evaluated by Hive). Vacuous cases would be better handled generically inside of Hive's optimizer (as part of constant folding), rather than specifically in index predicate analysis. Note that for non-key columns, youll need to change newIndexPredicateAnalyzer to not call clearAllowedColumnNames. Also watch out for the comment "We'll need to handle this better later when we support more interesting predicates" in HBaseStorageHandler.decomposePredicate.
          Hide
          Sandy Pratt added a comment -

          John,

          Thanks for the feedback. My reasoning about implementing HiveStorageHandler instead of HiveStoragePredicateHandler is that is seems a bit safer. With HiveStorageHandler, we still have the predicate passed to us during getSplits, allowing us to optimize, but we're not "on the hook" to sort out pathological queries and do the right thing. However, based on your feedback, I've added HiveStoragePredicateHandler back in and tried to filter out anything weird in decomposePredicate.

          I'm testing a patch which allows a single equals predicate or a single or double ended range scan against the rowkey. I'll let you all know how it goes. As I mentioned before, I'm running CDH3u0 in all my environments, so that's what it will be easiest for me to test against. The HBase integration package looks largely the same on trunk, so hopefully that won't be a problem.

          Thanks,
          Sandy

          Show
          Sandy Pratt added a comment - John, Thanks for the feedback. My reasoning about implementing HiveStorageHandler instead of HiveStoragePredicateHandler is that is seems a bit safer. With HiveStorageHandler, we still have the predicate passed to us during getSplits, allowing us to optimize, but we're not "on the hook" to sort out pathological queries and do the right thing. However, based on your feedback, I've added HiveStoragePredicateHandler back in and tried to filter out anything weird in decomposePredicate. I'm testing a patch which allows a single equals predicate or a single or double ended range scan against the rowkey. I'll let you all know how it goes. As I mentioned before, I'm running CDH3u0 in all my environments, so that's what it will be easiest for me to test against. The HBase integration package looks largely the same on trunk, so hopefully that won't be a problem. Thanks, Sandy
          Hide
          Sandy Pratt added a comment -

          A patch I am currently testing, which allows a single equals predicate on the rowkey or a single or double ended range scan to be passed to the HBase layer.

          Show
          Sandy Pratt added a comment - A patch I am currently testing, which allows a single equals predicate on the rowkey or a single or double ended range scan to be passed to the HBase layer.
          Hide
          Sandy Pratt added a comment -

          After testing a bit, it looks like calling convertFilter in getRecordReader is causing incorrect results in group by / count queries. I believe this is because the scan range is getting reset for each map, and then three identical result sets are getting merged.

          Show
          Sandy Pratt added a comment - After testing a bit, it looks like calling convertFilter in getRecordReader is causing incorrect results in group by / count queries. I believe this is because the scan range is getting reset for each map, and then three identical result sets are getting merged.
          Hide
          Ashutosh Chauhan added a comment -

          Hey Vaibhav / Sandy,
          Did you guys pursue this any further ?

          Show
          Ashutosh Chauhan added a comment - Hey Vaibhav / Sandy, Did you guys pursue this any further ?
          Hide
          Sandy Pratt added a comment -

          I have a serde that I've deployed for my internal customers, in which this bug if fixed. I made some fairly wide-reaching changes to the published hbase-serde though. For example, I made it read-only, and changed the treatment of fields to be by Java reflection rather than parsed out of the DDL strings (that is, you translate your HBase row to Hive by providing a reflectable row-reader class that the API calls).

          So far, things are going pretty well and users seem happy. I'm in the process of getting my manger's consent to put it up on Google Code or something for others to use. But I feel like it's too far diverged from mainline to submit back as a patch. Stay tuned.

          Show
          Sandy Pratt added a comment - I have a serde that I've deployed for my internal customers, in which this bug if fixed. I made some fairly wide-reaching changes to the published hbase-serde though. For example, I made it read-only, and changed the treatment of fields to be by Java reflection rather than parsed out of the DDL strings (that is, you translate your HBase row to Hive by providing a reflectable row-reader class that the API calls). So far, things are going pretty well and users seem happy. I'm in the process of getting my manger's consent to put it up on Google Code or something for others to use. But I feel like it's too far diverged from mainline to submit back as a patch. Stay tuned.
          Hide
          Ashutosh Chauhan added a comment -

          I created a subtask for this (range scans for key of string type) and added patch over there. Comments and suggestions welcome.

          Show
          Ashutosh Chauhan added a comment - I created a subtask for this (range scans for key of string type) and added patch over there. Comments and suggestions welcome.
          Hide
          Ashutosh Chauhan added a comment -

          Forgot to mention HIVE-2771

          Show
          Ashutosh Chauhan added a comment - Forgot to mention HIVE-2771
          Hide
          Ashutosh Chauhan added a comment -

          Just a quick update:
          Work on filter pushdown on primary key range scans have been completed via HIVE-1634 HIVE-2771 HIVE-2815 HIVE-2819

          Note that filters are still not pushed for non-primary keys. I don't plan to work on that in immediate future, but would be to help out if any one else is planning for it.

          Show
          Ashutosh Chauhan added a comment - Just a quick update: Work on filter pushdown on primary key range scans have been completed via HIVE-1634 HIVE-2771 HIVE-2815 HIVE-2819 Note that filters are still not pushed for non-primary keys. I don't plan to work on that in immediate future, but would be to help out if any one else is planning for it.
          Hide
          bharath v added a comment -

          This patch converts non row-key predicates into corresponding hbase filters and adds them to scan objects to save network IO. Current trunk does filtering after TableScan operator.Tested it by observing reduction in number of rows processed by TableScanOperator.

          Show
          bharath v added a comment - This patch converts non row-key predicates into corresponding hbase filters and adds them to scan objects to save network IO. Current trunk does filtering after TableScan operator.Tested it by observing reduction in number of rows processed by TableScanOperator.
          Hide
          Ashutosh Chauhan added a comment -

          @Bharath,

          You can first create a review request so that I continue the review. In the meanwhile you can work on tests.

          Show
          Ashutosh Chauhan added a comment - @Bharath, Can you create a phabricator review request for this? https://cwiki.apache.org/confluence/display/Hive/PhabricatorCodeReview There are no test cases in your patch. Would you mind adding few? You can first create a review request so that I continue the review. In the meanwhile you can work on tests.
          Hide
          bharath v added a comment -

          @Ashutosh I created a phabricator review for it! Im using it for the first time, please let me know if your didn't get it! I'll add testcases in the subsequent revisions of the patch!

          Show
          bharath v added a comment - @Ashutosh I created a phabricator review for it! Im using it for the first time, please let me know if your didn't get it! I'll add testcases in the subsequent revisions of the patch!
          Hide
          bharath v added a comment -
          Show
          bharath v added a comment - https://reviews.facebook.net/D3999 - link for review
          Hide
          Ashutosh Chauhan added a comment -

          This patch will only push a list of ANDed filters like

          select * from tt where col1 < 23 and col2 > 34 and col3 = 5 and col4 = 29 
          

          Is this correct? What about filters on OR conditions and nested filters. Do you plan to add support for those ?

          select * from tt where col1 < 23 or (col2 < 2 and col3 = 5) or (col4 = 6 and (col5 = 3 or col6 = 7));
          
          Show
          Ashutosh Chauhan added a comment - This patch will only push a list of ANDed filters like select * from tt where col1 < 23 and col2 > 34 and col3 = 5 and col4 = 29 Is this correct? What about filters on OR conditions and nested filters. Do you plan to add support for those ? select * from tt where col1 < 23 or (col2 < 2 and col3 = 5) or (col4 = 6 and (col5 = 3 or col6 = 7));
          Hide
          bharath v added a comment -

          Currently the IndexPredicateAnalyzer.analyzePredicate() accepts only pure conjunctions (not sure why, can you please explain)! I haven't modified that functionality as I thought it might effect other packages (especially ql.index). Thats the reason only ANDs are getting pushed.

          Show
          bharath v added a comment - Currently the IndexPredicateAnalyzer.analyzePredicate() accepts only pure conjunctions (not sure why, can you please explain)! I haven't modified that functionality as I thought it might effect other packages (especially ql.index). Thats the reason only ANDs are getting pushed.
          Hide
          Ashutosh Chauhan added a comment -

          IndexPredicateAnalyzer was originally written for analysis of index predicates (as name suggests). Since, it matched the use case here for hbase handler, we used it here too. But, its not required that we keep using it. We can write a new PredicateAnalyzer (may be extending from IndexPA) which can also process ORs in some way if we want to. Or, else you can accept any combination of filter conditions (i.e., do not use PA at all) and construct appropriate filter list for HBase.

          Show
          Ashutosh Chauhan added a comment - IndexPredicateAnalyzer was originally written for analysis of index predicates (as name suggests). Since, it matched the use case here for hbase handler, we used it here too. But, its not required that we keep using it. We can write a new PredicateAnalyzer (may be extending from IndexPA) which can also process ORs in some way if we want to. Or, else you can accept any combination of filter conditions (i.e., do not use PA at all) and construct appropriate filter list for HBase.
          Hide
          bharath v added a comment -

          The problem becomes easy if all the columns are binary. Then we can build nested FilterLists recursively (since a FilterList can contain another FilterList). However the problem comes when some of the columns are not binary. For eg In the query

          select * from tt where col1 < 23 or (col2 < 2 and col3 = 5) or (col4 = 6 or (col5 = 3 or col6 > 7));

          col6 is not binary, then we cannot put a Filter with (col6 > 7). Then how to determine the set of conditions that can be pushed? (In the above example , we cannot push whole (col4 = 6 or (col5 = 3 or col6 > 7)) . I've gone through the source of IPA and I feel it does the same. I takes only perfect ANDs so that they can be pushed as a whole. Any thoughts?

          Show
          bharath v added a comment - The problem becomes easy if all the columns are binary. Then we can build nested FilterLists recursively (since a FilterList can contain another FilterList). However the problem comes when some of the columns are not binary. For eg In the query select * from tt where col1 < 23 or (col2 < 2 and col3 = 5) or (col4 = 6 or (col5 = 3 or col6 > 7)); col6 is not binary, then we cannot put a Filter with (col6 > 7). Then how to determine the set of conditions that can be pushed? (In the above example , we cannot push whole (col4 = 6 or (col5 = 3 or col6 > 7)) . I've gone through the source of IPA and I feel it does the same. I takes only perfect ANDs so that they can be pushed as a whole. Any thoughts?
          Hide
          bharath v added a comment -

          One way to deal with this is to just create the HBase filters for these predicates and keep the FilterOperator as it is on the original (whole) predicate. This saves network IO. Any other suggestions?

          Show
          bharath v added a comment - One way to deal with this is to just create the HBase filters for these predicates and keep the FilterOperator as it is on the original (whole) predicate. This saves network IO. Any other suggestions?
          Hide
          bharath v added a comment -

          New patch attached. Works also for nested predicates! Implemented new methods for analyzing predicates rather than depending on IPA ! These methods work more accurately for HBase specific filter pushdowns! Added some test cases too !

          Show
          bharath v added a comment - New patch attached. Works also for nested predicates! Implemented new methods for analyzing predicates rather than depending on IPA ! These methods work more accurately for HBase specific filter pushdowns! Added some test cases too !
          Hide
          bharath v added a comment -

          Can someone review this? https://reviews.apache.org/r/6605/

          Show
          bharath v added a comment - Can someone review this? https://reviews.apache.org/r/6605/
          Hide
          Lianhui Wang added a comment -

          Ashutosh Chauhan
          Is this correct? What about filters on OR conditions and nested filters. Do you plan to add support for those ?
          select * from tt where col1 < 23 or (col2 < 2 and col3 = 5) or (col4 = 6 and (col5 = 3 or col6 = 7));

          i think there should need range analyze.
          in mysql, sql optimizer include the range analyze on partition and index.
          binary tree represent conditions ranges.
          but there are some difficulties in task split.
          because maybe there are many small ranges in one table region. so maybe merge multi small ranges in one region and use rowkeyFilter.
          that can reduce one region's visits.

          Show
          Lianhui Wang added a comment - Ashutosh Chauhan Is this correct? What about filters on OR conditions and nested filters. Do you plan to add support for those ? select * from tt where col1 < 23 or (col2 < 2 and col3 = 5) or (col4 = 6 and (col5 = 3 or col6 = 7)); i think there should need range analyze. in mysql, sql optimizer include the range analyze on partition and index. binary tree represent conditions ranges. but there are some difficulties in task split. because maybe there are many small ranges in one table region. so maybe merge multi small ranges in one region and use rowkeyFilter. that can reduce one region's visits.
          Hide
          Hudson added a comment -

          Integrated in Hive-trunk-hadoop2 #54 (See https://builds.apache.org/job/Hive-trunk-hadoop2/54/)
          HIVE-2771 [jira] Add support for filter pushdown for key ranges in hbase for
          keys of type string
          (Ashutosh Chauhan via Carl Steinbach)

          Summary:
          https://issues.apache.org/jira/browse/HIVE-2771

          This patch adds support for key range scans pushdown to hbase for keys of type
          string. With this patch filter pushdowns of following types are supported:
          a) Point lookups for keys of any types.
          b) Range scans for keys of type string.

          Test Plan:
          Added hbase_ppd_key_range.q which is modeled after hbase_pushdown.q

          This is a subtask of HIVE-1643

          Test Plan: EMPTY

          Reviewers: JIRA, jsichi, cwsteinbach

          Reviewed By: cwsteinbach

          CC: jsichi, ashutoshc

          Differential Revision: https://reviews.facebook.net/D1551 (Revision 1297675)

          Result = ABORTED
          cws : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1297675
          Files :

          • /hive/trunk/hbase-handler/src/java/org/apache/hadoop/hive/hbase/HBaseStorageHandler.java
          • /hive/trunk/hbase-handler/src/java/org/apache/hadoop/hive/hbase/HiveHBaseTableInputFormat.java
          • /hive/trunk/hbase-handler/src/test/queries/hbase_ppd_key_range.q
          • /hive/trunk/hbase-handler/src/test/results/hbase_ppd_key_range.q.out
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/Utilities.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/io/HiveInputFormat.java
          • /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/ppd/OpProcFactory.java
          Show
          Hudson added a comment - Integrated in Hive-trunk-hadoop2 #54 (See https://builds.apache.org/job/Hive-trunk-hadoop2/54/ ) HIVE-2771 [jira] Add support for filter pushdown for key ranges in hbase for keys of type string (Ashutosh Chauhan via Carl Steinbach) Summary: https://issues.apache.org/jira/browse/HIVE-2771 This patch adds support for key range scans pushdown to hbase for keys of type string. With this patch filter pushdowns of following types are supported: a) Point lookups for keys of any types. b) Range scans for keys of type string. Test Plan: Added hbase_ppd_key_range.q which is modeled after hbase_pushdown.q This is a subtask of HIVE-1643 Test Plan: EMPTY Reviewers: JIRA, jsichi, cwsteinbach Reviewed By: cwsteinbach CC: jsichi, ashutoshc Differential Revision: https://reviews.facebook.net/D1551 (Revision 1297675) Result = ABORTED cws : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1297675 Files : /hive/trunk/hbase-handler/src/java/org/apache/hadoop/hive/hbase/HBaseStorageHandler.java /hive/trunk/hbase-handler/src/java/org/apache/hadoop/hive/hbase/HiveHBaseTableInputFormat.java /hive/trunk/hbase-handler/src/test/queries/hbase_ppd_key_range.q /hive/trunk/hbase-handler/src/test/results/hbase_ppd_key_range.q.out /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/Utilities.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/io/HiveInputFormat.java /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/ppd/OpProcFactory.java
          Hide
          Craig Condit added a comment -

          Is this issue still being worked? Would love to see this in 0.13...

          Show
          Craig Condit added a comment - Is this issue still being worked? Would love to see this in 0.13...
          Hide
          Sandy Pratt added a comment -

          Craig, I've been running my patch for this issue in production for at least a year now, and it seems to work well enough. I have an item on my plate to contribute the source, but it will have to wait until I have an opening in my schedule. Because the HBase handler is a pluggable SerDe, and my implementation strays a bit from the one in Hive, I'll probably stick it on Github or something and post a pointer here.

          Show
          Sandy Pratt added a comment - Craig, I've been running my patch for this issue in production for at least a year now, and it seems to work well enough. I have an item on my plate to contribute the source, but it will have to wait until I have an opening in my schedule. Because the HBase handler is a pluggable SerDe, and my implementation strays a bit from the one in Hive, I'll probably stick it on Github or something and post a pointer here.

            People

            • Assignee:
              bharath v
              Reporter:
              John Sichi
            • Votes:
              4 Vote for this issue
              Watchers:
              19 Start watching this issue

              Dates

              • Created:
                Updated:

                Development