Use MERGE INTO to combine INSERT, UPDATE, and DELETE commands - MaxCompute

Cuando necesita ejecutar operaciones INSERT, UPDATE o DELETE en una tabla transaccional o en una delta table, utilice la función MERGE INTO para combinarlas en una sola instrucción SQL. Esta instrucción ejecuta inserciones, actualizaciones o eliminaciones en la tabla transaccional de destino con base en los resultados de la unión con una tabla de origen. Además, requiere un único escaneo completo de la tabla, lo que mejora la eficiencia de ejecución.

Descripción general

MaxCompute es compatible con las operaciones DELETE y UPDATE. Sin embargo, si desea procesar por lotes una tabla de destino mediante varias instrucciones INSERT, UPDATE y DELETE, tendrá que escribir múltiples instrucciones SQL y realizar varios escaneos completos de la tabla. La función MERGE INTO de MaxCompute completa todas estas operaciones con un solo escaneo completo de la tabla. Por ello, su eficiencia de ejecución es mayor que la de INSERT+UPDATE+DELETE.

La operación MERGE INTO es atómica. Un trabajo se considera exitoso únicamente cuando todas sus operaciones INSERT, UPDATE y DELETE finalizan correctamente. Si alguna de ellas falla, el trabajo completo también falla.

Además, MERGE INTO evita un problema común al ejecutar INSERT, UPDATE y DELETE por separado: algunas operaciones pueden tener éxito mientras otras fallan, sin posibilidad de revertir las que ya se completaron.

Notas de uso

Antes de ejecutar una operación MERGE INTO, debe contar con los permisos Select y Update sobre la tabla transaccional de destino. Para obtener más información sobre cómo otorgar permisos, consulte MaxCompute permissions.
No es posible ejecutar varias operaciones INSERT o UPDATE sobre la misma fila dentro de una sola instrucción MERGE INTO.
La tabla de destino de una operación MERGE INTO debe ser una tabla transaccional.
Toda instrucción MERGE INTO debe incluir como mínimo una cláusula merge_action.
Si utiliza varias cláusulas not_matched_by_target_clause, a lo sumo una de ellas puede omitir search_condition.
Si utiliza varias cláusulas not_matched_by_source_clause, a lo sumo una de ellas puede omitir search_condition.
Si utiliza varias cláusulas matched_clause, a lo sumo una de ellas puede omitir search_condition.
Las subconsultas no son compatibles en merge_condition ni en search_condition.

Sintaxis

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	Obligatorio	Descripción
target_table	Sí	Nombre de la tabla de destino. La tabla debe existir.
alias_name_t	No	Alias de la tabla de destino.
source expression\|table_name	Sí	Origen de datos, que puede ser una tabla, una vista o una subconsulta.
alias_name_s	No	Alias de la tabla, vista o subconsulta de origen.
merge_condition	Sí	Expresión booleana.
search_condition	No	Expresión booleana que especifica una condición adicional para una acción `UPDATE`, `DELETE` o `INSERT`. Tenga en cuenta las siguientes reglas: Cuando hay tres cláusulas WHEN, `UPDATE`, `DELETE` e `INSERT` solo pueden aparecer una vez cada uno. Si aparecen tanto `UPDATE` como `DELETE`, la primera operación debe incluir `[AND <BOOLEAN expression>]`. La cláusula `WHEN NOT MATCHED` solo puede aparecer como la última cláusula `WHEN`.
set_clause_list	No	Datos que se van a actualizar. Este parámetro es obligatorio para una operación `UPDATE`. Para obtener más información sobre `UPDATE`, consulte UPDATE.
value_list	No	Datos que se van a insertar. Este parámetro es obligatorio para una operación `INSERT`. Para obtener más información sobre `VALUES`, consulte VALUES.
merge_action	Sí	Una `merge_action` puede ser de tres tipos: `MATCHED`, `NOT MATCHED BY TARGET` y `NOT MATCHED BY SOURCE`. Una acción se aplica a una fila únicamente cuando se cumplen tanto `merge_condition` como `search_condition`. Si una fila satisface las condiciones de varias cláusulas, solo se ejecuta la primera `merge_action` coincidente para esa fila.
matched_clause	No	Define la acción `UPDATE` o `DELETE` que se ejecutará en una fila de la tabla de destino cuando coincida con una fila de la tabla de origen.
not_matched_by_target_clause	No	Especifica la acción para las filas de origen que no coinciden con ninguna fila de destino.
not_matched_by_source_clause	No	Especifica la acción para las filas de destino que no tienen una fila de origen coincidente.
merge_insert_clause	No	La cláusula `merge_insert_clause` tiene tres opciones: `value_list`, `ROW` y . El parámetro `value_list` especifica los datos que se insertarán mediante una expresión `VALUES`. Tanto `ROW` como indican que los datos coincidentes de la tabla de origen se insertan directamente en la tabla de destino. Para usar `ROW` y `*`, las tablas de origen y de destino deben tener el mismo número de columnas con tipos de datos compatibles.

Ejemplos

Ejemplo 1: cree la tabla de destino acid_address_book_base1 y la tabla de origen tmp_table1, e inserte datos. Ejecute la operación MERGE INTO para actualizar la tabla de destino con los datos de la tabla de origen en los registros que cumplan la condición ON. También sirve para insertar datos en la tabla de destino cuando los registros no cumplan la condición ON y tengan un _event_type_ igual a I en la tabla de origen. A continuación se muestra un comando de ejemplo:

--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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

Ejemplo 2: cree una tabla de destino acid_target1 y una tabla de origen tmp_table2. Ejecute una operación MERGE INTO para insertar las filas de la tabla de origen donde name sea 'Nika', eliminar las filas de la tabla de destino donde name sea 'Tommy' y actualizar las filas de la tabla de destino donde name sea '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  |
+------------+------+-------+

Ejemplo 3: cree la tabla de destino merge_acid_dp y la tabla de origen merge_acid_source, e inserte datos. Ejecute el comando MERGE INTO sin especificar una partición para actualizar o insertar datos. De este modo, la operación se aplicará a todas las particiones de la tabla 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 |
+------------+------------+----+----+

Ejemplo 4: cree la tabla de destino merge_acid_sp y la tabla de origen merge_acid_source, e inserte datos. Ejecute el comando MERGE INTO para actualizar o insertar datos en una partición específica de la tabla 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 |
+------------+------------+----+----+

Ejemplo 5: cree una tabla de destino mf_tt6 de tipo Delta Table y una tabla de origen mf_delta, e inserte datos. Ejecute el comando MERGE INTO para actualizar, insertar o eliminar datos en una partición específica de la tabla 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 |
+------------+------------+----+----+