Use MERGE INTO to combine INSERT, UPDATE, and DELETE commands - MaxCompute: Central de Documentação do Alibaba Cloud

Ao aplicar operações de INSERT, UPDATE e DELETE em uma tabela transacional ou Delta table, executar esses comandos como instruções separadas exige múltiplas varreduras completas da tabela. O comando MERGE INTO combina as três operações em uma única instrução e varre a tabela de destino apenas uma vez. Isso reduz o tempo de execução e elimina o risco de falhas parciais irreversíveis.

Os casos de uso mais comuns incluem upsert de dados de origem em uma tabela de destino, aplicação de eventos de captura de dados alterados (CDC) e sincronização de partições.

Pré-requisitos

Antes de começar, verifique se você tem:

Permissões de Select e Update na tabela transacional de destino

Para configurar permissões, consulte Permissões do MaxCompute.

Funcionamento

O comando MERGE INTO associa uma tabela de origem (ou subconsulta) à tabela de destino usando a condição ON e aplica cada cláusula WHEN ao resultado da junção:

WHEN MATCHED: linhas da tabela de destino correspondentes a uma linha de origem — aplica UPDATE ou DELETE.
WHEN NOT MATCHED: linhas da tabela de origem sem correspondência na tabela de destino — aplica INSERT.

Todas as operações são executadas atomicamente: se qualquer operação falhar, toda a instrução será revertida e nenhuma alteração será confirmada. Isso garante consistência de forma que instruções separadas de INSERT, UPDATE e DELETE não conseguem oferecer — uma falha durante a execução de instruções separadas deixa alterações já confirmadas sem possibilidade de reversão.

Limitações

Uma única instrução MERGE INTO não pode executar múltiplas operações de INSERT ou UPDATE nas mesmas linhas.

Sintaxe

MERGE INTO <target_table> [AS <alias_name_t>]
USING <source_expression | table_name> [AS <alias_name_s>]
ON <boolean_expression1>
[ matchedClause [ matchedClause ] ]
[ notMatchedClause ]

matchedClause:

WHEN MATCHED [AND <boolean_expression>]
  THEN UPDATE SET <set_clause_list>
  | DELETE

notMatchedClause:

WHEN NOT MATCHED [AND <boolean_expression>]
  THEN INSERT VALUES <value_list>

Parâmetros

Parâmetros principais

Parâmetro	Obrigatório	Descrição
`target_table`	Sim	Nome de uma tabela de destino existente
`alias_name_t`	Não	Alias para a tabela de destino
`source_expression	table_name`	Sim	Tabela de origem, visualização ou subconsulta para associar à tabela de destino
`alias_name_s`	Não	Alias para a tabela de origem, visualização ou subconsulta
`boolean_expression1`	Sim	Condição de junção que retorna um valor booleano (True ou False)

Parâmetros da cláusula WHEN

Parâmetro	Obrigatório	Descrição
`boolean_expression` (matched)	Não	Filtro adicional aplicado à ramificação de UPDATE ou DELETE
`boolean_expression` (not matched)	Não	Filtro adicional aplicado à ramificação de INSERT
`set_clause_list`	Obrigatório para UPDATE	Colunas e valores a atualizar. Consulte a seção "UPDATE" em UPDATE e DELETE
`value_list`	Obrigatório para INSERT	Valores a inserir. Consulte VALUES

Notas de uso para cláusulas WHEN:

Uma instrução pode incluir no máximo uma cláusula UPDATE, uma DELETE e uma INSERT.
Se UPDATE e DELETE estiverem presentes simultaneamente, adicione uma condição AND <boolean_expression> à ramificação que deve ser avaliada primeiro para evitar correspondência ambígua de linhas.
A cláusula WHEN NOT MATCHED deve ser a última cláusula WHEN e suporta apenas INSERT.

Exemplos

Os exemplos a seguir abordam os padrões mais comuns: upsert, mesclagem com escopo de partição e mesclagem de Delta table com UPDATE e DELETE condicionais.

Exemplo 1: Upsert de linhas usando uma coluna de tipo de evento

Este exemplo atualiza linhas correspondentes e insere linhas não correspondentes onde _event_type_ é I (evento de inserção). Esse padrão é típico para aplicar eventos de CDC de uma tabela de staging.

-- Create a target transactional table partitioned by date and hour.
CREATE TABLE IF NOT EXISTS acid_address_book_base1
(id BIGINT, first_name STRING, last_name STRING, phone STRING)
PARTITIONED BY (year STRING, month STRING, day STRING, hour STRING)
tblproperties ("transactional"="true");

-- Create a source table with an event-type column.
CREATE TABLE IF NOT EXISTS tmp_table1
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

-- Load test data into the target table.
INSERT OVERWRITE TABLE acid_address_book_base1
PARTITION(year='2020', month='08', day='20', hour='16')
VALUES (4, 'nihaho', 'li', '222'), (5, 'tahao', 'ha', '333'),
(7, 'djh', 'hahh', '555');

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Return results
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | nihaho     | li         | 222        | 2020       | 08         | 20         | 16         |
| 5          | tahao      | ha         | 333        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+

-- Load test data into the source table.
INSERT OVERWRITE TABLE tmp_table1 VALUES
(1, 'hh', 'liu', '999', 'I'), (2, 'cc', 'zhang', '888', 'I'),
(3, 'cy', 'zhang', '666', 'I'), (4, 'hh', 'liu', '999', 'U'),
(5, 'cc', 'zhang', '888', 'U'), (6, 'cy', 'zhang', '666', 'U');

