使用 OUTFILE 语句导出数据

SELECT INTO OUTFILE 语句常用的一种数据导出方式。 SELECT INTO OUTFILE 语句能够对需要导出的字段做出限制，这很好的满足了某些不需要导出主键字段的场景。配合 LOAD DATA INFILE 语句导入数据，是一种很便利的数据导入导出方式。

背景信息

OceanBase 数据库兼容这一个语法。

权限要求

• 在 MySQL 租户下执行 SELECT INTO 语句，需要拥有 FILE 权限和对应表的 SELECT 权限。如果需要为用户授予 FILE 权限，可以使用以下命令格式：

   GRANT FILE ON *.* TO user_name;
  

  其中，user_name 是需要执行 SELECT INTO 命令的用户。有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

• 在 Oracle 租户下执行 SELECT INTO 语句，需要拥有对应表的 SELECT 权限。有关 OceanBase 数据库权限的详细介绍，参见 Oracle 模式下的权限分类。

语法

SELECT [/*+parallel(N)*/] column_list_option 
INTO {OUTFILE 'file_name' [PARTITION BY part_expr] [{CHARSET | CHARACTER SET} charset_name] [field_opt] [line_opt] [file_opt]
     | OUTFILE 'file_name' [PARTITION BY part_expr] [FORMAT = (external_file_format_list)] [file_opt]
     | DUMPFILE 'file_name'
     | into_var_list}
FROM table_name_list
[WHERE where_conditions]
[GROUP BY group_by_list [HAVING having_search_conditions]]
[ORDER BY order_expression_list];

column_list_option:
    column_name [, column_name ...]

field_opt:
    {COLUMNS | FIELDS} field_term_list

field_term_list:
  field_term [, field_term ...]

field_term:
    {[OPTIONALLY] ENCLOSED | TERMINATED | ESCAPED} BY string

line_opt:
    LINES line_term_list

line_term_list:
    line_term [, line_term ...]

line_term:
    {STARTING | TERMINATED} BY string

file_opt:
    file_option [, file_option ...]

file_option:
    SINGLE [=] {TRUE | FALSE}
    | MAX_FILE_SIZE [=] {int | string}
    | BUFFER_SIZE [=] {int | string}

external_file_format_list:
    type_csv_option
    | type_parquet_option
    | type_orc_option

type_csv_option:
    TYPE = 'CSV'
    LINE_DELIMITER = '<string>' | <expr>
    FIELD_DELIMITER = '<string>' | <expr>
    ESCAPE = '<character>' | <expr>
    FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
    FIELD_ENCLOSED_BY = '<character>' | <expr>
    ENCODING = 'charset'
    COMPRESSION = [NONE, GZIP, DEFLATE, ZSTD] | '<string>'
    FILE_EXTENSION = ['<string>']

type_parquet_option:
    TYPE = 'PARQUET',
    COMPRESSION = '<string>'
    ROW_GROUP_SIZE = '<string>' | <int>

type_orc_option:
    TYPE = 'ORC'
    COMPRESSION = '<string>'
    COMPRESSION_BLOCK_SIZE = '<string>' | <int>
    STRIPE_SIZE = '<string>' | <int>
    ROW_INDEX_STRIDE = <int>

参数解释

file_name

file_name 有以下格式：

• 将导出文件保存在 OBServer 节点时，格式为：/\$PATH/\$FILENAME，其中各参数解释如下：

  • \$PATH：指定要保存导出文件的路径，即指定导出文件在 OBServer 节点的路径。
  • \$FILENAME：指定要导出文件的名称。当 SINGLE = FALSE 时表示导出文件的前缀，不指定时会生成默认的前缀 data，系统自动生成后缀。

• 将导出文件保存在 OSS 上时，格式为：oss://\$PATH/\$FILENAME/?host=\$HOST&access_id=\$ACCESS_ID&access_key=\$ACCESSKEY，其中各参数解释如下：

  • \$PATH：指定要保存导出文件的路径，即指定存储桶中的文件路径。

  • \$FILENAME：指定要导出文件的名称。当 SINGLE = FALSE 时表示导出文件的前缀，不指定时会生成默认的前缀 data，系统自动生成后缀。

  • \$HOST：指定 OSS 服务的主机名或 CDN 加速的域名，即要访问的 OSS 服务的地址。

  • \$ACCESS_ID：指定访问 OSS 服务所需的 Access Key ID，用于身份验证。

  • \$ACCESSKEY：指定了访问 OSS 服务所需的 Access Key Secret，用于身份验证。

    说明

    • OceanBase 数据库适配 S3 和基于 S3 协议的对象存储作为数据导出的目的端。
    • 由于阿里云 OSS 有文件大小的限制，对于超过 5 GB 的文件，导出到 OSS 时会被拆分成多个文件，每个文件小于 5 GB。

