CODEWITHSUNDEEP
hadoopjoinhive
D V N
D V N

Reputation: 41

How to select deleted records from table A compared with table B using HIVE HQL

I have Table_A and Table_B and data is partitioned by partition_id

Table_A has 650 records like

COL-1a, COL-1b, COL-1c, partition_id=20151015

COL-2a, COL-2b, COL-2c, partition_id=20151015

...

...

...

COL-650a, COL-650b, COL-650c, partition_id=20151015

Table_B has same records but only till 500 (150 records are deleted)

COL-1a, COL-1b, COL-1c, partition_id=20151015

COL-2a, COL-2b, COL-2c, partition_id=20151015

...

...

...

COL-500a, COL-500b, COL-500c, partition_id=20151015

Here, I want a HIVE query to find/select records from Table_A which are deleted/missing in Table_B where - partition_id of both tables shall be same.

-D V N

Upvotes: 0

Views: 77

Answers (2)

Shiva kumar
Shiva kumar

Reputation: 71

select * from Table_A except select * form Table_B

Upvotes: 1

Kishore
Kishore

Reputation: 5891

What you want to do can be done with a LEFT JOIN:

SELECT x
FROM Table_A
LEFT JOIN Table_B
ON Table_A.x = Table_B.x
WHERE Table_B.x IS NULL

Upvotes: 1

Related Questions