This document teaches you how to export query results by using SELECT INTO OUTFILE command .

Function introduction

SELECT INTO OUTFILE statements are able to export the query results to a file.

Currently you can use Broker process to export the query results into remote storage, such as HDFS, S3 and BOS. Or the results can be exported directly to local disk of the node where the Compute Node is located (not available for cloud users).

The user can export the expected query results by the command together with flexible SQL syntax.

Execute export command

SELECT INTO OUTFILE, in essence, is a synchronous SQL query command, which means it will be affected by timeout limit of the session variables query_ timeout. Please set a reasonable timeout in advance if relatively large result set is exported or if there is a relatively long time to execute SQL.

1. Export to BOS

   SELECT * FROM example_tbl
   INTO OUTFILE "bos://my_bucket/result_"
   FORMAT AS CSV
   PROPERTIES
   (
       "broker.name" = "bos",
       "broker.bos_endpoint" = "http://bj.bcebos.com",
       "broker.bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
       "broker.bos_secret_accesskey" = "yyyyyyyyyyyyyyyyyyyyyyyyyy"
       "column_separator" = ",",
       "line_delimiter" = "\n",
       "max_file_size" = "100MB"
   );

   The above commands export the results of SQL statement SELECT * FROM example_tblto BOS.

2. Export to local disk of Compute Node

   SELECT * FROM example_tbl
   INTO OUTFILE "file:///home/work/path/result_"
   FORMAT AS CSV
   PROPERTIES
   (
       "column_separator" = ",",
       "line_delimiter" = "\n",
       "max_file_size" = "100MB"
   );

   The above commands export the results to specified disk path of certain Compute Node.

   Public cloud Palo users cannot export the results to local disk for they have no direct access to the node.

3. Export to BOS and generate an identification file after successful export.

   SELECT * FROM example_tbl
   INTO OUTFILE "bos://my_bucket/result_"
   FORMAT AS CSV
   PROPERTIES
   (
       "broker.name" = "bos",
       "broker.bos_endpoint" = "http://bj.bcebos.com",
       "broker.bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
       "broker.bos_secret_accesskey" = "yyyyyyyyyyyyyyyyyyyyyyyyyy"
       "column_separator" = ",",
       "line_delimiter" = "\n",
       "max_file_size" = "100MB",
       "success_file_name" = "SUCCESS"
   );

   The above commands export the results of SQL statement SELECT * FROM example_tblto BOS and generate an empty file identification of result_SUCCESS after successful export. The user can judge whether the export is completed through this identification.

Please refer to SELECT INTO OUTFILE for a detailed description of the statement.

View returned results

Export commands are synchronous. The return of the commands indicates the end of the operation. And a row of results will be returned to show the execution result of the export.

For normal export and return, the results are as follows:

mysql> select * from tbl1 limit 10 into outfile "file:///home/work/path/result_";
+------------+-----------+----------+--------------+
| FileNumber | TotalRows | FileSize | URL          |
+------------+-----------+----------+--------------+
|          1 |         2 |        8 | 192.168.1.10 |
+------------+-----------+----------+--------------+
1 row in set (0.05 sec)

• FileNumber: Number of finally generated files.
• TotalRows: Row number of result set.
• FileSize: Total exported file size. Unit byte.
• URL: If the results are exported to a local disk, the specific Compute Node to which it is exported will be displayed here.

For execution errors, error messages will be returned, such as:

mysql> SELECT * FROM tbl INTO OUTFILE ...
ERROR 1064 (HY000): errCode = 2, detailMessage = Open broker writer failed ...