AnalyticDB：Impor data OSS menggunakan tabel eksternal

Edisi Perusahaan, Edisi Dasar, dan Edisi Data Lakehouse

1. Buka editor SQL.

   1. Masuk ke Konsol AnalyticDB for MySQL. Di pojok kiri atas konsol, pilih wilayah. Pada panel navigasi sebelah kiri, klik Clusters, lalu temukan kluster yang ingin Anda kelola dan klik ID-nya.

   2. Pada panel navigasi sebelah kiri, klik Job Development > SQL Development.

2. Impor data.

   Anda dapat mengimpor data menggunakan metode impor reguler (default) atau metode impor elastis. Metode impor reguler membaca data sumber pada node komputasi dan membuat indeks pada node penyimpanan, sehingga mengonsumsi sumber daya komputasi dan penyimpanan. Metode impor elastis membaca data sumber dan membuat indeks dalam Pekerjaan Spark arsitektur tanpa server, sehingga mengonsumsi sumber daya dari kelompok sumber daya jenis Pekerjaan. Metode impor elastis hanya didukung pada kluster Edisi Perusahaan, Edisi Dasar, dan Edisi Data Lakehouse yang menjalankan Milvus versi 3.1.10.0 atau lebih baru serta memiliki kelompok sumber daya jenis Pekerjaan. Untuk informasi selengkapnya, lihat Metode impor data.

   Impor reguler

   1. Buat basis data eksternal.

      CREATE EXTERNAL DATABASE adb_external_db;

   2. Buat tabel eksternal dengan menggunakan pernyataan CREATE EXTERNAL TABLE untuk membuat tabel eksternal OSS di basis data adb_external_db. Dalam contoh ini, tabel eksternal diberi nama adb_external_db.person.

      Catatan

      Tabel eksternal AnalyticDB for MySQL harus memiliki nama bidang, jumlah bidang, urutan bidang, dan tipe bidang yang sama dengan data sumber dalam file OSS.

      Buat tabel eksternal OSS non-partisi

      CREATE EXTERNAL TABLE adb_external_db.person
      (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023)
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION  'oss://testBucketName/adb/dt=2023-06-15/';

      Buat tabel eksternal OSS berpartisi

      Buat tabel eksternal OSS berpartisi dan tambahkan partisi ke tabel untuk mengkueri datanya.

      1. Buat tabel eksternal OSS berpartisi.

         CREATE EXTERNAL TABLE adb_external_db.person
         (
          id INT,
          name VARCHAR(1023) ,
          age INT
         )
         PARTITIONED BY (dt STRING)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
         STORED AS TEXTFILE
         LOCATION  'oss://testBucketName/adb/';

      2. Tambahkan partisi. Anda dapat menjalankan pernyataan ALTER TABLE ADD PARTITION untuk menambahkan partisi secara manual atau menjalankan pernyataan MSCK REPAIR TABLE untuk mengidentifikasi dan menambahkan partisi secara otomatis.

         ALTER TABLE adb_external_db.person ADD PARTITION (dt='2023-06-15') LOCATION 'oss://testBucketName/adb/dt=2023-06-15/';

         Catatan

         • Untuk menambah atau menghapus partisi pada tabel eksternal OSS, lihat ALTER TABLE (tabel eksternal).

         • Untuk menyinkronkan partisi tabel eksternal OSS secara otomatis, lihat MSCK REPAIR TABLE.

      Untuk informasi selengkapnya tentang sintaks pembuatan tabel eksternal OSS, lihat CREATE EXTERNAL TABLE.

   3. Kueri data.

      Setelah tabel dibuat, Anda dapat mengkueri data di OSS dengan menjalankan pernyataan SELECT di AnalyticDB for MySQL.

      SELECT * FROM adb_external_db.person;

      Hasil berikut dikembalikan:

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+

   4. Buat basis data di AnalyticDB for MySQL. Jika basis data sudah ada, lewati langkah ini. Contoh pernyataannya sebagai berikut:

      CREATE DATABASE adb_demo; 

   5. Buat tabel di AnalyticDB for MySQL untuk menyimpan data yang diimpor dari OSS. Contoh pernyataannya sebagai berikut:

      Catatan

      Tabel internal yang Anda buat harus memiliki nama bidang, jumlah bidang, urutan bidang, dan tipe bidang yang sama dengan tabel eksternal.

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
          id INT,
          name VARCHAR(1023),
          age INT,
          dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   6. Impor data ke dalam tabel.

      • Metode 1: Gunakan pernyataan INSERT INTO untuk mengimpor data. Jika terdapat nilai kunci primer duplikat, data baru akan diabaikan. Ini setara dengan menggunakan INSERT IGNORE INTO. Untuk informasi selengkapnya, lihat INSERT INTO.

        INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • Metode 2: Gunakan pernyataan INSERT OVERWRITE INTO untuk mengimpor data secara sinkron. Metode ini akan menimpa data yang ada di tabel.

        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • Metode 3: Gunakan pernyataan INSERT OVERWRITE INTO untuk mengimpor data secara asinkron. Untuk informasi selengkapnya, lihat Penulisan asinkron.

        SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   Impor elastis

   1. Buat basis data. Jika basis data tersebut sudah ada, lewati langkah ini. Pernyataan berikut merupakan contohnya:

      CREATE DATABASE adb_demo; 

   2. Buat tabel eksternal.

      Catatan

      • Nama bidang, jumlah bidang, urutan bidang, dan tipe bidang pada tabel eksternal AnalyticDB for MySQL harus sesuai dengan data sumber dalam file OSS.

      • Impor elastis hanya mendukung pembuatan tabel eksternal melalui pernyataan CREATE TABLE.

      CREATE TABLE oss_import_test_external_table
      (
        id INT(1023),
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      ENGINE='OSS'
      TABLE_PROPERTIES='{
          "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
          "url":"oss://testBucketName/adb/dt=2023-06-15/person.csv",
          "accessid":"accesskey_id",
          "accesskey":"accesskey_secret",
          "delimiter":","
      }';

      Penting

      Saat membuat tabel eksternal, parameter TABLE_PROPERTIES yang didukung bervariasi tergantung pada format file, seperti CSV, Parquet, atau ORC:

      • Format CSV: Hanya parameter endpoint, url, accessid, accesskey, format, delimiter, null_value, dan partition_column yang didukung.

      • Format Parquet: Hanya parameter endpoint, url, accessid, accesskey, format, dan partition_column yang didukung.

      • Format ORC: Hanya parameter endpoint, url, accessid, accesskey, format, dan partition_column yang didukung.

      Untuk informasi selengkapnya mengenai parameter yang dapat dikonfigurasi untuk tabel eksternal beserta deskripsinya, lihat Tabel eksternal OSS non-partisi dan Tabel eksternal OSS berpartisi.

   3. Kueri data.

      Setelah tabel dibuat, Anda dapat mengkueri data dari OSS dengan menjalankan pernyataan SELECT di AnalyticDB for MySQL.

      SELECT * FROM oss_import_test_external_table;

      Hasil berikut dikembalikan:

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)

   4. Buat tabel di AnalyticDB for MySQL untuk menyimpan data yang diimpor dari OSS. Pernyataan berikut merupakan contohnya:

      Catatan

      Tabel internal yang Anda buat harus memiliki nama bidang, jumlah bidang, urutan bidang, dan tipe bidang yang sama dengan tabel eksternal.

      CREATE TABLE adb_import_test
      (
        id INT,
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   5. Impor data.

      Penting

      Impor elastis hanya mendukung pengimporan data menggunakan pernyataan INSERT OVERWRITE INTO.

      • Metode 1: Jalankan pernyataan INSERT OVERWRITE INTO untuk mengimpor data secara elastis. Pernyataan ini akan menimpa data yang ada di tabel. Contohnya sebagai berikut:

        /+*elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

      • Metode 2: Jalankan pernyataan INSERT OVERWRITE INTO secara asinkron untuk mengimpor data secara elastis. Anda dapat menggunakan pernyataan SUBMIT JOB guna mengirimkan tugas asinkron yang dijadwalkan di latar belakang.

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

        Penting

        Antrian prioritas tidak dapat dikonfigurasi saat mengirimkan tugas impor elastis secara asinkron.

        Hasil berikut dikembalikan:

        +---------------------------------------+
        | job_id                                |
        +---------------------------------------+
        | 202308151719510210170190**********    |

      Setelah mengirimkan pekerjaan asinkron menggunakan SUBMIT JOB, hasil yang dikembalikan hanya menunjukkan bahwa pekerjaan berhasil dikirim. Anda dapat menggunakan job_id untuk menghentikan pekerjaan asinkron atau mengkueri statusnya guna memverifikasi apakah pekerjaan telah berhasil dieksekusi. Untuk informasi selengkapnya, lihat Kirimkan pekerjaan impor asinkron.

      Parameter petunjuk:

      • elastic_load: menentukan apakah akan menggunakan impor elastis. Nilai yang valid: true dan false. Nilai default: false.

      • None

        Parameter	Wajib	Deskripsi
        adb.load.resource.group.name	Ya	Nama kelompok sumber daya pekerjaan yang menjalankan pekerjaan impor elastis.
        adb.load.job.max.acu	Tidak	Jumlah maksimum sumber daya untuk pekerjaan impor elastis. Satuan: unit komputasi AnalyticDB (ACU). Nilai minimum: 5 ACU. Nilai default: jumlah shard ditambah 1. Jalankan pernyataan berikut untuk mengkueri jumlah shard di kluster: SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	Tidak	Tipe sumber daya driver Spark. Nilai default: kecil. Untuk informasi tentang nilai yang valid, lihat kolom Tipe dalam tabel "Parameter konfigurasi aplikasi Spark".
        spark.executor.resourceSpec	Tidak	Tipe sumber daya executor Spark. Nilai default: Tingkat Tinggi. Untuk informasi tentang nilai yang valid, lihat kolom Tipe dalam tabel "Parameter konfigurasi aplikasi Spark".
        spark.adb.executorDiskSize	Tidak	Kapasitas disk executor Spark. Nilai yang valid: (0,100]. Satuan: GiB. Nilai default: 10 GiB. Untuk informasi selengkapnya, lihat bagian "Tentukan sumber daya driver dan executor" dalam topik parameter konfigurasi Conf.

   6. (Opsional) Periksa apakah tugas impor yang dikirimkan merupakan tugas impor elastis.

      SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";

      Hasil berikut akan dikembalikan:

      +---------------------------------------+------------------+
      | job_name                              | is_elastic_load  |
      +---------------------------------------+------------------+
      | 20230815171951021017019072*********** |       1          |
      +---------------------------------------+------------------+

      Jika nilai is_elastic_load adalah 1, tugas impor tersebut merupakan tugas impor elastis. Jika nilainya 0, tugas impor tersebut merupakan tugas impor reguler.

Edisi Data Lakehouse

1. Sambungkan ke kluster dan buat basis data.

   CREATE DATABASE adb_demo;

2. Buat tabel eksternal menggunakan sintaks CREATE TABLE untuk membuat tabel eksternal OSS dalam format CSV, Parquet, atau ORC. Untuk informasi selengkapnya mengenai sintaks tersebut, lihat Sintaks tabel eksternal OSS.

   Contoh berikut menggunakan tabel eksternal non-partisi dalam format CSV.

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023) 
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/dt=2023-06-15/person.csv",
       "accessid":"accesskey_id",
       "accesskey":"accesskey_secret",
       "delimiter":",",
       "skip_header_line_count":0,
       "charset":"utf-8"
   }'; 

