MaxCompute：UPDATE dan DELETE

Prasyarat

Anda harus memiliki izin Select dan Update pada tabel transaksional tempat Anda ingin mengeksekusi pernyataan DELETE atau UPDATE. Untuk informasi lebih lanjut, lihat Izin MaxCompute.

Deskripsi

Pernyataan DELETE dan UPDATE di MaxCompute mirip dengan pernyataan SQL tradisional. Keduanya digunakan untuk menghapus atau memperbarui baris tertentu dalam tabel.

Jika beberapa pekerjaan dijalankan secara paralel pada tabel, konflik mungkin terjadi. Untuk informasi lebih lanjut, lihat Semantik ACID.

Skenario

Anda dapat mengeksekusi pernyataan DELETE atau UPDATE untuk menghapus atau memperbarui sejumlah kecil data dalam tabel atau partisi tabel dengan frekuensi rendah. Sebagai contoh, Anda dapat mengeksekusi pernyataan untuk menghapus atau memperbarui kurang dari 5% data dalam tabel atau partisi tabel pada hari berikutnya setelah data dihasilkan.

Pernyataan DELETE atau UPDATE tidak berlaku jika Anda ingin menghapus atau memperbarui data dengan frekuensi tinggi atau jika Anda ingin menulis data ke tabel secara real-time.

Batasan

Perhatian

DELETE

Anda dapat mengeksekusi pernyataan DELETE untuk menghapus satu atau lebih baris yang memenuhi kondisi tertentu dari tabel transaksional atau tabel Delta.

• Sintaksis

  DELETE FROM <table_name> [WHERE <where_condition>];

• Parameter

  Parameter	Wajib	Deskripsi
  table_name	Ya	Nama tabel transaksional atau tabel Delta tempat Anda ingin mengeksekusi pernyataan DELETE.
  where_condition	Tidak	Klausa WHERE yang digunakan untuk menyaring data berdasarkan kondisi. Untuk informasi lebih lanjut tentang klausa WHERE, lihat Klausa WHERE (WHERE_condition). Jika Anda mengeksekusi pernyataan DELETE pada tabel tanpa klausa WHERE, semua data dalam tabel dihapus.

