MaxCompute：Menyisipkan Data ke dalam atau Menimpa Data pada Tabel atau Partisi Statis (INSERT INTO dan INSERT OVERWRITE)

Prasyarat

Sebelum mengeksekusi pernyataan INSERT INTO atau INSERT OVERWRITE, pastikan Anda memiliki izin Update pada tabel tujuan dan izin Select pada metadata tabel sumber. Untuk informasi lebih lanjut, lihat Izin MaxCompute.

Fitur

Untuk informasi tentang cara menyisipkan data ke dalam partisi dinamis, lihat Menyisipkan atau Menimpa Data ke Partisi Dinamis (DYNAMIC PARTITION).

Batasan

Sintaks

INSERT {INTO|OVERWRITE} TABLE <table_name> [PARTITION (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
<select_statement>
FROM <from_statement>
[ZORDER BY <zcol_name> [, <zcol_name> ...]];

Tabel berikut menunjukkan parameter:

Perbedaan antara ZORDER BY dan SORT BY:

• Klausa ZORDER BY mendukung dua mode: Z-Ordering lokal dan Z-Ordering global. Mode default adalah local Z-Ordering. Dalam mode Z-Ordering lokal, hanya data dalam satu file, bukan data global, yang diurutkan berdasarkan klausa ZORDER BY. Jika data didistribusikan dalam beberapa file, data dikumpulkan secara longgar. Akibatnya, pengabaian data mungkin dilakukan dengan cara yang kurang efektif. Untuk menyelesaikan masalah ini, MaxCompute versi terbaru mendukung mode global Z-Ordering. Untuk menggunakan mode ini, Anda harus menambahkan konfigurasi set odps.sql.default.zorder.type=global;.

  Saat menggunakan klausa ZORDER BY, perhatikan batasan berikut:

  • Anda dapat mengeksekusi klausa ZORDER BY untuk mengurutkan data hanya dalam satu partisi dalam tabel partisi pada satu waktu.

  • Jumlah bidang tempat Anda mengeksekusi klausa ZORDER BY harus berkisar antara 2 hingga 4.

  • Jika tabel tujuan adalah tabel terkluster, klausa ZORDER BY tidak didukung.

  • ZORDER BY dapat digunakan bersama dengan DISTRIBUTE BY tetapi tidak dapat digunakan bersama dengan ORDER BY, CLUSTER BY, atau SORT BY.

  Catatan

  Jika Anda menggunakan klausa ZORDER BY untuk menyisipkan data, lebih banyak sumber daya dan waktu dikonsumsi.

• Klausa SORT BY menentukan metode yang digunakan untuk mengurutkan data dalam satu file. Jika Anda tidak menentukan klausa SORT BY, data dalam satu file diurutkan dalam mode local Z-Ordering.

Contoh: tabel umum

• Contoh 1: Eksekusi pernyataan INSERT INTO untuk menambahkan data ke tabel non-partisi bernama websites. Contoh pernyataan:

  -- Buat tabel non-partisi bernama websites.
  CREATE TABLE IF NOT EXISTS websites
  (id INT,
   name STRING,
   url STRING
  );
  -- Buat tabel non-partisi bernama apps.
  CREATE TABLE IF NOT EXISTS apps
  (id INT,
   app_name STRING,
   url STRING
  );
  -- Tambahkan data ke tabel apps. Bentuk singkat dari INSERT INTO TABLE table_name adalah INSERT INTO table_name.
  INSERT INTO apps (id,app_name,url) VALUES 
  (1,'Aliyun','https://www.aliyun.com');
  -- Salin data dari tabel apps dan tambahkan data ke tabel websites.
  INSERT INTO websites (id,name,url) SELECT id,app_name,url
  FROM apps;
  -- Eksekusi pernyataan SELECT untuk melihat data dalam tabel websites.
  SELECT * FROM websites;

  Hasil berikut dikembalikan:

  -- Hasil berikut dikembalikan:
  +------------+------------+------------+
  | id         | name       | url        |
  +------------+------------+------------+
  | 1          | Aliyun     | https://www.aliyun.com |
  +------------+------------+------------+

• Contoh 2: Eksekusi pernyataan INSERT INTO untuk menambahkan data ke tabel partisi bernama sale_detail. Contoh pernyataan:

  -- Buat tabel partisi bernama sale_detail.
  CREATE TABLE IF NOT EXISTS sale_detail
  (
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
  )
  PARTITIONED BY (sale_date STRING, region STRING);
  
  -- Tambahkan partisi ke tabel sale_detail. Operasi ini opsional. Jika Anda tidak membuat partisi terlebih dahulu, partisi dibuat secara otomatis saat Anda menulis data ke tabel.
  ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
  
  -- Tambahkan data ke tabel sale_detail. Bentuk singkat dari INSERT INTO TABLE table_name adalah INSERT INTO table_name. Kata kunci TABLE dalam INSERT OVERWRITE TABLE table_name tidak dapat dihilangkan.
  INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
  
  -- Aktifkan pemindaian tabel penuh hanya untuk sesi saat ini. Eksekusi pernyataan SELECT untuk melihat data dalam tabel sale_detail.
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM sale_detail;

  Hasil berikut dikembalikan:

  -- Hasil berikut dikembalikan:
  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  | s3         | c3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• Contoh 3: Eksekusi pernyataan INSERT OVERWRITE untuk menimpa data dalam tabel sale_detail_insert. Contoh pernyataan:

  -- Buat tabel sale_detail_insert yang memiliki skema yang sama dengan tabel sale_detail.
  CREATE TABLE sale_detail_insert LIKE sale_detail;
  
  -- Tambahkan partisi ke tabel sale_detail_insert. Operasi ini opsional. Jika Anda tidak membuat partisi terlebih dahulu, partisi dibuat secara otomatis saat Anda menulis data ke tabel.
  ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');
  
  -- Ekstrak data dari tabel sale_detail dan sisipkan data ke dalam tabel sale_detail_insert. Nama kolom kunci partisi dalam tabel sale_detail_insert tidak perlu dideklarasikan dan tidak dapat diatur ulang.
  -- Jika tabel sale_detail_insert berisi partisi statis, nilai kolom kunci partisi dideklarasikan dalam PARTITION(). Nilai-nilai ini tidak perlu disertakan dalam select_statement. Anda perlu mencari nama kolom berdasarkan hanya urutan kolom umum dalam tabel sale_detail_insert dan memetakan nilai kolom yang dideklarasikan secara berurutan ke kolom dalam tabel sale_detail_insert. Jika tabel sale_detail_insert berisi partisi dinamis, nama kolom kunci partisi harus disertakan dalam select_statement. Untuk informasi lebih lanjut, lihat Menyisipkan atau Menimpa Data ke Partisi Dinamis (DYNAMIC PARTITION).
  SET odps.sql.allow.fullscan=true;
  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
    SELECT 
    shop_name, 
    customer_id,
    total_price 
    FROM sale_detail
    ZORDER BY customer_id, total_price;
  
  -- Aktifkan pemindaian tabel penuh hanya untuk sesi saat ini. Eksekusi pernyataan SELECT untuk melihat data dalam tabel sale_detail_insert.
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM sale_detail_insert;

  Hasil berikut dikembalikan:

  -- Hasil berikut dikembalikan:
  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  | s3         | c3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• Contoh 4: Eksekusi pernyataan INSERT OVERWRITE untuk menimpa data dalam tabel sale_detail_insert dan menyesuaikan urutan kolom dalam select_statement. Pemetaan antara tabel sumber dan tabel tujuan didasarkan pada urutan kolom dalam select_statement, bukan pemetaan antara nama kolom dalam kedua tabel. Contoh pernyataan:

  SET odps.sql.allow.fullscan=true;
  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
      SELECT customer_id, shop_name, total_price FROM sale_detail;    
  
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM sale_detail_insert;                  

  Hasil berikut dikembalikan:

  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | c1         | s1          | 100.1       | 2013       | china      |
  | c2         | s2          | 100.2       | 2013       | china      |
  | c3         | s3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

  Saat membuat tabel sale_detail_insert, urutan kolom didefinisikan sebagai shop_name string, customer_id string, dan kemudian total_price bigint. Namun, data disisipkan dari tabel sale_detail ke tabel sale_detail_insert berdasarkan urutan customer_id, shop_name, dan kemudian total_price. Akibatnya, data dalam kolom sale_detail.customer_id disisipkan ke dalam kolom sale_detail_insert.shop_name, dan data dalam kolom sale_detail.shop_name disisipkan ke dalam kolom sale_detail_insert.customer_id.

• Contoh 5: Jika Anda menyisipkan data ke partisi, kolom kunci partisi dari partisi tersebut tidak dapat disertakan dalam select_statement. Setelah pernyataan berikut dieksekusi, kesalahan akan dikembalikan. Hal ini karena sale_date dan region adalah kolom kunci partisi. Kolom-kolom ini tidak dapat disertakan dalam select_statement jika pernyataan INSERT OVERWRITE atau INSERT INTO digunakan untuk menyisipkan atau memperbarui data ke partisi statis. Contoh pernyataan penggunaan yang salah:

  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
     SELECT shop_name, customer_id, total_price, sale_date, region FROM sale_detail;

• Contoh 6: pt_spec dalam PARTITION() harus berupa konstanta, bukan ekspresi. Contoh pernyataan penggunaan yang salah:

  INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
     SELECT shop_name, customer_id, total_price FROM sale_detail;

• Contoh 7: Eksekusi pernyataan INSERT OVERWRITE untuk menimpa data dalam tabel mf_src dan menyisipkan data ke tabel mf_zorder_src serta mengurutkan data dalam tabel mf_zorder_src dalam mode Z-Ordering global. Contoh pernyataan:

  -- Buat tabel mf_src, sisipkan data ke dalam tabel, lalu kueri data dari tabel.
  CREATE TABLE mf_src (key STRING, value STRING);
  
  INSERT OVERWRITE TABLE mf_src
  SELECT a, b FROM VALUES ('1', '1'),('3', '3'),('2', '2')
  AS t(a, b);
  
  SELECT * FROM mf_src;
  
  -- Hasil berikut dikembalikan:
  +-----+-------+
  | key | value |
  +-----+-------+
  | 1   | 1     |
  | 3   | 3     |
  | 2   | 2     |
  +-----+-------+
  
  -- Buat tabel mf_zorder_src yang memiliki skema yang sama dengan tabel mf_src.
  CREATE TABLE mf_zorder_src LIKE mf_src;
  
  -- Sisipkan data ke dalam tabel mf_zorder_src, urutkan data dalam tabel dalam mode Z-Ordering global, lalu kueri data dari tabel.
  SET odps.sql.default.zorder.type=global;
  INSERT OVERWRITE TABLE mf_zorder_src
  SELECT key, value FROM mf_src 
  ZORDER BY key, value;
  
  SELECT * FROM mf_zorder_src;

  Hasil berikut dikembalikan:

  -- Hasil berikut dikembalikan:
  +-----+-------+
  | key | value |
  +-----+-------+
  | 1   | 1     |
  | 2   | 2     |
  | 3   | 3     |
  +-----+-------+

• Contoh 8: Eksekusi pernyataan INSERT OVERWRITE untuk menimpa data dalam tabel yang ada bernama target. Contoh pernyataan:

  -- Timpa data dalam tabel yang ada bernama target.
  SET odps.sql.default.zorder.type=global;
  INSERT OVERWRITE TABLE target
  SELECT key, value FROM target 
  ZORDER BY key, value;

Contoh: Tabel Delta

Buat tabel Delta bernama mf_dt dan eksekusi pernyataan INSERT untuk menyisipkan data ke dalam serta menimpa data dalam tabel tersebut.

-- 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 uji ke partisi yang kolom kunci partisi dd dan hh-nya keduanya 01 dalam tabel mf_dt.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);

-- Kueri data dari partisi tujuan dalam tabel mf_dt.                
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- Hasil berikut dikembalikan:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Eksekusi pernyataan INSERT INTO untuk menambahkan data ke partisi tujuan dalam tabel mf_dt.
INSERT INTO TABLE mf_dt PARTITION(dd='01', hh='01') 
            VALUES (3, 30), (4, 4), (5, 5);
            
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- Hasil berikut dikembalikan:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Eksekusi pernyataan INSERT OVERWRITE untuk menimpa data dalam partisi tujuan tabel mf_dt.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- Hasil berikut dikembalikan:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Eksekusi pernyataan INSERT INTO untuk menulis data ke partisi yang kolom kunci partisi dd-nya 01 dan kolom kunci partisi hh-nya 02 dalam tabel mf_dt.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 11), (2, 22), (3, 32);
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
-- Hasil berikut dikembalikan:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

