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

Jika Anda perlu melakukan operasi INSERT, UPDATE, dan DELETE pada tabel transaksional atau tabel Delta, Anda dapat mengenkapsulasi operasi tersebut dalam satu pernyataan MERGE INTO. Pernyataan ini memungkinkan Anda mengeksekusi operasi-operasi tersebut berdasarkan kondisi penggabungan dengan tabel sumber. Dengan pendekatan ini, data di tabel hanya dipindai sekali, sehingga meningkatkan efisiensi eksekusi.

Prasyarat

Untuk mengeksekusi operasi MERGE INTO, Anda harus memiliki izin Select dan Update pada tabel transaksional tujuan. Untuk informasi lebih lanjut tentang cara memberikan izin, lihat Izin MaxCompute.

Deskripsi fitur

MaxCompute memungkinkan Anda melakukan operasi DELETE atau UPDATE pada tabel untuk menghapus atau memperbarui data. Jika Anda ingin menjalankan operasi INSERT, UPDATE, dan DELETE secara bersamaan, Anda harus menulis dan mengeksekusi pernyataan terpisah untuk setiap operasi, yang mengakibatkan pemindaian tabel penuh dilakukan beberapa kali. Untuk meningkatkan efisiensi, MaxCompute menyediakan pernyataan MERGE INTO yang memungkinkan Anda melakukan operasi INSERT, UPDATE, dan DELETE dalam satu langkah. Dengan pendekatan ini, pemindaian tabel penuh hanya dilakukan sekali, menjadikannya lebih efisien dibandingkan dengan mengeksekusi operasi INSERT, UPDATE, dan DELETE secara terpisah.

Pernyataan MERGE INTO memastikan keatomikan operasi. Pekerjaan hanya berhasil jika semua operasi INSERT, UPDATE, dan DELETE berhasil. Jika salah satu operasi gagal, seluruh pekerjaan juga gagal.

Jika Anda menjalankan operasi INSERT, UPDATE, dan DELETE secara terpisah, ada kemungkinan operasi tertentu gagal, sementara data yang telah berhasil dioperasikan tidak dapat dikembalikan. Untuk mencegah masalah ini, gunakan pernyataan MERGE INTO.

Batasan

Anda tidak dapat melakukan beberapa operasi INSERT atau UPDATE pada baris yang sama di tabel menggunakan satu pernyataan MERGE INTO.

Sintaksis

MERGE INTO <target_table> AS <alias_name_t> USING <source expression|table_name> AS <alias_name_s>
-- Klausul ON menentukan kondisi JOIN dari tabel sumber dan tabel tujuan.
ON <BOOLEAN expression1>
-- Klausul WHEN MATCHED...THEN menentukan operasi yang akan dilakukan ketika hasil dari klausul ON adalah True. Operasi dari beberapa klausa WHEN MATCHED...THEN tidak dapat dilakukan pada data yang sama.
WHEN matched [AND <BOOLEAN expression2>] THEN UPDATE SET <set_clause_list>
WHEN matched [AND <BOOLEAN expression3>] THEN DELETE
-- Klausul WHEN NOT MATCHED...THEN menentukan operasi yang akan dilakukan ketika hasil dari klausul ON adalah False.
WHEN NOT matched [AND <BOOLEAN expression4>] THEN INSERT VALUES <value_list>

Parameter

Parameter	Diperlukan	Deskripsi
target_table	Ya	Nama tabel tujuan, yang harus merupakan tabel yang sudah ada.
alias_name_t	Tidak	Alias tabel tujuan.
source expression\|table_name	Ya	Nama tabel, tampilan, atau subquery sumber yang ingin Anda gabungkan dengan tabel tujuan.
alias_name_s	Tidak	Alias tabel, tampilan, atau subquery sumber yang ingin Anda gabungkan dengan tabel tujuan.
BOOLEAN expression1	Ya	Kondisi yang mengembalikan nilai tipe BOOLEAN. Nilainya harus True atau False.
BOOLEAN expression2, BOOLEAN expression3, BOOLEAN expression4	Tidak	Anda dapat menentukan kondisi untuk setiap operasi `UPDATE`, `DELETE`, dan `INSERT` yang ingin Anda lakukan. Kondisi tersebut harus mengembalikan nilai tipe BOOLEAN. Perhatikan hal-hal berikut: Jika pernyataan MERGE INTO memiliki tiga klausa WHEN, Anda hanya dapat melakukan masing-masing operasi `UPDATE`, `DELETE`, dan `INSERT` satu kali. Jika kedua operasi `UPDATE` dan `DELETE` termasuk dalam pernyataan MERGE INTO, Anda harus menentukan kondisi `[and <boolean expression>]` untuk operasi yang perlu dilakukan terlebih dahulu. Klausa `WHEN NOT MATCHED` hanya dapat digunakan sebagai klausa WHEN terakhir dan mendukung hanya operasi `INSERT`.
set_clause_list	Tidak	Jika Anda ingin melakukan operasi `UPDATE`, parameter ini diperlukan. Parameter ini menentukan data yang ingin Anda perbarui. Untuk informasi lebih lanjut tentang `UPDATE`, lihat bagian "UPDATE" di UPDATE dan DELETE.
value_list	Tidak	Jika Anda ingin melakukan operasi `INSERT`, parameter ini diperlukan. Parameter ini menentukan data yang ingin Anda sisipkan. Untuk informasi lebih lanjut tentang `values`, lihat VALUES.

