hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rekha Joshi <rekha...@yahoo-inc.com>
Subject Re: How to support SQL NOT IN function in Hive QL
Date Tue, 08 Mar 2011 09:38:08 GMT
@Bejoy - AFAIK, NOT IN is not directly supported in current hive. Workaround, you can write
an outer join instead of antijoin. HIVE-1740, mentions another workaround - NOT(x LIKE p).
Thanks,
Rekha.

On 3/8/11 2:12 PM, "Bejoy Ks" <bejoy_ks@yahoo.com> wrote:

Hi Experts
    I'm facing a hurdle in transforming a SQL query to equivalent Hive QL with SQL NOT IN
functionality. My SQL query would like this

INSERT INTO Table1
(T1_field1,T1_field2_ID,T1_field3,T1_field4)
SELECT field1,field2,field3,field4
FROM Table2 JOIN Table3 T3
WHERE (field1,field2,field3,field4 ) NOT IN
(SELECT field1,field2,field3,field4  FROM Table4)
AND field4 BETWEEN T3.START_DATE  AND T3.END_DATE
AND T3.field3 = 'xyz' GROUP BY T1_field1,T1_field2_ID,T1_field3,T1_field4;


I'm comfortable with the rest of the query apart from the NOT IN part. I'm using Hive 0.7
with CDH3B4. I check the availability of SQL IN functionality in my hive and it worked like
a charm but NOT IN doesn't work out here. Has it been already included as a new patch in latest
trunk?
Can some one help me out how i can re frame the query avoiding the NOT IN part to get it working
on Hive?(I'm not really from a db background)
Also is there any JIRA tickets open to have this functionality supported in upcoming versions
of hive?

Regards
Bejoy.K.S




Mime
View raw message