ApsaraDB for SelectDB：Gunakan INSERT INTO untuk mengimpor data

Informasi latar belakang

Pernyataan INSERT INTO umumnya digunakan untuk mengimpor data ke dalam database seperti MySQL. ApsaraDB for SelectDB mendukung sintaks SQL standar dan memungkinkan Anda mengeksekusi pernyataan INSERT INTO untuk mengimpor data. Pernyataan ini terbagi menjadi dua jenis:

• INSERT INTO tbl SELECT ...

• INSERT INTO tbl (col1, col2, ...) VALUES (1, 2, ...), (1,3, ...);

  Penting

  Kinerja penulisan pernyataan INSERT INTO VALUES rendah. Hindari penggunaannya di lingkungan produksi. Sebagai alternatif, gunakan Stream Load untuk menulis sejumlah kecil data ke instans ApsaraDB for SelectDB secara bersamaan. Ini dapat meningkatkan kinerja penulisan hingga seratus kali lipat.

Catatan penggunaan

Dalam skenario tertentu, jika Anda sering menulis sejumlah kecil data ke instans ApsaraDB for SelectDB, kinerja instans dapat menurun signifikan dan menyebabkan deadlock pada tabel. Untuk menghindarinya, tulis sejumlah kecil data secara bersamaan dan tetapkan frekuensi penulisan data ke tabel lebih dari 10 detik.

Pernyataan INSERT INTO SELECT

Pernyataan INSERT INTO SELECT dapat secara efisien memproses data di SelectDB dan data lake eksternal menggunakan berbagai fungsi SQL serta federated query yang disediakan oleh SelectDB. Data yang telah diproses kemudian diimpor ke tabel baru di SelectDB untuk analisis lebih lanjut.

Lakukan operasi ETL pada data di tabel internal

Jika data sudah tersimpan di tabel di SelectDB, Anda dapat melakukan operasi ekstraksi, transformasi, dan pemuatan (ETL) dengan mengeksekusi pernyataan INSERT INTO SELECT dan mengimpor data ke tabel baru di ApsaraDB for SelectDB. Contohnya, Anda dapat mengeksekusi pernyataan berikut untuk mengimpor data wilayah bj dari tabel store_sales di ApsaraDB for SelectDB ke tabel baru bernama bj_store_sales:

INSERT INTO bj_store_sales
SELECT id, total, user_id, sale_timestamp FROM store_sales WHERE region = "bj";

Sinkronkan data dari data lake

Jika data disimpan di sistem eksternal seperti data lake, buat katalog di SelectDB, petakan katalog ke data di sistem eksternal, lalu eksekusi pernyataan INSERT INTO SELECT untuk mengimpor data ke tabel di SelectDB.

Anda dapat menggunakan katalog untuk mengintegrasikan sumber data seperti Hive, Iceberg, Hudi, Elasticsearch, dan Java Database Connectivity (JDBC) dengan ApsaraDB for SelectDB. Informasi berikut memberikan contoh cara menggunakan katalog untuk mengintegrasikan sumber data Hive dengan instans ApsaraDB for SelectDB dan menyinkronkan data dari data lake ke tabel di instans SelectDB. Untuk informasi lebih lanjut tentang sumber data lainnya, lihat Data Lakehouse.

1. Hubungkan ke instans SelectDB. Untuk informasi lebih lanjut, lihat Hubungkan ke Instans ApsaraDB for SelectDB Menggunakan Klien MySQL.

2. Buat katalog dan gunakan katalog untuk mengintegrasikan sumber data Hive dengan instans ApsaraDB for SelectDB. Untuk informasi lebih lanjut, lihat Sumber Data Hive.

3. Opsional. Buat database bernama hive_db.

   Jika Anda telah membuat database tujuan, lewati langkah ini.

   create database hive_db;

4. Beralih ke database tujuan.

   use hive_db;

