Gunakan MERGE INTO untuk menggabungkan perintah INSERT, UPDATE, dan DELETE - MaxCompute

Saat Anda perlu menjalankan operasi INSERT, UPDATE, atau DELETE pada tabel transaksional atau tabel delta, Anda dapat menggunakan fitur MERGE INTO untuk menggabungkan operasi tersebut dalam satu Pernyataan SQL. Berdasarkan hasil join dengan tabel sumber, pernyataan ini melakukan operasi insert, update, atau delete pada tabel transaksional target hanya dengan satu pemindaian tabel penuh, sehingga meningkatkan efisiensi eksekusi.

Ikhtisar

MaxCompute mendukung operasi DELETE dan UPDATE. Namun, untuk menjalankan operasi batch pada tabel target menggunakan beberapa pernyataan INSERT, UPDATE, dan DELETE, Anda harus menulis beberapa Pernyataan SQL dan melakukan beberapa pemindaian tabel penuh. Fitur MERGE INTO di MaxCompute hanya memerlukan satu pemindaian tabel penuh untuk menyelesaikan semua operasi tersebut, sehingga efisiensi eksekusinya lebih tinggi dibandingkan kombinasi INSERT+UPDATE+DELETE.

Operasi MERGE INTO bersifat atomik. Suatu Pekerjaan dianggap berhasil hanya jika seluruh operasi INSERT, UPDATE, dan DELETE berhasil. Jika salah satu operasi gagal, seluruh Pekerjaan akan gagal.

Selain itu, MERGE INTO mencegah masalah yang dapat terjadi ketika operasi INSERT, UPDATE, dan DELETE dijalankan secara terpisah—di mana sebagian operasi berhasil sementara yang lain gagal, dan operasi yang berhasil tidak dapat di-rollback.

Catatan Penggunaan

Sebelum menjalankan operasi MERGE INTO, Anda harus memiliki izin Select dan Update pada tabel transaksional target. Untuk informasi lebih lanjut tentang cara memberikan izin, lihat izin MaxCompute.
Anda tidak dapat menjalankan beberapa operasi INSERT atau UPDATE pada baris yang sama dalam satu pernyataan MERGE INTO.
Tabel target untuk operasi MERGE INTO harus berupa tabel transaksional.
Pernyataan MERGE INTO harus mencakup setidaknya satu klausa merge_action.
Jika Anda menggunakan beberapa klausa not_matched_by_target_clause, paling banyak satu di antaranya boleh tidak mencantumkan search_condition.
Jika Anda menggunakan beberapa klausa not_matched_by_source_clause, paling banyak satu di antaranya boleh tidak mencantumkan search_condition.
Jika Anda menggunakan beberapa klausa matched_clause, paling banyak satu di antaranya boleh tidak mencantumkan search_condition.
Subkueri tidak didukung dalam merge_condition dan search_condition.

Sintaks

MERGE INTO <target_table> AS <alias_name_t>
USING <source expression | table_name> AS <alias_name_s>
--Menentukan kondisi join untuk mencocokkan baris antara tabel sumber dan target.
ON merge_condition
{ merge_action } +

merge_action ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause

--Menentukan aksi yang akan dijalankan saat baris cocok berdasarkan kondisi ON. Kumpulan data yang diproses oleh beberapa klausa WHEN MATCHED tidak boleh tumpang tindih.
matched_clause ::= WHEN MATCHED [ AND <search_condition> ] THEN { merge_update_clause | merge_delete_clause }

--Menentukan aksi yang akan dijalankan saat baris di sumber tidak cocok dengan baris apa pun di target.
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND <search_condition> ] THEN merge_insert_clause

--Menentukan aksi yang akan dijalankan saat baris di target tidak cocok dengan baris apa pun di sumber.
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 | *

Parameter

