MaxCompute：Perbarui atau hapus data (UPDATE | DELETE)

Prasyarat

Sebelum menjalankan operasi DELETE atau UPDATE, Anda harus memiliki izin Select dan Update pada tabel Transaksional atau Delta Table target. Untuk informasi selengkapnya tentang otorisasi, lihat Izin MaxCompute.

Pengenalan fungsi

Seperti penggunaannya dalam database tradisional, fitur DELETE dan UPDATE di MaxCompute dapat menghapus atau memperbarui baris tertentu dalam sebuah tabel.

Jika beberapa pekerjaan dijalankan secara bersamaan pada tabel target yang sama, konflik pekerjaan dapat terjadi. Untuk informasi selengkapnya, lihat Semantik ACID.

Skenario

Fitur DELETE dan UPDATE cocok untuk penghapusan atau pembaruan acak dengan frekuensi rendah terhadap sejumlah kecil data dalam tabel atau partisi. Misalnya, Anda dapat secara berkala melakukan penghapusan atau pembaruan batch terhadap kurang dari 5% baris dalam tabel atau partisi setiap hari (T+1).

Fitur DELETE dan UPDATE tidak cocok untuk pembaruan, penghapusan, atau penulisan real-time dengan frekuensi tinggi ke tabel target.

Batasan

Perhatian

Hapus data (DELETE)

Operasi DELETE menghapus satu atau beberapa baris yang memenuhi kondisi tertentu dari tabel Transaksional atau Delta Table.

• Sintaks

  DELETE FROM <table_name> [[AS] alias] [WHERE <condition>];

• Parameter

  Parameter	Wajib	Deskripsi
  table_name	Ya	Nama tabel Transaksional atau Delta Table tempat Anda ingin menjalankan operasi DELETE.
  alias	Tidak	Alias untuk tabel.
  where_condition	Tidak	Klausa WHERE untuk memfilter data yang memenuhi kondisi. Untuk informasi selengkapnya tentang klausa WHERE, lihat Klausa WHERE (WHERE_condition). Jika Anda tidak menyertakan klausa WHERE, semua data dalam tabel akan dihapus.

