AnalyticDB:Impor data menggunakan tabel eksternal

Prasyarat

• Kluster AnalyticDB for MySQL Anda harus menjalankan kernel versi 3.1.4 atau yang lebih baru. Untuk membuat tabel eksternal untuk data di penyimpanan cloud eksternal, seperti AWS S3, Azure Blob Storage, atau Google Cloud Storage, versi kernel harus 3.2.6 atau yang lebih baru.

  Catatan

  Untuk melihat dan memperbarui versi minor, buka bagian Configuration Information pada halaman Cluster Information di Konsol AnalyticDB for MySQL.

• File data HDFS harus dalam format CSV, Parquet, atau ORC.

• Kluster HDFS tersedia, dan data yang akan diimpor disimpan dalam file HDFS. Topik ini menggunakan file hdfs_import_test_data.csv sebagai contoh.

• Port akses layanan berikut harus dikonfigurasi di kluster HDFS untuk kluster AnalyticDB for MySQL Anda:

  • namenode: Membaca dan menulis metadata sistem file. Nomor port dikonfigurasi dalam parameter fs.defaultFS. Port default adalah 8020.

    Untuk detail konfigurasi, lihat core-default.xml.

  • datanode: Membaca dan menulis data. Nomor port dikonfigurasi dalam parameter dfs.datanode.address. Port default adalah 50010.

    Untuk detail konfigurasi, lihat hdfs-default.xml.

• Antarmuka jaringan elastis (ENI) harus diaktifkan untuk kluster AnalyticDB for MySQL Anda yang berjalan di Edisi Data Warehouse dalam mode elastis.

  Penting

  • Masuk ke Konsol AnalyticDB for MySQL. Pada halaman Cluster Information, di bagian Network Information, aktifkan sakelar jaringan ENI.

  • Mengaktifkan atau menonaktifkan jaringan ENI mengganggu koneksi database selama sekitar 2 menit, sehingga operasi baca dan tulis tidak tersedia selama periode tersebut. Evaluasi secara cermat dampak potensial sebelum mengaktifkan atau menonaktifkan jaringan ENI.

Prosedur

1. (Opsional) Konfigurasi akses jaringan publik.

   Jika Anda perlu menggunakan tabel eksternal untuk terhubung ke layanan penyimpanan objek dari penyedia cloud lain, seperti AWS S3, Azure Blob Storage, atau Google Cloud Storage, pastikan kluster AnalyticDB for MySQL Anda dapat mengakses jaringan publik.

   • Konfigurasikan gerbang NAT dan Elastic IP Address (EIP) untuk VPC kluster AnalyticDB for MySQL Anda.

     1. Buat gerbang NAT.

        Gerbang NAT harus berada di wilayah yang sama dengan instans AnalyticDB for MySQL Anda.

     2. Asosiasikan Elastic IP Address (EIP).

     3. Buat entri SNAT.

        Kami merekomendasikan agar Anda membuat entri SNAT di tingkat vSwitch. Anda dapat menentukan vSwitch apa pun.

   • Aktifkan akses antarmuka jaringan elastis (ENI) untuk kluster AnalyticDB for MySQL Anda.

     Penting

     • Login ke Konsol AnalyticDB for MySQL. Pada halaman Cluster Information, di bagian Network Information, aktifkan sakelar ENI.

     • Mengaktifkan atau menonaktifkan ENI mengganggu koneksi database selama sekitar 2 menit. Selama periode ini, operasi baca dan tulis tidak tersedia. Evaluasi secara cermat dampak potensial sebelum mengaktifkan atau menonaktifkan ENI.

2. Buat database tujuan. Dalam contoh ini, database tujuan di kluster AnalyticDB for MySQL diberi nama adb_demo.

   CREATE DATABASE IF NOT EXISTS adb_demo; 

3. Jalankan pernyataan CREATE TABLE di database tujuan adb_demo untuk membuat tabel eksternal dalam format CSV, Parquet, atau ORC.

   • Jika Anda menggunakan sumber data HDFS:

     • Buat tabel eksternal HDFS.

     • Buat tabel eksternal HDFS terpartisi.

   • Untuk terhubung ke layanan penyimpanan objek dari penyedia cloud lain, seperti AWS S3, Azure Blob Storage, atau Google Cloud Storage, buat tabel eksternal untuk penyimpanan cloud.

4. Buat tabel tujuan.

   Gunakan salah satu pernyataan berikut untuk membuat tabel tujuan di database adb_demo guna menyimpan data yang diimpor dari HDFS:

   • Buat tabel tujuan untuk tabel eksternal standar. Dalam contoh ini, tabel tujuan diberi nama adb_hdfs_import_test. Sintaksnya sebagai berikut:

     CREATE TABLE IF NOT EXISTS adb_hdfs_import_test
     (
         uid string,
         other string
     )
     DISTRIBUTED BY HASH(uid);

   • Saat membuat tabel tujuan untuk tabel eksternal terpartisi, Anda harus mendefinisikan kolom standar, seperti uid dan other, serta kolom kunci partisi, seperti p1, p2, dan p3, dalam pernyataan tersebut. Dalam contoh ini, tabel tujuan diberi nama adb_hdfs_import_parquet_partition. Sintaksnya sebagai berikut:

     CREATE TABLE IF NOT EXISTS adb_hdfs_import_parquet_partition
     (
         uid string,
         other string,
         p1 date,
         p2 int,
         p3 varchar
     )
     DISTRIBUTED BY HASH(uid);

