MERGE INTO

适用于: 勾选“是” Databricks SQL 勾选“是” Databricks Runtime

基于源表将一组更新、插入和删除操作合并到目标 Delta 表中。

只有 Delta Lake 表支持此语句。

本页包含有关使用 MERGE 命令的正确语法的详细信息。 有关如何使用 MERGE 操作来管理数据的更多指导,请参阅使用合并更新插入到 Delta Lake 表

语法

MERGE [ WITH SCHEMA EVOLUTION ] INTO target_table_name [target_alias]
   USING source_table_reference [source_alias]
   ON merge_condition
   { WHEN MATCHED [ AND matched_condition ] THEN matched_action |
     WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition ] THEN not_matched_action |
     WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition ] THEN not_matched_by_source_action } [...]

matched_action
 { DELETE |
   UPDATE SET * |
   UPDATE SET { column = { expr | DEFAULT } } [, ...] }

not_matched_action
 { INSERT * |
   INSERT (column1 [, ...] ) VALUES ( expr | DEFAULT ] [, ...] )

not_matched_by_source_action
 { DELETE |
   UPDATE SET { column = { expr | DEFAULT } } [, ...] }

参数

  • WITH SCHEMA EVOLUTION

    适用于勾选“是”Databricks Runtime 15.2 及更高版本

    为此MERGE操作启用自动架构演变功能。 启用后,将自动更新目标 Delta 表的架构,以匹配源表的架构。

  • target_table_name

    一个表名称,用于识别要修改的表。 引用的表必须是 Delta 表。

    该表不得为外表。

  • target_alias

    目标表的表别名。 该别名不得包含列的列表。

  • source_table_reference

    一个表名称,用于识别要合并到目标表的源表。

  • source_alias

    源表的表别名。 该别名不得包含列的列表。

  • ON merge_condition

    如何将一个关系中的行与另一个关系中的行进行合并。 返回类型为布尔类型的表达式。

  • WHEN MATCHED [ AND matched_condition]

    当源行根据 merge_condition 和可选的 match_condition 与目标表行匹配时,将执行 WHEN MATCHED 子句。

  • matched_action

    • DELETE

      删除匹配的目标表行。

      无条件删除匹配项时,允许多个匹配项。 即使有多个匹配项,无条件删除也是明确的。

    • UPDATE

      更新匹配的目标表行。

      若要使用源数据集的相应列更新目标 Delta 表的所有列,请使用 UPDATE SET *。 这等效于目标 Delta 表的所有列 UPDATE SET col1 = source.col1 [, col2 = source.col2 ...]。 因此,此操作假定源表的列与目标表的列相同,否则查询将引发分析错误。

      注意

      启用自动架构迁移后,此行为将发生变化。 有关详细信息,请参阅 Delta Lake 合并的自动架构演变

      适用于: 勾选“是” Databricks SQL 勾选“是” Databricks Runtime 11.3 LTS 及更高版本

      可以指定 DEFAULT 作为 expr 以将列显式更新为其默认值。

    如果存在多个 WHEN MATCHED 子句,则会按照它们的指定顺序对其进行求值。 每个 WHEN MATCHED 子句(最后一个除外)都必须具有一个 matched_condition。 否则,查询将返回 NON_LAST_MATCHED_CLAUSE_OMIT_CONDITION 错误。

    如果对于匹配 merge_condition 的源行和目标行对,没有任何 WHEN MATCHED 条件的计算结果为 true,则目标行保持不变。

  • WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition]

    如果源行根据 merge_condition 和可选的 not_matched_condition 与任何目标行都不匹配,WHEN NOT MATCHED 子句将插入一行。

    适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime 12.2 LTS 及更高版本

    WHEN NOT MATCHED BY TARGET 可用作 WHEN NOT MATCHED 的别名。

    not_matched_condition 必须为布尔表达式。

    • INSERT *

      使用源数据集的相应列插入目标 Delta 表的所有列。 这等效于目标 Delta 表的所有列 INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...])。 此操作要求源表与目标表中的列相同。

      注意

      启用自动架构迁移后,此行为将发生变化。 有关详细信息,请参阅 Delta Lake 合并的自动架构演变

    • INSERT ( ... ) VALUES ( ... )

      新行是基于指定的列和相应的表达式生成的。 你无需指定目标表中的所有列。 对于未指定的目标列,将插入列默认值,如果不存在,则为 NULL

      适用于: 勾选“是” Databricks SQL 勾选“是” Databricks Runtime 11.3 LTS 及更高版本

      可以指定 DEFAULT 作为表达式来显式插入目标列的列默认值。

    如果存在多个 WHEN NOT MATCHED 子句,则会按照它们的指定顺序对其进行求值。 所有 WHEN NOT MATCHED 子句(最后一个除外)都必须具有多个 not_matched_condition。 否则,查询将返回 NON_LAST_NOT_MATCHED_CLAUSE_OMIT_CONDITION 错误。

  • WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition]

    适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime 12.2 LTS 及更高版本

    当目标行与源表中的任何行都不匹配(根据 merge_condition 进行匹配)并且可选的 not_match_by_source_condition 计算为 true 时,将执行 WHEN NOT MATCHED BY SOURCE 子句。

    not_matched_by_source_condition 必须是仅引用目标表中的列的布尔表达式。

  • not_matched_by_source_action

    • DELETE

      删除目标表行。

    • UPDATE

      更新目标表行。 expr 只能引用目标表中的列,否则查询将引发分析错误。

      适用于: 勾选“是” Databricks SQL 勾选“是” Databricks Runtime 11.3 LTS 及更高版本

      可以指定 DEFAULT 作为 expr 以将列显式更新为其默认值。

    重要

    添加 WHEN NOT MATCHED BY SOURCE 子句以便在 merge_condition 计算为 false 时更新或删除目标行可能会导致修改大量的目标行。 为获得最佳性能,请应用 not_matched_by_source_condition 来限制更新或删除的目标行数。

    如果有多个 WHEN NOT MATCHED BY SOURCE clauses,将按照其指定顺序对其进行计算。 每个 WHEN NOT MATCHED BY SOURCE 子句(最后一个除外)都必须具有一个 not_matched_by_source_condition。 否则,查询将返回 NON_LAST_NOT_MATCHED_BY_SOURCE_CLAUSE_OMIT_CONDITION 错误。

    对于不与源表中的任何行匹配的目标行(根据 merge_condition 进行匹配),如果没有任何 WHEN NOT MATCHED BY SOURCE 条件计算为 true,则目标行保持不变。

