使用MERGE INTO将插入更新和删除操作合并为单条语句-云原生大数据计算服务 MaxCompute-阿里云

当需要对 Transactional 表或 Delta 表执行 INSERT 、 UPDATE 、 DELETE 操作时，可以通过 MERGE INTO 功能将这些操作合并为一条 SQL 语句，根据与源表关联的结果，对目标 Transactional 表执行插入、更新或删除操作时，只需要执行一次全表扫描操作，以提高执行效率。

功能介绍

MaxCompute 支持了 DELETE 、 UPDATE 功能，但当需要使用多个 INSERT 、 UPDATE 、 DELETE 对目标表进行批量操作时，需要编写多条 SQL 语句，然后进行多次全表扫描才能完成操作。MaxCompute 提供的 MERGE INTO 功能，只需要进行一次全表扫描操作，就可以完成全部操作，执行效率要高于 INSERT + UPDATE + DELETE 。

MERGE INTO 操作具备原子性，作业中的 INSERT 、 UPDATE 、 DELETE 操作都执行成功时，作业才算执行成功；任一内部逻辑处理失败，则整体作业执行失败。

同时， MERGE INTO 可以避免分别执行 INSERT 、 UPDATE 、 DELETE 操作时，可能导致部分操作执行成功，部分操作执行失败，其中成功部分无法回退的问题。

适用范围

执行 MERGE INTO 操作前需要具备目标 Transactional 表的读取表数据权限（Select）及更新表数据权限（Update）。授权操作请参见 MaxCompute 权限。
不允许在同一条 MERGE INTO 语句中对相同的行执行多次 INSERT 或 UPDATE 操作。
MERGE INTO 的目标表只支持事务表。
MERGE INTO 语句中必须至少包含一个 merge_action 。
多个 not_matched_by_target_clause 语句时，最多只能有一个语句缺省 search_condition 。
多个 not_matched_by_source_clause 语句时，最多只能有一个语句缺省 search_condition 。
多个 matched_clause 语句时，最多只能有一个语句缺省 search_condition 。
merge_condition 和 search_condition 中不支持存在子查询。

命令格式

MERGE INTO <target_table> AS <alias_name_t>
USING <source expression | table_name> AS <alias_name_s>
--从on开始对源表和目标表的数据进行关联判断。
ON merge_condition
{ merge_action } +
merge_action ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause
--when matched…then指定on的结果为True的行为。多个when matched…then之间的数据无交集。
matched_clause ::= WHEN MATCHED [ AND <search_condition> ] THEN { merge_update_clause | merge_delete_clause }
--when not matched…then指定on的结果为False的行为。
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND <search_condition> ] THEN merge_insert_clause
not_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND <search_condition> ] THEN  { merge_update_clause | merge_delete_clause }
merge_condition ::= BOOLEAN expression
search_condition ::= BOOLEAN expression
merge_update_clause ::= UPDATE SET <set_clause_list>
merge_delete_clause ::= DELETE
merge_insert_clause ::= INSERT <value_list> | ROW | *

参数说明

参数名	是否必填	描述
target_table	是	目标表名称，必须是实际存在的表。
alias_name_t	否	目标表的别名。
source expression\|table_name	是	关联的源表名称、视图或子查询。
alias_name_s	否	关联的源表、视图或子查询的别名
merge_condition	是	BOOLEAN 类型判断条件，判断结果必须为 True 或 False。
search_condition	否	`UPDATE` 、 `DELETE` 、 `INSERT` 操作相应的 BOOLEAN 类型判断条件。需要注意：当出现三个 WHEN 子句时， `UPDATE` 、 `DELETE` 、 `INSERT` 都只能出现一次。如果 `UPDATE` 和 `DELETE` 同时出现，出现在前的操作必须包括 `[AND <BOOLEAN expression>]` 。 `WHEN NOT MATCHED` 只能出现在最后一个 WHEN 子句中。
set_clause_list	否	待更新数据信息。当出现 `UPDATE` 操作时必填。更多 `UPDATE` 信息，请参见更新数据（UPDATE）。
value_list	否	待插入数据信息。当出现 `INSERT` 操作时必填。更多 `VALUES` 信息，请参见 VALUES 。
merge_action	是	merge_action 有三种可选项：MATCHED、NOT MATCHED BY TARGET 和 NOT MATCHED BY SOURCE。每个 merge_action 可以包含一个可选的搜索条件（search_condition）。只有当 merge 条件（merge_condition）和搜索条件（search_condition）都满足时，该 merge_action 才会对某一行执行。当存在多个符合条件的子句时，仅第一个符合条件的 merge_action 会对该行执行。
matched_clause	否	matched_clause 有两个选项：UPDATE 和 DELETE，用于定义当目标表中的某一行与源表中的某一行匹配时，如何更新或删除该行。
not_matched_by_target_clause	否	用于定义当源表中的某一行在目标表中没有匹配行时，如何向目标表中插入该行。
not_matched_by_source_clause	否	用于定义当目标表中的某一行在源表中没有匹配行时，如何更新或删除该行。
merge_insert_clause	否	merge_insert_clause 有三个可选项：value_list、ROW 和。value_list 表示通过 values 表达式表示待插入数据；ROW 和都表示直接将源表匹配的数据插入到目标表，要求目标表和源表列个数一致，并且对应的类型是兼容的。

