hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ronak Bhatt <ronakb...@gmail.com>
Subject Re: question - how to handle OR with HIVe
Date Fri, 20 Aug 2010 15:58:08 GMT
This OR and AND are for use in select or condition..for example,

select (a OR B) as end_result_trur_or_false
from table
where id > 20;

What I'm looking for is,

select sum(amount)
from table
where id > 20 and
(a > 5  OR b < 10)

as you can see, I want to use OR in where condition...I could possibly use
UNION ALL to simulate the OR condition, but it is lot of writing
(duplicating the code) and also not efficient from performance perspective
as the table will be scanned multiple times for each select of the union...

hope this helps to clarify what kind of OR I'm looking for....



*thanks, ronak*
*
*
*408 504 4847*
*My Blog : http://ronakbaps.posterous.com*
*
*
*
*



On Fri, Aug 20, 2010 at 8:50 AM, Alexey Kovyrin <alexey@kovyrin.net> wrote:

> It definitely does support it.
> http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF#Logical_Operators
>
> On Fri, Aug 20, 2010 at 11:37 AM, Ronak Bhatt <ronakbaps@gmail.com> wrote:
> > I read somewhere that hive does NOT support OR condition....that's what I
> > was wondering as to how others handle the situation.
> >
> >
> >
> > On Fri, Aug 20, 2010 at 8:30 AM, Jacob R Rideout <
> apache@jacobrideout.net>
> > wrote:
> >>
> >> On Fri, Aug 20, 2010 at 8:37 AM, Ronak Bhatt <ronakbaps@gmail.com>
> wrote:
> >> > Hi - I've a following SQL query. What is the way to convert it into
> HIVE
> >> > runnable format -
> >> > Select a.id, count(b.url)
> >> > from a, b
> >> > where a.id = b.id
> >> > and (b.url  like '%google.com%' or b.url like '%h.google.com%' or
> b.url
> >> > like
> >> > '%bing%')
> >> > and a.exec_date = date(now()) ;  ----- get today's data
> >> > group by a.id
> >> >
> >> > thanks, ronak
> >> >
> >>
> >> I think something like:
> >>
> >> SELECT a.id, COUNT(distinct b.url)
> >> FROM a
> >> JOIN b on a.id = b.id
> >> WHERE
> >>    (b.url  LIKE '%google.com%' OR b.url LIKE '%h.google.com%' OR
> >> b.url LIKE '%bing%')
> >> AND a.exec_date = from_unixtime(unix_timestamp())
> >> GROUP BY a.id;
> >>
> >> should work, but I didn't test it. You will probably need to format
> >> the data string to match your data - hive doesn't really have a date
> >> type as such - but instead has date manipulation functions for
> >> strings.
> >>
> >> Take a look at
> >> http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF#Date_Functions
> >>
> >>
> >> Jacob Rideout
> >
> >
>
>
>
> --
> Alexey Kovyrin
> http://kovyrin.net/
>

Mime
View raw message