SELECT INTO OUTFILE

功能介绍

SELECT INTO OUTFILE命令用于将查询结果导出为文件。目前支持通过S3或HDFS协议导出到远端存储，如OSS、HDFS等。

SELECT INTO OUTFILE属于同步命令，命令返回即表示操作结束，并且会返回一行结果，用来展示导出的执行结果。同步命令的限制导致在执行过程中，若任务连接被断开，则无法确认导出的数据是否正常结束或是否完整。在这种情况下，可以通过指定success_file_name参数要求任务成功后，在目录下生成一个成功文件标识。用户可以通过这个文件来判断导出是否正常结束。

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

该命令不会检查文件及文件路径的存在性，也不会自动创建路径或覆盖已存在的文件，这些行为完全由远端存储系统的语义决定。云数据库SelectDB不会对导出的文件进行管理，无论是导出成功的还是导出失败后残留的文件，都需要用户在远端存储中自行处理。

使用方法

语法

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

请求参数说明

my_file_abcdefg_0.csv
my_file_abcdefg_1.csv
my_file_abcdegf_2.csv

Properties语法

Properties的语法如下所示，支持文件、HDFS、S3相关属性：

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

• 文件相关属性

  参数名称	参数说明
  column_separator	列分割符，只用于CSV相关格式。
  line_delimiter	行分割符，只用于CSV相关格式。
  max_file_size	单个文件大小限制。如果结果超过这个值，将切割成多个文件。 max_file_size取值范围是[5 MB, 2 GB]，默认为1 GB。 当指定导出为ORC文件格式时，实际切分文件的大小将是64MB的倍数。如：指定max_file_size = 5 MB，实际将以64 MB进行切分；指定max_file_size = 65 MB，实际将以128 MB进行切分。
  delete_existing_files	默认为false。若指定为true，则会先删除file_path指定目录下的所有文件，然后导出数据到该目录下。例如： "file_path" = "/user/tmp"，则会删除"/user/"下所有文件及目录； "file_path" = "/user/tmp/"，则会删除"/user/tmp/"下所有文件及目录。 警告 指定delete_existing_files = true是一个危险的操作，建议只在测试环境中使用。若要使用delete_existing_files参数，您可以向阿里云客服提工单，（在fe.conf配置文件中添加配置enable_delete_existing_files = true，并重启fe），此时delete_existing_files才会生效。
  file_suffix	指定导出文件的后缀，若不指定该参数，将使用文件格式的默认后缀。

• HDFS相关属性

  参数名称	参数说明
  fs.defaultFS	NameNode地址和端口。
  hadoop.username	HDFS用户名。
  dfs.nameservices	Name Service名称，与hdfs-site.xml保持一致。
  dfs.ha.namenodes.[nameservice ID]	NameNode的ID列表，与hdfs-site.xml保持一致。
  dfs.namenode.rpc-address.[nameservice ID].[name node ID]	NameNode的RPC地址，数量与NameNode数量相同，与hdfs-site.xml保持一致。
  dfs.client.failover.proxy.provider.[nameservice ID]	HDFS客户端连接活跃NameNode的Java类，通常是org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider。

  对于开启Kerberos认证的Hadoop集群，还需要额外设置如下PROPERTIES属性：

  参数名称	参数说明
  dfs.namenode.kerberos.principal	HDFS NameNode服务的Principal名称。
  hadoop.security.authentication	指定认证方式。指定为kerberos以启用Kerberos认证。
  hadoop.kerberos.principal	指定Kerberos的Principal。
  hadoop.kerberos.keytab	指定Kerberos的Keytab文件路径。

• S3相关属性

  支持S3协议的存储系统，包括但不限于S3，OSS等，都可参考该参数列表进行配置。

  参数名称	参数说明
  s3.endpoint	使用S3协议目标的endpoint。必选。
  s3.access_key	使用S3协议目标的用户身份密钥。必选。
  s3.secret_key	使用S3协议目标的用户加密认证字符串。必选。
  s3.region	使用S3协议目标的Region，必选。
  s3.session_token	使用S3协议目标的用户临时会话token。若启用临时会话验证，必选。
  use_path_style	可选参数，默认为false。 S3 SDK默认使用Virtual-hosted Style方式。但某些对象存储系统可能没开启或没支持Virtual-hosted Style方式的访问，此时我们可以添加use_path_style参数来强制使用Path Style方式。 说明 URI目前支持三种方案（schema）：http://、https://和 s3://。 如果使用http://或https://，则会根据use_path_style参数来决定是否使用Path Style方式访问S3协议目标。 如果使用s3://，则会使用Virtual-hosted Style方式访问S3协议目标。

