Hi, Do you have the equivalent of that operation in pure SQL. ---> This is my hive query: *select count(*) from u_data where unixtime like '%888904884%'* Query evaluates and results are correct. But point is that hive does not give like operator during predicate push down to custom storage handler. I am mapping with *hive's UDFLike* class.
Also have you tried Spark query tool with Hive table. ---> *No*. I gather you are doing this through Java? ---> *YES*. Has anybody tried mapping operators other than '=', '!=', '<', '<=', '>' and '>=' ? Regards, Amey On Thu, May 5, 2016 at 5:44 PM, Mich Talebzadeh <mich.talebza...@gmail.com> wrote: > 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 >>>> >>> >>> >> >