5. Buat tabel.

   Jika Anda telah membuat tabel tujuan, periksa apakah tipe data kolom di tabel tujuan dipetakan ke kolom di tabel sumber sumber data Hive. Lihat Pemetaan Tipe Data Kolom.

   Jika Anda belum membuat tabel tujuan, buat tabel tujuan dan pastikan bahwa tipe data kolom di tabel tujuan dipetakan ke kolom di tabel sumber sumber data Hive. Untuk informasi lebih lanjut, lihat Pemetaan Tipe Data Kolom.

   CREATE TABLE test_Hive2SelectDB
   (
       id int,
       name varchar(50),
       age int
   )
   DISTRIBUTED BY HASH(id) BUCKETS 4
   PROPERTIES("replication_num" = "1");

6. Opsional. Lihat data tabel.

   select * from test_Hive2SelectDB;

   

7. Migrasi data.

   Eksekusi pernyataan INSERT INTO SELECT untuk menyinkronkan data dari sumber data Hive ke tabel di ApsaraDB for SelectDB dan tentukan label unik untuk pekerjaan impor data.

   INSERT INTO test_Hive2SelectDB WITH LABEL test_label SELECT *  FROM hive_catalog.testdb.hive_t;

8. Kueri data.

   Data tabel tujuan ditampilkan di sebelah kiri dan tabel data sumber di sebelah kanan.

   

Pernyataan INSERT INTO VALUES

Pernyataan INSERT INTO VALUES umumnya digunakan untuk menulis data ke database seperti MySQL. Gunakan pernyataan ini hanya di lingkungan pengujian. Biasanya, Anda dapat mengirim permintaan untuk menulis data menggunakan klien SQL atau program JDBC.

Contoh kode berikut menunjukkan cara membuat tabel tempat data diimpor di ApsaraDB for SelectDB.

CREATE TABLE test_table
(
    id int,
    name varchar(50),
    age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

Impor data menggunakan klien SQL

BEGIN;
INSERT INTO test_table VALUES (1, 'Zhang San', 32),(2, 'Li Si', 45),(3, 'Zhao Liu', 23);
INSERT INTO test_table VALUES (4, 'Wang Yi', 32),(5, 'Zhao Er', 45),(6, 'Li Er', 23);
INSERT INTO test_table VALUES (7, 'Li Yi', 32),(8, 'Wang San', 45),(9, 'Zhao Si', 23);
COMMIT;

Impor data menggunakan program JDBC

public static void main(String[] args) throws Exception {
    // Jumlah pernyataan INSERT yang digunakan untuk mengimpor data dalam batch.
    int insertNum = 10;
    // Jumlah entri data yang akan diimpor dalam batch.
    int batchSize = 10000;
    String URL="jdbc:mysql://<Host IP address >:< MySQL protocol port>/test_db?useLocalSessionState=true"; // Anda dapat masuk ke konsol ApsaraDB for SelectDB untuk melihat titik akhir virtual private cloud (VPC) atau titik akhir publik di bagian Informasi Jaringan halaman Detail Instans.
    Connection connection = DriverManager.getConnection(URL, "admin", "password");  // Nama pengguna dan kata sandi akun yang digunakan untuk masuk ke instans ApsaraDB for SelectDB.
    Statement statement = connection.createStatement();
    statement.execute("begin;");
    // Tambahkan beberapa pernyataan INSERT.
    for (int num = 0; num < insertNum; num++) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO test_table values ");
        for(int i = 0; i < batchSize; i++){
            if(i > 0){
                sql.append(",");
            }
            // Tambahkan baris data yang berisi bidang seperti nama dan usia. Anda dapat memodifikasi bidang yang dimiliki baris data berdasarkan kebutuhan bisnis Anda.
            sql.append("(1, 'Zhang San', 32)");
        }
        // tambahkan sql ke batch: INSERT INTO tbl values(),(),()
        statement.addBatch(sql.toString());
    }
    statement.addBatch("commit;");
    statement.executeBatch();
    // Tutup sumber daya.
    statement.close();
    connection.close();
}

Praktik terbaik

