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

İşlemsel bir tabloda veya delta tabloda INSERT, UPDATE veya DELETE işlemleri yapmanız gerektiğinde, bu işlemleri tek bir SQL ifadesinde birleştirmek için MERGE INTO özelliğini kullanabilirsiniz. Bu ifade, kaynak tablo ile yapılan birleştirme sonuçlarına göre hedef işlemsel tabloda ekleme, güncelleme veya silme işlemleri yapar ve yalnızca tek bir tam tablo taraması gerektirir; bu da çalıştırma verimliliğini artırır.

Genel Bakış

MaxCompute, DELETE ve UPDATE işlemlerini destekler. Ancak, birden fazla INSERT, UPDATE ve DELETE deyimi kullanarak hedef tabloda toplu işlemler yapmak isterseniz, birden çok SQL deyimi yazmanız ve ardından birden fazla tam tablo taraması yapmanız gerekir. MaxCompute'deki MERGE INTO özelliği, tüm işlemleri tamamlamak için yalnızca tek bir tam tablo taraması gerektirir ve çalıştırma verimliliği INSERT+UPDATE+DELETE kombinasyonundan daha yüksektir.

MERGE INTO işlemi atomiktir. Bir işin başarılı sayılması için içindeki tüm INSERT, UPDATE ve DELETE işlemlerinin başarılı olması gerekir. Bu işlemlerden herhangi biri başarısız olursa, tüm iş başarısız olur.

Ayrıca, MERGE INTO, INSERT, UPDATE ve DELETE işlemlerinin ayrı ayrı çalıştırılması durumunda ortaya çıkabilecek bir sorunu önler. Ayrı çalıştırmada bazı işlemler başarılı olurken diğerleri başarısız olabilir ve başarılı olan işlemler geri alınamaz.

Kullanım notları

Bir MERGE INTO işlemi yapmadan önce, hedef işlemsel tabloda Select ve Update izinlerine sahip olmanız gerekir. İzinlerin nasıl verileceği hakkında daha fazla bilgi için bkz. MaxCompute izinleri.
Tek bir MERGE INTO deyimi içinde aynı satır üzerinde birden fazla INSERT veya UPDATE işlemi yapamazsınız.
MERGE INTO işlemi için hedef tablo bir işlemsel tablo olmalıdır.
Bir MERGE INTO deyimi en az bir merge_action yan tümcesi içermelidir.
Birden fazla not_matched_by_target_clause yan tümcesi kullanırsanız, bunlardan en fazla biri search_condition öğesini atlayabilir.
Birden fazla not_matched_by_source_clause yan tümcesi kullanırsanız, bunlardan en fazla biri search_condition öğesini atlayabilir.
Birden fazla matched_clause yan tümcesi kullanırsanız, bunlardan en fazla biri search_condition öğesini atlayabilir.
merge_condition ve search_condition içinde alt sorgular desteklenmez.

Söz dizimi

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

Parametreler

Parametre	Zorunlu	Açıklama
target_table	Evet	Hedef tablonun adı. Tablo mevcut olmalıdır.
alias_name_t	Hayır	Hedef tablo için bir takma ad.
kaynak ifadesi\|tablo_adı	Evet	Veri kaynağı; bir tablo, görünüm veya alt sorgu olabilir.
alias_name_s	Hayır	Kaynak tablo, görünüm veya alt sorgu için bir takma ad.
merge_condition	Evet	Boolean ifadesi.
search_condition	Hayır	`UPDATE`, `DELETE` veya `INSERT` eylemi için ek bir koşul belirten boolean ifadesi. Aşağıdaki kurallara dikkat edin: Üç WHEN yan tümcesi bulunduğunda, `UPDATE`, `DELETE` ve `INSERT` her biri yalnızca bir kez görünebilir. `UPDATE` ve `DELETE` birlikte kullanılıyorsa, ilk işlem `[AND <BOOLEAN ifadesi>]` içermelidir. `WHEN NOT MATCHED` yan tümcesi yalnızca son `WHEN` yan tümcesi olarak görünebilir.
set_clause_list	Hayır	Güncellenecek veriler. Bu parametre bir `UPDATE` işlemi için zorunludur. `UPDATE` hakkında daha fazla bilgi için bkz. UPDATE.
value_list	Hayır	Eklenecek veriler. Bu parametre bir `INSERT` işlemi için zorunludur. `VALUES` hakkında daha fazla bilgi için bkz. VALUES.
merge_action	Evet	Bir `merge_action` üç türden biri olabilir: `MATCHED`, `NOT MATCHED BY TARGET` ve `NOT MATCHED BY SOURCE`. Bir eylem, yalnızca hem `merge_condition` hem de `search_condition` karşılandığında bir satıra uygulanır. Bir satır birden fazla yan tümce için koşulları karşılarsa, o satır için yalnızca ilk eşleşen `merge_action` yürütülür.
matched_clause	Hayır	Bir hedef tablo satırı bir kaynak tablo satırıyla eşleştiğinde gerçekleştirilecek `UPDATE` veya `DELETE` eylemini tanımlar.
not_matched_by_target_clause	Hayır	Herhangi bir hedef satırla eşleşmeyen kaynak satırlar için eylemi belirtir.
not_matched_by_source_clause	Hayır	Eşleşen bir kaynak satırı olmayan hedef satırlar için eylemi belirtir.
merge_insert_clause	Hayır	`merge_insert_clause` üç seçeneğe sahiptir: `value_list`, `ROW` ve . `value_list`, bir `VALUES` ifadesi kullanarak eklenecek verileri belirtir. `ROW` ve , kaynak tablodan gelen eşleşen verilerin doğrudan hedef tabloya eklendiğini gösterir. `ROW` ve `*` için kaynak ve hedef tablolar uyumlu veri türlerine sahip aynı sayıda sütuna sahip olmalıdır.

Örnekler

Örnek 1: Hedef tablo acid_address_book_base1 ve kaynak tablo tmp_table1'i oluşturun ve veri ekleyin. ON koşulunu karşılayan kayıtlar için kaynak tablodaki verilerle hedef tabloyu güncellemek ve ON koşulunu karşılamayan ancak kaynak tabloda _event_type_ değeri I olan kayıtları hedef tabloya eklemek için MERGE INTO işlemini çalıştırın. Aşağıda örnek bir komut verilmiştir:

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

Örnek 2: Bir hedef tablo acid_target1 ve bir kaynak tablo tmp_table2 oluşturun. Kaynak tablodan name değeri 'Nika' olan satırları eklemek, hedef tablodan name değeri 'Tommy' olan satırları silmek ve hedef tablodan name değeri 'Gene' olan satırları güncellemek için bir MERGE INTO işlemi çalıştırın.

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

Örnek 3: Hedef tablo merge_acid_dp ve kaynak tablo merge_acid_source oluşturup veri ekleyin. Bir bölüm belirtmeden veri güncellemek veya eklemek için MERGE INTO komutunu çalıştırın; bu işlem hedef tablonun tüm bölümlerine uygulanır.

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

Örnek 4: Hedef tablo merge_acid_sp ve kaynak tablo merge_acid_source'u oluşturun ve veri ekleyin. Hedef tablonun belirtilen bir bölümüne veri güncellemek veya eklemek için MERGE INTO komutunu çalıştırın.

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

Örnek 5: Delta Table türünde bir hedef tablo mf_tt6 ve bir kaynak tablo mf_delta oluşturun ve veri ekleyin. Hedef tablonun belirtilen bir bölümünde verileri güncellemek, eklemek veya silmek için MERGE INTO komutunu çalıştırın.

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