响应结果说明

导出命令为同步命令。命令返回即表示操作结束，并且会返回一行结果，来展示导出的执行结果。

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

  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)

  其中：

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

• 如果进行了并发导出，则会返回多行数据。

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

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

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

并发导出

默认情况下，查询结果集的导出是非并发的，也就是由单个BE节点，单线程导出的。因此导出时间和导出结果集大小正相关。开启并发导出可以降低导出的时间。如果希望查询结果集可以并发导出，需要满足以下条件：

1. 设置会话变量以开启并发导出：set enable_parallel_outfile = true;。

2. 查询可以满足并发导出的需求，例如查询计划顶层不包含排序处理。

并发导出查询结果集的并发度为be_instance_num * parallel_fragment_exec_instance_num。

验证是否可以并发导出

用户通过Session变量设置开启并发导出后，如果想验证当前查询是否能进行并发导出，可以执行以下语句进行验证：

EXPLAIN SELECT xxx FROM xxx WHERE xxx INTO outfile "s3://xxx" format AS csv properties ("AWS_ENDPOINT" = "xxx", ...);

对查询进行EXPLAIN后，SelectDB会返回该查询的规划：

• 如果发现RESULT FILE SINK出现在PLAN FRAGMENT 1中，就说明导出并发开启成功。

• 如果RESULT FILE SINK出现在PLAN FRAGMENT 0中，则说明当前查询不能进行并发导出（当前查询不同时满足并发导出的条件）。

并发导出的规划示例：
+-----------------------------------------------------------------------------+
| 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                                                    |
+-----------------------------------------------------------------------------+

注意事项

• 对于结果集为空的查询，依然会产生一个文件。

• 文件切分会保证一行数据完整的存储在单一文件中，因此文件的大小并不严格等于max_file_size。

• 对于部分输出为非可见字符的函数，如BITMAP、HLL类型，输出为\N，即NULL。

• 目前部分地理信息函数，如ST_Point的输出类型为VARCHAR，但实际输出值为经过编码的二进制字符。当前这些函数会输出乱码。对于地理函数，请使用ST_AsText进行输出。

使用示例

• 使用HDFS方式导出。将简单查询结果导出到文件hdfs://${host}:${fileSystem_port}/path/to/result.txt，其中指定导出格式为CSV、用户名为work、指定列分隔符为,和行分隔符为\n，示例如下。

  -- fileSystem_port默认值为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"
  );

  如果Hadoop集群开启高可用，示例如下。

  --HA fileSystem_port默认值为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'
  );

  生成的文件如果大小不超过1 GB，则为：result_0.csv。 如果大于1 GB，则可能为result_0.csv, result_1.csv, ...。

• 将UNION语句的查询结果导出到文件s3://oss-bucket/result.txt。其中，存储系统指定为位于可用区cn-hangzhou的OSS中的桶oss-bucket。指定导出格式为PARQUET（PARQUET格式无需指定列分割符），并且导出完成后，生成一个标识文件，示例如下。

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

  重要

  使用S3协议导出数据时，对于不同云厂商提供的对象存储系统，URI都应该使用三种schema（http://，https://和 s3://）之一作为路径开头标志。否则将提示ERROR 1105 (HY000): errCode = 2, detailMessage = Unknown properties: [s3.region, s3.endpoint, s3.secret_key, s3.access_key]。

• 使用S3协议导出到OSS，并且开启并发导出。最终生成的文件前缀为my_file_{fragment_instance_id}_，示例如下。

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

• 使用S3协议导出到OSS。由于查询中包含对最终结果的排序处理（order by k1），所以这个查询即使开启并发导出的会话变量，也是无法并发导出的，示例如下。

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

数据类型映射

Parquet 和 ORC 文件格式都有各自的数据类型。SelectDB的导出功能能够自动将SelectDB的数据类型导出为Parquet/ORC文件格式所对应的数据类型。

• SelectDB导出到ORC文件格式的数据类型映射：

  SelectDB Type	Orc Type
  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

• 在将数据从SelectDB导出到Parquet文件格式时，会先将SelectDB内存中的数据转换为Arrow内存数据格式，然后由Arrow写出到Parquet文件格式。SelectDB数据类型到Arrow数据类的映射关系为：

  SelectDB Type	Arrow Type
  boolean	boolean
  tinyint	int8
  smallint	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