• Contoh

  • Contoh 1: Buat tabel transaksional non-partisi bernama acid_delete dan sisipkan data ke dalam tabel. Kemudian, eksekusi pernyataan DELETE untuk menghapus baris yang memenuhi kondisi tertentu dari tabel. Contoh pernyataan:

    -- Buat tabel transaksional non-partisi bernama acid_delete. 
    CREATE TABLE IF  NOT EXISTS acid_delete(id BIGINT) tblproperties ("transactional"="true"); 
    
    -- Sisipkan data ke dalam tabel. 
    INSERT OVERWRITE TABLE acid_delete VALUES(1),(2),(3),(2); 
    
    -- Kueri tabel untuk memeriksa apakah data telah dimasukkan. 
    SELECT * FROM acid_delete; 
    -- Hasil berikut dikembalikan:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 2          |
    | 3          |
    | 2          |
    +------------+
    
    -- Hapus baris yang nilainya pada kolom id adalah 2. Jika Anda mengeksekusi pernyataan pada klien MaxCompute (odpscmd), Anda harus memasukkan ya atau tidak untuk mengonfirmasi penghapusan. 
    DELETE FROM acid_delete WHERE id = 2; 
    
    -- Kueri tabel untuk memeriksa apakah tabel hanya berisi baris yang nilainya pada kolom id adalah 1 dan 3. 
    SELECT * FROM acid_delete; 
    -- Hasil berikut dikembalikan:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 3          |
    +------------+

  • Contoh 2: Buat tabel transaksional partisi bernama acid_delete_pt dan sisipkan data ke dalam tabel. Kemudian, eksekusi pernyataan DELETE untuk menghapus baris yang memenuhi kondisi tertentu dari tabel. Contoh pernyataan:

    -- Buat tabel transaksional partisi bernama acid_delete_pt.  
    CREATE TABLE IF  NOT EXISTS acid_delete_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");
    
    -- Tambahkan partisi ke tabel. 
    ALTER TABLE acid_delete_pt ADD IF  NOT EXISTS PARTITION (ds= '2019');
    ALTER TABLE acid_delete_pt ADD IF  NOT EXISTS PARTITION (ds= '2018');
    
    -- Sisipkan data ke dalam partisi. 
    INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds='29') VALUES(1),(2),(3);
    INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds='2018') VALUES(1),(2),(3);
    
    -- Kueri tabel untuk memeriksa apakah data telah dimasukkan. 
    SET odps.sql.allow.fullscan=true;
    SELECT * FROM acid_delete_pt;
    -- Hasil berikut dikembalikan:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2018       |
    | 2          | 2018       |
    | 3          | 2018       |
    | 1          | 2019       |
    | 2          | 2019       |
    | 3          | 2019       |
    +------------+------------+
    
    -- Hapus baris yang nilainya pada kolom id dan ds adalah 2 dan 2019. Jika Anda mengeksekusi pernyataan pada klien MaxCompute (odpscmd), Anda harus memasukkan ya atau tidak untuk mengonfirmasi penghapusan. 
    DELETE FROM acid_delete_pt WHERE ds='2019' AND id = 2;
    
    -- Kueri tabel untuk memeriksa apakah baris yang nilainya pada kolom id dan ds adalah 2 dan 2019 dihapus. 
    SET odps.sql.allow.fullscan=true;
    SELECT * FROM acid_delete_pt;
    -- Hasil berikut dikembalikan:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2018       |
    | 2          | 2018       |
    | 3          | 2018       |
    | 1          | 2019       |
    | 3          | 2019       |
    +------------+------------+

  • Contoh 3: Buat tabel tujuan bernama acid_delete_t dan tabel terkait bernama acid_delete_s. Kemudian, hapus baris yang memenuhi kondisi tertentu dari tabel tujuan berdasarkan tabel terkait. Contoh pernyataan:

    -- Buat tabel tujuan bernama acid_delete_t dan tabel terkait bernama acid_delete_s. 
    CREATE TABLE IF  NOT EXISTS acid_delete_t(id INT,value1 INT,value2 INT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_delete_s(id INT,value1 INT,value2 INT);
    
    -- Sisipkan data ke dalam tabel. 
    INSERT OVERWRITE TABLE acid_delete_t VALUES(2,20,21),(3,30,31),(4,40,41);
    INSERT OVERWRITE TABLE acid_delete_s VALUES(1,100,101),(2,200,201),(3,300,301);
    
    -- Hapus baris dalam tabel acid_delete_t yang nilai pada kolom id tidak cocok dengan nilai pada baris dalam tabel acid_delete_s. Jika Anda ingin mengeksekusi pernyataan pada klien MaxCompute (odpscmd), Anda harus memasukkan ya atau tidak untuk mengonfirmasi penghapusan. 
    DELETE FROM acid_delete_t WHERE  NOT EXISTS (SELECT * FROM acid_delete_s WHERE acid_delete_t.id=acid_delete_s.id);
    
    -- Kueri tabel acid_delete_t untuk memeriksa apakah tabel hanya berisi baris yang nilainya pada kolom id adalah 2 dan 3. 
    SELECT * FROM acid_delete_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    +------------+------------+------------+

  • Contoh 4: Buat tabel Delta bernama mf_dt, impor data ke tabel tersebut, dan kemudian eksekusi pernyataan DELETE untuk menghapus baris yang memenuhi kondisi tertentu dari tabel. Contoh pernyataan:

    -- Buat tabel Delta bernama mf_dt. 
    CREATE TABLE IF  NOT EXISTS mf_dt (pk BIGINT  NOT NULL PRIMARY KEY, 
                      val  BIGINT NOT NULL) 
                      PARTITIONED BY(dd STRING, hh STRING) 
                      tblproperties ("transactional"="true");
    
    -- Sisipkan data ke dalam tabel.
    INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
    
    -- Kueri tabel untuk memeriksa apakah data telah dimasukkan.                 
    SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
    
    -- Hasil berikut dikembalikan:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+
    
    -- Hapus baris yang nilainya pada kolom val adalah 2 dari partisi dd='01' dan hh='02'. 
    DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';
    
    -- Kueri tabel hasil. Hanya baris yang nilainya pada kolom val adalah 1 dan 3 yang ada di partisi yang ditentukan.
    SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
    
    -- Hasil berikut dikembalikan:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    +------------+------------+----+----+

ALTER TABLE CLEAR COLUMN

Anda dapat mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN untuk membersihkan data dalam kolom yang tidak lagi diperlukan dalam tabel biasa dari disk dan mengatur nilai dalam kolom menjadi null. Ini dapat mengurangi biaya penyimpanan.

• Sintaksis

  ALTER TABLE <table_name> 
             [PARTITION ( <pt_spec>[, <pt_spec>....] )] 
  					 CLEAR COLUMN column1[, column2, column3, ...]
  			                         [WITHOUT TOUCH];

• Parameter

  Parameter	Deskripsi
  table_name	Nama tabel tempat Anda ingin mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN.
  column1 , column2 ...	Nama kolom yang datanya perlu dibersihkan.
  PARTITION	Partisi tempat Anda ingin mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN. Jika tidak ada partisi yang ditentukan, pernyataan dieksekusi pada semua partisi dalam tabel.
  pt_spec	Deskripsi partisi. Konfigurasikan parameter ini dalam format (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...).
  WITHOUT TOUCH	Jika Anda mengonfigurasi parameter ini, waktu modifikasi terakhir yang ditentukan oleh LastDataModifiedTime tidak diperbarui. Jika Anda tidak mengonfigurasi parameter ini, waktu modifikasi terakhir diperbarui.

  Catatan

  Secara default, parameter without touch dikonfigurasi. Anda juga dapat menghapus data dari kolom di masa mendatang meskipun parameter without touch tidak dikonfigurasi. Jika Anda tidak mengonfigurasi parameter without touch, waktu data terakhir dimodifikasi diperbarui.

• Batasan

  • Pernyataan ALTER TABLE CLEAR COLUMN tidak dapat dieksekusi untuk kolom yang memiliki properti non-null. Anda dapat menghapus properti non-null untuk kolom.

    ALTER TABLE <table_name> change COLUMN <old_col_name> NULL;

  • Pernyataan ALTER TABLE CLEAR COLUMN tidak dapat dieksekusi pada tabel atomicity, consistency, isolation, durability (ACID).

  • Pernyataan ALTER TABLE CLEAR COLUMN tidak dapat dieksekusi pada tabel terkluster.

  • Pernyataan ALTER TABLE CLEAR COLUMN tidak dapat dieksekusi untuk membersihkan data dalam subkolom dalam kolom tipe NESTED.

  • Pernyataan ALTER TABLE CLEAR COLUMN tidak dapat dieksekusi untuk membersihkan data dalam semua kolom tabel. Jika Anda ingin membersihkan data dalam semua kolom tabel, Anda dapat mengeksekusi pernyataan DROP TABLE, yang memberikan kinerja lebih baik.

• Perhatian

  • Pernyataan ALTER TABLE CLEAR COLUMN tidak mengubah properti Arsip tabel.

  • Anda mungkin gagal mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN untuk membersihkan data dalam kolom tipe NESTED.

    Kegagalan terjadi jika Anda membersihkan data bersarang yang berada dalam mode penyimpanan berorientasi kolom tetapi mode tersebut dinonaktifkan untuk data tersebut.

  • Eksekusi pernyataan ALTER TABLE CLEAR COLUMN bergantung pada layanan penyimpanan online. Jika Anda memiliki banyak pekerjaan untuk dijalankan, pernyataan tersebut mungkin perlu antre dan memerlukan waktu lama untuk diselesaikan.

  • Saat Anda mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN, sumber daya komputasi dikonsumsi untuk melakukan operasi baca dan tulis data. Jika Anda menggunakan metode penagihan langganan, sumber daya komputasi Anda digunakan. Jika Anda menggunakan metode penagihan bayar sesuai pemakaian, Anda dikenakan biaya untuk mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN berdasarkan aturan penagihan yang sama seperti pekerjaan SQL. Pernyataan ALTER TABLE CLEAR COLUMN untuk metode penagihan bayar sesuai pemakaian sedang dalam pratinjau undangan. Pengguna yang menggunakan metode penagihan ini dapat mengeksekusi pernyataan secara gratis.

• Contoh

  • -- Buat tabel.
    CREATE TABLE IF  NOT EXISTS mf_cc(key STRING, 
         value STRING, 
         a1 BIGINT , 
         a2 BIGINT , 
         a3 BIGINT , 
         a4 BIGINT)  
         PARTITIONED BY(ds STRING, hr STRING);
                     
    -- Tambahkan partisi ke tabel.
    ALTER TABLE mf_cc ADD IF  NOT EXISTS PARTITION (ds='20230509', hr='1641');
    
    -- Sisipkan data ke dalam partisi.
    INSERT INTO mf_cc PARTITION (ds='20230509', hr='1641') 
         VALUES("key","value",1,22,3,4);
         
    -- Kueri partisi untuk memeriksa apakah data telah dimasukkan.     
    SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
    -- Hasil berikut dikembalikan:
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | a1         | a2         | a3     | a4   | ds      | hr  |
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | 1          | 22         | 3      | 4    | 20230509| 1641|
    +-----+-------+------------+------------+--------+------+---------+-----+
    -- Bersihkan data dari kolom.
    ALTER TABLE mf_cc PARTITION(ds='20230509', hr='1641') 
                      CLEAR COLUMN key,a1 
        WITHOUT TOUCH;
    -- Kueri data dari kolom.     
    SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
    -- Hasil berikut dikembalikan. Nilai kolom key dan a1 menjadi null.
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | a1         | a2         | a3     | a4   | ds      | hr  |
    +-----+-------+------------+------------+--------+------+---------+-----+
    | null| value | null       | 22         | 3      | 4    | 20230509| 1641|
    +-----+-------+------------+------------+--------+------+---------+-----+

  • Gambar berikut menunjukkan perubahan total ruang penyimpanan tabel lineitem yang semua kolomnya dibersihkan satu per satu dengan mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN beberapa kali. Format penyimpanan data tabel lineitem adalah AliORC. Tabel lineitem berisi 16 kolom, yang bertipe BIGINT, DECIMAL, CHAR, DATE, dan VARCHAR.

    Setelah pernyataan ALTER TABLE CLEAR COLUMN dieksekusi berulang kali untuk mengubah nilai semua kolom dalam tabel menjadi null secara berurutan, ruang penyimpanan yang ditempati oleh tabel berubah dari 186,783,526 byte menjadi 236,715 byte, berkurang sebesar 99,97%.

    Catatan

    • Ukuran ruang penyimpanan yang dilepaskan dengan mengeksekusi pernyataan ALTER TABLE CLEAR COLUMN terkait dengan tipe data kolom dan nilai dalam kolom. Dalam contoh ini, ruang penyimpanan kolom l_extendedprice bertipe DECIMAL berubah dari 146,538,799 byte menjadi 111,138,117 byte, berkurang sebesar 24,2%. Ini jauh lebih besar daripada persentase rata-rata ruang penyimpanan yang dilepaskan.

    • Setelah nilai semua kolom disetel menjadi null, ruang penyimpanan yang ditempati oleh tabel menjadi 236,715 byte, bukan 0. Hal ini karena struktur file yang dihasilkan untuk tabel masih ada. Nilai null juga menempati sejumlah kecil ruang penyimpanan, dan sistem mempertahankan informasi footer file.

UPDATE

Anda dapat mengeksekusi pernyataan UPDATE untuk memperbarui nilai satu atau lebih kolom dari baris yang memenuhi kondisi tertentu dalam tabel transaksional atau tabel Delta.

• Sintaksis

  -- Metode 1
  UPDATE <table_name> SET <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];
  
  -- Metode 2
  UPDATE <table_name> SET (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];
  
  -- Metode 3
  UPDATE <table_name>
         SET <col1_name> = <value1> [ , <col2_name> = <value2> , ... ]
          [ FROM <additional_tables> ]
          [ WHERE <where_condition> ]

