quick start guide to test Hive Merge features

Hive_Merge_Quick_Start.md

Create Non-ACID table

create external table nonacid_n1 (key int, a1 string, value string) stored as orc;
insert into nonacid_n1 values(1, 'a11', 'val1');
insert into nonacid_n1 values(2, 'a12', 'val2');

+-----------------+----------------+-------------------+
| nonacid_n1.key  | nonacid_n1.a1  | nonacid_n1.value  |
+-----------------+----------------+-------------------+
| 1               | a11            | val1              |
| 2               | a12            | val2              |
+-----------------+----------------+-------------------+

Create ACID table

create table acidTable(key int NOT NULL enable, a1 string DEFAULT 'a1', value string) stored as orc tblproperties ("transactional"="true");
insert into acidTable values(1, 'a10','val100');

+----------------+---------------+------------------+
| acidtable.key  | acidtable.a1  | acidtable.value  |
+----------------+---------------+------------------+
| 1              | a10           | val100           |
+----------------+---------------+------------------+

Run Merge Statement

MERGE INTO acidTable as t using nonacid_n1 as s ON t.key = s.key
WHEN MATCHED AND s.key < 3 THEN DELETE
WHEN MATCHED AND s.key > 3 THEN UPDATE set a1 = "DEFAULT"
WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.a1, "DEFAULT");

+----------------+---------------+------------------+
| acidtable.key  | acidtable.a1  | acidtable.value  |
+----------------+---------------+------------------+
| 2              | a12           | DEFAULT          |
+----------------+---------------+------------------+