ApsaraDB for SelectDB：Ekspor data menggunakan pernyataan SELECT INTO OUTFILE

Ikhtisar

Anda dapat menggunakan pernyataan SELECT INTO OUTFILE untuk mencadangkan atau memigrasi data dari instans SelectDB. Pernyataan ini memiliki karakteristik berikut:

• Pernyataan ini dijalankan secara sinkron.

  • Setelah menjalankan pernyataan ini, baris yang menggambarkan status pekerjaan ekspor data akan segera dikembalikan.

  • Karena mekanisme sinkronisasi, status pekerjaan ekspor data hanya dikembalikan setelah Anda menjalankan pernyataan ini. Jika koneksi terputus, status pekerjaan tidak dikembalikan, sehingga Anda tidak dapat menentukan apakah semua data yang di-query telah diekspor. Untuk mencegah masalah ini, kami sarankan Anda mengonfigurasi pengaturan "success_file_name" = "SUCCESS" di Properties saat membuat pekerjaan ekspor data. Dengan cara ini, jika pekerjaan berhasil, file identifikasi akan dibuat di direktori tempat file yang diekspor disimpan, dan Anda dapat menggunakannya untuk memverifikasi apakah semua data telah diekspor.

• Pernyataan ini dijalankan dengan melakukan query SQL.

  Secara default, hanya satu thread yang digunakan selama eksekusi.

• Durasi ekspor dihitung menggunakan rumus berikut: Total durasi eksekusi = Durasi eksekusi query + Waktu yang diperlukan untuk mengembalikan set hasil.

  Jika Anda ingin meng-query sejumlah besar data, konfigurasikan variabel sesi query_timeout untuk memperpanjang periode timeout query. Untuk informasi lebih lanjut tentang cara mengonfigurasi variabel sesi, lihat Manajemen Variabel.

• Pernyataan ini mengekspor hasil query ke dalam file.

  File-file tersebut dapat disimpan di sistem penyimpanan jarak jauh seperti OSS dan HDFS melalui protokol Amazon S3 dan HDFS.

• Pernyataan ini tidak memeriksa apakah file yang diekspor dan jalur file ada.

  Pernyataan ini tidak secara otomatis membuat jalur atau menimpa file yang sudah ada. Operasi yang dapat dilakukan oleh pernyataan ini ditentukan oleh sistem penyimpanan jarak jauh seperti protokol Amazon S3 dan HDFS.

  SelectDB tidak mengelola file yang diekspor. Anda harus mengelola file yang dihasilkan oleh pekerjaan ekspor data yang berhasil dan file sisa yang dihasilkan oleh pekerjaan ekspor data yang gagal di sistem penyimpanan jarak jauh.

Batasan

Pernyataan ini dapat digunakan untuk mengekspor data melalui protokol Amazon S3 dan HDFS.

Catatan Penggunaan

• Format file yang diekspor bervariasi berdasarkan tipe data.

  • Tipe data dasar: Format file CVS, CSV_with_names, CVS_with_names_and_types, Parquet, dan Optimized Row Columnar (ORC) didukung.

  • Tipe data kompleks:

    • Data tipe ARRAY, MAP, dan STRUCT dapat diekspor ke file dengan format berikut: CSV, CSV_WITH_NAMES, CSV_WITH_NAMES_AND_TYPES, dan ORC.

    • Data bersarang tidak dapat diekspor.

• Catatan penggunaan untuk file hasil:

  • Jika set hasil kosong, file tetap akan dibuat.

  • Pembagian file memastikan bahwa setiap file segmen menyimpan baris data yang lengkap. Oleh karena itu, ukuran file segmen mungkin sedikit berbeda dari nilai parameter max_file_size.

• Catatan penggunaan untuk isi file hasil:

  • Jika output fungsi, seperti fungsi BITMAP atau HyperLogLog (HLL), berisi karakter tak terlihat, \N akan dikembalikan. Ini menunjukkan bahwa fungsi mengembalikan nilai null.

  • Output beberapa fungsi yang digunakan dalam database geospasial adalah karakter biner yang dikodekan. Kami sarankan Anda menggunakan fungsi ST_AsText. Fungsi-fungsi yang digunakan dalam database geospasial termasuk ST_CIRCLE, ST_POINT, ST_POLYGON, ST_GEOMETRYFROMTEXT, ST_LINEFROMTEXT, dan ST_GEOMETRYFROMWKB.