• Parameter

  Parameter	Wajib	Deskripsi
  table_name	Ya	Nama tabel transaksional tempat Anda ingin mengeksekusi pernyataan UPDATE.
  col1_name/col2_name	Ya	Kolom yang ingin Anda perbarui. Anda harus menentukan setidaknya satu kolom. Parameter ini menentukan nama kolom yang berisi baris yang memenuhi kondisi filter tertentu.
  value1/value2	Ya	Nilai baru yang ingin Anda tetapkan ke kolom tertentu. Anda harus memperbarui nilai setidaknya satu kolom. Parameter ini menentukan nilai baru dari kolom yang sesuai dengan baris yang memenuhi kondisi filter tertentu.
  where_condition	Tidak	Klausa WHERE yang digunakan untuk menyaring data berdasarkan kondisi. Untuk informasi lebih lanjut, lihat Klausa WHERE (where_condition). Jika Anda mengeksekusi pernyataan UPDATE pada tabel tanpa klausa WHERE, semua data dalam tabel diperbarui.
  additional_tables	Tidak	Klausa from.

  Pernyataan UPDATE dapat digunakan dengan klausa from. Klausa from membuat penggunaan pernyataan UPDATE lebih nyaman. Tabel berikut menjelaskan pernyataan UPDATE yang digunakan dengan dan tanpa klausa from.

  Skenario	Kode contoh
  Tanpa klausa from	UPDATE target SET v = (SELECT MIN(v) FROM src GROUP BY k WHERE target.k = src.key) WHERE target.k IN (SELECT k FROM src);
  Dengan klausa from	UPDATE target SET v = b.v FROM (SELECT k, MIN(v) v FROM src GROUP BY k) b WHERE target.k = b.k;

  Kesimpulan berikut diperoleh berdasarkan kode contoh sebelumnya:

  • Jika Anda menggunakan beberapa baris data dalam tabel sumber untuk memperbarui satu baris data dalam tabel tujuan, Anda harus menulis operasi agregat untuk memastikan keunikan sumber data. Kode operasi agregat lebih sederhana dan lebih mudah dipahami saat Anda menggunakan klausa from daripada saat Anda tidak menggunakan klausa from.

  • Jika Anda hanya perlu memperbarui data irisan selama operasi join, Anda harus menggunakan klausa from atau klausa where. Klausa where lebih kompleks daripada klausa from.