5. Impor data dari HDFS ke kluster tujuan AnalyticDB for MySQL.

   Pilih metode impor berdasarkan kebutuhan bisnis Anda. Sintaks impor sama untuk tabel terpartisi maupun tabel standar. Contoh berikut menggunakan tabel standar:

   • (Direkomendasikan) Metode 1: Gunakan INSERT OVERWRITE untuk mengimpor data. Metode ini mendukung impor batch dan memberikan performa tinggi. Data menjadi terlihat setelah impor berhasil. Jika impor gagal, data akan dikembalikan (rollback). Contoh kode sebagai berikut:

     INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Metode 2: Gunakan INSERT INTO untuk mengimpor data. Anda dapat melakukan kueri data yang dimasukkan secara real time. Gunakan metode ini untuk jumlah data yang kecil. Contoh kode sebagai berikut:

     INSERT INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Metode 3: Jalankan tugas asinkron untuk mengimpor data. Contoh kode sebagai berikut:

     SUBMIT JOB INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

     Hasil berikut dikembalikan:

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

     Anda juga dapat memeriksa status tugas asinkron menggunakan job_id yang dikembalikan. Untuk informasi selengkapnya, lihat Kirimkan tugas impor secara asinkron.

Langkah selanjutnya

SELECT * FROM adb_hdfs_import_test LIMIT 100;

Buat tabel eksternal HDFS

• Buat tabel eksternal untuk file CSV

  Gunakan pernyataan berikut:

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"csv",
      "delimiter":",",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv"
  }';

  Parameter	Wajib	Deskripsi
  ENGINE='HDFS'	Ya	Menentukan mesin penyimpanan. Harus diatur ke HDFS untuk tabel eksternal HDFS.
  TABLE_PROPERTIES		Menentukan properti untuk AnalyticDB for MySQL agar dapat mengakses data HDFS.
  format		Format file data. Diatur ke csv untuk file CSV.
  delimiter		Pemisah kolom dalam file data CSV. Contoh ini menggunakan koma (,).
  hdfs_url		Alamat absolut file atau folder data target di kluster HDFS. Alamat harus diawali dengan hdfs://. Contoh: hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv
  partition_column	Tidak	Kolom kunci partisi dari tabel eksternal. Pisahkan beberapa kolom dengan koma (,). Untuk informasi selengkapnya, lihat Buat tabel eksternal HDFS terpartisi.
  compress_type		Jenis kompresi file data. Untuk file CSV, hanya jenis Gzip yang didukung.
  skip_header_line_count		Jumlah baris header yang dilewati dari awal file. Untuk melewati satu baris header tabel, atur parameter ini ke 1. Nilai default: 0. Artinya tidak ada baris yang dilewati.
  hdfs_ha_host_port		Jika Ketersediaan tinggi (HA) dikonfigurasi untuk kluster HDFS, Anda harus menentukan parameter hdfs_ha_host_port saat membuat tabel eksternal. Formatnya adalah ip1:port1,ip2:port2. Alamat IP dan port sesuai dengan instans namenode aktif dan cadangan. Contoh: 192.168.xx.xx:8020,192.168.xx.xx:8021

