Code sql

Hive SQL - Merge Statement on ACID Tables

Kontext Kontext visibility 428 comment 0 access_time 2 years ago language English

descriptionCode description

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.

20220819124209-image.png

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.

fork_rightFork
more_vert
info Last modified by Kontext 2 years ago copyright This page is subject to Site terms.
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts