MERGE INTO
Aplica-se a: Databricks SQL
Databricks Runtime
Mescla um conjunto de atualizações, inserções e exclusões com base em uma tabela de origem em uma tabela Delta de destino.
Esta instrução é suportada apenas para tabelas Delta Lake.
Esta página contém detalhes para usar a sintaxe correta com o MERGE
comando. Consulte Upsert numa tabela Delta Lake usando mesclagem para obter mais orientações sobre como usar as operações de MERGE
para gerir os seus dados.
Sintaxe
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 } } [, ...] }
Parâmetros
WITH SCHEMA EVOLUTION
Aplica-se a:
Databricks Runtime 15.2 e superior
Permite a evolução automática do esquema para esta operação
MERGE
. Quando habilitado, o esquema da tabela Delta de destino é atualizado automaticamente para corresponder ao esquema da tabela de origem.-
Um nome de tabela que identifica a tabela que está a ser modificada. A tabela referenciada deve ser uma tabela Delta.
O nome não deve incluir uma especificação de opções.
A mesa não deve ser uma mesa estrangeira.
-
Um alias de tabela para a tabela de destino. O alias não deve incluir uma lista de colunas.
-
Um nome de tabela identificando a tabela de origem a ser mesclada na tabela de destino.
-
Um alias de tabela para a tabela de origem. O alias não deve incluir uma lista de colunas.
-
Como as linhas de uma relação são combinadas com as linhas de outra relação. Uma expressão com um tipo de retorno de BOOLEAN.
WHEN MATCHED [ AND
matched_condition]
WHEN MATCHED
cláusulas são executadas quando uma linha de origem corresponde a uma linha da tabela de destino com base nomerge_condition
e nomatch_condition
opcional.matched_action
DELETE
Exclui a linha da tabela de destino correspondente.
Várias correspondências são permitidas quando as correspondências são excluídas incondicionalmente. Uma exclusão incondicional não é ambígua, mesmo que haja várias correspondências.
UPDATE
Atualiza a linha correspondente da tabela de destino.
Para atualizar todas as colunas da tabela Delta de destino com as colunas correspondentes do conjunto de dados de origem, use
UPDATE SET *
. Isso é equivalente aUPDATE SET col1 = source.col1 [, col2 = source.col2 ...]
para todas as colunas da tabela Delta de destino. Portanto, essa ação pressupõe que a tabela de origem tenha as mesmas colunas que as da tabela de destino, caso contrário, a consulta lançará um erro de análise.Nota
Esse comportamento muda quando a evolução automática do esquema está habilitada. Consulte Evolução automática do esquema de mesclagem do Delta Lake para obter detalhes.
Aplica-se a:
Databricks SQL
Databricks Runtime 11.3 LTS e superior
Você pode especificar
DEFAULT
comoexpr
para atualizar explicitamente a coluna para seu valor padrão.
Se houver várias
WHEN MATCHED
cláusulas, elas são avaliadas na ordem em que são especificadas. CadaWHEN MATCHED
cláusula, exceto a última, deve ter ummatched_condition
. Caso contrário, a consulta retornará um erro de NON_LAST_MATCHED_CLAUSE_OMIT_CONDITION .Se nenhuma das
WHEN MATCHED
condições for avaliada como verdadeira para um par de linhas de origem e destino que corresponda aomerge_condition
, a linha de destino será mantida inalterada.WHEN NOT MATCHED [BY TARGET] [ AND
not_matched_condition]
WHEN NOT MATCHED
cláusulas inserem uma linha quando uma linha de origem não corresponde a nenhuma linha de destino com base nomerge_condition
e nonot_matched_condition
opcional.Aplica-se a:
Databricks SQL
Databricks Runtime 12.2 LTS e superior
WHEN NOT MATCHED BY TARGET
pode ser usado como um alias paraWHEN NOT MATCHED
.not_matched_condition
deve ser uma expressão booleana.INSERT *
Insere todas as colunas da tabela Delta de destino com as colunas correspondentes do conjunto de dados de origem. Isso é equivalente a
INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...])
para todas as colunas da tabela Delta de destino. Essa ação requer que a tabela de origem tenha as mesmas colunas da tabela de destino.Nota
Esse comportamento muda quando a evolução automática do esquema está habilitada. Consulte Evolução automática do esquema de mesclagem do Delta Lake para obter detalhes.
INSERT ( ... ) VALUES ( ... )
A nova linha é gerada com base na coluna especificada e nas expressões correspondentes. Todas as colunas na tabela de destino não precisam ser especificadas. Para colunas de destino não especificadas, o padrão da coluna é inserido ou, se não existir um padrão, é utilizado o
NULL
.Aplica-se a:
Databricks SQL
Databricks Runtime 11.3 LTS e superior
Você pode especificar
DEFAULT
como uma expressão para inserir explicitamente o padrão de coluna para uma coluna de destino.
Se houver várias
WHEN NOT MATCHED
cláusulas, elas são avaliadas na ordem em que são especificadas. Todas asWHEN NOT MATCHED
cláusulas, exceto a última, devem ternot_matched_condition
s. Caso contrário, a consulta retornará um erro NON_LAST_NOT_MATCHED_CLAUSE_OMIT_CONDITION .WHEN NOT MATCHED BY SOURCE [ AND
not_matched_by_source_condition]
Aplica-se a:
Databricks SQL
Databricks Runtime 12.2 LTS e superior
As cláusulas
WHEN NOT MATCHED BY SOURCE
são executadas quando uma linha de destino não corresponde a nenhuma linha na tabela de origem com base nomerge_condition
e onot_match_by_source_condition
opcional é avaliado como verdadeiro.not_matched_by_source_condition
deve ser uma expressão booleana que faça referência apenas a colunas da tabela de destino.not_matched_by_source_action
DELETE
Exclui a linha da tabela de destino.
UPDATE
Atualiza a linha da tabela alvo.
expr
só pode fazer referência a colunas da tabela de destino, caso contrário, a consulta gerará um erro de análise.Aplica-se a:
Databricks SQL
Databricks Runtime 11.3 LTS e superior
Você pode especificar
DEFAULT
comoexpr
para atualizar explicitamente a coluna para seu valor padrão.
Importante
Adicionar uma cláusula
WHEN NOT MATCHED BY SOURCE
para atualizar ou excluir linhas de destino quando omerge_condition
avalia como false pode levar a um grande número de linhas de destino sendo modificadas. Para obter o melhor desempenho, apliquenot_matched_by_source_condition
s para limitar o número de linhas de destino atualizadas ou excluídas.Se houver vários
WHEN NOT MATCHED BY SOURCE clauses
, então eles são avaliados na ordem em que são especificados. CadaWHEN NOT MATCHED BY SOURCE
cláusula, exceto a última, deve ter umnot_matched_by_source_condition
. Caso contrário, a consulta retornará um erro NON_LAST_NOT_MATCHED_BY_SOURCE_CLAUSE_OMIT_CONDITION .Se nenhuma das condições de
WHEN NOT MATCHED BY SOURCE
for avaliada como verdadeira para uma linha de destino que não corresponda a nenhuma linha na tabela de origem com base nomerge_condition
, a linha de destino será mantida inalterada.
Importante
As operações MERGE
falham com um erro de DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE quando mais de uma linha na tabela de origem corresponde à mesma linha na tabela de destino conforme as condições especificadas nas cláusulas ON
e WHEN MATCHED
. De acordo com a semântica SQL da mesclagem, esse tipo de operação de atualização é ambíguo porque não está claro qual linha de origem deve ser usada para atualizar a linha de destino correspondente. Você pode pré-processar a tabela de origem para eliminar a possibilidade de várias correspondências. Veja o exemplo de captura de dados de alteração. Este exemplo pré-processa o conjunto de dados de alteração (o conjunto de dados de origem) para reter apenas a alteração mais recente para cada chave antes de aplicar essa alteração na tabela Delta de destino. No Databricks Runtime 15.4 LTS e inferior, MERGE
só considera as condições na cláusula antes de ON
avaliar várias correspondências.
Exemplos
Você pode usar MERGE INTO
para operações complexas, como a desduplicação de dados, inserção ou atualização de dados de mudanças, aplicação de operações SCD Tipo 2, etc. Consulte a inserção ou atualização numa tabela Delta Lake usando uma junção com para obter alguns exemplos.
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