• 将导出文件保存在 HDFS 上时，格式为：

  注意

  使用 HDFS 外表写能力时候，需要 OceanBase 数据库在具有 JAVA SDK 的环境当中使用。关于部署 JAVA SDK 环境的详细介绍信息，参见 部署 OceanBase 数据库 JAVA SDK 环境。

  • SIMPLE 认证的导出格式：

    • 基础认证（无 Kerberos）：

      hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME[?username=\$USERNAME]
      

    • 说明：

      • 当使用基础认证（SIMPLE）时，可通过 username 参数指定 HDFS 用户名。
      • 若未指定 username 参数时，默认使用 OceanBase 数据库配置中定义的 HDFS 用户名。

  • KERBEROS 认证的导出格式：

    hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME?principal=\$PRINCIPAL&keytab=\$KEYTAB&krb5conf=\$KRB5CONF&configs=\$CONFIGS
    

    说明：

    • Kerberos 认证需提供 Kerberos 主体（principal）、密钥表（keytab）和 Kerberos 配置文件（krb5conf）。
    • username 参数在此场景下不适用，因为 Kerberos 认证通过 principal 标识身份。

  其中各参数解释如下：

  • 通用参数：

    • \$NAMENODE：表示 HDFS NameNode 的 IP 地址。
    • \$PORT：表示 HDFS NameNode 的端口。
    • \$PATH：表示文件在 HDFS 中的存储路径。
    • \$FILENAME：表示导出文件的名称。

  • SIMPLE 认证专用参数：

    • ?username=\$USERNAME：可选项，表示 HDFS 用户名。未指定 username 时，默认使用 OceanBase 数据库配置中定义的 HDFS 用户名。

  • KERBEROS 认证专用参数：

    • \$PRINCIPAL：表示 Kerberos 主体名称。例如 principal=hdfs/hadoop@EXAMPLE.COM。
    • \$KEYTAB：表示 Kerberos 密钥表文件的路径。例如 keytab=/path/to/hdfs.keytab。
    • \$KRB5CONF：表示 Kerberos 配置文件路径。例如 krb5conf=/path/to/krb5.conf。
    • \$CONFIGS：表示 HDFS 额外配置参数，格式为键值对列表。例如 configs=dfs.data.transfer.protection=authentication,privacy。

  导出文件保存在 HDFS 上 file_name 取值示例如下：

  • SIMPLE 认证（基础认证）：

    • 适用于无 Kerberos 认证的 HDFS 集群。

    • 示例 1（默认用户名）：

      hdfs://10.10.10.1:9000/data/export/sales_data.csv
      

    • 示例 2（指定用户名）：

      hdfs://10.10.10.1:9000/data/export/sales_data.csv?username=hiveuser
      

  • KERBEROS 认证：

    • 适用于需要 Kerberos 认证的 HDFS 集群。

    • 查询参数需按需填写，支持多个配置项（如 configs）。

    • 示例：

      hdfs://10.10.10.1:9000/data/export/sales_data.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/etc/security/keytabs/hdfs.keytab&krb5conf=/etc/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy
      

field_term

• [OPTIONALLY] ENCLOSED BY string：用来指定包裹字段值的符号，默认没有引用符号。例如，ENCLOSED BY '"' 表示字符值放在双引号之间。如果使用了 OPTIONALLY 关键字，则仅对字符串类型的值使用指定字符包裹。
• TERMINATED BY string：用来指定字段值之间的符号。例如，TERMINATED BY ',' 指定了逗号作为两个字段值之间的标志。
• ESCAPED BY string：用来指定转义字符，以便处理特殊字符或解析特殊格式的数据。默认的转义字符是反斜杠（\）。

line_term

