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.
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
info Last modified by Kontext 8 months ago
copyright
This page is subject to Site terms.
Log in with external accounts
comment Comments
No comments yet.
warning Please login first to view stats information.