ApsaraDB for SelectDB:SELECT INTO OUTFILE

How it works

1. Write a SELECT query to define the data to export.

2. Specify a destination path in OSS or HDFS and set the output format and properties.

3. Run the statement. SelectDB executes the query and writes the results to the destination in a single synchronous operation.

4. Check the returned row for file count, row count, and file size to confirm success.

Usage notes

Total duration = Query execution time + Result write time

SET query_timeout = <seconds>;

Limitations

SELECT INTO OUTFILE supports the Amazon Simple Storage Service (Amazon S3) protocol and the HDFS protocol only.

Syntax

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

<prefix><instance-name>_<sequence>.csv

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

Response

SELECT INTO OUTFILE is synchronous. When the statement finishes, it returns one row per export thread.

Success response:

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)

Parallel export response (multiple rows, one 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)

Error response:

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

Enable parallel export

By default, export runs on a single BE node thread. Enable parallel export to distribute the workload across multiple BE nodes.

Step 1: Enable the session variable.

SET enable_parallel_outfile = true;

Step 2: Verify that your query supports parallel export.

Run EXPLAIN on your SELECT INTO OUTFILE statement:

EXPLAIN <select_into_outfile_statement>;

Check the output:

• Parallel export supported: RESULT FILE SINK appears in PLAN FRAGMENT 1.

• Parallel export not supported: RESULT FILE SINK appears in PLAN FRAGMENT 0.

A query with ORDER BY cannot run in parallel even when enable_parallel_outfile = true, because ordering requires a single-threaded merge step.

Example query plan (parallel export supported):

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

Step 3: Calculate maximum concurrency (optional).

When parallel export is supported, the maximum number of concurrent requests is:

be_instance_num × parallel_fragment_exec_instance_num

• be_instance_num: number of BE nodes. Clusters with up to 32 CPU cores use 1 node; clusters with at least 32 CPU cores use multiple nodes, each with 32 CPU cores.

• parallel_fragment_exec_instance_num: parallelism setting per node.

To view the current parallelism setting:

SHOW VARIABLES LIKE '%parallel_fragment_exec_instance_num%';

To set it:

SET parallel_fragment_exec_instance_num = <value>;

Examples

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

Data type mappings

When exporting to Parquet or ORC, SelectDB automatically maps its data types to the formats supported by each file type.