-- Aktifkan pemindaian tabel penuh hanya untuk sesi saat ini. Eksekusi pernyataan SELECT untuk mengkueri data dalam tabel mf_dt.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_dt;
-- Hasil berikut dikembalikan:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

Praktik Terbaik

Fitur Z-Ordering tidak cocok untuk semua skenario bisnis. MaxCompute tidak memberikan panduan spesifik tentang apakah dan bagaimana menggunakan fitur Z-Ordering. Dalam banyak kasus, Anda harus menentukan sendiri apakah akan menggunakan fitur ini berdasarkan kebutuhan bisnis Anda. Evaluasi komprehensif diperlukan untuk memastikan bahwa biaya komputasi tambahan yang dihasilkan saat mengurutkan data menggunakan fitur Z-Ordering lebih kecil daripada penghematan biaya penyimpanan dan konsumsi komputasi hilir. Berikut adalah beberapa saran berdasarkan pengalaman praktik. Anda juga dapat memberikan umpan balik dan rekomendasi.

Skenario di mana Anda perlu menggunakan pengindeksan terkluster secara prioritas dibandingkan Z-Ordering

• Jika kondisi filter terdiri dari kombinasi awalan, seperti kolom a, kombinasi a dan b, atau kombinasi a, b, dan c, pengindeksan terkluster (ORDER BY a, b, c) lebih efektif dibandingkan klausa ZORDER BY. Klausa ORDER BY memberikan hasil pengurutan yang lebih baik pada kolom pertama tetapi memiliki dampak kecil pada kolom lainnya. Sementara itu, ZORDER BY memberikan bobot yang sama untuk setiap kolom, sehingga hasil pengurutannya pada kolom tertentu kurang efektif dibandingkan dengan hasil pengurutan ORDER BY pada kolom pertama.