-- Verify the source table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
-- Return results
+------------+------------+------------+------------+--------------+
| id         | first_name | last_name  | phone      | _event_type_ |
+------------+------------+------------+------------+--------------+
| 1          | hh         | liu        | 999        | I            |
| 2          | cc         | zhang      | 888        | I            |
| 3          | cy         | zhang      | 666        | I            |
| 4          | hh         | liu        | 999        | U            |
| 5          | cc         | zhang      | 888        | U            |
| 6          | cy         | zhang      | 666        | U            |
+------------+------------+------------+------------+--------------+

-- Run MERGE INTO: update matched rows, insert unmatched rows with event type 'I'.
MERGE INTO acid_address_book_base1 AS t USING tmp_table1 AS s
ON s.id = t.id AND t.year='2020' AND t.month='08' AND t.day='20' AND t.hour='16'
WHEN MATCHED THEN UPDATE SET t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone
WHEN NOT MATCHED AND (s._event_type_='I') THEN INSERT VALUES(s.id, s.first_name, s.last_name, s.phone, '2020', '08', '20', '16');

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Return results
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 5          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
| 1          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 2          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 3          | cy         | zhang      | 666        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+

Nota

A condição WHEN NOT MATCHED AND (s._event_type_='I') filtra eventos do tipo atualização (U) para impedir sua inserção. Linhas com _event_type_='U' sem correspondência na tabela de destino são ignoradas silenciosamente. Se seu fluxo de CDC contiver tipos de evento além de I e U, adicione filtros explícitos a cada ramificação para tratá-los corretamente.

Exemplo 2: Mesclagem em todas as partições

Sem restrições de partição na cláusula ON, o comando MERGE INTO opera em todas as partições da tabela de destino.

-- Create the target table.
CREATE TABLE IF NOT EXISTS merge_acid_dp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");

-- Create the source table.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

-- Load test data into the target table.
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

-- Load test data into the source table.
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

-- Verify the source table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Return results
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Run MERGE INTO across all partitions.
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_dp tar USING merge_acid_source src
ON tar.c2 = src.c2
WHEN MATCHED THEN
UPDATE SET tar.c1 = src.c1
WHEN NOT MATCHED THEN
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 6          | 6          | 02 | 02 |
| 5          | 5          | 05 | 05 |
| 8          | 2          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
+------------+------------+----+----+

Nota

A cláusula ON faz a junção apenas por c2, sem filtro de partição. Isso aciona uma varredura completa da tabela em ambas as partições. Para tabelas grandes, adicione colunas de partição à cláusula ON (consulte o Exemplo 3) para limitar o escopo da varredura e melhorar o desempenho.

Exemplo 3: Mesclagem em uma partição específica

Adicione colunas de partição à cláusula ON para restringir a mesclagem a uma partição específica. Linhas fora da partição especificada não são afetadas.

-- Create the target table.
CREATE TABLE IF NOT EXISTS merge_acid_sp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");

-- Create the source table.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

-- Load test data into the target table.
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

-- Load test data into the source table.
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

-- Verify the source table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Return results
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Run MERGE INTO scoped to partition dd='01', hh='01'.
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_sp tar USING merge_acid_source src
ON tar.c2 = src.c2 AND tar.dd = '01' AND tar.hh = '01'
WHEN MATCHED THEN
UPDATE SET tar.c1 = src.c1
WHEN NOT MATCHED THEN
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

Nota

O filtro de partição na cláusula ON (tar.dd = '01' AND tar.hh = '01') limita o escopo de correspondência — linhas na partição dd='02', hh='02' não têm correspondência. No entanto, linhas de origem sem correspondência (aquelas sem par em dd='01', hh='01') ainda são inseridas, e sua partição de destino é determinada pelos valores em value_list. Neste exemplo, as novas linhas vão para novas partições (05/05 e 02/02) com base nos valores de origem.

Exemplo 4: UPDATE, DELETE e INSERT condicionais em uma Delta table

Este exemplo usa uma Delta table como destino e aplica as três operações em uma única instrução. Uma condição adicional na ramificação de correspondência determina se uma linha correspondente é atualizada ou excluída.

-- Create a Delta table with a primary key.
CREATE TABLE IF NOT EXISTS mf_tt6 (pk BIGINT NOT NULL PRIMARY KEY,
                  val BIGINT NOT NULL)
                  PARTITIONED BY (dd STRING, hh STRING)
                  tblproperties ("transactional"="true");

-- Load test data into the target table.
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='01') VALUES (1, 10), (2, 20), (3, 30);

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Return results
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

-- Create the source table.
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

-- Verify the source table.
SELECT * FROM mf_delta;
-- Return results
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

-- Run MERGE INTO scoped to partition dd='01', hh='02'.
-- Matched rows where pk > 1 are updated; matched rows where pk = 1 are deleted.
-- Unmatched rows are inserted into the partition.
MERGE INTO mf_tt6 USING mf_delta
ON mf_tt6.pk = mf_delta.pk AND mf_tt6.dd='01' AND mf_tt6.hh='02'
WHEN MATCHED AND (mf_tt6.pk > 1) THEN
UPDATE SET mf_tt6.val = mf_delta.val
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
INSERT VALUES (mf_delta.pk, mf_delta.val, '01', '02');

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Return results
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 3          | 3          | 01 | 02 |
| 6          | 60         | 01 | 02 |
| 2          | 20         | 01 | 02 |
+------------+------------+----+----+

Nota

Quando duas cláusulas WHEN MATCHED estão presentes, elas são avaliadas em ordem. A primeira cláusula correspondente prevalece. Neste exemplo, linhas com pk > 1 correspondem à primeira cláusula e são atualizadas; a linha correspondente restante (pk = 1) passa para a segunda cláusula e é excluída. Coloque a condição mais específica primeiro.