• Contoh

  • Contoh 1: Buat tabel non-partisi bernama acid_update dan sisipkan data ke dalam tabel. Kemudian, eksekusi pernyataan UPDATE untuk memperbarui kolom baris yang memenuhi kondisi tertentu dalam tabel. Contoh pernyataan:

    -- Buat tabel non-partisi bernama acid_update.   
    CREATE TABLE IF  NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");
    
    -- Sisipkan data ke dalam tabel. 
    INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);
    
    -- Kueri tabel untuk memeriksa apakah data telah dimasukkan. 
    SELECT * FROM acid_update; 
    
    -- Hasil berikut dikembalikan:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 2          |
    | 3          |
    | 2          |
    +------------+
    
    -- Perbarui nilai 2 dalam kolom id menjadi 4. 
    UPDATE acid_update SET id = 4 WHERE id = 2; 
    
    -- Kueri partisi untuk memeriksa apakah nilai 2 diperbarui menjadi 4 dalam kolom id. 
    SELECT * FROM acid_update; 
    
    -- Hasil berikut dikembalikan:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 3          |
    | 4          |
    | 4          |
    +------------+

  • Contoh 2: Buat tabel partisi bernama acid_update dan sisipkan data ke dalam tabel. Kemudian, eksekusi pernyataan UPDATE untuk memperbarui kolom baris yang memenuhi kondisi tertentu dalam tabel. Contoh pernyataan:

    -- Buat tabel partisi bernama acid_update_pt.  
    CREATE TABLE IF  NOT EXISTS acid_update_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");
    
    -- Tambahkan partisi ke tabel. 
    ALTER TABLE acid_update_pt ADD IF  NOT EXISTS PARTITION (ds= '2019');
    
    -- Sisipkan data ke dalam partisi. 
    INSERT OVERWRITE TABLE acid_update_pt PARTITION (ds='2019') VALUES(1),(2),(3);
    
    -- Kueri partisi untuk memeriksa apakah data telah dimasukkan.
    SELECT * FROM acid_update_pt WHERE ds = '2019';
    
    -- Hasil berikut dikembalikan:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2019       |
    | 2          | 2019       |
    | 3          | 2019       |
    +------------+------------+
    
    -- Perbarui nilai 2 dari kolom id dalam partisi 2019 menjadi 4. 
    UPDATE acid_update_pt SET id = 4 WHERE ds = '2019' AND id = 2; 
    
    -- Kueri partisi untuk memeriksa apakah nilai 2 diperbarui menjadi 4 dalam kolom id. 
    SELECT * FROM acid_update_pt WHERE ds = '2019';
    
    -- Hasil berikut dikembalikan:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 4          | 2019       |
    | 1          | 2019       |
    | 3          | 2019       |
    +------------+------------+

  • Contoh 3: Buat tabel transaksional bernama acid_update_t yang ingin Anda perbarui dan tabel terkait bernama acid_update_s. Kemudian, perbarui nilai beberapa kolom sekaligus dalam tabel acid_update_t. Contoh pernyataan:

    -- Buat tabel transaksional bernama acid_update_t yang ingin Anda perbarui dan tabel terkait bernama acid_update_s. 
    CREATE TABLE IF  NOT EXISTS acid_update_t(id INT,value1 INT,value2 INT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_update_s(id INT,value1 INT,value2 INT);
    
    -- Sisipkan data ke dalam tabel. 
    INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);
    INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);
    
    -- Metode 1: Perbarui nilai kolom tertentu dengan konstanta. 
    UPDATE acid_update_t SET (value1, value2) = (60,61);
    -- Kueri tabel acid_update_t untuk memeriksa apakah data diperbarui sesuai harapan. 
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 60         | 61         |
    | 3          | 60         | 61         |
    | 4          | 60         | 61         |
    +------------+------------+------------+
    
    -- Metode 2: Gunakan data dalam tabel acid_update_s untuk memperbarui semua baris dalam tabel acid_update_t. Jika baris tertentu dalam tabel acid_update_t tidak dapat dicocokkan, nilai null ditetapkan untuk baris tersebut. 
    UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id);
    -- Kueri tabel acid_update_t untuk memeriksa apakah data diperbarui sesuai harapan. 
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+
    
    -- Metode 3: Gunakan data dalam tabel acid_update_s untuk memperbarui tabel acid_update_t. Tambahkan kondisi filter untuk tabel acid_update_t untuk hanya memperbarui baris yang beririsan dengan baris dalam tabel acid_update_s. 
    UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);
    -- Kueri tabel acid_update_t untuk memeriksa apakah data diperbarui sesuai harapan. 
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+
    
    -- Metode 4: Gunakan hasil agregat dari tabel acid_update_t dan acid_update_s untuk memperbarui tabel acid_update_t. 
    UPDATE acid_update_t SET (id, value1, value2) = (SELECT id, MAX(value1),MAX(value2) FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id GROUP BY acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);
    -- Kueri tabel acid_update_t untuk memeriksa apakah data diperbarui sesuai harapan. 
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+

  • Contoh 4: Lakukan operasi join pada dua tabel. Contoh pernyataan:

    -- Buat tabel tujuan bernama acid_update_t dan buat tabel bernama acid_update_s untuk join.
    CREATE TABLE IF  NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);
    
    -- Sisipkan data ke dalam tabel ini.
    INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);
    INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);
    
    -- Kueri data dari tabel acid_update_t.
    SELECT * FROM acid_update_t;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    | 4          | 40         | 41         |
    +------------+------------+------------+
    
    -- Kueri data dari tabel acid_update_s.
    SELECT * FROM acid_update_s;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 1          | 100        | 101        |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    +------------+------------+------------+
    
    -- Gunakan data dalam tabel acid_update_s untuk memperbarui tabel acid_update_t. Tambahkan kondisi filter untuk tabel acid_update_t untuk hanya memperbarui irisan kedua tabel.
    UPDATE acid_update_t SET value1 = b.value1, value2 = b.value2 
    FROM acid_update_s b WHERE acid_update_t.id = b.id;
    
    -- Nilai 20 diperbarui menjadi 200, nilai 21 diperbarui menjadi 201, nilai 30 diperbarui menjadi 300, dan nilai 31 diperbarui menjadi 301.
    SELECT * FROM acid_update_t;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 4          | 40         | 41         |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    +------------+------------+------------+                          

  • Contoh 5: Lakukan operasi join yang kompleks pada lebih dari dua tabel. Contoh pernyataan:

    -- Buat tabel tujuan bernama acid_update_t dan buat tabel bernama acid_update_s untuk join.
    CREATE TABLE IF  NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");
    CREATE TABLE IF  NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);
    CREATE TABLE IF  NOT EXISTS acid_update_m(id BIGINT,value1 BIGINT,value2 BIGINT);
    
    -- Sisipkan data ke dalam tabel ini.
    INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41),(5,50,51);
    INSERT OVERWRITE TABLE acid_update_s VALUES (1,100,101),(2,200,201),(3,300,301),(4,400,401),(5,500,501);
    INSERT OVERWRITE TABLE acid_update_m VALUES(3,30,101),(4,400,201),(5,300,301);
    
    -- Kueri data dari tabel acid_update_t.
    SELECT * FROM acid_update_t;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    | 4          | 40         | 41         |
    | 5          | 50         | 51         |
    +------------+------------+------------+
    
    -- Kueri data dari tabel acid_update_s.
    SELECT * FROM acid_update_s;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 1          | 100        | 101        |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | 400        | 401        |
    | 5          | 500        | 501        |
    +------------+------------+------------+
    
    -- Kueri data dari tabel acid_update_m.
    SELECT * FROM acid_update_m;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 3          | 30         | 101        |
    | 4          | 400        | 201        |
    | 5          | 300        | 301        |
    +------------+------------+------------+
    
    -- Gunakan data dalam tabel acid_update_s untuk memperbarui tabel acid_update_t. Gunakan klausa where untuk menyaring nilai dalam tabel acid_update_s, acid_update_t, dan acid_update_m.
    UPDATE acid_update_t 
    SET value1 = acid_update_s.value1, value2 = acid_update_s.value2 
    FROM acid_update_s 
    WHERE acid_update_t.id = acid_update_s.id 
    AND acid_update_s.id > 2 
    AND acid_update_t.value1 NOT IN 
    (SELECT value1 FROM acid_update_m WHERE id = acid_update_t.id) 
    AND acid_update_s.value1 NOT IN 
    (SELECT value1 FROM acid_update_m WHERE id = acid_update_s.id);
    
    -- Lihat hasil pembaruan. Hanya baris data yang mengandung nilai 5 dalam kolom id tabel acid_update_t yang memenuhi kondisi. Nilai dalam kolom value1 diperbarui menjadi 500, dan nilai dalam kolom value2 diperbarui menjadi 501.
    SELECT * FROM acid_update_t;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 5          | 500        | 501        |
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    | 4          | 40         | 41         |
    +------------+------------+------------+

  • Contoh 6: Buat tabel Delta bernama mf_dt, impor data ke dalam tabel, dan kemudian eksekusi pernyataan UPDATE untuk menghapus baris yang memenuhi kondisi tertentu dari tabel. Contoh pernyataan:

    -- Buat tabel Delta bernama mf_dt. 
    CREATE TABLE IF  NOT EXISTS mf_dt (pk BIGINT  NOT NULL PRIMARY KEY, 
                      val  BIGINT NOT NULL) 
                      PARTITIONED BY(dd STRING, hh STRING) 
                      tblproperties ("transactional"="true");
    
    -- Sisipkan data ke dalam tabel.
    INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                     VALUES (1, 1), (2, 2), (3, 3);
    
    -- Kueri tabel untuk memeriksa apakah data telah dimasukkan.                 
    SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
    -- Hasil berikut dikembalikan:
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 3          | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+
    
    -- Perbarui nilai kolom val dalam baris yang nilainya pada kolom pk adalah 3 dalam partisi dd='01' dan hh='02' menjadi 30. 
    -- Metode 1
    UPDATE mf_dt SET val = 30 WHERE pk = 3 AND dd='01' AND hh='02';
    
    -- Metode 2
    UPDATE mf_dt SET val = delta.val 
                  FROM (SELECT pk, val FROM VALUES (3, 30) t (pk, val)) delta 
                  WHERE delta.pk = mf_dt.pk AND mf_dt.dd='01' AND mf_dt.hh='02';
    
    -- Lihat hasil pembaruan. 
    SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
    -- Hasil berikut dikembalikan. Nilai kolom val dalam baris yang nilainya pada kolom pk adalah 3 diperbarui menjadi 30. 
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 30         | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+

