OUTFILE

Name

OURFILE

description

该语句用于使用 SELECT INTO OUTFILE 命令将查询结果的导出为文件。目前支持通过 Broker 进程, 通过 S3 协议, 或直接通过 HDFS 协议,导出到远端存储,如 HDFS,S3,BOS,COS(腾讯云)上。

语法:

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

说明:

  1. file_path

    1. file_path 指向文件存储的路径以及文件前缀。如 `hdfs://path/to/my_file_`
    2. 最终的文件名将由 `my_file_`,文件序号以及文件格式后缀组成。其中文件序号由0开始,数量为文件被分割的数量。如:
    3. my_file_abcdefg_0.csv
    4. my_file_abcdefg_1.csv
    5. my_file_abcdegf_2.csv
  2. format_as

    1. FORMAT AS CSV

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

    注:PARQUET、CSV_WITH_NAMES、CSV_WITH_NAMES_AND_TYPES、ORC 在 1.2 版本开始支持。

  3. properties

    1. ```
    2. 指定相关属性。目前支持通过 Broker 进程, 或通过 S3 协议进行导出。
    3. 语法:
    4. [PROPERTIES ("key"="value", ...)]
    5. 支持如下属性:
    6. column_separator: 列分隔符。<version since="1.2.0">支持多字节分隔符,如:"\\x01", "abc"</version>
    7. line_delimiter: 行分隔符。<version since="1.2.0">支持多字节分隔符,如:"\\x01", "abc"</version>
    8. max_file_size: 单个文件大小限制,如果结果超过这个值,将切割成多个文件。
    9. Broker 相关属性需加前缀 `broker.`:
    10. broker.name: broker名称
    11. broker.hadoop.security.authentication: 指定认证方式为 kerberos
    12. broker.kerberos_principal: 指定 kerberos 的 principal
    13. broker.kerberos_keytab: 指定 kerberos 的 keytab 文件路径。该文件必须为 Broker 进程所在服务器上的文件的绝对路径。并且可以被 Broker 进程访问
    14. HDFS 相关属性:
    15. fs.defaultFS: namenode 地址和端口
    16. hadoop.username: hdfs 用户名
    17. dfs.nameservices: name service名称,与hdfs-site.xml保持一致
    18. dfs.ha.namenodes.[nameservice ID]: namenode的id列表,与hdfs-site.xml保持一致
    19. dfs.namenode.rpc-address.[nameservice ID].[name node ID]: Name node的rpc地址,数量与namenode数量相同,与hdfs-site.xml保

    持一 dfs.client.failover.proxy.provider.[nameservice ID]: HDFS客户端连接活跃namenode的java类,通常是”org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider”

    1. 对于开启kerberos认证的Hadoop 集群,还需要额外设置如下 PROPERTIES 属性:
    2. dfs.namenode.kerberos.principal: HDFS namenode 服务的 principal 名称
    3. hadoop.security.authentication: 认证方式设置为 kerberos
    4. hadoop.kerberos.principal: 设置 Doris 连接 HDFS 时使用的 Kerberos 主体
    5. hadoop.kerberos.keytab: 设置 keytab 本地文件路径
    6. S3 协议则直接执行 S3 协议配置即可:
    7. AWS_ENDPOINT
    8. AWS_ACCESS_KEY
    9. AWS_SECRET_KEY
    10. AWS_REGION
    11. use_path_stype: (选填) 默认为false S3 SDK 默认使用 virtual-hosted style 方式。但某些对象存储系统可能没开启或不支持virtual-hosted style 方式的访问,此时可以添加 use_path_style 参数来强制使用 path style 访问方式。
    12. ```

