MERGE INTO

命令功能

通过MERGE INTO命令，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE或DELETE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部同步工作，执行效率要高于INSERT＋UPDATE。

注意事项

分区表合并需要设置参数spark.sql.forcePartitionPredicatesOnPartitionedTable.enabled为false。

命令格式

MERGE INTO [database_name.]table_name AS target_alias

USING (sub_query | tableIdentifier) AS source_alias

ON <merge_condition>

[ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ]

[ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ]

[ WHEN NOT MATCHED [ AND <condition> ] THEN <not_matched_action> ]

<merge_condition> =A equal bool condition

<matched_action> =

DELETE |

UPDATE SET * |

UPDATE SET column1 = expression1 [, column2 = expression2 ...]

<not_matched_action> =

INSERT * |

INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

参数描述

所需权限

• SQL权限

  表2 MERGE INTO所需权限列表

  权限描述
  表的UPDATE权限
  表的INSERT_INTO_TABLE权限
  表的DELETE权限

• 细粒度权限：dli:table:update, dli:table:insertIntoTable, dli:table:delete。
• 由LakeFormation提供的元数据服务，权限配置详见LakeFormation文档。

示例

• 部分字段更新

  create table h0(id int, comb int, name string, price int) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1);
  insert into s0 values(1, 1, 's1', 1),(2, 2, 's2', 2);
  //写法1
  merge into h0 using s0
  on h0.id = s0.id
  when matched then update set h0.id = s0.id, h0.comb = s0.comb, price = s0.price * 2;
  //写法2
  merge into h0 using s0
  on h0.id = s0.id
  when matched then update set id = s0.id,
  name = s0.name,
  comb = s0.comb + h0.comb,
  price = s0.price + h0.price;

• 缺省字段更新和插入

  create table h0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1, false);
  insert into s0 values(1, 2, 's1', 1, true);
  insert into s0 values(2, 2, 's2', 2, false);
  
  merge into h0 as target
  using (
  select id, comb, name, price, flag from s0
  ) source
  on target.id = source.id
  when matched then update set *
  when not matched then insert *;

• 多条件更新和删除

  create table h0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1, false);
  insert into h0 values(2, 2, 'h2', 1, false);
  insert into s0 values(1, 1, 's1', 1, true);
  insert into s0 values(2, 2, 's2', 2, false);
  insert into s0 values(3, 3, 's3', 3, false);
  
  merge into h0
  using (
  select id, comb, name, price, flag from s0
  ) source
  on h0.id = source.id
  when matched and h0.flag = false then update set id = source.id, comb = h0.comb + source.comb, price = source.price * 2
  when matched and h0.flag = true then delete
  when not matched then insert *;

系统响应

可在driver日志和客户端中查看命令运行成功或失败。