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

Ao precisar executar operações de INSERT, UPDATE ou DELETE em uma tabela transacional ou delta table, use o recurso MERGE INTO para combinar essas operações em uma única instrução SQL. Com base nos resultados da junção com uma tabela de origem, essa instrução insere, atualiza ou exclui dados na tabela transacional de destino. Além disso, requer apenas uma varredura completa da tabela, o que aumenta a eficiência da execução.

Visão geral

O MaxCompute oferece suporte a operações de DELETE e UPDATE. No entanto, para executar operações em lote em uma tabela de destino usando várias instruções INSERT, UPDATE e DELETE, é necessário escrever várias instruções SQL e, consequentemente, realizar múltiplas varreduras completas da tabela. O recurso MERGE INTO no MaxCompute requer apenas uma única varredura completa para concluir todas as operações, apresentando uma eficiência de execução superior à combinação de INSERT+UPDATE+DELETE.

A operação MERGE INTO é atômica. Um job só é considerado bem-sucedido quando todas as suas operações de INSERT, UPDATE e DELETE são concluídas com êxito. Se qualquer uma dessas operações falhar, o job inteiro falhará.

Além disso, o MERGE INTO evita um problema que pode ocorrer quando as operações de INSERT, UPDATE e DELETE são executadas separadamente. Nesse cenário, algumas operações podem ter êxito enquanto outras falham, e as operações bem-sucedidas não podem ser revertidas.

Notas de uso

Antes de executar uma operação de MERGE INTO, você deve ter as permissões Select e Update na tabela transacional de destino. Para obter mais informações sobre como conceder permissões, consulte MaxCompute permissions.
Não é possível executar múltiplas operações de INSERT ou UPDATE na mesma linha em uma única instrução MERGE INTO.
A tabela de destino para uma operação MERGE INTO deve ser uma tabela transacional.
Uma instrução MERGE INTO deve incluir pelo menos uma cláusula merge_action.
Ao usar várias cláusulas not_matched_by_target_clause, no máximo uma delas pode omitir a search_condition.
Ao utilizar múltiplas cláusulas not_matched_by_source_clause, apenas uma pode omitir a search_condition.
Se houver várias cláusulas matched_clause, a search_condition pode ser omitida em no máximo uma delas.
Subconsultas não são suportadas na merge_condition e na search_condition.

Sintaxe

MERGE INTO <target_table> AS <alias_name_t>
USING <source expression | table_name> AS <alias_name_s>
--Specifies the join condition to match rows between the source and target tables.
ON merge_condition
{ merge_action } +

merge_action ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause

--Specifies the action to perform when rows match based on the ON condition. The data sets processed by multiple WHEN MATCHED clauses cannot overlap.
matched_clause ::= WHEN MATCHED [ AND <search_condition> ] THEN { merge_update_clause | merge_delete_clause }

--Specifies the action to perform when a row in the source does not match any row in the target.
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND <search_condition> ] THEN merge_insert_clause

--Specifies the action to perform when a row in the target does not match any row in the source.
not_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND <search_condition> ] THEN  { merge_update_clause | merge_delete_clause }

merge_condition ::= BOOLEAN expression

search_condition ::= BOOLEAN expression

merge_update_clause ::= UPDATE SET <set_clause_list>

merge_delete_clause ::= DELETE

merge_insert_clause ::= INSERT <value_list> | ROW | *

Parâmetros

Parâmetro	Obrigatório	Descrição
target_table	Sim	O nome da tabela de destino. A tabela já deve existir.
alias_name_t	Não	Um alias para a tabela de destino.
source expression\|table_name	Sim	A origem dos dados, que pode ser uma tabela, visualização (view) ou subconsulta.
alias_name_s	Não	Um alias para a tabela, visualização ou subconsulta de origem.
merge_condition	Sim	Uma expressão booleana.
search_condition	Não	Uma expressão booleana que especifica uma condição adicional para uma ação de `UPDATE`, `DELETE` ou `INSERT`. Observe as seguintes regras: Quando há três cláusulas WHEN, `UPDATE`, `DELETE` e `INSERT` podem aparecer apenas uma vez cada. Se `UPDATE` e `DELETE` ocorrerem juntos, a primeira operação deve incluir `[AND <BOOLEAN expression>]`. A cláusula `WHEN NOT MATCHED` pode aparecer apenas como a última cláusula `WHEN`.
set_clause_list	Não	Os dados a serem atualizados. Este parâmetro é obrigatório para uma operação de `UPDATE`. Para obter mais informações sobre o `UPDATE`, consulte UPDATE.
value_list	Não	Os dados a serem inseridos. Este parâmetro é obrigatório para uma operação de `INSERT`. Para obter mais informações sobre `VALUES`, consulte VALUES.
merge_action	Sim	Uma `merge_action` pode ser de três tipos: `MATCHED`, `NOT MATCHED BY TARGET` e `NOT MATCHED BY SOURCE`. Uma ação se aplica a uma linha somente se a `merge_condition` e a `search_condition` forem atendidas. Se uma linha satisfizer as condições de múltiplas cláusulas, apenas a primeira `merge_action` correspondente será executada para essa linha.
matched_clause	Não	Define a ação de `UPDATE` ou `DELETE` a ser executada em uma linha da tabela de destino quando ela corresponder a uma linha da tabela de origem.
not_matched_by_target_clause	Não	Especifica a ação para as linhas de origem que não correspondem a nenhuma linha de destino.
not_matched_by_source_clause	Não	Especifica a ação para as linhas de destino que não possuem uma linha de origem correspondente.
merge_insert_clause	Não	A `merge_insert_clause` possui três opções: `value_list`, `ROW` e . A `value_list` especifica os dados a serem inseridos usando uma expressão `VALUES`. Tanto `ROW` quanto indicam que os dados correspondentes da tabela de origem são inseridos diretamente na tabela de destino. Para `ROW` e `*`, as tabelas de origem e destino devem ter o mesmo número de colunas com tipos de dados compatíveis.