ALTER TABLE COMPACT

File dasar dan file delta dari tabel transaksional menempati penyimpanan fisik. Anda tidak dapat membaca file tersebut secara langsung. Jika Anda mengeksekusi pernyataan UPDATE atau DELETE pada tabel transaksional, data dalam file dasar tidak diperbarui, tetapi file delta dihasilkan untuk setiap operasi. Dalam hal ini, semakin banyak operasi penghapusan atau pembaruan dilakukan, semakin banyak ruang penyimpanan yang ditempati oleh tabel. Jumlah file delta meningkat. Akibatnya, Anda dikenakan biaya lebih untuk penggunaan penyimpanan dan kueri berikutnya.

Jika Anda mengeksekusi pernyataan UPDATE atau DELETE pada tabel atau partisi tabel beberapa kali, sejumlah besar file delta dihasilkan. Saat sistem membaca data dari tabel, sistem memuat file delta untuk mengidentifikasi baris yang dihapus dan diperbarui. Sejumlah besar file delta mengurangi efisiensi pembacaan data. Dalam hal ini, Anda dapat menggabungkan file dasar dengan file delta untuk mengurangi penggunaan penyimpanan dan meningkatkan efisiensi pembacaan.

• Sintaksis

  ALTER TABLE <table_name> [PARTITION (<partition_key> = '<partition_value>' [, ...])] compact {minor|major};