使用示例

示例 1：创建目标表 acid_address_book_base1 及源表 tmp_table1，并插入数据。执行 MERGE INTO 操作，对符合 ON 条件的数据用源表的数据对目标表进行更新操作，对不符合 ON 条件并且源表中满足 _event_type_ 为 I 的数据插入目标表。命令示例如下：

--创建目标表acid_address_book_base1。
CREATE TABLE IF NOT EXISTS acid_address_book_base1 
(id BIGINT,first_name STRING,last_name STRING,phone STRING) 
PARTITIONED BY(year STRING, month STRING, day STRING, hour STRING) 
tblproperties ("transactional"="true"); 
--创建源表tmp_table1。
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);
--向目标表acid_address_book_base1插入测试数据。
INSERT OVERWRITE TABLE acid_address_book_base1 
PARTITION(year='2020', month='08', day='20', hour='16') 
VALUES (4, 'nihaho', 'li', '222'), (5, 'tahao', 'ha', '333'), 
(7, 'djh', 'hahh', '555');
--查询目标表的数据确认插入测试数据的操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--返回结果
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | nihaho     | li         | 222        | 2020       | 08         | 20         | 16         |
| 5          | tahao      | ha         | 333        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+
--向源表tmp_table1插入测试数据。
INSERT OVERWRITE TABLE tmp_table1 VALUES 
(1, 'hh', 'liu', '999', 'I'), (2, 'cc', 'zhang', '888', 'I'),
(3, 'cy', 'zhang', '666', 'I'),(4, 'hh', 'liu', '999', 'U'),
(5, 'cc', 'zhang', '888', 'U'),(6, 'cy', 'zhang', '666', 'U');
--查询源表的数据确认插入测试数据的操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
--返回结果
+------------+------------+------------+------------+--------------+
| id         | first_name | last_name  | phone      | _event_type_ |
+------------+------------+------------+------------+--------------+
| 1          | hh         | liu        | 999        | I            |
| 2          | cc         | zhang      | 888        | I            |
| 3          | cy         | zhang      | 666        | I            |
| 4          | hh         | liu        | 999        | U            |
| 5          | cc         | zhang      | 888        | U            |
| 6          | cy         | zhang      | 666        | U            |
+------------+------------+------------+------------+--------------+
--执行merge into操作。
MERGE INTO acid_address_book_base1 AS t USING tmp_table1 as s 
ON s.id = t.id AND t.year='2020' AND t.month='08' AND t.day='20' AND t.hour='16' 
WHEN MATCHED THEN UPDATE SET t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone 
WHEN NOT MATCHED AND (s._event_type_='I') THEN INSERT VALUES(s.id, s.first_name, s.last_name,s.phone,'2020','08','20','16');
--查询目标表的数据确认merge into操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--返回结果
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 5          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
| 1          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 2          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 3          | cy         | zhang      | 666        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+

示例 2：创建目标表 acid_target1 及源表 tmp_table2，并插入数据。执行 MERGE INTO 操作实现：对不符合 ON 条件并且源表中满足 name 为'Nika'的数据插入目标表；对不符合 ON 条件并且目标表中满足 name 为'Tommy'的数据删除；对不符合 ON 条件并且目标表中满足 name 为'Gene'的数据进行更新操作。

--创建目标表acid_target1。
CREATE TABLE IF NOT EXISTS acid_target1 
(id BIGINT, name STRING, phone STRING)  
tblproperties ("transactional"="true"); 
--创建源表tmp_table2。
CREATE TABLE IF NOT EXISTS tmp_table2 
(id BIGINT, name STRING, phone STRING);
--向目标表acid_target1插入测试数据。
INSERT OVERWRITE TABLE acid_target1 
VALUES (1, 'Tommy', '111'), (2, 'Gene', '222'), 
(3, 'Abram', '333');
--查询目标表的数据确认插入测试数据的操作结果。
SELECT * FROM acid_target1;
--返回结果
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 1          | Tommy | 111   |
| 2          | Gene | 222   |
| 3          | Abram | 333   |
+------------+------+-------+
--向源表tmp_table2插入测试数据。
INSERT OVERWRITE TABLE tmp_table2 
VALUES (6, 'Queen', '666'), (7, 'Nika', '777');
--查询源表的数据确认插入测试数据的操作结果。
SELECT * FROM tmp_table2;
--返回结果
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 6          | Queen | 666   |
| 7          | Nika | 777   |
+------------+------+-------+
--执行merge into操作。
--将源表中name为'Nika'的数据插入目标表。
--将目标表中name为'Tommy'的数据删除。
--对目标表中name为'Gene'的数据进行更新操作，将phone值从'222'更新为'999'。
MERGE INTO acid_target1 t
USING tmp_table2 s 
ON t.id = s.id
WHEN NOT MATCHED BY TARGET AND s.name = 'Nika'  THEN
  INSERT (id, name, phone) VALUES(s.id, s.name, s.phone)