Parameter	Wajib	Deskripsi
target_table	Ya	Nama tabel target. Tabel tersebut harus sudah ada.
alias_name_t	Tidak	Alias untuk tabel target.
source expression\|table_name	Ya	Sumber data, yang dapat berupa tabel, Tampilan, atau subkueri.
alias_name_s	Tidak	Alias untuk tabel, Tampilan, atau subkueri sumber.
merge_condition	Ya	Ekspresi boolean.
search_condition	Tidak	Ekspresi boolean yang menentukan kondisi tambahan untuk aksi `UPDATE`, `DELETE`, atau `INSERT`. Perhatikan aturan berikut: Ketika tiga klausa WHEN hadir, `UPDATE`, `DELETE`, dan `INSERT` masing-masing hanya boleh muncul sekali. Jika `UPDATE` dan `DELETE` keduanya muncul, operasi pertama harus menyertakan `[AND <BOOLEAN expression>]`. Klausa `WHEN NOT MATCHED` hanya boleh muncul sebagai klausa `WHEN` terakhir.
set_clause_list	Tidak	Data yang akan diperbarui. Parameter ini wajib untuk operasi `UPDATE`. Untuk informasi lebih lanjut tentang `UPDATE`, lihat UPDATE.
value_list	Tidak	Data yang akan dimasukkan. Parameter ini wajib untuk operasi `INSERT`. Untuk informasi lebih lanjut tentang `VALUES`, lihat VALUES.
merge_action	Ya	`merge_action` dapat berupa salah satu dari tiga jenis: `MATCHED`, `NOT MATCHED BY TARGET`, dan `NOT MATCHED BY SOURCE`. Aksi hanya berlaku untuk suatu baris jika kedua kondisi `merge_condition` dan `search_condition` terpenuhi. Jika suatu baris memenuhi kondisi untuk beberapa klausa, hanya `merge_action` pertama yang sesuai yang dieksekusi untuk baris tersebut.
matched_clause	Tidak	Menentukan aksi `UPDATE` atau `DELETE` yang akan dijalankan pada baris tabel target saat baris tersebut cocok dengan baris tabel sumber.
not_matched_by_target_clause	Tidak	Menentukan aksi untuk baris sumber yang tidak cocok dengan baris target apa pun.
not_matched_by_source_clause	Tidak	Menentukan aksi untuk baris target yang tidak memiliki baris sumber yang cocok.
merge_insert_clause	Tidak	`merge_insert_clause` memiliki tiga opsi: `value_list`, `ROW`, dan ``. `value_list` menentukan data yang akan dimasukkan menggunakan ekspresi `VALUES`. `ROW` dan `` keduanya menunjukkan bahwa data yang cocok dari tabel sumber dimasukkan langsung ke tabel target. Untuk `ROW` dan `*`, tabel sumber dan target harus memiliki jumlah kolom yang sama dengan tipe data yang kompatibel.

Contoh

Contoh 1: Buat tabel target acid_address_book_base1 dan tabel sumber tmp_table1, lalu masukkan data. Jalankan operasi MERGE INTO untuk memperbarui tabel target dengan data dari tabel sumber untuk catatan yang sesuai dengan kondisi ON, serta memasukkan data ke tabel target untuk catatan yang tidak sesuai dengan kondisi ON dan memiliki _event_type_ bernilai I di tabel sumber. Berikut adalah contoh perintahnya:

--Buat tabel target 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"); 

--Buat tabel sumber tmp_table1.
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

--Masukkan data sampel ke tabel target 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');

--Kueri tabel target untuk memverifikasi hasil operasi insert.
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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

--Masukkan data sampel ke tabel sumber 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');

--Kueri tabel sumber untuk memverifikasi hasil operasi insert.
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            |
+------------+------------+------------+------------+--------------+

--Jalankan operasi MERGE INTO.
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');

--Kueri tabel target untuk memverifikasi hasil operasi MERGE INTO.
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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

Contoh 2: Buat tabel target acid_target1 dan tabel sumber tmp_table2. Jalankan operasi MERGE INTO untuk memasukkan baris dari tabel sumber yang memiliki name bernilai 'Nika', menghapus baris di tabel target yang memiliki name bernilai 'Tommy', dan memperbarui baris di tabel target yang memiliki name bernilai 'Gene'.

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

--Buat tabel sumber tmp_table2.
CREATE TABLE IF NOT EXISTS tmp_table2 
(id BIGINT, name STRING, phone STRING);

--Masukkan data sampel ke tabel target acid_target1.
INSERT OVERWRITE TABLE acid_target1 
VALUES (1, 'Tommy', '111'), (2, 'Gene', '222'), 
(3, 'Abram', '333');