• Contoh

  • Contoh 1: Buat tabel non-partisi bernama acid_delete, impor data, lalu jalankan operasi DELETE untuk menghapus baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

    -- Buat tabel Transaksional bernama acid_delete.
    CREATE TABLE IF NOT EXISTS acid_delete (id BIGINT) TBLPROPERTIES ("transactional"="true");
    
    -- Masukkan data.
    INSERT OVERWRITE TABLE acid_delete VALUES (1), (2), (3), (2);
    
    -- Lihat data yang dimasukkan.
    SELECT * FROM acid_delete;
    
    +------------+
    | id         |
    +------------+
    | 1          |
    | 2          |
    | 3          |
    | 2          |
    +------------+
    
    -- Hapus baris di mana id adalah 2. Jika Anda menjalankan perintah ini di klien MaxCompute (odpscmd), Anda harus memasukkan yes atau no untuk mengonfirmasi.
    DELETE FROM acid_delete WHERE id = 2;
    -- Perintah berikut setara dengan yang di atas.
    DELETE FROM acid_delete ad WHERE ad.id = 2;
    
    -- Lihat hasilnya. Tabel sekarang hanya berisi data untuk 1 dan 3.
    SELECT * FROM acid_delete;
    
    +------------+
    | id         |
    +------------+
    | 1          |
    | 3          |
    +------------+

  • Contoh 2: Buat tabel berpartisi bernama acid_delete_pt, impor data, lalu jalankan operasi DELETE untuk menghapus baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

    -- Buat tabel Transaksional bernama acid_delete_pt.  
    CREATE TABLE IF NOT EXISTS acid_delete_pt (id BIGINT) PARTITIONED BY (ds STRING) TBLPROPERTIES ("transactional"="true");
    
    -- Tambahkan partisi.
    ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2019');
    ALTER TABLE acid_delete_pt ADD IF NOT EXISTS PARTITION (ds = '2018');
    
    -- Masukkan data.
    INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2019') VALUES (1), (2), (3);
    INSERT OVERWRITE TABLE acid_delete_pt PARTITION (ds = '2018') VALUES (1), (2), (3);
    
    -- Lihat data yang dimasukkan.
    SELECT * FROM acid_delete_pt;
    
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2018       |
    | 2          | 2018       |
    | 3          | 2018       |
    | 1          | 2019       |
    | 2          | 2019       |
    | 3          | 2019       |
    +------------+------------+
    
    -- Hapus data di mana partisi adalah 2019 dan id adalah 2. Jika Anda menjalankan perintah ini di klien MaxCompute (odpscmd), Anda harus memasukkan yes atau no untuk mengonfirmasi.
    DELETE FROM acid_delete_pt WHERE ds = '2019' AND id = 2;
    
    -- Lihat hasilnya. Data di mana partisi adalah 2019 dan id adalah 2 telah dihapus.
    SELECT * FROM acid_delete_pt;
    
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2018       |
    | 2          | 2018       |
    | 3          | 2018       |
    | 1          | 2019       |
    | 3          | 2019       |
    +------------+------------+

  • Contoh 3: Buat tabel target bernama acid_delete_t dan tabel terkait bernama acid_delete_s. Kemudian, hapus baris yang memenuhi kondisi tertentu melalui operasi join. Berikut adalah perintah contohnya:

    -- Buat tabel Transaksional target 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);
    
    -- Masukkan data.
    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 dari tabel acid_delete_t di mana id tidak cocok dengan id di tabel acid_delete_s. Jika Anda menjalankan perintah ini di klien MaxCompute (odpscmd), Anda harus memasukkan yes atau no untuk mengonfirmasi.
    DELETE FROM acid_delete_t WHERE NOT EXISTS (SELECT * FROM acid_delete_s WHERE acid_delete_t.id = acid_delete_s.id);
    -- Perintah berikut setara dengan yang di atas.
    DELETE FROM acid_delete_t a WHERE NOT EXISTS (SELECT * FROM acid_delete_s b WHERE a.id = b.id);
    
    -- Lihat hasilnya. Tabel sekarang hanya berisi data untuk id 2 dan 3.
    SELECT * FROM acid_delete_t;
    
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 20         | 21         |
    | 3          | 30         | 31         |
    +------------+------------+------------+

  • Contoh 4: Buat Delta Table bernama mf_dt, impor data, lalu jalankan operasi DELETE untuk menghapus baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

    -- Buat Delta Table target 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");
    
    -- Masukkan data.
    INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
    
    -- Lihat data yang 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 data di mana partisi adalah 01 dan 02, dan val adalah 2.
    DELETE FROM mf_dt WHERE val = 2  AND dd='01' AND hh='02';
    
    -- Lihat hasilnya. Tabel sekarang hanya berisi data di mana val adalah 1 dan 3.
    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 |
    +------------+------------+----+----+

Bersihkan data kolom

Anda dapat menggunakan perintah CLEAR COLUMN untuk membersihkan data dari kolom dalam tabel standar. Operasi ini menghapus data yang tidak lagi digunakan dari disk dan mengatur nilai kolom menjadi NULL, yang membantu mengurangi biaya penyimpanan.

• Sintaks

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

• Parameter

  Parameter	Deskripsi
  table_name	Nama tabel yang datanya ingin Anda bersihkan.
  column1 , column2 ...	Nama kolom yang datanya ingin Anda bersihkan.
  PARTITION	Menentukan partisi. Jika tidak ditentukan, operasi berlaku untuk semua partisi.
  pt_spec	Deskripsi partisi, dalam format (partition_col1 = PARTITION_col_value1, PARTITION_col2 = PARTITION_col_value2, ...).
  WITHOUT TOUCH	Jika ditentukan, LastDataModifiedTime tidak diperbarui. Jika tidak ditentukan, LastDataModifiedTime diperbarui.

  Catatan

  Saat ini, `WITHOUT TOUCH` ditentukan secara default. Pada rilis mendatang, perilaku pembersihan data kolom tanpa menentukan `WITHOUT TOUCH` akan didukung. Artinya, jika `WITHOUT TOUCH` tidak ditentukan, LastDataModifiedTime akan diperbarui.

