Cara menggunakan INSERT OVERWRITE SELECT untuk menimpa data - AnalyticDB

Topik ini menjelaskan INSERT OVERWRITE SELECT, metode berkinerja tinggi untuk menulis data ke tabel di AnalyticDB for MySQL. Topik ini mencakup kasus penggunaan, cara kerja, tindakan pencegahan, sintaksis, dan penulisan asinkron.

Cara Kerja

Pernyataan INSERT OVERWRITE SELECT pertama-tama menghapus semua data yang ada dari suatu partisi, lalu menulis data baru ke dalamnya secara Batch.

Jika tabel target adalah tabel partisi, INSERT OVERWRITE SELECT hanya akan menimpa partisi-partisi tempat data baru dimasukkan. Partisi lain tidak terpengaruh.
Jika tabel target adalah tabel non-partisi, INSERT OVERWRITE SELECT menghapus seluruh data yang ada di tabel tersebut, lalu menulis data baru secara Batch.

Pekerjaan penulisan untuk satu tabel dieksekusi secara berurutan, artinya konkurensi penulisan untuk satu tabel adalah 1 dan tidak dapat diubah. Konkurensi penulisan default untuk suatu kluster adalah 2 guna memastikan kinerja pekerjaan tunggal dan mencegah beban berlebih pada kluster. Hindari mengubah nilai ini karena dapat berdampak negatif pada kinerja dan meningkatkan beban kluster.

Catatan

Untuk menyesuaikan konkurensi penulisan, Submit a ticket kepada dukungan teknis untuk Evaluasi.

Ikhtisar

Fitur-fitur INSERT OVERWRITE SELECT adalah sebagai berikut:

Konsumsi resource tinggi: INSERT OVERWRITE SELECT merupakan operasi yang intensif resource. Lakukan operasi ini selama jam sepi agar tidak memengaruhi kinerja kluster.
Visibilitas Batch: Data yang ditulis oleh pekerjaan tidak terlihat hingga pekerjaan selesai, saat itu seluruh data menjadi terlihat sekaligus.
Penimpaan partisi: INSERT OVERWRITE SELECT menimpa data di partisi-partisi yang sesuai pada tabel target.
Pembuatan indeks Otomatis: Sistem membangun Indeks secara sinkron saat data ditulis. Setelah pekerjaan penulisan selesai, tabel target memiliki Indeks yang dapat meningkatkan kinerja kueri.

Kasus penggunaan umum untuk INSERT OVERWRITE SELECT adalah sebagai berikut:

Penulisan data tingkat partisi.
Inisialisasi data (penulisan data lengkap).
Penulisan data massal skala besar. Metode ini tidak disarankan untuk volume data kecil.

Tindakan Pencegahan

Jangan gunakan INSERT OVERWRITE SELECT dan metode penulisan real-time (seperti INSERT INTO, REPLACE INTO, DELETE, dan UPDATE) untuk menulis ke tabel yang sama secara konkuren. Jika dilakukan, data dari penulisan real-time akan hilang.

Sintaksis

INSERT OVERWRITE table_name (column_name[,...])
select_statement

Parameter

table_name: Nama tabel target.
column_name: Nama kolom di tabel target.
select_statement: Pernyataan SELECT.
Tipe data setiap kolom dalam pernyataan SELECT harus sesuai dengan tipe data kolom yang bersesuaian di tabel target.
Jika jumlah kolom dalam pernyataan SELECT lebih banyak daripada jumlah kolom di tabel target, operasi penulisan gagal. Jika jumlah kolom dalam pernyataan SELECT lebih sedikit daripada jumlah kolom di tabel target, kolom-kolom sisa di tabel target diisi dengan nilai default-nya. Jika suatu kolom tidak memiliki nilai default, sistem mengisinya dengan NULL.

Contoh

Data Sampel

Persiapkan tabel dan data uji untuk contoh penimpaan.

Buat tabel sumber bernama test_source dan tabel target bernama test_target.

CREATE TABLE test_source (a BIGINT, b BIGINT) 
DISTRIBUTED BY HASH(a);

CREATE TABLE test_target (a BIGINT, b BIGINT) 
DISTRIBUTED BY HASH(a) 
PARTITION BY VALUE(b) LIFECYCLE 10;

Tulis data uji ke tabel sumber test_source.

INSERT INTO test_source VALUES (1,1);  
INSERT INTO test_source VALUES (1,2); 
INSERT INTO test_source VALUES (1,3);  
INSERT INTO test_source VALUES (2,1); 
INSERT INTO test_source VALUES (2,2); 
INSERT INTO test_source VALUES (2,3);

Timpa data

Masukkan data awal ke tabel test_target.

INSERT OVERWRITE test_target 
SELECT * FROM test_source WHERE a = 1;

Kueri tabel test_target. Hasil kueri sebagai berikut.

+-----+------+
|1    |1     |
|1    |2     |
|1    |3     |
+-----+------+

Timpa partisi dengan b=1 di tabel test_target.

INSERT OVERWRITE test_target (a,b)
SELECT a,b FROM test_source 
WHERE a = 2 AND b = 1;

Setelah penimpaan, hasil kueri test_target adalah:

+-----+------+
|2    |1     |
|1    |2     |
|1    |3     |
+-----+------+

Timpa partisi dengan b=2 dan b=3 di tabel test_target.

INSERT OVERWRITE test_target
SELECT * FROM test_source 
WHERE a = 2 AND b >= 2 AND b <= 3;

Setelah penimpaan, hasil kueri test_target adalah:

+-----+------+
|2    |1     |
|2    |2     |
|2    |3     |
+-----+------+

Penulisan Asinkron

Submit a job

SUBMIT JOB mengirimkan pekerjaan asinkron yang dijadwalkan dan dijalankan di latar belakang. Contohnya:

SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

Optimalkan Kinerja Penulisan

Menambahkan hint /* direct_batch_load=true */ sebelum operasi penulisan dapat mempercepat Tugas penulisan. Hint ini secara signifikan meningkatkan kinerja penulisan sekaligus mengonsumsi lebih sedikit resource. Contohnya:

/* direct_batch_load=true*/
SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

Catatan

/* direct_batch_load=true*/ hanya didukung pada versi kernel 3.1.5 dan yang lebih baru. Jika tidak terlihat peningkatan kinerja signifikan setelah mengaktifkannya, Submit a ticket untuk bantuan. Untuk memeriksa versi kernel kluster Anda, lihat View instance version information.

Kueri Progres Pekerjaan

Setelah Anda mengirimkan pekerjaan dengan SUBMIT JOB, sistem mengembalikan job_id. Anda dapat menggunakan job_id ini untuk mengecek status pekerjaan. Contohnya:

SHOW JOB STATUS WHERE job='<job_id>';

Jika kolom status pada hasil menunjukkan SUCCEEDED, pekerjaan telah selesai.