Hi, Do you have the equivalent of that operation in pure SQL. Also have you tried Spark query tool with Hive table.
I gather you are doing this through Java? Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com On 5 May 2016 at 13:10, Amey Barve <ameybarv...@gmail.com> wrote: > Thanks Mich, > > It does work, but with operators other than '=', '!=', '<', '<=', '>' and > '>=' , my code with custom storage handler gets null expression. > > *final String expression = conf.get(TableScanDesc.FILTER_EXPR_CONF_STR); > // expression is null for hive query that has like operator* > > Why does above API return *null* for hive query that has like operator? I > need to pass like operator for predicate push down to my custom storage > handler. > > Regards, > Amey > > On Thu, May 5, 2016 at 5:30 PM, Mich Talebzadeh <mich.talebza...@gmail.com > > wrote: > >> On a normal query using sql in* Hive 2* LIKE predicate works fine. Case >> in point in a 1 billion rows table with the column random_string of >> varchar(50) I have one row that satisfies the followinh@ >> >> >> +-----------+------------------+------------------+-------------------+-----------------------------------------------------+-----------------+----------------+--+ >> | dummy.id | dummy.clustered | dummy.scattered | dummy.randomised >> | dummy.random_string | dummy.small_vc | >> dummy.padding | >> >> +-----------+------------------+------------------+-------------------+-----------------------------------------------------+-----------------+----------------+--+ >> | 1 | 0 | 0 | 63 | >> rMLTDXxxqXOZnqYRJwInlGfGBTxNkAszBGEUGELqTSRnFjRGbi | 1 | >> xxxxxxxxxx | >> | 2 | 0 | 1 | 926 | >> UEDJsfIgoYqwreSuuvjIcPZarpxMdCthpDCsgPlJfvIiylLiBS | 2 | >> xxxxxxxxxx | >> >> Now let us try to select that row with LIKE predicate: >> >> 0: jdbc:hive2://rhes564:10010/default> select count(1) from dummy where >> random_string like 'rMLTDXxxqXOZnqYRJ%'; >> >> INFO : >> Query Hive on Spark job[0] stages: >> INFO : 0 >> INFO : 1 >> INFO : >> Status: Running (Hive on Spark job[0]) >> >> INFO : Completed executing >> command(queryId=hduser_20160505125700_cbc415b6-91bb-4ed6-95e4-d177e12988f6); >> Time taken: 153.544 seconds >> INFO : OK >> +-----+--+ >> | c0 | >> +-----+--+ >> | 1 | >> +-----+--+ >> 1 row selected (153.959 seconds) >> >> So it does work >> >> HTH >> >> >> Dr Mich Talebzadeh >> >> >> >> LinkedIn * >> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >> >> >> >> http://talebzadehmich.wordpress.com >> >> >> >> On 5 May 2016 at 11:53, Amey Barve <ameybarv...@gmail.com> wrote: >> >>> Hi All, >>> >>> I have implemented custom storage-handler and able to get predicates >>> from hive for '=', '!=', '<', '<=', '>' and '>=' operators. >>> But I cannot get predicates from hive for 'like', 'between' operators. >>> >>> Here's my code: >>> >>> *final String expression = conf.get(TableScanDesc.FILTER_EXPR_CONF_STR);* >>> >>> here expression remains null for like and between operators but not null >>> for above operators. >>> >>> Does hive not give predicates for 'like' and 'between' operators to >>> custom storage handler ? >>> *Is there some other mechanism to get predicates for 'like' operator*? >>> >>> I tested with hive version 1.2 and 0.14. >>> >>> Thanks and Regards, >>> Amey >>> >> >> >
