SELECT INTO OUTFILE

功能介绍

SELECT INTO OUTFILE可用于备份或者迁移SelectDB中的数据，该命令具有以下特点。

• 属于同步命令。

  • 该命令执行完毕后，会立即返回一行结果作为导出执行状态。

  • 由于同步机制下任务状态仅在完成后返回，若连接中断，系统无法获取中间状态，因此无法判断导出是否完整完成”。

    为避免任务中断后无法判断导出是否完整，建议您在创建导出任务时，在Properties中配置"success_file_name" = "SUCCESS"，任务成功后在输出目录会生成指定标识文件。您可通过验证该文件是否存在，确认导出完整性。

• 本质是执行了一个SQL查询。

  该命令本质为执行SQL查询，默认采用单线程输出结果。

• 导出耗时：导出总耗时=查询执行时间+结果集写出耗时

  若处理大规模查询，需通过会话变量query_timeout调大超时阈值。如何设置查询会话变量，请参见查询变量。

• 导出结果为文件。

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

• 导出时不检查文件及文件路径是否存在。

  该命令不检查文件及文件路径是否存在，也不会自动创建目录或覆盖现有文件，具体行为完全取决于远端存储系统（如S3/HDFS）的规则。

  SelectDB不会对导出的文件进行任何管理，所有导出文件（包括成功产物及失败残留文件）均需您在远端存储侧自行管理及清理。

使用限制

目前仅支持通过S3或HDFS协议导出数据。

注意事项

• 数据类型不同，支持导出至的文件类型会有所不同。

  • 基本数据类型：支持导出至CSV、PARQUET、CSV_WITH_NAMES、CSV_WITH_NAMES_AND_TYPES和ORC格式的文件中。

  • 复杂数据类型：

    • 仅支持将复杂数据类型（ARRAY、MAP、STRUCT）导出到CSV、CSV_WITH_NAMES、CSV_WITH_NAMES_AND_TYPES和ORC格式的文件中。

    • 不支持导出嵌套的复杂数据类型。

• 结果文件注意事项：

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

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

• 结果文件内容输出注意事项：

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

  • 目前部分地理位置函数的实际输出值内容为经过编码的二进制字符，请使用ST_AsText进行输出。这类地理位置函数有：

    ST_CIRCLE、ST_POINT、ST_POLYGON、ST_GEOMETRYFROMTEXT、ST_LINEFROMTEXT、ST_GEOMETRYFROMWKB

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

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

并发导出

默认情况下，查询结果集的导出是非并发的，也就是由单个BE节点，单线程导出的。因此导出时间和导出结果集大小正相关。

如果希望查询结果集可以并发导出，可开启并发导出以降低导出的时间。操作如下：

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

2. 验证查询是否可以并发导出。

   您开启并发导出后，如果想验证当前查询是否能进行并发导出，可以使用EXPLAIN分析查询导出语句。语法如下：

   EXPLAIN <select_into_outfile>;

   select_into_outfile为您要执行的SELECT INTO OUTFILE语句，具体语法，请参见语法。

   对查询进行EXPLAIN后，SelectDB会返回该查询的规划，您需分析查询规划判断询是否可以并发导出。

   • RESULT FILE SINK在PLAN FRAGMENT 1中：查询可并发导出。

     满足并发的查询，您可通be_instance_num * parallel_fragment_exec_instance_num计算并发导出查询结果集的并发度。

     • be_instance_num是BE集群的节点数。

       • 计算资源为32核及以下的集群为单节点。

       • 计算资源为32核以上的集群，每32核为一个节点。

     • parallel_fragment_exec_instance_num是并发参数。

       您可通过以下语句查看此参数。

       SHOW variables LIKE  '% parallel_fragment_exec_instance_num%'; 

       通过以下语句设置此参数。

       SET  parallel_fragment_exec_instance_num = <parallel_fragment_exec_instance_num>;  

   • RESULT FILE SINK在PLAN FRAGMENT 0中：查询不能并发导出。

     以下为一个SELECT INTO OUTFILE任务的查询规划，RESULT FILE SINK在PLAN FRAGMENT 0中，该SELECT INTO OUTFILE可以并发导出。

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

3. 根据SELECT INTO OUTFILE的语法创建导出任务，导出数据。

使用示例

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

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

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

数据类型映射

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