• Jika beberapa kolom sering digunakan sebagai kunci gabungan, pengelompokan rentang atau pengelompokan hash lebih cocok untuk kolom-kolom tersebut. Fitur Z-Ordering MaxCompute hanya dapat digunakan untuk mengurutkan data dalam satu file. Mesin SQL tidak peka terhadap data yang diurutkan menggunakan fitur Z-Ordering, namun peka terhadap data yang diurutkan menggunakan pengindeksan terkluster. Anda dapat mengoptimalkan kinerja penggabungan dengan lebih baik menggunakan pengindeksan terkluster selama tahap perencanaan kueri.

• Jika Anda sering mengeksekusi klausa GROUP BY atau ORDER BY untuk kolom tertentu, pengindeksan terkluster dapat memberikan kinerja yang lebih baik.

Saran untuk menggunakan fitur Z-Ordering

• Pilih kolom yang sering digunakan dalam kondisi filter, terutama kolom yang sering digabungkan untuk penyaringan.

• Pastikan jumlah kolom tempat Anda mengeksekusi klausa ZORDER BY tidak melebihi 4. Jika Anda mengeksekusi klausa ZORDER BY pada sejumlah besar kolom, kinerja pengurutan untuk setiap kolom menjadi buruk. Jika hanya satu kolom yang perlu diurutkan, kami merekomendasikan Anda menggunakan pengindeksan terkluster daripada Z-Ordering.

• Jumlah nilai unik dari kolom yang dipilih tidak boleh terlalu besar atau terlalu kecil. Sebagai contoh, kolom gender hanya memiliki dua nilai unik. Dalam hal ini, pengurutan nilai-nilai tersebut tidak bermakna. Namun, jika sebuah kolom memiliki jumlah nilai unik yang sangat besar, pengurutan nilai-nilai tersebut menghasilkan biaya tinggi karena klausa ZORDER BY perlu menyimpan semua nilai kolom dalam memori untuk menghitung nilai Z.

• Pastikan jumlah data tabel yang ingin Anda urutkan tidak terlalu besar atau terlalu kecil. Jika jumlah data tabel yang ingin Anda urutkan terlalu kecil, efek pengurutan menggunakan Z-Ordering tidak terlihat jelas. Jika jumlah data tabel yang ingin Anda urutkan terlalu besar, biaya tinggi dihasilkan saat data diurutkan menggunakan Z-Ordering. Sebagai contoh, keluaran tugas baseline mungkin tertunda.