Sintaks

query_stmt
INTO OUTFILE "file_path"
[format_as]
[properties]

Parameter

Sintaks objek properties

Kode sampel berikut menunjukkan contoh sintaks objek properties. Anda dapat menentukan properti terkait file yang diekspor, protokol HDFS, dan protokol Amazon S3.

[PROPERTIES ("key"="value", ...)]

• Properti terkait file

  Parameter	Wajib	Deskripsi
  column_separator	Tidak	Pemisah kolom, yang hanya digunakan untuk file tipe CSV.
  line_delimiter	Tidak	Pemisah baris, yang hanya digunakan untuk file tipe CSV.
  max_file_size	Tidak	Ukuran maksimum file. Jika ukuran hasil query melebihi nilai ini, beberapa file segmen akan dibuat. Nilai valid: [5 MB, 2 GB]. Nilai default: 1 GB. Jika Anda menyetel format_as ke ORC, ukuran file segmen dihitung menggunakan rumus berikut: ceil (max_file_size/64) × 64 MB.
  delete_existing_files	Tidak	Menentukan apakah akan menghapus semua file di direktori yang ditentukan oleh parameter file_path. Nilai valid: false (default): tidak menghapus semua file di direktori yang ditentukan oleh parameter file_path tetapi langsung mengekspor file. true: menghapus semua file di direktori yang ditentukan oleh parameter file_path dan menambahkan data yang diekspor ke direktori. Contoh: Jika Anda menggunakan pengaturan "file_path" = "/user/tmp", semua file dan direktori di path "/user/" akan dihapus. Jika Anda menggunakan pengaturan "file_path" = "/user/tmp/", semua file dan direktori di path "/user/tmp/" akan dihapus. Peringatan Jika Anda menggunakan pengaturan delete_existing_files = true, risiko mungkin terjadi. Kami sarankan Anda hanya melakukan operasi ini di lingkungan pengujian. Untuk menentukan parameter delete_existing_files, ajukan tiket untuk menghubungi dukungan teknis Alibaba Cloud. Tim dukungan teknis SelectDB menambahkan pengaturan enable_delete_existing_files = true ke file fe.conf dan memulai ulang antarmuka depan agar parameter delete_existing_files berlaku.
  file_suffix	Tidak	Ekstensi nama file dari file yang diekspor. Jika parameter ini tidak ditentukan, ekstensi nama file default akan digunakan.

• Properti terkait HDFS

  Parameter	Wajib	Deskripsi
  fs.defaultFS	Ya	Titik akhir dan port NameNode.
  hadoop.username	Ya	Nama pengguna yang digunakan untuk masuk ke HDFS.
  dfs.nameservices	Ya	Nama Layanan Nama. Nilai parameter ini sama dengan yang ada di file hdfs-site.xml.
  dfs.ha.namenodes.[nameservice ID]	Ya	ID NameNode dalam Layanan Nama. Nilai parameter ini sama dengan yang ada di file hdfs-site.xml.
  dfs.namenode.rpc-address.[nameservice ID].[name node ID]	Ya	URL Remote Procedure Call (RPC) dari NameNode. Setiap URL RPC sesuai dengan satu NameNode. Nilai parameter ini sama dengan yang ada di file hdfs-site.xml.
  dfs.client.failover.proxy.provider.[nameservice ID]	Ya	Kelas Java yang mengimplementasikan koneksi NameNode aktif untuk klien HDFS. Nilai default: org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider.

  Jika Otentikasi Kerberos diaktifkan untuk kluster Hadoop, Anda juga harus menentukan properti berikut.

  Parameter	Wajib	Deskripsi
  dfs.namenode.kerberos.principal	Ya	Nama principal Kerberos dari NameNode HDFS.
  hadoop.security.authentication	Ya	Metode autentikasi. Atur parameter ini ke kerberos untuk mengaktifkan Otentikasi Kerberos.
  hadoop.kerberos.principal	Ya	Principal untuk Otentikasi Kerberos.
  hadoop.kerberos.keytab	Ya	Jalur ke file keytab Kerberos.