Exemplos

Exemplo 1: Crie a tabela de destino acid_address_book_base1 e a tabela de origem tmp_table1 e insira dados. Execute a operação de MERGE INTO para atualizar a tabela de destino com os dados da tabela de origem para os registros que correspondem à condição ON, e insira dados na tabela de destino para os registros que não correspondem à condição ON e possuem um _event_type_ igual a I na tabela de origem. O comando de exemplo é o seguinte:

--Create the target table acid_address_book_base1.
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 the source table tmp_table1.
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

--Insert sample data into the target table acid_address_book_base1.
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');

--Query the target table to verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--Result:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

--Insert sample data into the source table tmp_table1.
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');

--Query the source table to verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
--Result:
+------------+------------+------------+------------+--------------+
| 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 the MERGE INTO operation.
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');

--Query the target table to verify the result of the MERGE INTO operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--Result:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

Exemplo 2: Crie uma tabela de destino acid_target1 e uma tabela de origem tmp_table2. Execute uma operação MERGE INTO para inserir linhas da tabela de origem onde name é 'Nika', excluir linhas na tabela de destino onde name é 'Tommy' e atualizar linhas na tabela de destino onde name é 'Gene'.

--Create the target table acid_target1.
CREATE TABLE IF NOT EXISTS acid_target1 
(id BIGINT, name STRING, phone STRING)  
tblproperties ("transactional"="true"); 

--Create the source table tmp_table2.
CREATE TABLE IF NOT EXISTS tmp_table2 
(id BIGINT, name STRING, phone STRING);

--Insert sample data into the target table acid_target1.
INSERT OVERWRITE TABLE acid_target1 
VALUES (1, 'Tommy', '111'), (2, 'Gene', '222'), 
(3, 'Abram', '333');

--Query the target table to verify the result of the insert operation.
SELECT * FROM acid_target1;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 1          | Tommy | 111   |
| 2          | Gene | 222   |
| 3          | Abram | 333   |
+------------+------+-------+

--Insert sample data into the source table tmp_table2.
INSERT OVERWRITE TABLE tmp_table2 
VALUES (6, 'Queen', '666'), (7, 'Nika', '777');

--Query the source table to verify the result of the insert operation.
SELECT * FROM tmp_table2;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 6          | Queen | 666   |
| 7          | Nika | 777   |
+------------+------+-------+

--Run the MERGE INTO operation.
--Insert rows from the source table where name is 'Nika' into the target table.
--Delete rows from the target table where name is 'Tommy'.
--Update the phone number from '222' to '999' for rows where name is 'Gene' in the target table.
MERGE INTO acid_target1 t
USING tmp_table2 s 
ON t.id = s.id
WHEN NOT MATCHED BY TARGET AND s.name = 'Nika'  THEN
  INSERT (id, name, phone) VALUES(s.id, s.name, s.phone)
WHEN NOT MATCHED BY SOURCE AND t.name = 'Tommy'  THEN
  DELETE
WHEN NOT MATCHED BY SOURCE AND t.name = 'Gene'  THEN
  UPDATE SET t.phone = '999';

--Query the target table to verify the result of the MERGE INTO operation.
SELECT * FROM acid_target1;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 2          | Gene | 999   |
| 7          | Nika | 777   |
| 3          | Abram | 333  |
+------------+------+-------+

Exemplo 3: Crie a tabela de destino merge_acid_dp e a tabela de origem merge_acid_source e insira dados. Execute o comando MERGE INTO sem especificar uma partição para atualizar ou inserir dados, o que se aplica a todas as partições da tabela de destino.

--Create the target table merge_acid_dp.
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 merge_acid_source.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

--Insert sample data into the target table merge_acid_dp.
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);

--Query the target table to verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--Result:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

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

--Query the source table to verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--Result:
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--Run the MERGE INTO operation.
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);

--Query the target table to verify the result of the MERGE INTO operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--Result:
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+

Exemplo 4: Crie a tabela de destino merge_acid_sp e a tabela de origem merge_acid_source e insira dados. Execute o comando MERGE INTO para atualizar ou inserir dados em uma partição específica da tabela de destino.

--Create the target table merge_acid_sp.
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 merge_acid_source.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

--Insert sample data into the target table merge_acid_sp.
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);

--Query the target table to verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
--Result:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

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

--Query the source table to verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--Result:
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--Run the MERGE INTO operation and specify in the ON condition to update or insert data only in the partition where dd = '01' and 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);

--Query the target table to verify the result of the MERGE INTO operation.
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 |
+------------+------------+----+----+

Exemplo 5: Crie uma tabela de destino mf_tt6 do tipo Delta Table e uma tabela de origem mf_delta e insira dados. Execute o comando MERGE INTO para atualizar, inserir ou excluir dados em uma partição específica da tabela de destino.

--Create the target table mf_tt6.
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");
--Insert sample data into the target table mf_tt6.
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);

--Enable full table scan for the current session. Then, run a SELECT statement to view the data in the mf_tt6 table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--Result:
+------------+------------+----+----+
| 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 mf_delta and insert sample data.
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

--Query the source table to verify the result of the insert operation.
SELECT * FROM mf_delta;
--Result:
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

--Run the MERGE INTO operation, specifying in the ON condition to update, insert, or delete data only in the partition of mf_tt6 where dd = '01' and hh = '02'.

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');         

--Query the target table to verify the result of the MERGE INTO operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--Result:
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+