Hive SQL - Merge Statement on ACID Tables

Code description

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.

Code snippet

MERGE INTO test_db.crud_table as tgt
USING test_db.crud_table_stg as src
on src.id = tgt.id
WHEN MATCHED AND src.value<>tgt.value and src.op='U' THEN UPDATE SET value = src.value
WHEN MATCHED AND src.op='D' THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES(src.id, src.value);