AnalyticDB:Gunakan tabel eksternal untuk mengimpor data HDFS

Prasyarat

• Kluster AnalyticDB for MySQL Anda harus menjalankan versi kernel 3.1.4 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 berformat CSV, Parquet, atau ORC.

• Kluster HDFS telah dibuat dan data yang akan diimpor telah disiapkan dalam folder HDFS. Topik ini menggunakan file hdfs_import_test_data.csv sebagai contoh.

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

  • namenode: Membaca dan menulis metadata sistem file. Anda dapat mengonfigurasi nomor port pada parameter fs.defaultFS. Port default-nya adalah 8020.

    Untuk informasi selengkapnya tentang konfigurasi, lihat core-default.xml.

  • datanode: Membaca dan menulis data. Anda dapat mengonfigurasi nomor port pada parameter dfs.datanode.address. Port default-nya adalah 50010.

    Untuk informasi selengkapnya tentang konfigurasi, lihat hdfs-default.xml.

• AnalyticDB for MySQL Edisi Data Warehouse dalam mode elastis mendukung akses Elastic Network Interface (ENI).

  Penting

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

  • Mengaktifkan atau menonaktifkan jaringan ENI akan menginterupsi koneksi database selama sekitar 2 menit. Selama periode ini, operasi baca dan tulis tidak tersedia. Evaluasi dampaknya sebelum mengaktifkan atau menonaktifkan jaringan ENI.

Prosedur

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

   CREATE DATABASE IF NOT EXISTS adb_demo;    

2. Di database tujuan adb_demo, gunakan pernyataan CREATE TABLE untuk membuat tabel eksternal dalam format CSV, Parquet, atau ORC.

   • Buat tabel eksternal HDFS.

   • Buat tabel eksternal HDFS terpartisi.

3. Buat tabel tujuan.

   Gunakan salah satu pernyataan berikut untuk membuat tabel tujuan di database tujuan 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 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);

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

   Pilih metode impor sesuai kebutuhan. Sintaks untuk mengimpor data ke tabel terpartisi sama dengan tabel standar. Contoh berikut menggunakan tabel standar:

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

     INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Metode 2: Gunakan INSERT INTO untuk mengimpor data. Data yang dimasukkan tersedia untuk kueri real-time. Gunakan metode ini untuk jumlah data yang kecil. Contoh kodenya sebagai berikut:

     INSERT INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Metode 3: Jalankan tugas asinkron untuk mengimpor data. Contoh kodenya 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 berdasarkan job_id. Untuk informasi selengkapnya, lihat Kirim tugas impor secara asinkron.

Langkah selanjutnya

SELECT * FROM adb_hdfs_import_test LIMIT 100;

Buat tabel eksternal HDFS

• Buat tabel eksternal untuk file CSV

  Pernyataannya sebagai 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'	Wajib	Mesin penyimpanan untuk tabel eksternal. Contoh ini menggunakan HDFS.
  TABLE_PROPERTIES		Metode yang digunakan AnalyticDB for MySQL untuk mengakses data HDFS.
  format		Format file data. Untuk membuat tabel eksternal untuk file CSV, atur parameter ini ke csv.
  delimiter		Pemisah kolom untuk file data CSV. Contoh ini menggunakan koma (,).
  hdfs_url		Alamat absolut file atau folder data tujuan 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	Opsional	Kolom kunci partisi tabel eksternal. Pisahkan beberapa kolom dengan koma (,). Untuk informasi tentang cara mendefinisikan kolom kunci partisi, lihat Buat tabel eksternal HDFS terpartisi.
  compress_type		Jenis kompresi file data. File CSV hanya mendukung jenis kompresi Gzip.
  skip_header_line_count		Jumlah baris header yang dilewati di awal file selama impor data. Baris pertama file CSV adalah header tabel. Jika Anda mengatur parameter ini ke 1, baris pertama akan dilewati secara otomatis selama impor data. Nilai default-nya adalah 0, artinya tidak ada baris yang dilewati.
  hdfs_ha_host_port		Jika fitur High Availability (HA) dikonfigurasi untuk kluster HDFS, konfigurasikan parameter hdfs_ha_host_port saat membuat tabel eksternal. Formatnya adalah ip1:port1,ip2:port2. Alamat IP dan port tersebut untuk instans namenode primary dan secondary. Contoh: 192.168.xx.xx:8020,192.168.xx.xx:8021

• Buat tabel eksternal untuk file Parquet atau ORC

  Pernyataan berikut menunjukkan cara membuat tabel eksternal 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'	Wajib	Mesin penyimpanan untuk tabel eksternal. Contoh ini menggunakan HDFS.
  TABLE_PROPERTIES		Metode yang digunakan AnalyticDB for MySQL untuk 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 tujuan di kluster HDFS. Alamat harus diawali dengan hdfs://.
  partition_column	Opsional	Kolom kunci partisi tabel. Pisahkan beberapa kolom dengan koma (,). Untuk informasi tentang cara mendefinisikan kolom kunci partisi, lihat Buat tabel eksternal HDFS terpartisi.
  hdfs_ha_host_port		Jika fitur HA dikonfigurasi untuk kluster HDFS, konfigurasikan parameter hdfs_ha_host_port saat membuat tabel eksternal. Formatnya adalah ip1:port1,ip2:port2. Alamat IP dan port tersebut untuk instans namenode primary dan secondary. Contoh: 192.168.xx.xx:8020,192.168.xx.xx:8021

  Catatan

  • Nama kolom dalam pernyataan `CREATE TABLE` untuk tabel eksternal harus identik dengan nama kolom dalam file Parquet atau ORC, tetapi tidak peka huruf besar/kecil. Urutan kolom juga harus sama.

  • Saat membuat tabel eksternal, Anda dapat memilih hanya beberapa kolom dari file Parquet atau ORC untuk menjadi kolom dalam tabel eksternal. Kolom yang tidak dipilih tidak akan diimpor.

  • Jika pernyataan `CREATE TABLE` untuk tabel eksternal mencakup kolom yang tidak ada dalam file Parquet atau ORC, kueri untuk 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 berformat 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 dalam 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 berformat 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
        ├── p2=HANGZHOU
        └── 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 data HDFS terpartisi, jika Anda ingin mengkueri data berdasarkan partisi, Anda harus menentukan parameter partition_column dalam pernyataan CREATE EXTERNAL TABLE saat mengimpor data ke AnalyticDB for MySQL.
}';

Buat tabel eksternal penyimpanan cloud eksternal

{
   "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"
}'