cara menggunakan tabel eksternal untuk mengekspor data dari AnalyticDB for MySQL ke Apsara File Storage for HDFS - AnalyticDB

AnalyticDB for MySQL memungkinkan Anda menggunakan tabel eksternal untuk mengimpor dan mengekspor data. Dokumen ini menjelaskan cara menggunakan tabel eksternal untuk mengekspor data dari AnalyticDB for MySQL Data Warehouse Edition (V3.0) ke Apsara File Storage for HDFS.

Prasyarat

Kluster AnalyticDB for MySQL versi V3.1.4.4 atau lebih baru telah dibuat.
Catatan
Untuk melihat dan memperbarui versi minor dari kluster AnalyticDB for MySQL, masuk ke konsol AnalyticDB for MySQL dan pergi ke bagian Configuration Information pada halaman Cluster Information.
Kluster Apsara File Storage for HDFS telah dibuat, serta folder untuk menyimpan data AnalyticDB for MySQL yang diimpor. Dalam contoh ini, folder tersebut diberi nama hdfs_output_test_csv_data.
Catatan
Saat menggunakan pernyataan INSERT OVERWRITE untuk mengimpor data, file yang ada di folder tujuan akan ditimpa. Untuk mencegah file yang ada tertimpa, disarankan membuat folder tujuan lain saat mengekspor data.
Port akses layanan berikut untuk kluster AnalyticDB for MySQL dikonfigurasi di kluster HDFS:
- namenode: digunakan untuk membaca dan menulis metadata sistem file. Anda dapat mengonfigurasi nomor port menggunakan parameter fs.defaultFS. Nomor port default adalah 8020.
  Untuk informasi lebih lanjut tentang konfigurasi, lihat core-default.xml.
- datanode: digunakan untuk membaca dan menulis data. Anda dapat mengonfigurasi nomor port menggunakan parameter dfs.datanode.address. Nomor port default adalah 50010.
  Untuk informasi lebih lanjut tentang konfigurasi, lihat hdfs-default.xml.
Jika kluster AnalyticDB for MySQL berada dalam mode elastis, Anda harus mengaktifkan ENI di bagian Network Information pada halaman Cluster Information.
Penting
Saat mengaktifkan atau menonaktifkan ENI, koneksi database mungkin terputus selama sekitar 2 menit. Selama periode ini, operasi baca atau tulis tidak dapat dilakukan. Lanjutkan dengan hati-hati saat mengaktifkan atau menonaktifkan ENI.

Catatan penggunaan

Hanya file CSV dan Parquet yang dapat diekspor dari kluster AnalyticDB for MySQL ke kluster Apsara File Storage for HDFS. File ORC tidak didukung.
AnalyticDB for MySQL tidak mendukung sintaks INSERT yang digunakan untuk menyisipkan data ke tabel eksternal per baris, seperti INSERT INTO VALUES dan REPLACE INTO VALUES.
Anda tidak dapat mengekspor file individual dari AnalyticDB for MySQL ke Apsara File Storage for HDFS menggunakan tabel eksternal terpartisi.
Saat mengekspor data dari tabel eksternal terpartisi, file data tidak berisi data kolom partisi. Data kolom partisi ditampilkan dalam bentuk direktori Apsara File Storage for HDFS.
Sebagai contoh, tiga kolom biasa dan dua kolom partisi didefinisikan dalam tabel eksternal terpartisi. Nama kolom partisi tingkat pertama adalah p1, dengan nilai 1. Nama kolom partisi tingkat kedua adalah p2, dengan nilai a. Anda harus menggunakan tabel eksternal terpartisi untuk mengekspor data ke direktori adb_data/ kluster Apsara File Storage for HDFS.
Jika p1 disetel ke 1 dan p2 disetel ke a, jalur relatif tempat data diekspor adalah adb_data/p1=1/p2=a/. File CSV atau Parquet yang diekspor hanya berisi nilai dari tiga kolom biasa dan tidak mencakup nilai dari kolom p1 dan p2.

Prosedur

