How to use NOT IN query in HQL

[#1]

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.

[#2 · Accepted solution]

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