文档

SELECT INTO OUTFILE

更新时间:
一键部署
重要

本文中含有需要您注意的重要提示信息,忽略该信息可能对您的业务造成影响,请务必仔细阅读。

当您需要备份或者迁移云数据库SelectDB中的数据时,通过SELECT INTO OUTFILE命令,可以将查询结果导出为文件,并且支持到通过S3或HDFS协议导出到远端存储(如OSS、HDFS等)。

功能介绍

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]

请求参数说明

参数名称

参数说明

file_path

指向文件存储的路径以及文件前缀。如"s3://bucket_name/to/my_file_""hdfs://path/to/my_file_"

当指定文件前缀为my_file_时,最终的文件名将由my_file_、文件序号以及文件格式后缀组成。其中文件序号由0开始,数量为文件被分割的数量。如:

my_file_abcdefg_0.csv
my_file_abcdefg_1.csv
my_file_abcdegf_2.csv

可以省略文件前缀,只指定文件目录,如"hdfs://path/to/"

format_as

指定导出的格式。支持CSV、PARQUET、CSV_WITH_NAMES、CSV_WITH_NAMES_AND_TYPES和ORC,默认导出为CSV。

properties

指定相关属性。详情请参见Properties语法

目前支持通过S3或HDFS协议进行导出。

说明
  • 支持导出基本数据类型到所有类型的文件中。

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

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

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://

    1. 如果使用http://https://,则会根据use_path_style参数来决定是否使用Path Style方式访问S3协议目标。

    2. 如果使用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

  • 本页导读 (1)