AnalyticDB:Impor data OSS menggunakan tabel eksternal

Edisi Perusahaan, Edisi Dasar, dan Edisi Danau Data Terpadu

1. Buka editor Pengembangan SQL.

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

   2. Di panel navigasi sebelah kiri, pilih Job Development > SQL Development.

2. Impor data.

   Anda dapat mengimpor data menggunakan impor reguler (default) atau impor elastis. Dalam mode impor reguler, sistem membaca data sumber dari node komputasi dan membuat indeks pada node penyimpanan, sehingga mengonsumsi sumber daya komputasi dan penyimpanan. Metode impor elastis hanya didukung untuk kluster Edisi Perusahaan, Edisi Dasar, dan Edisi Danau Data Terpadu yang menjalankan kernel 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 database eksternal.

      CREATE EXTERNAL DATABASE adb_external_db;

   2. Buat tabel eksternal. Gunakan pernyataan CREATE EXTERNAL TABLE untuk membuat tabel eksternal OSS di database eksternal adb_external_db. Topik ini menggunakan adb_external_db.person sebagai contoh.

      Catatan

      Tabel eksternal AnalyticDB for MySQL harus memiliki nama bidang, jumlah bidang, urutan bidang, dan tipe data yang sama dengan file sumber 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 partisi

      Sebelum Anda dapat melakukan kueri pada tabel eksternal OSS partisi, Anda harus membuatnya dan menambahkan partisi yang diperlukan.

      1. Buat tabel eksternal OSS partisi.

         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. Anda dapat menggunakan pernyataan ALTER TABLE ADD PARTITION untuk menambahkan partisi secara manual, atau menggunakan pernyataan MSCK REPAIR TABLE untuk menemukan 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 menambahkan atau menghapus partisi dari tabel eksternal OSS, lihat ALTER TABLE (tabel eksternal).

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

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

   3. Lakukan kueri data.

      Setelah membuat tabel eksternal, Anda dapat menjalankan pernyataan SELECT di AnalyticDB for MySQL untuk mengkueri data dari OSS.

      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 database di AnalyticDB for MySQL. Jika database sudah ada, Anda dapat melewati langkah ini. Berikut adalah contoh pernyataan:

      CREATE DATABASE adb_demo; 

   5. Buat tabel di AnalyticDB for MySQL untuk menyimpan data OSS yang diimpor. Berikut adalah contoh pernyataan:

      Catatan

      Tabel internal harus sesuai dengan tabel eksternal dari langkah b dalam hal nama bidang, jumlah bidang, urutan bidang, dan tipe data.

      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. Jika terjadi duplikasi primary key, data baru akan diabaikan. Ini setara dengan INSERT IGNORE INTO. Untuk informasi selengkapnya, lihat INSERT INTO. Contoh:

        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. Ini akan menimpa data yang sudah ada di tabel. Contoh:

        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. Contoh:

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

   Impor elastis

   1. Buat database. Jika database sudah ada, lewati langkah ini. Berikut adalah contoh pernyataan:

      CREATE DATABASE adb_demo; 

   2. Buat tabel eksternal.

      Catatan

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

      • Impor elastis hanya mendukung pembuatan tabel eksternal dengan 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 berdasarkan format file (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 tentang parameter yang dapat Anda atur untuk tabel eksternal dan deskripsinya, lihat Tabel eksternal OSS non-partisi dan Tabel eksternal OSS partisi.

   3. Lakukan kueri data.

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

      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 OSS yang diimpor. Berikut adalah contoh pernyataan:

      Catatan

      Tabel internal harus sesuai dengan tabel eksternal dari langkah b dalam hal nama bidang, jumlah bidang, urutan bidang, dan tipe data.

      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, menimpa data yang sudah ada di tabel. Berikut adalah contoh pernyataan:

        /*+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 untuk 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

        Anda tidak dapat mengatur antrian prioritas saat mengirimkan tugas impor elastis secara asinkron.

        Hasil berikut dikembalikan:

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

      Setelah Anda mengirimkan tugas asinkron menggunakan SUBMIT JOB, hasil yang dikembalikan hanya menunjukkan bahwa tugas berhasil dikirim. Anda dapat menggunakan job ID untuk menghentikan tugas asinkron atau memeriksa statusnya guna menentukan apakah tugas tersebut berhasil dieksekusi. Untuk informasi selengkapnya, lihat Kirimkan pekerjaan impor asinkron.

      Parameter hint:

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

      • elastic_load_configs: parameter konfigurasi fitur impor elastis. Anda harus membungkus parameter dalam tanda kurung siku ([ ]) dan memisahkan beberapa parameter dengan tanda pipa vertikal (|). Tabel berikut menjelaskan parameter tersebut.

        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	Jenis sumber daya Spark driver. Nilai default: small. Untuk informasi tentang nilai yang valid, lihat kolom Type pada tabel "Parameter konfigurasi aplikasi Spark" di topik parameter konfigurasi Conf.
        spark.executor.resourceSpec	Tidak	Jenis sumber daya Spark executor. Nilai default: large. Untuk informasi tentang nilai yang valid, lihat kolom Type pada tabel "Parameter konfigurasi aplikasi Spark" di topik parameter konfigurasi Conf.
        spark.adb.executorDiskSize	Tidak	Kapasitas disk Spark executor. Nilai yang valid: (0,100]. Satuan: GiB. Nilai default: 10 GiB. Untuk informasi selengkapnya, lihat bagian "Tentukan sumber daya driver dan executor" di 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 dikembalikan:

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

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

Edisi data warehouse

1. Hubungkan ke kluster dan buat database.

   CREATE DATABASE adb_demo;

2. Buat tabel eksternal. Gunakan sintaksis CREATE TABLE untuk membuat tabel eksternal OSS dalam format CSV, Parquet, atau ORC. Untuk informasi selengkapnya tentang sintaksis, lihat Sintaksis tabel eksternal OSS.

   Topik ini menggunakan tabel eksternal non-partisi dalam format CSV sebagai contoh.

   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. Lakukan kueri data dari tabel eksternal oss_import_test_external_table.

   Catatan

   Untuk file data CSV, Parquet, atau ORC, melakukan kueri pada tabel eksternal berukuran besar dapat menyebabkan overhead performa yang signifikan. Untuk meningkatkan efisiensi kueri, kami menyarankan agar Anda mengimpor data dari tabel eksternal OSS ke AnalyticDB for MySQL sebelum menjalankan kueri, seperti yang dijelaskan pada Langkah 4 dan 5.

   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

   Secara default, operasi impor data yang menggunakan INSERT INTO atau INSERT OVERWRITE SELECT dijalankan secara sinkron. Untuk dataset berukuran besar, seperti ratusan gigabyte, klien harus mempertahankan koneksi persisten ke server AnalyticDB for MySQL dalam waktu lama. Selama periode ini, masalah jaringan dapat mengganggu koneksi dan menyebabkan impor gagal. Oleh karena itu, untuk volume data besar, kami menyarankan agar Anda menggunakan SUBMIT JOB INSERT OVERWRITE SELECT untuk melakukan impor secara asinkron.

   • Metode 1: Jalankan pernyataan INSERT INTO untuk mengimpor data. Jika terjadi duplikasi primary key, operasi penulisan saat ini akan diabaikan dan data tidak diperbarui. Perilaku ini setara dengan INSERT IGNORE INTO. Untuk informasi selengkapnya, lihat INSERT INTO. Berikut adalah contoh pernyataan:

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Metode 2: Jalankan pernyataan INSERT OVERWRITE untuk mengimpor data, menimpa data yang sudah ada di tabel. Berikut adalah contoh pernyataan:

     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 guna penjadwalan latar belakang. Anda dapat menambahkan hint (/*+ direct_batch_load=true*/) sebelum tugas penulisan untuk mempercepat tugas tersebut. Untuk informasi selengkapnya, lihat Penulisan asinkron. Berikut adalah contoh pernyataan:

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     Hasil berikut dikembalikan:

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

     Untuk informasi selengkapnya tentang cara mengirimkan tugas asinkron, lihat Kirimkan pekerjaan impor asinkron.

Edisi Perusahaan, Edisi Dasar, dan Edisi Danau Data Terpadu

Untuk informasi tentang sintaksis pembuatan tabel eksternal OSS di Edisi Perusahaan, Edisi Dasar, dan Edisi Danau Data Terpadu, lihat Tabel eksternal OSS.

Data Warehouse

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 ke AnalyticDB for MySQL

Pemetaan tipe data file Paimon dan AnalyticDB for MySQL