• Batasan

  • Anda tidak dapat melakukan operasi pembersihan kolom pada kolom dengan Kendala NOT NULL. Anda dapat menghapus kendala NOT NULL secara manual:

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

  • Pembersihan data kolom tidak didukung untuk tabel ACID.

  • Pembersihan data kolom tidak didukung untuk Tabel terkluster.

  • Pembersihan data kolom dalam tipe bersarang tidak didukung.

  • Pembersihan semua kolom tidak didukung. Perintah `DROP TABLE` memberikan efek yang sama dengan kinerja lebih baik.

• Perhatian

  • Operasi `CLEAR COLUMN` tidak mengubah properti Archive tabel.

  • Operasi `CLEAR COLUMN` pada kolom tipe bersarang mungkin gagal.

    Kegagalan ini terjadi jika Anda melakukan operasi `CLEAR COLUMN` pada tabel yang berisi tipe bersarang kolom-kolom sementara penyimpanan kolom untuk tipe bersarang dinonaktifkan.

  • Perintah `CLEAR COLUMN` bergantung pada Layanan Penyimpanan online. Tugas mungkin lambat jika perlu antri selama periode volume pekerjaan tinggi.

  • Operasi `CLEAR COLUMN` memerlukan sumber daya komputasi untuk membaca dan menulis data. Untuk pengguna langganan, ini mengonsumsi sumber daya komputasi. Untuk pengguna bayar sesuai pemakaian, ini dikenai biaya yang sama seperti pekerjaan SQL. (Fitur ini saat ini dalam pratinjau undangan dan sementara 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.
    ALTER TABLE mf_cc ADD IF  NOT EXISTS PARTITION (ds='20230509', hr='1641');
    
    -- Masukkan data.
    INSERT INTO mf_cc PARTITION (ds='20230509', hr='1641') 
         VALUES("key","value",1,22,3,4);
         
    -- Kueri data.     
    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 kolom.
    ALTER TABLE mf_cc PARTITION(ds='20230509', hr='1641') 
                      CLEAR COLUMN key,a1 
        WITHOUT TOUCH;
    -- Kueri data.     
    SELECT * FROM mf_cc WHERE ds='20230509' AND  hr='1641';
    -- Hasil berikut dikembalikan. Data di kolom key dan a1 telah menjadi null.
    +-----+-------+------------+------------+--------+------+---------+-----+
    | key | value | a1         | a2         | a3     | a4   | ds      | hr  |
    +-----+-------+------------+------------+--------+------+---------+-----+
    | null| value | null       | 22         | 3      | 4    | 20230509| 1641|
    +-----+-------+------------+------------+--------+------+---------+-----+

  • Gambar berikut menunjukkan perubahan ukuran total penyimpanan tabel lineitem (dalam format AliORC) saat setiap kolom dibersihkan menggunakan perintah `CLEAR COLUMN`. Tabel lineitem memiliki 16 kolom dengan berbagai tipe, termasuk BIGINT, DECIMAL, CHAR, DATE, dan VARCHAR.

    Seperti yang terlihat, setelah 16 kolom tabel diatur menjadi NULL secara berurutan oleh perintah `CLEAR COLUMN`, ruang penyimpanan total berkurang sebesar 99,97% (dari awal 186.783.526 byte menjadi 236.715 byte).

    Catatan

    • Jumlah ruang yang dihemat oleh operasi `CLEAR COLUMN` tergantung pada tipe data dan nilai yang benar-benar disimpan di kolom tersebut. Sebagai contoh, membersihkan kolom l_extendedprice yang bertipe DECIMAL menghemat 24,2% ruang (dari 146.538.799 byte menjadi 111.138.117 byte), yang jauh lebih baik daripada rata-rata.

    • Ketika semua kolom diatur menjadi NULL, ukuran tabel adalah 236.715 byte, bukan 0. Hal ini karena struktur file tabel masih ada. Kolom NULL menempati ruang penyimpanan kecil, dan sistem juga perlu menyimpan informasi footer file.

Perbarui data (UPDATE)

Operasi UPDATE memperbarui nilai dalam satu atau beberapa kolom untuk baris-baris di tabel Transaksional atau Delta Table.

• Sintaks

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

• Parameter

  • table_name: Wajib. Nama tabel Transaksional atau Delta Table untuk operasi UPDATE.

  • alias: Opsional. Alias untuk tabel.

  • col1_name, col2_name: Wajib. Nama kolom yang akan dimodifikasi. Anda harus memperbarui setidaknya satu kolom.

  • value1, value2: Wajib. Nilai baru untuk kolom-kolom tersebut. Anda harus memperbarui setidaknya satu nilai kolom.

  • where_condition: Opsional. Klausa WHERE untuk memfilter data. Untuk informasi selengkapnya mengenai klausa WHERE, lihat Sintaks SELECT. Jika klausa WHERE tidak disertakan, seluruh data dalam tabel akan diperbarui.

  • additional_tables: Opsional. Klausul FROM.

    Pernyataan UPDATE mendukung klausa FROM, yang dapat menyederhanakan pernyataan UPDATE. Tabel berikut membandingkan pernyataan UPDATE yang menggunakan klausa FROM dengan yang tidak.

    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) AS v FROM src GROUP BY k) b WHERE target.k = b.k;

    Seperti yang ditunjukkan oleh contoh kode:

    • Ketika Anda memperbarui satu baris di tabel target menggunakan beberapa baris dari tabel sumber, Anda harus menggunakan operasi agregat untuk memastikan data sumber unik karena sistem tidak tahu baris sumber mana yang harus digunakan. Sintaks yang tidak menggunakan klausa `FROM` kurang ringkas. Sintaks dengan klausa `FROM` lebih sederhana dan mudah dipahami.

    • Ketika Anda melakukan pembaruan join, jika Anda hanya ingin memperbarui irisan data, sintaks yang tidak menggunakan klausa `FROM` memerlukan kondisi `WHERE` tambahan dan kurang ringkas dibandingkan sintaks yang menggunakan klausa `FROM`.