3. Kueri data dari tabel eksternal oss_import_test_external_table.

   Catatan

   Mengkueri tabel eksternal dengan volume data besar dalam format CSV, Parquet, atau ORC dapat menyebabkan overhead kinerja tinggi. Untuk meningkatkan efisiensi kueri, impor data dari tabel eksternal OSS ke AnalyticDB for MySQL, seperti dijelaskan dalam langkah-langkah berikut.

   SELECT * FROM oss_import_test_external_table;

4. Buat tabel di AnalyticDB for MySQL untuk menyimpan data yang diimpor dari tabel eksternal OSS.

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
      id INT,
      name VARCHAR(1023),
      age INT,
      dt VARCHAR(1023) 
   )
   DISTRIBUTED BY HASH(id);

5. Jalankan pernyataan INSERT untuk mengimpor data dari tabel eksternal OSS ke AnalyticDB for MySQL.

   Penting

   Pernyataan INSERT INTO dan INSERT OVERWRITE SELECT secara default mengimpor data secara sinkron. Proses ini memerlukan koneksi jangka panjang antara klien dan server AnalyticDB for MySQL. Jika Anda mengimpor data dalam jumlah besar—misalnya ratusan gigabyte—koneksi dapat terputus akibat masalah jaringan, sehingga menyebabkan kegagalan impor data. Oleh karena itu, untuk volume data besar, kami menyarankan penggunaan SUBMIT JOB INSERT OVERWRITE SELECT guna mengimpor data secara asinkron.

   • Metode 1: Jalankan pernyataan INSERT INTO untuk mengimpor data. Jika terdapat kunci primer duplikat, operasi penulisan saat ini diabaikan dan data tidak diperbarui. Ini setara dengan INSERT IGNORE INTO. Untuk informasi selengkapnya, lihat INSERT INTO. Contoh pernyataannya sebagai berikut:

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Metode 2: Jalankan pernyataan INSERT OVERWRITE untuk mengimpor data. Metode ini akan menimpa data yang ada di tabel. Contoh pernyataannya sebagai berikut:

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Metode 3: Jalankan pernyataan INSERT OVERWRITE secara asinkron untuk mengimpor data. Anda dapat menggunakan SUBMIT JOB untuk mengirimkan tugas asinkron yang dijadwalkan di latar belakang. Untuk mempercepat proses penulisan, tambahkan petunjuk (/*+ direct_batch_load=true*/) ke dalam tugas tersebut. Untuk informasi selengkapnya, lihat Penulisan asinkron. Contoh pernyataannya sebagai berikut:

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     Hasil berikut dikembalikan:

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     Untuk informasi lebih lanjut tentang cara mengirimkan tugas secara asinkron, lihat Kirimkan pekerjaan impor asinkron.

Edisi Perusahaan, Edisi Dasar, dan Edisi Data Lakehouse

Untuk informasi selengkapnya mengenai sintaks dan deskripsi tabel eksternal OSS di Edisi Perusahaan, Edisi Dasar, dan Edisi Data Lakehouse, lihat Tabel eksternal OSS.

Edisi Data Lakehouse

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"csv|orc|parquet|text
    "delimiter|field_delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

Pemetaan tipe data file Parquet dan AnalyticDB for MySQL

Pemetaan tipe data file ORC dan AnalyticDB for MySQL

Pemetaan tipe data file Paimon dan AnalyticDB for MySQL