• Parameter

  Parameter	Wajib	Deskripsi
  table_name	Ya	Nama tabel transaksional tempat Anda ingin menggabungkan file dasar dan file delta.
  partition_key	Tidak	Nama kolom kunci partisi dalam tabel transaksional partisi.
  partition_value	Tidak	Nilai kolom kunci partisi dalam tabel transaksional partisi.
  major|minor	Ya	Salah satu dari mereka harus ditentukan. Perbedaan antara minor compaction dan major compaction: minor: menggabungkan setiap file dasar dengan semua file delta yang dihasilkan berdasarkan file dasar dan menghapus file delta. major: menggabungkan setiap file dasar dengan semua file delta yang dihasilkan berdasarkan file dasar, menghapus file delta, dan menggabungkan file dasar kecil. Jika ukuran file dasar kurang dari 32 MB atau file delta dihasilkan, efek penggabungan file setara dengan efek mengeksekusi pernyataan INSERT OVERWRITE. Namun, jika ukuran file dasar lebih besar dari atau sama dengan 32 MB dan tidak ada file delta yang dihasilkan, data tabel tidak ditimpa.

• Perhatian

  File kecil yang digabungkan melalui operasi Compact akan dihapus setelah 1 hari. Jika Anda menggunakan fungsi Backup untuk memulihkan data historis yang bergantung pada file kecil ini, pemulihan akan gagal karena ketiadaan file tersebut.

