ApsaraDB for SelectDB:SELECT\ INTO\ OUTFILE

Cara kerja

1. Tulis kueri SELECT untuk menentukan data yang akan diekspor.

2. Tentukan jalur tujuan di OSS atau HDFS serta atur format dan properti output.

3. Jalankan pernyataan tersebut. SelectDB mengeksekusi kueri dan menulis hasilnya ke tujuan dalam satu operasi sinkron.

4. Periksa baris yang dikembalikan untuk memverifikasi jumlah file, jumlah baris, dan ukuran file sebagai konfirmasi keberhasilan.

Catatan penggunaan

Durasi total = Waktu eksekusi kueri + Waktu penulisan hasil

SET query_timeout = <seconds>;

Batasan

SELECT INTO OUTFILE hanya mendukung protokol Amazon Simple Storage Service (Amazon S3) dan protokol HDFS.

Sintaks

query_stmt
INTO OUTFILE "file_path"
[FORMAT AS format_name]
[PROPERTIES ("key"="value", ...)]

<awalan><nama-instans>_<urutan>.csv

my_file_abcdefg_0.csv
my_file_abcdefg_1.csv
my_file_abcdefg_2.csv
...

Respons

SELECT INTO OUTFILE bersifat sinkron. Saat pernyataan selesai, respons berupa satu baris per thread ekspor.

Respons sukses:

SELECT * FROM tbl1 LIMIT 10 INTO OUTFILE "file:///home/work/path/result_";

+------------+-----------+----------+--------------------------------------------------------------------+
| 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 baris dalam set (2,218 dtk)

Respons ekspor paralel (beberapa baris, satu per thread):

+------------+-----------+----------+--------------------------------------------------------------------+
| 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)

Respons error:

ERROR 1064 (HY000): errCode = 2, detailMessage = ...

Aktifkan ekspor paralel

Secara default, ekspor berjalan pada satu thread node BE. Aktifkan ekspor paralel untuk mendistribusikan beban kerja ke beberapa node BE.

Langkah 1: Aktifkan variabel sesi.

SET enable_parallel_outfile = true;

Langkah 2: Verifikasi bahwa kueri Anda mendukung ekspor paralel.

Jalankan EXPLAIN pada pernyataan SELECT INTO OUTFILE Anda:

EXPLAIN <select_into_outfile_statement>;

Periksa output:

• Ekspor paralel didukung: RESULT FILE SINK muncul di PLAN FRAGMENT 1.

• Ekspor paralel tidak didukung: RESULT FILE SINK muncul di PLAN FRAGMENT 0.

Kueri dengan ORDER BY tidak dapat berjalan secara paralel meskipun enable_parallel_outfile = true, karena pengurutan memerlukan langkah merge single-threaded.

Contoh rencana kueri (ekspor paralel didukung):

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

Langkah 3: Hitung konkurensi maksimum (opsional).

Saat ekspor paralel didukung, jumlah maksimum permintaan konkuren adalah:

be_instance_num × parallel_fragment_exec_instance_num

• be_instance_num: jumlah node BE. Kluster dengan hingga 32 core CPU menggunakan 1 node; kluster dengan minimal 32 core CPU menggunakan beberapa node, masing-masing dengan 32 core CPU.

• parallel_fragment_exec_instance_num: pengaturan paralelisme per node.

Untuk melihat pengaturan paralelisme saat ini:

SHOW VARIABLES LIKE '%parallel_fragment_exec_instance_num%';

Untuk mengaturnya:

SET parallel_fragment_exec_instance_num = <value>;

Contoh

-- fileSystem_port 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"
);

-- fileSystem_port 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"
);

SELECT k1 FROM tbl1 UNION SELECT k2 FROM tbl1
INTO OUTFILE "s3://oss-bucket/result_"
FORMAT AS PARQUET
PROPERTIES
(
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.access_key" = "****",
    "s3.secret_key" = "****",
    "s3.region" = "cn-hangzhou"
);

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"
);

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

Saat mengekspor ke Parquet atau ORC, SelectDB secara otomatis memetakan tipe datanya ke format yang didukung oleh masing-masing jenis file.