• Properti terkait Amazon S3

  Tabel berikut menjelaskan properti yang dapat Anda atur untuk sistem penyimpanan yang mendukung protokol Amazon S3. Sistem penyimpanan ini termasuk Amazon S3 dan OSS.

  Parameter	Wajib	Deskripsi
  s3.endpoint	Ya	Titik akhir sistem penyimpanan tujuan.
  s3.access_key	Ya	Kunci akses yang digunakan untuk mengotentikasi permintaan Anda untuk mengakses sistem penyimpanan tujuan.
  s3.secret_key	Ya	Kunci rahasia yang digunakan untuk mengotentikasi permintaan Anda untuk mengakses sistem penyimpanan tujuan.
  s3.region	Ya	Wilayah tempat sistem penyimpanan tujuan berada.
  s3.session_token	Ya	Token sesi yang digunakan untuk kredensial sesi sementara untuk mengakses sistem penyimpanan tujuan. Parameter ini diperlukan jika otentikasi sesi sementara diaktifkan.
  use_path_style	Tidak	Menentukan apakah akan mengakses sistem penyimpanan tujuan dalam gaya jalur. Nilai default: false. Secara default, SDK Amazon S3 menggunakan gaya virtual-hosted untuk mengakses sistem penyimpanan tujuan. Namun, beberapa sistem penyimpanan objek mungkin tidak mendukung gaya virtual-hosted. Dalam hal ini, Anda dapat menentukan parameter use_path_style untuk memaksa mengakses sistem penyimpanan tujuan dalam gaya jalur. Catatan Skema URI berikut didukung: http://, https://, dan s3://. Jika Anda menggunakan http:// atau https://, parameter use_path_style digunakan untuk menentukan apakah akan mengakses sistem penyimpanan tujuan dalam gaya jalur. Jika Anda menggunakan s3://, sistem penyimpanan tujuan diakses dalam gaya virtual-hosted.

Parameter respons

Data diekspor secara sinkron. Ketika hasil dikembalikan, eksekusi pernyataan selesai. Baris dalam hasil yang dikembalikan menunjukkan hasil dari ekspor data.

• Jika eksekusi berhasil, informasi berikut akan dikembalikan:

  SELECT * FROM tbl1 LIMIT 10 into outfile "file:///home/work/path/result_";
  +------------+-----------+----------+--------------------------------------------------------------------+
  | FileNumber | TotalRows | FileSize | URL                                                                |
  +------------+-----------+----------+--------------------------------------------------------------------+
  |          1 |         2 |        8 | file:///192.168.1.10/home/work/path/result_{fragment_instance_id}_ |
  +------------+-----------+----------+--------------------------------------------------------------------+
  1 row in set (0.05 sec)

  Tabel berikut menjelaskan parameter respons.

  Parameter	Deskripsi
  FileNumber	Jumlah file yang dihasilkan.
  TotalRows	Jumlah baris yang dikembalikan dalam set hasil.
  FileSize	Ukuran total file yang diekspor. Satuan: byte.
  URL	Node komputasi tempat data diekspor jika data diekspor ke disk lokal.

• Jika ekspor paralel dilakukan, beberapa baris data akan dikembalikan.

  +------------+-----------+----------+--------------------------------------------------------------------+
  | FileNumber | TotalRows | FileSize | URL                                                                |
  +------------+-----------+----------+--------------------------------------------------------------------+
  |          1 |         3 |        7 | file:///192.168.1.10/home/work/path/result_{fragment_instance_id}_ |
  |          1 |         2 |        4 | file:///192.168.1.11/home/work/path/result_{fragment_instance_id}_ |
  +------------+-----------+----------+--------------------------------------------------------------------+
  2 rows in set (2.218 sec)

• Jika eksekusi gagal, pesan kesalahan akan dikembalikan. Pernyataan SQL berikut menunjukkan contoh.

  SELECT * FROM tbl INTO OUTFILE ...
  ERROR 1064 (HY000): errCode = 2, detailMessage = ...