重要

如果源数据集的多个行匹配并尝试更新目标 Delta 表的相同行,则 MERGE 操作可能失败并发生 DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE 错误。 根据合并的 SQL 语义,这种更新操作模棱两可,因为尚不清楚应使用哪个源行来更新匹配的目标行。 你可以预处理源表来消除出现多个匹配项的可能性。 请参阅变更数据捕获示例 - 它对变更数据集(即源数据集)进行预处理,以仅保留每键的最新更改,然后再将更改应用到目标 Delta 表中。

示例

可以将 MERGE INTO 用于复杂的操作,如删除重复数据、更新插入更改数据、应用 SCD 类型 2 操作等。请参阅使用合并以更新插入的方式插入到 Delta Lake 表中获取一些示例。

WHEN MATCHED

-- Delete all target rows that have a match in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED THEN DELETE

-- Conditionally update target rows that have a match in the source table using the source value.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.updated_at < source.updated_at THEN UPDATE SET *

-- Multiple MATCHED clauses conditionally deleting matched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.marked_for_deletion THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.updated_at = source.updated_at, target.value = DEFAULT

WHEN NOT MATCHED [BY TARGET]

-- Insert all rows from the source that are not already in the target table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED THEN INSERT *

-- Conditionally insert new rows in the target table using unmatched rows from the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY TARGET AND source.created_at > now() - INTERVAL "1" DAY THEN INSERT (created_at, value) VALUES (source.created_at, DEFAULT)

WHEN NOT MATCHED BY SOURCE

-- Delete all target rows that have no matches in the source table.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE THEN DELETE

-- Multiple NOT MATCHED BY SOURCE clauses conditionally deleting unmatched target rows and updating two columns for all other matched rows.
> MERGE INTO target USING source
  ON target.key = source.key
  WHEN NOT MATCHED BY SOURCE AND target.marked_for_deletion THEN DELETE
  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET target.value = DEFAULT

WITH SCHEMA EVOLUTION

-- Multiple MATCHED and NOT MATCHED clauses with schema evolution enabled.
> MERGE WITH SCHEMA EVOLUTION INTO target USING source
  ON source.key = target.key
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *
  WHEN NOT MATCHED BY SOURCE THEN DELETE