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. Pada panel navigasi sebelah kiri, klik Clusters. Temukan kluster yang ingin Anda kelola, lalu klik ID-nya.

   2. Pada 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 versi kernel 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 dengan menggunakan 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 tanpa 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

      Sebelum melakukan kueri pada tabel eksternal OSS berpartisi, Anda harus membuat tabel tersebut dan menambahkan partisi yang diperlukan.

      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. Anda dapat menggunakan pernyataan ALTER TABLE ADD PARTITION untuk menambahkan partisi secara manual atau pernyataan MSCK REPAIR TABLE untuk secara otomatis menemukan dan menambahkan partisi.

         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, langkah ini dapat dilewati. Berikut adalah contoh pernyataannya:

      CREATE DATABASE adb_demo; 

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

      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. Pernyataan 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 tersebut sudah ada, lewati langkah ini. Berikut contoh pernyataannya:

      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 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 (CSV, Parquet, atau ORC):

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

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

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

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

   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 contoh pernyataannya:

      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 dengan menimpa data yang sudah ada di tabel. Berikut contoh pernyataannya:

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

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

        Hasil berikut dikembalikan:

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

      Setelah 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 Kirim 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 menanyakan 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".
        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".
        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".

   6. (Opsional) Periksa apakah tugas impor yang dikirim 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 tersebut merupakan tugas impor reguler.

Edisi gudang data

1. Hubungkan ke kluster dan buat database.

   CREATE DATABASE adb_demo;

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

   Topik ini menggunakan tabel eksternal tanpa 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, 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 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 menggunakan INSERT INTO atau INSERT OVERWRITE SELECT dijalankan secara sinkron. Untuk dataset besar—misalnya ratusan gigabyte—klien harus mempertahankan koneksi persisten ke server AnalyticDB for MySQL dalam waktu lama. Selama periode tersebut, gangguan jaringan dapat memutus koneksi dan menyebabkan kegagalan impor. Oleh karena itu, untuk volume data besar, kami menyarankan penggunaan SUBMIT JOB INSERT OVERWRITE SELECT guna 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 contoh pernyataannya:

     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 contoh pernyataannya:

     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 juga dapat menambahkan petunjuk (/*+ direct_batch_load=true*/) sebelum tugas penulisan untuk mempercepat prosesnya. Untuk informasi selengkapnya, lihat Penulisan asinkron. Berikut contoh pernyataannya:

     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 Kirim pekerjaan impor asinkron.

Edisi Perusahaan, Edisi Dasar, dan Edisi Danau Data Terpadu

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

Gudang Data

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
}';

File Parquet dan AnalyticDB for MySQL pemetaan tipe data

File ORC ke AnalyticDB for MySQL pemetaan tipe data

File Paimon dan AnalyticDB for MySQL pemetaan tipe data