Ekspor Paralel

Secara default, ekspor set hasil query tidak paralel, dan satu thread pada node BE digunakan untuk mengekspor data dan mengembalikan set hasil. Oleh karena itu, durasi ekspor memiliki korelasi positif dengan ukuran set hasil.

Anda dapat menggunakan ekspor paralel untuk mempercepat ekspor data. Untuk mengaktifkan ekspor paralel, lakukan langkah-langkah berikut:

1. Atur variabel sesi set enable_parallel_outfile menjadi true.

2. Periksa apakah set hasil query mendukung ekspor paralel.

   Setelah mengaktifkan ekspor paralel, gunakan pernyataan EXPLAIN untuk memeriksa apakah ekspor paralel didukung. Sintaks:

   EXPLAIN <select_into_outfile>;

   Jalankan pernyataan SELECT INTO OUTFILE. Untuk informasi lebih lanjut tentang sintaks, lihat Sintaks.

   Setelah menjalankan pernyataan EXPLAIN, SelectDB segera mengembalikan rencana query. Anda harus menganalisis rencana query dan memeriksa apakah ekspor paralel didukung.

• Jika RESULT FILE SINK ditampilkan di PLAN FRAGMENT 1, ekspor paralel didukung.

  Jika ekspor paralel didukung, Anda dapat menggunakan be_instance_num * parallel_fragment_exec_instance_num untuk menghitung jumlah permintaan yang dapat diproses secara bersamaan oleh satu instans SelectDB.

  • be_instance_num menentukan jumlah node dalam kluster BE.

    • Jika kluster menyediakan hingga 32 core CPU, kluster hanya berisi satu node.

    • Jika kluster menyediakan setidaknya 32 core CPU, kluster berisi beberapa node, dan setiap node memiliki 32 core CPU.

  • parallel_fragment_exec_instance_num menentukan parameter paralel.

    Anda dapat menjalankan pernyataan berikut untuk melihat parameter:

    SHOW variables LIKE  '% parallel_fragment_exec_instance_num%'; 

    Anda dapat menjalankan pernyataan berikut untuk mengonfigurasi parameter:

    SET  parallel_fragment_exec_instance_num = <parallel_fragment_exec_instance_num>;  

• Jika RESULT FILE SINK ditampilkan di PLAN FRAGMENT 0, ekspor paralel tidak didukung.

  Dalam rencana query sampel berikut dari pekerjaan SELECT INTO OUTFILE, RESULT FILE SINK ditampilkan di PLAN FRAGMENT 0. Dalam hal ini, ekspor paralel tidak didukung.

  Sample parallel export plan:
  +-----------------------------------------------------------------------------+
  | Explain String                                                              |
  +-----------------------------------------------------------------------------+
  | PLAN FRAGMENT 0                                                             |
  |  OUTPUT EXPRS:<slot 2> | <slot 3> | <slot 4> | <slot 5>                     |
  |   PARTITION: UNPARTITIONED                                                  |
  |                                                                             |
  |   RESULT SINK                                                               |
  |                                                                             |
  |   1:EXCHANGE                                                                |
  |                                                                             |
  | PLAN FRAGMENT 1                                                             |
  |  OUTPUT EXPRS:`k1` + `k2`                                                   |
  |   PARTITION: HASH_PARTITIONED: `default_cluster:test`.`multi_tablet`.`k1`   |
  |                                                                             |
  |   RESULT FILE SINK                                                          |
  |   FILE PATH: s3://ml-bd-repo/bpit_test/outfile_1951_                        |
  |   STORAGE TYPE: S3                                                          |
  |                                                                             |
  |   0:OlapScanNode                                                            |
  |      TABLE: multi_tablet                                                    |
  +-----------------------------------------------------------------------------+

• Buat pekerjaan ekspor data untuk mengekspor data berdasarkan sintaks.

Contoh

Ekspor data melalui protokol HDFS

Ekspor hasil query sederhana ke file CSV. Contoh:

-- fileSystem_port Nilai default: 9000.
SELECT * FROM tbl
INTO OUTFILE "hdfs://${host}:${fileSystem_port}/path/to/result_"
FORMAT AS CSV
PROPERTIES
(
    "fs.defaultFS" = "hdfs://ip:port",
    "hadoop.username" = "work"
);

--HA fileSystem_port Nilai default: 8020.
SELECT * FROM tbl
INTO OUTFILE "hdfs:///path/to/result_"
FORMAT AS CSV
PROPERTIES
(
'fs.defaultFS'='hdfs://hacluster/',
'dfs.nameservices'='hacluster',
'dfs.ha.namenodes.hacluster'='n1,n2',
'dfs.namenode.rpc-address.hacluster.n1'='192.168.0.1:8020',
'dfs.namenode.rpc-address.hacluster.n2'='192.168.0.2:8020',
'dfs.client.failover.proxy.provider.hacluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

Jika ukuran file total kurang dari atau sama dengan 1 GB, file result_0.csv akan dibuat. Jika ukuran file total lebih besar dari 1 GB, beberapa file segmen bernama result_0.csv, result_1.csv, ... akan dibuat.

OSS Ekspor data ke bucket OSS melalui protokol Amazon S3

Ekspor data ke OSS melalui protokol Amazon S3. Kode sampel:

SET enable_parallel_outfile = true;

SELECT k1 FROM tb1 LIMIT 1000
INTO outfile "s3://my_bucket/export/my_file_"
format AS csv
properties
(
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.access_key" = "****",
    "s3.secret_key" = "****",
    "s3.region" = "cn-hangzhou"
)

Dalam set hasil yang sama, hasil query berisi ORDER BY k1, yang menunjukkan bahwa hasil yang dikembalikan diurutkan berdasarkan nilai yang ditentukan. Oleh karena itu, meskipun ekspor paralel diaktifkan untuk permintaan ini, data tidak diekspor secara bersamaan. Kode sampel:

SET enable_parallel_outfile = true;

SELECT k1 FROM tb1 ORDER BY k1 LIMIT 1000
INTO outfile "s3://my_bucket/export/my_file_"
format AS csv
properties
(
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.access_key" = "****",
    "s3.secret_key" = "****",
    "s3.region" = "cn-hangzhou"
)

Pemetaan tipe data

File Parquet dan ORC mendukung tipe data yang berbeda. SelectDB menyediakan fitur ekspor data untuk secara otomatis mengekspor data dari instans SelectDB ke data dengan tipe yang didukung oleh file Parquet dan ORC.

• Tabel berikut menjelaskan pemetaan tipe data antara data SelectDB dan data dalam file ORC.

  Tipe data ApsaraDB for SelectDB	Tipe data yang didukung oleh file ORC
  BOOLEAN	BOOLEAN
  TINYINT	TINYINT
  SMALLINT	SMALLINT
  INT	INT
  BIGINT	BIGINT
  LARGEINT	STRING
  DATE	STRING
  DATEV2	STRING
  DATETIME	STRING
  DATETIMEV2	TIMESTAMP
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  CHAR / VARCHAR / STRING	STRING
  DECIMAL	DECIMAL
  STRUCT	STRUCT
  MAP	MAP
  ARRAY	ARRAY

• Jika Anda mengekspor data dari memori instans SelectDB ke file Parquet, data pertama kali dipetakan ke tipe data yangdidukung oleh Apache Arrow dan kemudian ditulis ke file Parquet oleh Apache Arrow. Tabel berikut menjelaskan pemetaan tipe data antara SelectDB dan data Apache Arrow.

  Tipe data ApsaraDB for SelectDB	Tipe data Apache Arrow
  BOOLEAN	BOOLEAN
  TINYINT	INT8
  SNALLINT	INT16
  INT	INT32
  BIGINT	INT64
  LARGEINT	UTF8
  DATE	UTF8
  DATEV2	UTF8
  DATETIME	UTF8
  DATETIMEV2	UTF8
  FLOAT	FLOAT32
  DOUBLE	FLOAT64
  CHAR / VARCHAR / STRING	UTF8
  DECIMAL	DECIMAL128
  STRUCT	STRUCT
  MAP	MAP
  ARRAY	LIST