example

  1. 使用 broker 方式导出,将简单查询结果导出到文件 hdfs://path/to/result.txt。指定导出格式为 CSV。使用 my_broker 并设置 kerberos 认证信息。指定列分隔符为 ,,行分隔符为 \n

    1. SELECT * FROM tbl
    2. INTO OUTFILE "hdfs://path/to/result_"
    3. FORMAT AS CSV
    4. PROPERTIES
    5. (
    6. "broker.name" = "my_broker",
    7. "broker.hadoop.security.authentication" = "kerberos",
    8. "broker.kerberos_principal" = "doris@YOUR.COM",
    9. "broker.kerberos_keytab" = "/home/doris/my.keytab",
    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. 将简单查询结果导出到文件 hdfs://path/to/result.parquet。指定导出格式为 PARQUET。使用 my_broker 并设置 kerberos 认证信息。

    1. SELECT c1, c2, c3 FROM tbl
    2. INTO OUTFILE "hdfs://path/to/result_"
    3. FORMAT AS PARQUET
    4. PROPERTIES
    5. (
    6. "broker.name" = "my_broker",
    7. "broker.hadoop.security.authentication" = "kerberos",
    8. "broker.kerberos_principal" = "doris@YOUR.COM",
    9. "broker.kerberos_keytab" = "/home/doris/my.keytab",
    10. "schema"="required,int32,c1;required,byte_array,c2;required,byte_array,c2"
    11. );

    查询结果导出到parquet文件需要明确指定schema

  3. 将 CTE 语句的查询结果导出到文件 hdfs://path/to/result.txt。默认导出格式为 CSV。使用 my_broker 并设置 hdfs 高可用信息。使用默认的行列分隔符。

    1. WITH
    2. x1 AS
    3. (SELECT k1, k2 FROM tbl1),
    4. x2 AS
    5. (SELECT k3 FROM tbl2)
    6. SELEC k1 FROM x1 UNION SELECT k3 FROM x2
    7. INTO OUTFILE "hdfs://path/to/result_"
    8. PROPERTIES
    9. (
    10. "broker.name" = "my_broker",
    11. "broker.username"="user",
    12. "broker.password"="passwd",
    13. "broker.dfs.nameservices" = "my_ha",
    14. "broker.dfs.ha.namenodes.my_ha" = "my_namenode1, my_namenode2",
    15. "broker.dfs.namenode.rpc-address.my_ha.my_namenode1" = "nn1_host:rpc_port",
    16. "broker.dfs.namenode.rpc-address.my_ha.my_namenode2" = "nn2_host:rpc_port",
    17. "broker.dfs.client.failover.proxy.provider" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
    18. );

    最终生成文件如如果不大于 1GB,则为:result_0.csv。 如果大于 1GB,则可能为 result_0.csv, result_1.csv, ...

  4. 将 UNION 语句的查询结果导出到文件 bos://bucket/result.txt。指定导出格式为 PARQUET。使用 my_broker 并设置 hdfs 高可用信息。PARQUET 格式无需指定列分割符。 导出完成后,生成一个标识文件。

    1. SELECT k1 FROM tbl1 UNION SELECT k2 FROM tbl1
    2. INTO OUTFILE "bos://bucket/result_"
    3. FORMAT AS PARQUET
    4. PROPERTIES
    5. (
    6. "broker.name" = "my_broker",
    7. "broker.bos_endpoint" = "http://bj.bcebos.com",
    8. "broker.bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
    9. "broker.bos_secret_accesskey" = "yyyyyyyyyyyyyyyyyyyyyyyyyy",
    10. "schema"="required,int32,k1;required,byte_array,k2"
    11. );
  5. 将 select 语句的查询结果导出到文件 s3a://${bucket_name}/path/result.txt。指定导出格式为 csv。 导出完成后,生成一个标识文件。

    1. select k1,k2,v1 from tbl1 limit 100000
    2. into outfile "s3a://my_bucket/export/my_file_"
    3. FORMAT AS CSV
    4. PROPERTIES
    5. (
    6. "broker.name" = "hdfs_broker",
    7. "broker.fs.s3a.access.key" = "xxx",
    8. "broker.fs.s3a.secret.key" = "xxxx",
    9. "broker.fs.s3a.endpoint" = "https://cos.xxxxxx.myqcloud.com/",
    10. "column_separator" = ",",
    11. "line_delimiter" = "\n",
    12. "max_file_size" = "1024MB",
    13. "success_file_name" = "SUCCESS"
    14. )

    最终生成文件如如果不大于 1GB,则为:my_file_0.csv。 如果大于 1GB,则可能为 my_file_0.csv, result_1.csv, ...。 在cos上验证

    1. 1. 不存在的path会自动创建
    2. 2. access.key/secret.key/endpoint需要和cos的同学确认。尤其是endpoint的值,不需要填写bucket_name
  6. 使用 s3 协议导出到 bos,并且并发导出开启。

    1. set enable_parallel_outfile = true;
    2. select k1 from tb1 limit 1000
    3. into outfile "s3://my_bucket/export/my_file_"
    4. format as csv
    5. properties
    6. (
    7. "AWS_ENDPOINT" = "http://s3.bd.bcebos.com",
    8. "AWS_ACCESS_KEY" = "xxxx",
    9. "AWS_SECRET_KEY" = "xxx",
    10. "AWS_REGION" = "bd"
    11. )

    最终生成的文件前缀为 my_file_{fragment_instance_id}_

  7. 使用 s3 协议导出到 bos,并且并发导出 session 变量开启。 注意:但由于查询语句带了一个顶层的排序节点,所以这个查询即使开启并发导出的 session 变量,也是无法并发导出的。

    1. set enable_parallel_outfile = true;
    2. select k1 from tb1 order by k1 limit 1000
    3. into outfile "s3://my_bucket/export/my_file_"
    4. format as csv
    5. properties
    6. (
    7. "AWS_ENDPOINT" = "http://s3.bd.bcebos.com",
    8. "AWS_ACCESS_KEY" = "xxxx",
    9. "AWS_SECRET_KEY" = "xxx",
    10. "AWS_REGION" = "bd"
    11. )
  8. 使用 hdfs 方式导出,将简单查询结果导出到文件 hdfs://${host}:${fileSystem_port}/path/to/result.txt。指定导出格式为 CSV,用户名为work。指定列分隔符为 ,,行分隔符为 \n

    1. -- fileSystem_port默认值为9000
    2. SELECT * FROM tbl
    3. INTO OUTFILE "hdfs://${host}:${fileSystem_port}/path/to/result_"
    4. FORMAT AS CSV
    5. PROPERTIES
    6. (
    7. "fs.defaultFS" = "hdfs://ip:port",
    8. "hadoop.username" = "work"
    9. );

    如果Hadoop 集群开启高可用并且启用 Kerberos 认证,可以参考如下SQL语句:

    1. SELECT * FROM tbl
    2. INTO OUTFILE "hdfs://path/to/result_"
    3. FORMAT AS CSV
    4. PROPERTIES
    5. (
    6. 'fs.defaultFS'='hdfs://hacluster/',
    7. 'dfs.nameservices'='hacluster',
    8. 'dfs.ha.namenodes.hacluster'='n1,n2',
    9. 'dfs.namenode.rpc-address.hacluster.n1'='192.168.0.1:8020',
    10. 'dfs.namenode.rpc-address.hacluster.n2'='192.168.0.2:8020',
    11. 'dfs.client.failover.proxy.provider.hacluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider',
    12. 'dfs.namenode.kerberos.principal'='hadoop/_HOST@REALM.COM'
    13. 'hadoop.security.authentication'='kerberos',
    14. 'hadoop.kerberos.principal'='doris_test@REALM.COM',
    15. 'hadoop.kerberos.keytab'='/path/to/doris_test.keytab'
    16. );

    最终生成文件如如果不大于 100MB,则为:result_0.csv。 如果大于 100MB,则可能为 result_0.csv, result_1.csv, ...

  9. 将 select 语句的查询结果导出到腾讯云cos的文件 cosn://${bucket_name}/path/result.txt。指定导出格式为 csv。 导出完成后,生成一个标识文件。

    1. select k1,k2,v1 from tbl1 limit 100000
    2. into outfile "cosn://my_bucket/export/my_file_"
    3. FORMAT AS CSV
    4. PROPERTIES
    5. (
    6. "broker.name" = "broker_name",
    7. "broker.fs.cosn.userinfo.secretId" = "xxx",
    8. "broker.fs.cosn.userinfo.secretKey" = "xxxx",
    9. "broker.fs.cosn.bucket.endpoint_suffix" = "https://cos.xxxxxx.myqcloud.com/",
    10. "column_separator" = ",",
    11. "line_delimiter" = "\n",
    12. "max_file_size" = "1024MB",
    13. "success_file_name" = "SUCCESS"
    14. )

keywords

  1. SELECT, INTO, OUTFILE

Best Practice

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

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

  2. 导出文件的管理

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

  3. 导出到本地文件

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

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

  4. 结果完整性保证

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