• TERMINATED BY string：指定每一行的结束字符，默认使用换行符。例如，... LINES TERMINATED BY '\n' ... 表示一行将以换行符作为结束标志。

file_option

• SINGLE [=] {TRUE | FALSE}：用于控制将数据导出到单个文件或多个文件。

  • SINGLE [=] TRUE：默认值，表示只能导出到单个文件。

  • SINGLE [=] FALSE：表示可以导出到多个文件。

    注意

    当并行度大于 1 且 SINGLE = FALSE 时，可以导出到多个文件，达到并行读并行写和提高导出速度的效果。

• MAX_FILE_SIZE [=] {int | string}：用于控制导出时单个文件的大小，仅在 SINGLE = FALSE 时生效。

• BUFFER_SIZE [=] {int | string}：用于控制导出时每个线程为每个分区专门申请的内存大小（不分区可视为单个分区），默认取值为 1 MB。

  说明

  BUFFER_SIZE 用于导出性能调优，当机器内存充足且希望提高导出效率时，可设置一个较大的值（例如 4 MB），当机器内存不足时，可设置一个较小的值（例如 4 KB。设置为 0 时，表示单个线程中所有分区都使用一块公共内存）。

external_file_format_list

• TYPE = 'CSV' 时，包含以下字段：

  • LINE_DELIMITER：指定 CSV 文件的行分隔符，默认为 LINE_DELIMITER='\n'。
  • FIELD_DELIMITER：可选项，指定 CSV 文件的列分隔符，默认为 FIELD_DELIMITER='\t'。
  • ESCAPE：指定 CSV 文件的转义符号，只能为 1 个字节，默认为 ESCAPE ='\'。
  • FIELD_OPTIONALLY_ENCLOSED_BY：可选项，指定 CSV 文件中包裹字段值的符号，默认为空。使用此选项表示只对部分类型的字段（如 CHAR, VARCHAR, TEXT, JSON 等）加包裹符。
  • FIELD_ENCLOSED_BY：可选项，指定 CSV 文件中包裹字段值的符号，默认为空，表示不加包裹符。此选项会对所有类型的字段都应用包裹符的设置。
  • ENCODING：指定文件的字符集编码格式，如果不指定，默认值为 UTF8MB4。
  • COMPRESSION：可选项，指定文件的压缩格式，支持 NONE、GZIP、DEFLATE、ZSTD 四种压缩格式。
    • GZIP/DEFLATE：GZIP 压缩文件。
    • ZSTD：ZSTD 压缩文件。
    • NONE：表示文件没有压缩（默认值）。
  • FILE_EXTENSION：可选项，指定用户自定义的文件扩展名，仅多文件导出时使用，仅 CSV 格式支持指定。不指定该参数时，文件扩展名由格式类型决定。CSV 格式默认文件扩展名为 .csv。

• TYPE = 'PARQUET' 时，包含以下字段：

  • COMPRESSION：指定 PARQUET 文件的压缩格式，默认压缩后缀名为 .parquet，支持 UNCOMPRESSED（表示文件没有压缩）、SNAPPY、GZIP、BROTLI、ZSTD、LZ4、LZ4_HADOOP 七种压缩格式。不指定时默认为 UNCOMPRESSED。
  • ROW_GROUP_SIZE：指定 PARQUET 文件的 ROW GROUP 大小，单位为字节。该选项可以写数字，或者写形如 '64MB' 的字符串，不指定时默认为 256MB。推荐使用默认值。

• TYPE = 'ORC' 时，包含以下字段：

  • COMPRESSION：指定 ORC 文件的压缩格式，默认压缩后缀名为 .orc，支持 UNCOMPRESSED（表示文件没有压缩）、SNAPPY、ZLIB、LZ4、ZSTD 五种压缩格式。不指定时默认为 UNCOMPRESSED。
  • COMPRESSION_BLOCK_SIZE：指定数据在压缩时被分割成的块大小，单位为字节。该选项可以写数字，或者写形如 '64KB' 的字符串，不指定时默认为 256KB。推荐使用默认值。
  • STRIPE_SIZE：指定 ORC 文件的 Stripe 大小，单位为字节。该选项可以写数字，或者写形如 '64MB' 的字符串，不指定时默认为 64MB。推荐使用默认值。
  • ROW_INDEX_STRIDE：控制索引记录的频率的参数，定义了每隔多少行记录一次索引，不指定时默认为 10000。推荐使用默认值。

