Hive supports standard ANSI SQL MERGE statement from version 2.2. However it can be only be applied to tables that support ACID transactions. To learn more about ACID support in Hive, refer to article: Hive ACID Inserts, Updates and Deletes with ORC.
Sample table
This code snippet merges into a sample table named test_db.crud_table. It has two records before the merge.
The staging table was created using the following statements:
create table crud_table_stg (id int, value string, op string); insert into crud_table_stg values (1,'AA','U'),(2,'B','D'),(3,'C', 'I');
It has one additional column named op to indicate the delta changes:
- U - updates
- D - deletes
- I - inserts (i.e. new records)
Syntax
MERGE INTO <target table> AS T USING <source expression/table> AS S ON <boolean expression1> WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list> WHEN MATCHED [AND <boolean expression3>] THEN DELETE WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>
Output
After the merge, record 1 is updated; record 2 is deleted and record 3 is inserted into the table.
