SELECT INTO OUTFILE

Description

该命令用于将 SQL 产出的结果集导出到本地磁盘，或通过 Broker 导出到远端存储上。

1query_stmt
2INTO OUTFILE "file_path"
3[format_as]
4[properties]

• query_stmt

  任意查询语句。

• file_path

  指向文件存储的路径以及文件前缀。如：

  Plain Text 复制

  1bos://my_bucket/my_file_            # 远端目录
  2file:///to/loca/path/my_file_       # 本地目录

  最终的文件名将由 my_file_ + 文件序号 + 文件格式后缀 组成。

  其中文件序号由0开始，数量为文件被分割的数量。如：

  Plain Text 复制

  1my_file_0.csv
  2my_file_1.csv
  3my_file_2.csv

  如果使用本地文件方式，则导出的文件会存储在集群内随机一台 Compute Node 节点上。具体节点信息在返回结果的 URL 中获知。

• format_as

  导出文件格式。目前仅支持 CSV。

  Plain Text 复制

  1FORMAT AS CSV

• properties

  相关属性。其中 broker 所需属性必须以 broker. 开头。如：

  Plain Text 复制

  1(
  2    "broker.prop_key" = "prop_val",
  3)

  其他参数如：

  • column_separator：列分隔符，仅对 CSV 格式适用。默认为 \t。
  • line_delimiter：行分隔符，仅对 CSV 格式适用。默认为 \n。
  • max_file_size：单个文件的最大大小。默认为 1GB。取值范围在 5MB 到 2GB 之间。超过这个大小的文件将会被切分。
  • success_file_name：成功后是否产生一个空文件标识。文件名为 "my_file_file_name"。其中 mysq_file 是在 file_path 中指定的前缀，file_name 为该参数的值。

返回结果说明：

如果正常导出并返回，则结果如下：

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

• FileNumber：最终生成的文件个数。
• TotalRows：结果集行数。
• FileSize：导出文件总大小。单位字节。
• URL：如果是导出到本地磁盘，则这里显示具体导出到哪个 Compute Node。

如果执行错误，则会返回错误信息，如：

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

Example

1. 将简单查询结果导出到文件 bos://my_bucket/result_。指定导出格式为 CSV。使用 my_broker 并设置 kerberos 认证信息。指定列分隔符为 ,，行分隔符为 \n。

   SQL 复制

   1SELECT * FROM tbl
   2INTO OUTFILE "bos://my_bucket/result_"
   3FORMAT AS CSV
   4PROPERTIES
   5(
   6    "broker.name" = "bos",
   7    "broker.bos_endpoint" = "http://bj.bcebos.com",
   8    "broker.bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
   9    "broker.bos_secret_accesskey" = "yyyyyyyyyyyyyyyyyyyyyyyyyy",
   10    "column_separator" = ",",
   11    "line_delimiter" = "\n",
   12    "max_file_size" = "100MB"
   13);

   最终生成文件如如果不大于 100MB，则为：result_0.csv。

   如果大于 100MB，则可能为 result_0.csv, result_1.csv, ...。

2. 将 CTE 语句的查询结果导出到文件 bos://my_bucket/result_。

   SQL 复制

   1WITH
   2x1 AS
   3(SELECT k1, k2 FROM tbl1),
   4x2 AS
   5(SELECT k3 FROM tbl2)
   6SELEC k1 FROM x1 UNION SELECT k3 FROM x2
   7INTO OUTFILE "bos://my_bucket/result_"
   8PROPERTIES
   9(
   10    "broker.name" = "bos",
   11    "broker.bos_endpoint" = "http://bj.bcebos.com",
   12    "broker.bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
   13    "broker.bos_secret_accesskey" = "yyyyyyyyyyyyyyyyyyyyyyyyyy"
   14);

   最终生成文件如如果不大于 1GB，则为：result_0.csv。

   如果大于 1GB，则可能为 result_0.csv, result_1.csv, ...。

3. 将 UNION 语句的查询结果导出到文件 bos://my_bucket/result.txt。并在成功后产生一个空文件标识。

   SQL 复制

   1SELECT k1 FROM tbl1 UNION SELECT k2 FROM tbl1
   2INTO OUTFILE "bos://bucket/result_"
   3PROPERTIES
   4(
   5    "broker.name" = "my_broker",
   6    "broker.bos_endpoint" = "http://bj.bcebos.com",
   7    "broker.bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
   8    "broker.bos_secret_accesskey" = "yyyyyyyyyyyyyyyyyyyyyyyyyy",
   9    "success_file_name" = "SUCCESS"
   10);

   最终生成文件如如果不大于 1GB，则为：result_0.parquet。

   如果大于 1GB，则可能为 result_0.parquet, result_1.parquet, ...。

   成功文件标识为 result_SUCCESS。

4. 导出结果到本地磁盘。

   SQL 复制

   1SELECT k1 FROM tbl1 UNION SELECT k2 FROM tbl1
   2INTO OUTFILE "file:///local/path/result_"
   3PROPERTIES
   4(
   5    "column_separator" = ",",
   6    "line_delimiter" = "\n",
   7    "max_file_size" = "100MB"
   8);

Keywords

1SELECT, INTO, OUTFILE

典型实践

1. 导出数据量和导出效率

   该功能本质上是执行一个 SQL 查询命令。最终的结果是单线程输出的。所以整个导出的耗时包括查询本身的耗时，和最终结果集写出的耗时。如果查询较大，需要设置会话变量 query_timeout 适当的延长查询超时时间。

2. 导出文件的管理

   PALO 不会管理导出的文件。包括导出成功的，或者导出失败后残留的文件，都需要用户自行处理。

3. 导出到本地文件

   导出到本地文件的功能不适用于公有云用户，仅适用于私有化部署的用户。并且默认用户对集群节点有完全的控制权限。PALO 对于用户填写的导出路径不会做合法性检查。如果 PALO 的进程用户对该路径无写权限，或路径不存在，则会报错。同时处于安全性考虑，如果该路径已存在同名的文件，则也会导出失败。

   PALO 不会管理导出到本地的文件，也不会检查磁盘空间等。这些文件需要用户自行管理，如清理等。

4. 结果完整性保证

   该命令是一个同步命令，因此有可能在执行过程中任务连接断开了，从而无法活着导出的数据是否正常结束，或是否完整。此时可以使用 success_file_name 参数要求任务成功后，在目录下生成一个成功文件标识。用户可以通过这个文件，来判断导出是否正常结束。