WHEN NOT MATCHED BY SOURCE AND t.name = 'Tommy'  THEN
  DELETE
WHEN NOT MATCHED BY SOURCE AND t.name = 'Gene'  THEN
  UPDATE SET t.phone = '999';
--查询目标表的数据确认merge into操作结果。
SELECT * FROM acid_target1;
--返回结果
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 2          | Gene | 999   |
| 7          | Nika | 777   |
| 3          | Abram | 333  |
+------------+------+-------+

示例 3：创建目标表 merge_acid_dp 及源表 merge_acid_source，并插入数据。以不指定分区方式执行 MERGE INTO 命令，进行更新或者插入数据，对目标表的所有分区生效。

--创建目标表merge_acid_dp。
CREATE TABLE IF NOT EXISTS merge_acid_dp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");
--创建源表merge_acid_source。
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;
--向目标表merge_acid_dp插入测试数据。
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);
--查询目标表的数据确认插入测试数据的操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--返回结果
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+
--向源表merge_acid_source插入测试数据。
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');
--查询源表的数据确认插入测试数据的操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--返回结果
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+
--执行merge into操作。
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_dp tar USING merge_acid_source src 
ON tar.c2 = src.c2 
WHEN MATCHED THEN 
UPDATE SET tar.c1 = src.c1 
WHEN NOT MATCHED THEN 
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);
--查询目标表的数据确认merge into操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--返回结果
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 6          | 6          | 02 | 02 |
| 5          | 5          | 05 | 05 |
| 8          | 2          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
+------------+------------+----+----+

示例 4：创建目标表 merge_acid_sp 及源表 merge_acid_source，并插入数据。以指定分区方式执行 MERGE INTO 命令，进行更新或者插入数据，对目标表的指定分区生效。

--创建目标表merge_acid_sp。
CREATE TABLE IF NOT EXISTS merge_acid_sp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");
--创建源表merge_acid_source。
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;
--向目标表merge_acid_sp插入测试数据。
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);
--查询目标表的数据确认插入测试数据的操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
--返回结果
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+
--向源表merge_acid_source插入测试数据。
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');
--查询源表的数据确认插入测试数据的操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--返回结果
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+
--执行merge into操作，同时在on条件中指定只对目标表的dd = '01' and hh = '01'分区执行更新或者插入操作。
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_sp tar USING merge_acid_source src 
ON tar.c2 = src.c2 AND tar.dd = '01' AND tar.hh = '01' 
WHEN MATCHED THEN 
UPDATE SET tar.c1 = src.c1 
WHEN NOT MATCHED THEN 
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);
--查询目标表的数据确认merge into操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

示例 5：创建 Delta Table 类型目标表 mf_tt6 及源表 mf_delta，并插入数据。以指定分区方式执行 MERGE INTO 命令，进行更新、插入或删除数据，对目标表的指定分区生效。

--创建Transaction Table2.0类型目标表mf_tt6。
CREATE TABLE IF NOT EXISTS mf_tt6 (pk BIGINT NOT NULL PRIMARY key, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
--向目标表mf_tt6插入测试数据。
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='01') VALUES (1, 10), (2, 20), (3, 30);
--开启全表扫描，仅此Session有效。执行select语句查看表mf_tt6中的数据。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+
--创建源表mf_delta，并插入测试数据。
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);
--查询源表的数据，确认插入测试数据的操作结果。
SELECT * FROM mf_delta;
--返回结果
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+
--执行merge into操作，同时在on条件中指定只对目标表mf_tt6的dd = '01' and hh = '02'分区执行更新、插入或删除操作。
MERGE INTO mf_tt6 USING mf_delta 
ON mf_tt6.pk = mf_delta.pk AND mf_tt6.dd='01' AND mf_tt6.hh='02' 
WHEN MATCHED AND (mf_tt6.pk > 1) THEN 
UPDATE SET mf_tt6.val = mf_delta.val 
WHEN MATCHED THEN DELETE 
WHEN NOT MATCHED THEN 
INSERT VALUES (mf_delta.pk, mf_delta.val, '01', '02');         
--查询目标表的数据确认merge into操作结果。
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 3          | 3          | 01 | 02 |
| 6          | 60         | 01 | 02 |
| 2          | 20         | 01 | 02 |
+------------+------------+----+----+