Hubungkan ke kluster AnalyticDB for MySQL. Untuk informasi lebih lanjut, lihat Hubungkan ke kluster AnalyticDB for MySQL.
Buat basis data sumber. Untuk informasi lebih lanjut, lihat Buat basis data.
Dalam contoh ini, basis data bernama adb_demo digunakan sebagai basis data sumber di kluster AnalyticDB for MySQL.
Buat tabel sumber dan sisipkan data ke dalam tabel sumber.
Eksekusi pernyataan berikut untuk membuat tabel sumber bernama adb_hdfs_import_source di basis data sumber adb_demo:
```
CREATE TABLE IF NOT EXISTS adb_hdfs_import_source
(
    uid string,
    other string
)
DISTRIBUTED BY HASH(uid);
```
Eksekusi pernyataan berikut untuk menyisipkan satu baris data uji ke dalam tabel adb_hdfs_import_source:
```
INSERT INTO adb_hdfs_import_source VALUES ("1", "a"), ("2", "b"), ("3", "c");
```
Buat tabel eksternal.
Gunakan sintaks berikut untuk membuat tabel eksternal di basis data sumber adb_demo. Tabel ini digunakan untuk mengekspor data AnalyticDB for MySQL ke Apsara File Storage for HDFS.
- Eksekusi pernyataan berikut untuk membuat tabel eksternal standar. Dalam contoh ini, tabel tujuan diberi nama hdfs_import_external.
```
CREATE TABLE IF NOT EXISTS hdfs_import_external
(
    uid string,
    other string
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
    "format":"csv",
    "delimiter":",",
    "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
}';
```
- Eksekusi pernyataan berikut untuk membuat tabel eksternal terpartisi. Anda harus mendefinisikan kolom biasa (seperti uid dan other) dan kolom partisi (seperti p1, p2, dan p3) dalam pernyataan tersebut. Dalam contoh ini, tabel tujuan diberi nama hdfs_import_external_par.
```
CREATE TABLE IF NOT EXISTS hdfs_import_external_par
(
    uid string,
    other string,
    p1 date,
    p2 int,
    p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
    "format":"csv",
    "delimiter":",",
    "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
    "partition_column":"p1, p2, p3"
}';
```
Catatan
- Hanya file CSV dan Parquet yang dapat diekspor dari kluster AnalyticDB for MySQL ke kluster Apsara File Storage for HDFS. File ORC tidak didukung.
- Untuk sintaks detail dalam membuat tabel eksternal, lihat bagian "Buat tabel eksternal Apsara File Storage for HDFS" dan "Buat tabel eksternal Apsara File Storage for HDFS terpartisi" dari topik Gunakan tabel eksternal untuk mengimpor data ke Data Warehouse Edition.
Ekspor data sumber dari kluster AnalyticDB for MySQL ke Apsara File Storage for HDFS.
- Untuk sintaks detail dalam mengekspor data menggunakan tabel eksternal standar, lihat bagian "Lampiran 1: Sintaks untuk mengekspor data menggunakan tabel eksternal standar" dari topik ini.
- Untuk sintaks detail dalam mengekspor data menggunakan tabel eksternal terpartisi, lihat bagian "Lampiran 2: Sintaks untuk mengekspor data menggunakan tabel eksternal terpartisi" dari topik ini.

Apa yang harus dilakukan selanjutnya

Setelah data diekspor, Anda dapat melihat data yang diekspor di folder tujuan hdfs_output_test_csv_data menggunakan klien Hadoop. Anda juga dapat menghubungkan ke kluster AnalyticDB for MySQL dan mengeksekusi pernyataan berikut untuk menanyakan data yang diekspor menggunakan tabel eksternal. Sintaks kueri sama untuk tabel eksternal terpartisi dan tabel eksternal standar. Dalam contoh ini, tabel eksternal standar hdfs_import_external digunakan.

SELECT * FROM hdfs_import_external LIMIT 100;

Lampiran 1: Sintaks untuk mengekspor data menggunakan tabel eksternal standar

Jika kolom partisi tidak ditentukan saat membuat tabel eksternal, Anda dapat menggunakan salah satu metode berikut untuk mengekspor data:

Metode 1: Jika tabel tujuan sudah berisi data, gunakan pernyataan INSERT INTO untuk mengimpor data ke dalam tabel eksternal. Setiap kali Anda menggunakan pernyataan ini untuk menulis data dari tabel sumber ke folder Apsara File Storage for HDFS, file Apsara File Storage for HDFS baru akan dihasilkan.
Catatan
Kolom yang ingin diekspor harus semuanya ditulis ke tabel eksternal. INSERT INTO menghasilkan file baru tanpa menimpa file yang ada.
Sintaks:
```
INSERT INTO <target_table> 
SELECT <col_name> FROM <source_table>;
```
Contoh:
```
INSERT INTO hdfs_import_external
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
```
Catatan
col1, col2, col3 menunjukkan semua kolom dari tabel eksternal.
Metode 2: Anda tidak dapat mendefinisikan kunci utama dalam tabel eksternal untuk Apsara File Storage for HDFS. REPLACE INTO dan INSERT INTO sepenuhnya setara. Keduanya mereplikasi data ke dalam tabel eksternal. Jika tabel tujuan sudah berisi data, data yang ada tetap tidak berubah dan data baru ditambahkan ke file tujuan saat Anda mengimpor data dengan mengeksekusi pernyataan REPLACE INTO.
Catatan
- Kolom yang ingin diekspor harus semuanya ditulis ke tabel eksternal. Anda tidak dapat menentukan kolom tertentu untuk ditulis.
- REPLACE INTO menghasilkan file baru tanpa menimpa file yang ada.
Sintaks:
```
REPLACE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
```
Contoh:
```
REPLACE INTO hdfs_import_external
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
```
Metode 3: Sisipkan data secara batch ke dalam tabel eksternal menggunakan pernyataan INSERT OVERWRITE. Jika tabel eksternal tujuan sudah berisi data, semua file di jalur tabel eksternal dihapus sebelum file Apsara File Storage for HDFS baru dihasilkan.
Penting
- Kolom yang ingin diekspor harus semuanya ditulis ke tabel eksternal. Anda tidak dapat menentukan kolom tertentu untuk ditulis.
- INSERT OVERWRITE menimpa data yang ada di direktori. Lanjutkan dengan hati-hati.
Sintaks:
```
INSERT OVERWRITE <target_table> 
SELECT <col_name> FROM <source_table>;
```
Contoh:
```
INSERT OVERWRITE hdfs_import_external 
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
```