--Kueri tabel target untuk memverifikasi hasil operasi insert.
SELECT * FROM acid_target1;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 1          | Tommy | 111   |
| 2          | Gene | 222   |
| 3          | Abram | 333   |
+------------+------+-------+

--Masukkan data sampel ke tabel sumber tmp_table2.
INSERT OVERWRITE TABLE tmp_table2 
VALUES (6, 'Queen', '666'), (7, 'Nika', '777');

--Kueri tabel sumber untuk memverifikasi hasil operasi insert.
SELECT * FROM tmp_table2;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 6          | Queen | 666   |
| 7          | Nika | 777   |
+------------+------+-------+

--Jalankan operasi MERGE INTO.
--Masukkan baris dari tabel sumber yang memiliki name 'Nika' ke tabel target.
--Hapus baris dari tabel target yang memiliki name 'Tommy'.
--Perbarui nomor telepon dari '222' menjadi '999' untuk baris yang memiliki name 'Gene' di tabel target.
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';

--Kueri tabel target untuk memverifikasi hasil operasi MERGE INTO.
SELECT * FROM acid_target1;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 2          | Gene | 999   |
| 7          | Nika | 777   |
| 3          | Abram | 333  |
+------------+------+-------+

Contoh 3: Buat tabel target merge_acid_dp dan tabel sumber merge_acid_source, lalu masukkan data. Jalankan perintah MERGE INTO tanpa menentukan partisi untuk memperbarui atau memasukkan data, yang berlaku untuk semua partisi tabel target.

--Buat tabel target 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");
--Buat tabel sumber 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;

--Masukkan data sampel ke tabel target 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);

--Kueri tabel target untuk memverifikasi hasil operasi insert.
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 |
+------------+------------+----+----+

--Masukkan data sampel ke tabel sumber merge_acid_source.
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

--Kueri tabel sumber untuk memverifikasi hasil operasi insert.
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 |
+------------+------------+----+----+

--Jalankan operasi MERGE INTO.
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);

--Kueri tabel target untuk memverifikasi hasil operasi MERGE INTO.
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 |
+------------+------------+----+----+

Contoh 4: Buat tabel target merge_acid_sp dan tabel sumber merge_acid_source, lalu masukkan data. Jalankan perintah MERGE INTO untuk memperbarui atau memasukkan data ke partisi tertentu dari tabel target.

--Buat tabel target 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");
--Buat tabel sumber 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;

--Masukkan data sampel ke tabel target 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);

--Kueri tabel target untuk memverifikasi hasil operasi insert.
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 |
+------------+------------+----+----+

--Masukkan data sampel ke tabel sumber merge_acid_source.
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

--Kueri tabel sumber untuk memverifikasi hasil operasi insert.
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 |
+------------+------------+----+----+

--Jalankan operasi MERGE INTO dan tentukan dalam kondisi ON untuk hanya memperbarui atau memasukkan data di partisi dengan dd = '01' dan 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);

--Kueri tabel target untuk memverifikasi hasil operasi MERGE INTO.
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 |
+------------+------------+----+----+

Contoh 5: Buat tabel target mf_tt6 bertipe Delta Table dan tabel sumber mf_delta, lalu masukkan data. Jalankan perintah MERGE INTO untuk memperbarui, memasukkan, atau menghapus data di partisi tertentu dari tabel target.

--Buat tabel target 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");
--Masukkan data sampel ke tabel target 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);

--Aktifkan pemindaian tabel penuh untuk Sesi saat ini. Kemudian, jalankan pernyataan SELECT untuk melihat data di tabel mf_tt6.
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 |
+------------+------------+----+----+

--Buat tabel sumber mf_delta dan masukkan data sampel.
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

--Kueri tabel sumber untuk memverifikasi hasil operasi insert.
SELECT * FROM mf_delta;
--Result:
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

--Jalankan operasi MERGE INTO, dengan menentukan dalam kondisi ON untuk hanya memperbarui, memasukkan, atau menghapus data di partisi mf_tt6 dengan dd = '01' dan 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');         

--Kueri tabel target untuk memverifikasi hasil operasi MERGE INTO.
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 |
+------------+------------+----+----+