• Contoh

  • Contoh 1: Buat tabel non-partisi bernama acid_update, impor data, lalu jalankan operasi UPDATE untuk memperbarui kolom baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

    -- Buat tabel Transaksional bernama acid_update.  
    CREATE TABLE IF  NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");
    
    -- Masukkan data.
    INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);
    
    -- Lihat data yang dimasukkan.
    SELECT * FROM acid_update; 
    
    -- Hasil berikut dikembalikan:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 2          |
    | 3          |
    | 2          |
    +------------+
    
    -- Perbarui nilai id menjadi 4 untuk semua baris di mana id adalah 2.
    UPDATE acid_update SET id = 4 WHERE id = 2; 
    
    -- Lihat hasil pembaruan. 2 telah diperbarui menjadi 4.
    SELECT * FROM acid_update; 
    
    -- Hasil berikut dikembalikan:
    +------------+
    | id         |
    +------------+
    | 1          |
    | 3          |
    | 4          |
    | 4          |
    +------------+

  • Contoh 2: Buat tabel berpartisi bernama acid_update, impor data, lalu jalankan operasi UPDATE untuk memperbarui kolom baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

    -- Buat tabel Transaksional bernama acid_update_pt. 
    CREATE TABLE IF  NOT EXISTS acid_update_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");
    
    -- Tambahkan partisi.
    ALTER TABLE acid_update_pt ADD IF  NOT EXISTS PARTITION (ds= '2019');
    
    -- Masukkan data.
    INSERT OVERWRITE TABLE acid_update_pt PARTITION (ds='2019') VALUES(1),(2),(3);
    
    -- Lihat data yang dimasukkan.
    SELECT * FROM acid_update_pt WHERE ds = '2019'; 
    
    -- Hasil berikut dikembalikan:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 1          | 2019       |
    | 2          | 2019       |
    | 3          | 2019       |
    +------------+------------+
    
    -- Perbarui kolom dalam baris tertentu. Atur nilai id menjadi 4 untuk semua baris di mana partisi adalah 2019 dan id adalah 2.
    UPDATE acid_update_pt SET id = 4 WHERE ds = '2019' AND id = 2; 
    
    -- Lihat hasil pembaruan. 2 telah diperbarui menjadi 4.
    SELECT * FROM acid_update_pt WHERE ds = '2019';
    
    -- Hasil berikut dikembalikan:
    +------------+------------+
    | id         | ds         |
    +------------+------------+
    | 4          | 2019       |
    | 1          | 2019       |
    | 3          | 2019       |
    +------------+------------+

  • Contoh 3: Buat tabel target bernama acid_update_t dan tabel terkait bernama acid_update_s untuk memperbarui beberapa nilai kolom sekaligus. Berikut adalah perintah contohnya:

    -- Buat tabel Transaksional target yang akan diperbarui, bernama acid_update_t, 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); 
    
    -- Masukkan data.
    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 dengan konstanta.
    UPDATE acid_update_t SET (value1, value2) = (60,61);
    -- Kueri data hasil di tabel target untuk Metode 1.
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 60         | 61         |
    | 3          | 60         | 61         |
    | 4          | 60         | 61         |
    +------------+------------+------------+
    
    -- Metode 2: Perbarui join. Aturannya adalah left join dari acid_update_t ke 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);
    -- Kueri data hasil di tabel target untuk Metode 2.
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+
    
    -- Metode 3 (pembaruan berdasarkan hasil Metode 2): Perbarui join. Aturannya adalah menambahkan kondisi filter untuk hanya memperbarui irisan.
    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 data hasil di tabel target untuk Metode 3.
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+
    
    -- Metode 4 (pembaruan berdasarkan hasil Metode 3): Perbarui join dengan hasil agregat.
    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 data hasil di tabel target untuk Metode 4.
    SELECT * FROM acid_update_t;
    
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    | 4          | NULL       | NULL       |
    +------------+------------+------------+

  • Contoh 4: Kueri join sederhana yang melibatkan dua tabel. Berikut adalah perintah contohnya:

    -- Buat tabel target untuk pembaruan, acid_update_t, dan tabel terkait, acid_update_s. 
    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);
    
    -- Masukkan data.
    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        |
    +------------+------------+------------+
    
    -- Perbarui join. Tambahkan kondisi filter ke tabel target untuk hanya memperbarui irisan.
    UPDATE acid_update_t SET value1 = b.value1, value2 = b.value2
    FROM acid_update_s b WHERE acid_update_t.id = b.id;
    -- Perintah berikut setara dengan yang di atas.
    UPDATE acid_update_t a SET a.value1 = b.value1, a.value2 = b.value2 
    FROM acid_update_s b WHERE a.id = b.id;
    
    -- Lihat hasil pembaruan. 20 diperbarui menjadi 200, 21 menjadi 201, 30 menjadi 300, dan 31 menjadi 301.
    SELECT * FROM acid_update_t;
    -- Hasil berikut dikembalikan:
    +------------+------------+------------+
    | id         | value1     | value2     |
    +------------+------------+------------+
    | 4          | 40         | 41         |
    | 2          | 200        | 201        |
    | 3          | 300        | 301        |
    +------------+------------+------------+                          

  • Contoh 5: Kueri join kompleks yang melibatkan beberapa tabel. Berikut adalah perintah contohnya:

    -- Buat tabel target untuk pembaruan, acid_update_t, dan tabel terkait, acid_update_s. 
    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);
    
    -- Masukkan data.
    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        |
    +------------+------------+------------+
    
    -- Perbarui join, dan filter kedua tabel sumber dan target dalam klausa WHERE.
    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 data di tabel acid_update_t dengan id 5 yang memenuhi kondisi. Nilai value1 yang sesuai diperbarui menjadi 500, dan 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: Perintah berikut adalah contoh cara membuat Delta table bernama mf_dt, impor data, dan eksekusi operasi UPDATE untuk menghapus baris yang memenuhi kondisi tertentu:

    -- Buat Delta Table target 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");
    
    -- Masukkan data.
    INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                     VALUES (1, 1), (2, 2), (3, 3);
    
    -- Lihat data yang 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 kolom dalam baris tertentu. Atur nilai val menjadi 30 untuk semua baris di mana partisi adalah 01 dan 02, dan pk adalah 3.
    -- 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 val untuk baris dengan pk=3 diperbarui menjadi 30.
    +------------+------------+----+----+
    | pk         | val        | dd | hh |
    +------------+------------+----+----+
    | 1          | 1          | 01 | 02 |
    | 3          | 30         | 01 | 02 |
    | 2          | 2          | 01 | 02 |
    +------------+------------+----+----+

