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);
visibility 213 comment 0 access_time 8 months ago language English
Hive SQL - Merge Statement on ACID Tables
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.
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)
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>
After the merge, record 1 is updated; record 2 is deleted and record 3 is inserted into the table.
info Last modified by Kontext 8 months ago copyright This page is subject to Site terms.
Please log in or register to comment.account_circle Log in person_add Register
Log in with external accounts
No comments yet.