• Hasil yang Dikembalikan

  Operasi INSERT INTO adalah operasi sinkron. Operasi insert selesai ketika hasil dikembalikan. Lakukan operasi berdasarkan hasil yang diterima.

  1. Operasi INSERT INTO berhasil dan set hasil kosong.

     Jika set hasil klausa SELECT dalam pernyataan INSERT INTO kosong, hasil serupa dengan output berikut dikembalikan:

     INSERT INTO tbl1 SELECT * FROM empty_tbl;
     Query OK, 0 rows affected (0.02 sec)

     Query OK menunjukkan bahwa operasi berhasil. 0 rows affected menunjukkan bahwa tidak ada data yang diimpor.

  2. Operasi INSERT INTO berhasil dan set hasil tidak kosong.

     Dalam kasus di mana set hasil tidak kosong, hasil serupa dengan salah satu output berikut dikembalikan:

     INSERT INTO tbl1 SELECT * FROM tbl2;
     Query OK, 4 rows affected (0.38 sec)
     {'label':'insert_8510c568-9eda-****-9e36-6adc7d35291c', 'status':'visible', 'txnId':'4005'}
      
     INSERT INTO tbl1 with label my_label1 SELECT * FROM tbl2;
     Query OK, 4 rows affected (0.38 sec)
     {'label':'my_label1', 'status':'visible', 'txnId':'4005'}
      
     INSERT INTO tbl1 SELECT * FROM tbl2;
     Query OK, 2 rows affected, 2 warnings (0.31 sec)
     {'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'visible', 'txnId':'4005'}
      
     INSERT INTO tbl1 SELECT * FROM tbl2;
     Query OK, 2 rows affected, 2 warnings (0.31 sec)
     {'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'committed', 'txnId':'4005'}

     Query OK menunjukkan bahwa operasi berhasil. 4 rows affected menunjukkan bahwa empat baris data diimpor. 2 warnings menunjukkan bahwa dua baris data difilter. String JSON juga dikembalikan.

     {'label':'my_label1', 'status':'visible', 'txnId':'4005'}
     {'label':'insert_f0747f0e-7a35-****-affa-13a235f4020d', 'status':'committed', 'txnId':'4005'}
     {'label':'my_label1', 'status':'visible', 'txnId':'4005', 'err':'some other error'}

     label menunjukkan label yang ditentukan pengguna atau dihasilkan secara otomatis. Label digunakan untuk mengidentifikasi pekerjaan impor data menggunakan pernyataan INSERT INTO. Setiap pekerjaan impor data memiliki label unik dalam satu database. status menunjukkan visibilitas data yang diimpor. Jika data terlihat, nilai visible dikembalikan untuk parameter status. Jika data tidak terlihat, nilai committed dikembalikan. txnId menunjukkan ID transaksi impor yang sesuai dengan operasi insert. err menunjukkan kesalahan tak terduga yang mungkin terjadi.

     Untuk menanyakan baris yang difilter, eksekusi pernyataan berikut:

     SHOW LOAD WHERE label="xxx";

     URL dalam hasil yang dikembalikan dapat digunakan untuk menanyakan data kesalahan. Untuk informasi lebih lanjut, lihat ringkasan baris kesalahan dalam topik ini. Data mungkin sementara tidak terlihat. Namun, batch data ini pada akhirnya akan terlihat. Anda dapat mengeksekusi pernyataan berikut untuk memeriksa apakah batch data ini terlihat:

     SHOW TRANSACTION WHERE id=4005;

     Jika visible ditampilkan di kolom TransactionStatus dalam hasil yang dikembalikan, data tersebut terlihat.

  3. Operasi INSERT INTO gagal.

     Jika operasi INSERT INTO gagal, tidak ada data yang diimpor dan hasil serupa dengan output berikut dikembalikan:

     INSERT INTO tbl1 SELECT * FROM tbl2 WHERE k1 = "a";
     ERROR 1064 (HY000): all partitions have no load data. url: http://10.74.167.16:8042/api/_load_error_log?file=__shard_2/error_log_insert_stmt_ba8bb9e158e4879-ae8de8507c0bf8a2_ba8bb9e158e4879_ae8de8507c0bf8a2

     ERROR 1064 (HY000): all partitions have no load data menunjukkan penyebab kegagalan. URL dalam hasil yang dikembalikan dapat digunakan untuk menanyakan data kesalahan:

     SHOW LOAD WARNINGS ON "url";

• Periode Timeout

  Periode timeout operasi INSERT INTO ditentukan oleh variabel sesi query_timeout. Periode timeout default untuk operasi INSERT INTO adalah 5 menit. Jika pekerjaan impor tidak selesai dalam periode timeout yang ditentukan, sistem akan membatalkan pekerjaan tersebut.

• Label dan Atomicity

  Operasi INSERT INTO menjamin atomicity pekerjaan impor. Jika Anda ingin menggunakan ekspresi tabel umum (CTE) untuk mendefinisikan subquery dalam pernyataan INSERT INTO, Anda harus menentukan WITH LABEL dan kolom.

• Ambang Batas Filter

  Jika Anda mengimpor data menggunakan pernyataan INSERT INTO, Anda tidak dapat menentukan ambang batas filter dengan menentukan parameter max_filter_ratio. Ambang batas filter default adalah 1, yang menunjukkan bahwa semua baris kesalahan dapat diabaikan.

  Dalam skenario bisnis di mana data tidak dapat difilter, Anda dapat menetapkan variabel sesi enable_insert_strict menjadi true. Hal ini memastikan bahwa pernyataan INSERT INTO tidak akan berhasil dieksekusi jika data difilter.

• Masalah Kinerja

  Kami menyarankan agar Anda tidak menggunakan pernyataan INSERT INTO VALUES untuk mengimpor data, terutama di lingkungan produksi daring untuk big data. Jika penggunaan pernyataan INSERT INTO VALUES diperlukan, gabungkan beberapa baris data menjadi satu pernyataan INSERT INTO untuk mengimpor data secara batch. Disarankan untuk mengimpor 1.000 hingga 1.000.000 baris data dalam satu batch.

• Pembaruan Kolom Tertentu

  Secara default, pernyataan INSERT INTO menulis data berdasarkan baris. Dalam metode implementasi Merge-on-Write (MOW) dari model data Unique, Anda dapat mengonfigurasi variabel sesi berikut untuk memperbarui kolom tertentu sesuai dengan kebutuhan bisnis Anda.

  set enable_unique_key_partial_update=true

  Untuk informasi lebih lanjut tentang variabel, lihat Manajemen Variabel.

  Penting

  • Variabel ini hanya tersedia jika tabel menggunakan model Unique yang menggunakan mode Merge on Write (MOW).

  • Jika Anda mengatur variabel enable_unique_key_partial_update dan enable_insert_strict ke true, Anda dapat menjalankan pernyataan INSERT INTO hanya untuk memperbarui data. Nilai default true untuk variabel enable_insert_strict menunjukkan bahwa mode ketat diaktifkan untuk pernyataan INSERT. Jika pernyataan INSERT INTO mencakup kunci yang tidak ada di tabel, error akan dilaporkan.

  • Setelah Anda mengatur variabel ini ke true, jika Anda ingin mengeksekusi pernyataan INSERT INTO untuk memperbarui kolom tertentu dan menyisipkan data ke kolom tersebut, Anda harus mengatur variabel sesi enable_unique_key_partial_update ke true dan variabel sesi enable_insert_strict ke false. Untuk informasi lebih lanjut tentang pengaturan variabel, lihat Konfigurasi Variabel.

Tanya Jawab Umum

Apa yang harus saya lakukan jika pesan kesalahan get table cloud commit lock timeout ditampilkan selama impor data?

Anda sering menulis data ke instans ApsaraDB for SelectDB. Akibatnya, deadlock terjadi. Untuk menghindarinya, tulis sejumlah kecil data secara bersamaan dan tetapkan frekuensi penulisan data ke tabel lebih dari 5 detik.