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 表的架构,以匹配源表的架构。-
一个表名称,用于识别要修改的表。 引用的表必须是 Delta 表。
该表不得为外表。
-
目标表的表别名。 该别名不得包含列的列表。
-
一个表名称,用于识别要合并到目标表的源表。
-
源表的表别名。 该别名不得包含列的列表。
-
如何将一个关系中的行与另一个关系中的行进行合并。 返回类型为布尔类型的表达式。
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