Gabungkan file tabel Transaksional

Penyimpanan fisik dasar tabel Transaksional terdiri dari file dasar dan file Delta, yang tidak dapat dibaca secara langsung. Ketika Anda menjalankan operasi UPDATE atau DELETE pada tabel Transaksional, file dasar tidak dimodifikasi. Sebaliknya, file Delta ditambahkan. Artinya, semakin banyak pembaruan atau penghapusan yang Anda lakukan, semakin besar penyimpanan yang digunakan tabel tersebut. Akumulasi banyak file Delta dapat meningkatkan biaya penyimpanan dan kueri selanjutnya.

Menjalankan beberapa operasi UPDATE atau DELETE pada tabel atau partisi yang sama menghasilkan banyak file Delta. Saat sistem membaca data, sistem harus memuat file-file Delta ini untuk menentukan baris mana yang diperbarui atau dihapus. Banyaknya file Delta dapat menurunkan kinerja pembacaan data. Dalam kasus ini, Anda dapat menggabungkan file dasar dan file Delta untuk mengurangi penyimpanan dan meningkatkan kinerja pembacaan data.

• Sintaks

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

• Parameter

  Parameter	Wajib	Deskripsi
  table_name	Ya	Nama tabel Transaksional yang file-filenya ingin Anda gabungkan.
  partition_key	Tidak	Jika tabel Transaksional adalah tabel berpartisi, tentukan nama kolom kunci partisi.
  partition_value	Tidak	Jika tabel Transaksional adalah tabel berpartisi, tentukan nilai untuk kolom kunci partisi.
  major|minor	Ya	Anda harus memilih salah satu. Perbedaannya adalah: minor: Menggabungkan hanya file dasar dan semua file Delta dasarnya, menghilangkan file Delta. major: Tidak hanya menggabungkan file dasar dan semua file Delta dasarnya untuk menghilangkan file Delta, tetapi juga menggabungkan file-file kecil dalam file dasar tabel yang sesuai. Jika file dasar kecil (kurang dari 32 MB) atau jika file Delta ada, ini setara dengan menjalankan operasi INSERT OVERWRITE pada tabel lagi. Namun, jika file dasar cukup besar (lebih besar atau sama dengan 32 MB) dan tidak ada file Delta, file tersebut tidak akan ditulis ulang.