Metode 4: Ekspor data dengan mengeksekusi pernyataan INSERT OVERWRITE secara asinkron. Sintaks:

SUBMIT job INSERT OVERWRITE <target_table> 
SELECT <col_name> FROM <source_table>;

Contoh:

SUBMIT JOB INSERT OVERWRITE hdfs_import_external  
SELECT col1, col2, col3 FROM adb_hdfs_import_source;

Hasil berikut dikembalikan:

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

Anda juga dapat memeriksa status tugas asinkron berdasarkan nilai job_id. Untuk informasi lebih lanjut, lihat Kirim tugas impor secara asinkron.

Lampiran 2: Sintaks untuk mengekspor data menggunakan tabel eksternal terpartisi

Saat mengekspor data menggunakan tabel eksternal terpartisi, Anda harus menambahkan bidang PARTITION ke sintaks. Anda juga dapat menentukan kolom partisi dan nilai partisi di bidang PARTITION untuk menentukan apakah akan menggunakan partisi statis atau dinamis.

Metode 1: Gunakan pernyataan INSERT INTO PARTITION untuk menyisipkan data secara batch ke dalam tabel eksternal terpartisi.
Catatan
Saat menulis data, data tersebut ditambahkan ke kolom yang sesuai. Setiap kali Anda menulis data, file Apsara File Storage for HDFS baru dihasilkan, dan data yang ada tidak ditimpa. Kolom yang ingin diekspor harus semuanya ditulis ke tabel eksternal. Anda tidak dapat menentukan kolom tertentu untuk ditulis.
- Partisi sepenuhnya statis
  Sintaks:
```
INSERT INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
SELECT <col_name> FROM <source_table>;
```
  Contoh:
```
INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
```
- Partisi statis-dinamis
  Catatan
  Partisi statis harus ditempatkan di depan partisi dinamis. Anda tidak dapat mengubah urutan.
  Sintaks:
```
INSERT INTO <target_table> PARTITION(par1=val1,par2,...) 
SELECT <col_name> FROM <source_table>;
```
  Contoh:
```
INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-27',p2,p3)  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
```
- Partisi sepenuhnya dinamis (partisi yang tidak memerlukan bidang PARTITION)
  Sintaks:
```
INSERT INTO <target_table> 
SELECT <col_name> FROM <source_table>;
```
  Contoh:
```
INSERT INTO hdfs_import_external_par  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
```
Metode 2: Anda tidak dapat mendefinisikan kunci utama dalam tabel eksternal untuk Apsara File Storage for HDFS. REPLACE INTO PARTITION dan INSERT INTO PARTITION sepenuhnya setara.
Catatan
Kolom yang ingin diekspor harus semuanya ditulis ke tabel eksternal. Anda tidak dapat menentukan kolom tertentu untuk ditulis. REPLACE INTO PARTITION menghasilkan file baru tanpa menimpa file yang ada.
Sintaks:
- Partisi sepenuhnya statis
  Sintaks:
```
REPLACE INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
SELECT <col_name> FROM <source_table>;
```
  Contoh:
```
REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
```
- Partisi statis-dinamis
  Catatan
  Partisi statis harus ditempatkan di depan partisi dinamis. Anda tidak dapat mengubah urutan.
  Sintaks:
```
REPLACE INTO <target_table> PARTITION(par1=val1,par2,...) 
SELECT <col_name> FROM <source_table>;
```
  Contoh:
```
REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2,p3)  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
```
- Partisi sepenuhnya dinamis (partisi yang tidak memerlukan bidang PARTITION)
  Sintaks:
```
REPLACE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
```
  Contoh:
```
REPLACE INTO hdfs_import_external_par  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
```
Metode 3: Pernyataan INSERT OVERWRITE PARTITION dapat digunakan dengan cara yang sama seperti pernyataan INSERT INTO PARTITION. Namun, saat mengeksekusi pernyataan INSERT OVERWRITE PARTITION, data yang ada di partisi tujuan akan ditimpa. Jika tidak ada data baru yang ditulis ke partisi, data yang ada tidak akan dihapus.
Sintaks:
```
INSERT OVERWRITE <target_table> PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
SELECT <col_name> FROM <source_table>;
```
Penting
- Kolom yang ingin diekspor harus semuanya ditulis ke tabel eksternal. Anda tidak dapat menentukan kolom tertentu untuk ditulis. INSERT OVERWRITE PARTITION menimpa data yang ada di direktori. Lanjutkan dengan hati-hati.
- IF NOT EXISTS: menunjukkan bahwa jika tabel eksternal memiliki partisi, data tidak akan diimpor ke partisi ini.
Contoh:
```
INSERT OVERWRITE hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS 
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
```