Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to use NOT IN query in HQL

Solved Go to solution

How to use NOT IN query in HQL

New Contributor

I want to use NOT IN query in HIVE. like this

INSERT INTO TBL2 
SELECT 
   t1.*
FROM
   tbl1 t1
WHERE
  (COl1,COl2,COl3,COl4)
NOT IN
  (
    SELECT 
     COl1,COl2,COl3,COl4
    FROM
     TBL2
) ;

TBL1 is a daily processing data table.TBL2 is a Storage table.

TBL1 and TBL2 are same format tables.

I want to load data that does not exist in TBL 2 to TBL 2 in the daily processing data (TBL1).

How to implement this in hive?

regards.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: How to use NOT IN query in HQL

Super Collaborator

I guess you are using HIVE > 0.13, before i am not sure if IN/NOT IN is implemented. But even then, there is this limitation:

  • IN/NOT IN subqueries may only select a single column.

So you might try to append the values into one result column, or you try a left join with a condition like TBL2.COL1 is null

View solution in original post

1 REPLY 1
Highlighted

Re: How to use NOT IN query in HQL

Super Collaborator

I guess you are using HIVE > 0.13, before i am not sure if IN/NOT IN is implemented. But even then, there is this limitation:

  • IN/NOT IN subqueries may only select a single column.

So you might try to append the values into one result column, or you try a left join with a condition like TBL2.COL1 is null

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here