Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
MIME-Version: 1.0
In-Reply-To: <CAJ3fcbBj+EVU_ebgLsGW0oSa2toDEMBKNs6Ty5mXNmSNaqZ2Wg@mail.gmail.com>
References: <CAOr_Eshd63uqFTVxZiUYsujej0xwik-WTzhwh-UghqFv3i3C-A@mail.gmail.com>
 <CAJ3fcbAeB2v=n4Y0YTO++Zt3uHk2JMt=DsrxK2AWPmxk4-Y-KQ@mail.gmail.com>
 <CAOr_EshYZ-ThBttfUe1WDd0-UnoBeqiRk5BumprEM7a9xt=c7w@mail.gmail.com> <CAJ3fcbBj+EVU_ebgLsGW0oSa2toDEMBKNs6Ty5mXNmSNaqZ2Wg@mail.gmail.com>
From: Amey Barve <ameybarve15@gmail.com>
Date: Thu, 5 May 2016 17:55:32 +0530
Message-ID: <CAOr_EsjJN8+H16SOVOSAxa=Zv-HLkVY9cpjgmCa0522HTGJ6Jg@mail.gmail.com>
Subject: Re: Predicates for 'like' and 'between' operators to custom storage handler.
To: user@hive.apache.org
Cc: dev@hive.apache.org
Content-Type: multipart/alternative; boundary=001a114a7f889876f70532177202
archived-at: Thu, 05 May 2016 12:26:19 -0000

--001a114a7f889876f70532177202
Content-Type: text/plain; charset=UTF-8

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.talebzadeh@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 <ameybarve15@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.talebzadeh@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 <ameybarve15@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
>>>>
>>>
>>>
>>
>

--001a114a7f889876f70532177202--