MERGE INTO

场景描述

在数据处理和分析的场景中，用户经常需要将一个数据源的数据合并到另一个数据源中，例如将增量数据合并到主表中。然而，在实际操作中，由于缺乏有效的单一语句支持，用户不得不使用多个语句组合来实现这一功能，这不仅降低了处理效率，还可能影响数据的一致性和准确性。如何在保证数据处理效率的同时，确保数据的一致性和准确性，成为了一个亟待解决的问题。为此，FabricSQL引入了对Iceberg表的MERGE INTO语法支持，允许用户通过单一语句实现数据的插入、更新或删除操作，从而简化了数据合并流程，提高了数据处理的效率和可靠性。

功能描述

MERGE INTO语句将目标表和源表中数据针对关联条件进行匹配，如果关联条件匹配时对目标表进行UPDATE或DELETE，不匹配时对目标表执行INSERT。MERGE INTO合并执行UPDATE和INSERT，避免多次执行以提高执行效率。

注意事项

• 执行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限，以及源表的SELECT权限。
• MERGE INTO的目标表仅支持Iceberg格式。

语法格式

MERGE [/*+ plan_hint */] INTO table_name [ partition_clause ] [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ partition_clause ] [ [ AS ] alias ]
ON ( condition )
{ when_matched_clause | when_not_matched_clause } [, ...]

where when_matched_clause can be:
WHEN MATCHED [AND condition] THEN
{ UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] | DELETE }

and when_not_matched_clause can be:
WHEN NOT MATCHED [AND condition] THEN
INSERT { DEFAULT VALUES | [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] }

参数说明

• plan_hint子句

  以/*+ */的形式在关键字后，用于对指定语句块生成的计划进行hint调优，详细用法请参见使用Plan Hint进行调优。

• INTO子句

  指定正在更新或插入的目标表。

  • table_name

    目标表的表名。

  • partition_clause

    用来指定目标表分区的子句。

    需要目标表已存在的分区，或者指定的分区键值对应的分区已存在。

  • alias

    目标表的别名。

• USING子句

  指定源表，源表可以是表、视图或子查询。

  • partition_clause

    用来指定源表分区的子句。

    只有当源表是分区表时可以指定分区，视图或者子查询不支持指定分区。

  • alias

    源表的别名。

• ON子句

  关联条件，用于指定目标表和源表的关联条件。不支持更新关联条件中的字段。

• WHEN MATCHED子句

  当源表和目标表中数据根据关联条件可以匹配上时，选择WHEN MATCHED子句进行UPDATE或DELETE操作。

  • 不支持更新分布列。不支持更新系统表、系统列。
  • 一行被多次更新时，会报错。
• WHEN NOT MATCHED子句

  当源表和目标表中数据根据关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。

  不支持INSERT子句中包含多个VALUES。

  WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换，可以缺省其中一个，但不能同时缺省，可以同时指定多个WHEN MATCHED或WHEN NOT MATCHED子句。

• DEFAULT

  用对应字段的缺省值填充该字段。

• AND condition

  WHEN MATCHED子句和WHEN NOT MATCHED子句的条件，只有在条件满足时才进行操作，可缺省。

示例

创建目标表products和源表newproducts，并插入数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE products
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
) STORE AS iceberg;

INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs'),
(1502, 'olympus is50', 'electrncs'),
(1600, 'play gym', 'toys'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'dvd');

CREATE TABLE newproducts
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
) STORE AS iceberg;

INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs'),
(1601, 'lamaze', 'toys'),
(1666, 'harry potter', 'toys'),
(1700, 'wait interface', 'books');

执行MERGE INTO操作，仅当product_id字段匹配且目标表product_name字段非'play gym' 并且源表product_name字段非'lamaze'时UPDATE，源表product_name字段为'lamaze'时DELETE；product_id字段不匹配且源表category字段为'books'时INSERT。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED AND p.product_name != 'play gym' and np.product_name != 'lamaze' THEN
  UPDATE SET product_name = np.product_name, category = np.category
WHEN MATCHED AND np.product_name = 'lamaze' THEN
  DELETE
WHEN NOT MATCHED AND np.category = 'books' THEN
  INSERT VALUES (np.product_id, np.product_name, np.category)
;

查询更新后的结果显示：目标表products的对应product_id字段1502、1666行相关数据已更新，1601行相关数据被删除，1700行为新插入的数据。

1
2
3
4
5
6
7
8
9

SELECT * FROM products ORDER BY product_id;
 product_id |  product_name  | category  
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1502 | olympus camera | electrncs
       1600 | play gym       | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(5 rows)