• Contoh

  • Contoh 1: Gabungkan file tabel acid_delete. Contoh pernyataan:

    ALTER TABLE acid_delete compact minor;

    Hasil berikut dikembalikan:

    Summary:
    Nothing found to merge, set odps.merge.cross.paths=true if cross path merge is permitted.
    OK

  • Contoh 2: Gabungkan file tabel acid_update_pt. Contoh pernyataan:

    ALTER TABLE acid_update_pt PARTITION (ds = '2019') compact major;

    Hasil berikut dikembalikan:

    Summary:
    table name: acid_update_pt /ds=2019  instance count: 2  run time: 6
      before merge, file count:        8  file size: 2613  file physical size: 7839
       after merge, file count:        2  file size: 679  file physical size: 2037
    
    OK

FAQ

• Masalah 1:

  • Deskripsi masalah: Saat saya mengeksekusi pernyataan UPDATE, pesan kesalahan berikut dikembalikan: ODPS-0010000:System internal error - fuxi job failed, caused by: Data Set should contain exactly one row.

  • Penyebab: Baris yang ingin Anda perbarui tidak cocok dengan baris yang diquery oleh subquery. Dalam hal ini, sistem tidak dapat menentukan baris mana yang perlu diperbarui. Contoh pernyataan:

    UPDATE store SET (s_county, s_manager) = (SELECT d_country, d_manager FROM store_delta sd WHERE sd.s_store_sk = store.s_store_sk) WHERE s_store_sk IN (SELECT s_store_sk FROM store_delta);

    Subquery select d_country, d_manager from store_delta sd where sd.s_store_sk = store.s_store_sk digunakan untuk menyaring data dalam tabel store_delta. Kemudian, data yang memenuhi kondisi tertentu digunakan untuk memperbarui data tabel store. Sebagai contoh, tiga baris yang memiliki kolom s_store_sk dalam tabel store adalah [1, 2, 3]. Jika baris yang memiliki kolom s_store_sk dalam tabel store_delta adalah [1, 1] dan tidak cocok dengan baris dalam tabel store, pesan kesalahan di atas dikembalikan.

  • Solusi: Pastikan baris yang ingin Anda perbarui persis cocok dengan baris yang diquery oleh subquery.

• Masalah 2:

  • Deskripsi masalah: Saat saya menjalankan perintah compact di DataWorks DataStudio, pesan kesalahan berikut dikembalikan: ODPS-0130161:[1,39] Parse exception - invalid token 'minor', expect one of 'StringLiteral','DoubleQuoteStringLiteral'.

  • Penyebab: Versi klien MaxCompute yang sesuai dengan grup sumber daya eksklusif DataWorks tidak mendukung perintah compact.

  • Solusi: Bergabunglah dengan grup DingTalk DataWorks dan hubungi tim dukungan teknis untuk memperbarui versi klien MaxCompute yang sesuai dengan grup sumber daya eksklusif.