Contoh

Contoh 1: Buat tabel tujuan bernama acid_address_book_base1 dan tabel sumber bernama tmp_table1, lalu sisipkan data ke dalam tabel-tabel tersebut. Eksekusi pernyataan MERGE INTO pada tabel tujuan. Data dari tabel sumber yang memenuhi kondisi ON join digunakan untuk memperbarui entri data di tabel tujuan. Data yang tidak memenuhi kondisi ON join dan memiliki nilai kolom event_type sebagai I disisipkan ke dalam tabel tujuan. Contoh pernyataan:

-- Buat tabel tujuan bernama 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 bernama tmp_table1.
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

-- Sisipkan data uji ke dalam tabel 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 data dari tabel tujuan dan periksa apakah operasi INSERT berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Hasil yang dikembalikan
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

-- Sisipkan data uji ke dalam tabel 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 data dari tabel sumber dan periksa apakah operasi INSERT berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
-- Hasil yang dikembalikan
+------------+------------+------------+------------+--------------+
| 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            |
+------------+------------+------------+------------+--------------+

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

-- Query data dari tabel tujuan dan periksa apakah operasi MERGE INTO berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Hasil yang dikembalikan
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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 tujuan bernama merge_acid_dp dan tabel sumber bernama merge_acid_source. Sisipkan data ke dalam tabel-tabel tersebut. Kemudian, eksekusi pernyataan MERGE INTO tanpa menentukan partisi untuk memperbarui atau menyisipkan data ke dalam tabel tujuan. Operasi MERGE INTO berlaku untuk semua partisi.

-- Buat tabel tujuan bernama 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 bernama 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;

-- Sisipkan data uji ke dalam tabel tujuan 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 data dari tabel tujuan dan periksa apakah operasi INSERT berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

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

-- Query data dari tabel sumber untuk memeriksa apakah operasi INSERT berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Eksekusi pernyataan 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);

-- Query data dari tabel tujuan untuk memeriksa apakah operasi MERGE INTO berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| 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 3: Buat tabel tujuan bernama merge_acid_sp dan tabel sumber bernama merge_acid_source. Sisipkan data ke dalam tabel-tabel tersebut. Eksekusi pernyataan MERGE INTO untuk memperbarui atau menyisipkan data ke dalam partisi tertentu dari tabel tujuan.

-- Buat tabel tujuan bernama 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 bernama 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;

-- Sisipkan data uji ke dalam tabel tujuan 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 data dari tabel tujuan dan periksa apakah operasi INSERT berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

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

-- Query data dari tabel sumber untuk memeriksa apakah operasi INSERT berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Tentukan partisi dd = '01' dan hh = '01' dalam klausa ON pernyataan MERGE INTO dan eksekusi pernyataan tersebut untuk memperbarui atau menyisipkan data ke dalam partisi tertentu dari tabel tujuan.
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 data dari tabel tujuan untuk memeriksa apakah operasi MERGE INTO berhasil dilakukan.
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 4: Buat tabel Delta bernama mf_tt6 sebagai tabel tujuan dan buat tabel sumber bernama mf_delta. Sisipkan data ke dalam tabel sumber dan tabel tujuan. Tentukan partisi dalam pernyataan MERGE INTO dan eksekusi pernyataan tersebut untuk melakukan operasi UPDATE, INSERT, atau DELETE pada partisi tertentu dari tabel tujuan.

-- Buat tabel Delta bernama mf_tt6 sebagai tabel tujuan.
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");
-- Sisipkan data uji ke dalam tabel tujuan 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 hanya untuk sesi saat ini. Eksekusi pernyataan SELECT untuk query data dari tabel mf_tt6.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| 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 bernama mf_delta dan sisipkan data uji ke dalam tabel tersebut.
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

-- Query data dari tabel sumber dan konfirmasi hasil operasi INSERT.
SELECT * FROM mf_delta;
-- Hasil yang dikembalikan
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

-- Tentukan partisi dd = '01' dan hh = '02' dalam klausa ON pernyataan MERGE INTO dan eksekusi pernyataan tersebut untuk melakukan operasi UPDATE, INSERT, atau DELETE pada partisi tertentu di tabel tujuan mf_tt6.
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 data dari tabel tujuan dan periksa apakah operasi MERGE INTO berhasil dilakukan.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Hasil yang dikembalikan
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+