注意事项

• 当多个导出任务同时导出到相同路径时，可能出现报错、只导出一部分数据等问题。可以通过合理设置导出路径规避，例如

  SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1;
  

  SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t2;
  

  同时执行时可能由于导出文件名相同而报错，建议将导出路径设置为 test/data1 和 test/data2。

• 当 SINGLE = FALSE，且导出因为 file already exist 等原因失败后，可以清除导出目录下所有与导出目标具有相同前缀的文件，或者删除导出目录再重建，然后再次执行导出操作。例如

  SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1;
  

  失败后，可以删除 test 目录下所有 data 前缀的文件，或者直接删除 test 目录再重建，然后再次尝试执行导出操作。

• PARQUET、ORC 格式的压缩是文件内部数据块级别的压缩，并不是指整个文件是压缩格式。故压缩后缀名仅适用于 CSV 格式，不适用于 PARQUET、ORC 格式。

• 在导出 CSV 格式的压缩文件时，单文件和多文件的命名方式略有不同：

  • 对于导出多文件(SINGLE = FALSE)，当指定了压缩算法，则使用压缩算法对应的后缀名，压缩后缀名位于文件名最后。例如，对于导出文件名为 data_1_0_1 的文件，如果指定压缩算法 gzip 后，则文件名为 data_1_0_1.gz。

  • 对于导出单文件(SINGLE = TRUE)，输出文件名与用户指定文件名完全一致。即如果指定了压缩后缀名，输出文件名也包含压缩后缀名，如果没有指定压缩后缀名，则不会输出压缩后缀名。

• CSV 格式文件导出时 FILE_EXTENSION 字段的逻辑顺序：

  • 同时指定 COMPRESSION 和 FILE_EXTENSION时，后缀顺序为 .file_extension.compression。例如，对于导出文件名为 data_0_0_1 的文件，指定 FILE_EXTENSION 为 xls，指定 COMPRESSION 为 gzip，则导出多文件时文件名为 data_0_0_1.xls.gz。

  • 无论设置的 FILE_EXTENSION 是否以 . 开始，输出的文件扩展名有且仅包含一个 .。例如，对于导出文件名为 data 的文件，指定 FILE_EXTENSION 为 xls 或 .xls 时，文件名均为 data.xls。

示例

导出数据文件到本地

示例 1：使用 SELECT INTO OUTFILE 语句导出 CSV 文件

示例 2：使用 SELECT INTO OUTFILE 语句导出 PARQUET 文件

示例 3：使用 SELECT INTO OUTFILE 语句导出 ORC 文件

导出数据文件到 OSS

使用 SELECT INTO OUTFILE 语句从 test_tbl2 表中按分区导出数据到指定的 OSS 存储位置。分区依据是 col1 和 col2 列的组合，相同的行属于同一个分区，将导出到同一个目录中。

SELECT /*+parallel(3)*/ * FROM test_tbl2
  INTO OUTFILE 'oss://$DATA_FOLDER_NAME/?host=$OSS_HOST&access_id=$OSS_ACCESS_ID&access_key=$OSS_ACCESS_KEY'
    PARTITION BY CONCAT(col1,'/',col2)
    SINGLE = FALSE BUFFER_SIZE = '2MB';

存储位置由 $DATA_FOLDER_NAME 变量指定，同时需要提供 OSS 的主机地址、访问 ID 和访问密钥。

导出数据文件到 HDFS

SELECT * FROM lineitem_external
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv' 
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE;

SELECT * FROM lineitem_external
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/path/to/hdfs.keytab&krb5conf=/path/to/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy' 
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE;

SELECT *
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv' 
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE
FROM lineitem_external;

SELECT *
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/path/to/hdfs.keytab&krb5conf=/path/to/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy' 
FORMAT = (
    TYPE = 'CSV'
    LINE_DELIMITER = '\n'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    )
SINGLE = TRUE
FROM lineitem_external;

更多信息

通过 SELECT INTO OUTFILE 方法导出的文件，可以通过 LOAD DATA 语句进行导入，详细方法，请参考 使用 LOAD DATA 导入数据。