• Perhatian

  File-file kecil yang digabungkan oleh operasi `compact` dihapus setelah satu hari. Jika Anda menggunakan fitur cadangan lokal untuk memulihkan riwayat yang bergantung pada file-file kecil ini, pemulihan akan gagal karena file-file tersebut hilang.

• Contoh

  • Contoh 1: Gabungkan file untuk tabel Transaksional acid_delete. Berikut adalah perintah contohnya:

    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 untuk tabel Transaksional acid_update_pt. Berikut adalah perintah contohnya:

    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 menjalankan pernyataan UPDATE, muncul error ODPS-0010000:System internal error - fuxi job failed, caused by: Data Set should contain exactly one row.

  • Penyebab: Baris yang akan diperbarui tidak memiliki korespondensi satu-ke-satu dengan data dalam hasil subkueri. Sistem tidak dapat menentukan baris mana yang harus diperbarui. Berikut adalah perintah contohnya:

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

    Subkueri SELECT d_country, d_manager FROM store_delta sd WHERE sd.s_store_sk = store.s_store_sk digunakan untuk join dengan store_delta, dan data dari store_delta digunakan untuk memperbarui store. Asumsikan kolom s_store_sk dalam tabel store berisi tiga baris data: [1, 2, 3]. Jika kolom s_store_sk dalam tabel store_delta memiliki dua baris data, [1, 1], korespondensi satu-ke-satu tidak ada, dan eksekusi gagal.

  • Solusi: Pastikan baris yang akan diperbarui memiliki korespondensi satu-ke-satu dengan data dalam hasil subkueri.

• Masalah 2:

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

  • Penyebab: Versi klien MaxCompute dalam grup sumber daya eksklusif untuk DataWorks tidak mendukung perintah compact.

  • Solusi: Hubungi tim dukungan teknis melalui grup DingTalk DataWorks untuk meningkatkan versi klien MaxCompute dalam grup sumber daya eksklusif.