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