• Buat tabel eksternal HDFS untuk file Parquet atau ORC

  Contoh berikut menunjukkan cara membuat tabel eksternal HDFS untuk file Parquet:

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"parquet",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_parquet_data/"
  }';

  Parameter	Wajib	Deskripsi
  ENGINE='HDFS'	Ya	Menentukan mesin penyimpanan. Harus diatur ke HDFS untuk tabel eksternal HDFS.
  TABLE_PROPERTIES		Menentukan properti untuk AnalyticDB for MySQL agar dapat mengakses data HDFS.
  format		Format file data. Untuk membuat tabel eksternal untuk file Parquet, atur parameter ini ke parquet. Untuk membuat tabel eksternal untuk file ORC, atur parameter ini ke orc.
  hdfs_url		Alamat absolut file atau folder data target di kluster HDFS. Alamat harus diawali dengan hdfs://.
  partition_column	Tidak	Kolom kunci partisi dari tabel eksternal. Pisahkan beberapa kolom dengan koma (,). Untuk informasi selengkapnya, lihat Buat tabel eksternal HDFS terpartisi.
  hdfs_ha_host_port		Jika Ketersediaan tinggi (HA) dikonfigurasi untuk kluster HDFS, Anda harus menentukan parameter hdfs_ha_host_port saat membuat tabel eksternal. Formatnya adalah ip1:port1,ip2:port2. Alamat IP dan port sesuai dengan instans namenode aktif dan cadangan. Contoh: 192.168.xx.xx:8020,192.168.xx.xx:8021

  Catatan

  • Nama kolom dan urutannya dalam pernyataan CREATE EXTERNAL TABLE harus sesuai dengan yang ada di file sumber Parquet atau ORC. Nama kolom tidak peka huruf besar/kecil.

  • Anda dapat membuat tabel eksternal menggunakan subset kolom dari file sumber. Kolom yang tidak ditentukan dalam pernyataan CREATE EXTERNAL TABLE akan diabaikan.

  • Jika pernyataan CREATE EXTERNAL TABLE mencakup kolom yang tidak ada di file Parquet atau ORC, kueri pada kolom tersebut akan mengembalikan NULL.

  Pemetaan tipe data antara file Parquet dan AnalyticDB for MySQL

  Tipe data primitif Parquet	Parquet logicalType	Tipe data di AnalyticDB for MySQL
  BOOLEAN	None	BOOLEAN
  INT32	INT_8	TINYINT
  INT32	INT_16	SMALLINT
  INT32	None	INT atau INTEGER
  INT64	None	BIGINT
  FLOAT	None	FLOAT
  DOUBLE	None	DOUBLE
  FIXED_LEN_BYTE_ARRAY BINARY INT64 INT32	DECIMAL	DECIMAL
  BINARY	UTF-8	VARCHAR STRING JSON (jika kolom Parquet diketahui dalam format JSON)
  INT32	DATE	DATE
  INT64	TIMESTAMP_MILLIS	TIMESTAMP atau DATETIME
  INT96	None	TIMESTAMP atau DATETIME

  Penting

  Tabel eksternal untuk file Parquet tidak mendukung tipe STRUCT. Jika Anda menggunakan tipe ini, pembuatan tabel akan gagal.

  Pemetaan tipe data antara file ORC dan AnalyticDB for MySQL

  Tipe data di file ORC	Tipe data di AnalyticDB for MySQL
  BOOLEAN	BOOLEAN
  BYTE	TINYINT
  SHORT	SMALLINT
  INT	INT atau INTEGER
  LONG	BIGINT
  DECIMAL	DECIMAL
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  BINARY STRING VARCHAR	VARCHAR STRING JSON (jika kolom ORC diketahui dalam format JSON)
  TIMESTAMP	TIMESTAMP atau DATETIME
  DATE	DATE

  Penting

  Tabel eksternal untuk file ORC tidak mendukung tipe kompleks seperti LIST, STRUCT, atau UNION. Jika Anda menggunakan tipe-tipe ini, pembuatan tabel akan gagal. Anda dapat membuat tabel eksternal untuk file ORC jika suatu kolom menggunakan tipe MAP, tetapi kueri pada tabel tersebut akan gagal.

Buat tabel eksternal HDFS terpartisi

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│ │ │ └── 000000_1
│ │ └── p3=SHENZHEN
│ │ └── 000000_0
│ └── p2=6
│ └── p3=SHENZHEN
│ └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│ ├── p3=SHANGHAI
│ │ └── 000000_0
│ └── p3=SHENZHEN
│ └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p3=HANGZHOU
        │ └── 000000_0
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE IF NOT EXISTS hdfs_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
  "hdfs_url":"hdfs://172.17.***.**:9000/adb/parquet_partition_classic/",
  "format":"parquet", //Untuk membuat tabel eksternal untuk file CSV atau ORC, ubah nilai format menjadi csv atau orc.
  "partition_column":"p1, p2, p3" // Untuk melakukan kueri data HDFS terpartisi berdasarkan partisi, tentukan parameter partition_column dalam pernyataan CREATE TABLE saat mengimpor data ke AnalyticDB for MySQL.
}';

Buat tabel eksternal untuk penyimpanan cloud

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:Get*",
         "s3:List*",
         "s3:Describe*",
         "s3-object-lambda:Get*",
         "s3-object-lambda:List*"
       ],
       "Resource": "*"
     }
   ]
}

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:",
         "s3-object-lambda:"
       ],
       "Resource": "*"
     }
   ]
}

CREATE TABLE t2(c1 int, c2 int, p1 int)
ENGINE='hdfs'
TABLE_PROPERTIES='{
  "partition_column":"p1",
  "format" : "parquet",
  "hdfs_url" : "gs://adbtest2/tbls/table1",
  "gcs.project_id":"test-project",
  "gcs.client_email":"adbtest@test-project.iam.gserviceaccount.com",
  "gcs.token_uri":"https://oauth2.googleapis.cn/token",
  "gcs.private_key_id":"xxxx",
  "gcs.private_key":"-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFA****-----END PRIVATE KEY-----\n"
}'