How to use NOT IN query in HQL

tomoya_yoshida — Tue, 30 Jan 2018 17:59:14 GMT

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.

Re: How to use NOT IN query in HQL

arald — Tue, 30 Jan 2018 20:13:20 GMT

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

question Re: How to use NOT IN query in HQL in Archives of Support Questions (Read Only)

How to use NOT IN query in HQL

Re: How to use NOT IN query in HQL