跳到主要内容

SELECT INTO

描述​

该语句用于将查询结果存储在变量或文件中。其中：

  • SELECT ... INTO OUTFILE 用于将结果集存储在外部文件，并可以指定输出格式。

    信息

    SELECT ... INTO OUTFILE 输出数据时支持使用 \N 代表 NULL。

  • SELECT ... INTO DUMPFILE 用于向外部文件写入不带格式的单行。

  • SELECT ... INTO var_list 用于将结果集存储到变量。

权限要求​

执行 SELECT INTO 语句，需要拥有 FILE 权限和对应表的 SELECT 权限。有关 seekdb 权限的详细介绍，参见 seekdb 的权限分类。

示例如下：

要为用户授予 FILE 权限，可以使用以下命令格式：

GRANT FILE ON *.* TO user_name;

其中，user_name 是需要执行 SELECT INTO 命令的用户。

语法​

select_stmt
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};

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>

参数解释​

参数描述
select_stmt指定要执行的查询语句。select_stmt 必须返回一个结果集，查询语句的具体结构和选项信息，参见 SELECT。
说明INTO 子句内容的位置也可以在查询语句的 FROM 之前。例如，SELECT * INTO OUTFILE '/home/admin/test_data/test_tbl1.csv' FROM test_tbl1 WHERE id > 5;。
file_name用于指定导出文件的路径和文件名。详细介绍可参见下文 file_name。
PARTITION BY part_expr
说明 对于 seekdb 支持控制导出数据的分区方式。可选项，用于控制导出数据的分区方式，part_expr 的值作为导出路径的一部分，对每行数据计算 part_expr 的值，part_expr 取值相同的行属于同一个分区，将导出到同一个目录中。
注意
  • 当数据按分区导出数据时，要求 SINGLE = FALSE，即允许导出多文件。
  • 当前按分区导出数据仅支持导入到 OSS 上。
FORMAT = (external_file_format_list)可选项，FORMAT 子句用于指定导出文件格式相关的属性，使用 TYPE 指定导出 CSV、PARQUET、ORC 三种文件格式，TYPE 不能为空。详细介绍可参见下文 external_file_format_list。
CHARSET | CHARACTER SET charset_name可选项，指定导出到外部文件的字符集。charset_name 表示字符集的名称。
field_opt可选项，导出字段格式选项。指定输出文件中各个字段的格式，通过 FIELDS 或 COLUMNS 子句来指定。详细介绍可参见下文 field_term。
line_opt可选项，导出数据行的开始和结束符选项。指定输出文件中每一行的开始和结束字符，通过 LINES 子句设置。详细介绍可参见下文 line_term。
file_opt可选项，控制是否导出到多个文件和导出到多文件时单个文件的大小。详细介绍可参见下文 file_option。

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，用于身份验证。
    信息
    • seekdb 中 SELECT INTO 适配 S3 和基于 S3 协议的对象存储作为数据导出的目的端。
    • 由于阿里云 OSS 有文件大小的限制，对于超过 5 GB 的文件，导出到 OSS 时会被拆分成多个文件，每个文件小于 5 GB。

field_term​

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

line_term​

  • STARTING BY string：指定每一行的起始字符。
  • 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 时，表示单个线程中所有分区都使用一块公共内存）。
    • seekdb 支持 BUFFER_SIZE 参数。

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。推荐使用默认值。

示例​

导出数据文件到本地​

  1. 设置导出的文件路径。

    要导出文件，需要先设置系统变量 secure_file_priv，配置导出文件可以访问的路径。

    提示

    由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Unix Socket 连接数据库执行修改该全局变量的 SQL 语句。更多信息，参见 secure_file_priv。

    1. 登录到要连接 seekdb 的节点。

      ssh admin@xxx.xxx.xxx.xxx

    2. 执行以下命令，通过本地 Unix Socket 连接方式连接租户 mysql001。

      mysql -S /home/admin/oceanbase/run/sql.sock -uroot -p******

    3. 设置导出路径为 /home/admin/test_data。

      SET GLOBAL secure_file_priv = "/home/admin/test_data";

    4. 退出登录。

  2. 重新连接数据库后，使用 SELECT INTO OUTFILE 语句导出数据。指定逗号作为两个字段值之间的标志；对字符串类型的值使用 " 字符包裹；使用换行符作为结束标志。

    • 串行写单个文件，指定文件名为 test_tbl1.csv。

      SELECT /*+parallel(2)*/ * FROM test_tbl1
      INTO OUTFILE '/home/admin/test_data/test_tbl1.csv'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n';

      返回结果如下：

      Query OK, 9 rows affected (0.087 sec)

    • 并行写多个文件，不指定文件名（即文件名使用默认前缀 data），并且每个文件的大小不超过 4MB。

      SELECT /*+parallel(2)*/ * FROM test_tbl1
        INTO OUTFILE '/home/admin/test_data/'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        SINGLE = FALSE MAX_FILE_SIZE = '4MB';

      返回结果如下：

      Query OK, 9 rows affected (0.093 sec)

    • 并行写多个文件，指定文件名的前缀为 dd2024，并且每个文件的大小不超过 4MB。

      SELECT /*+parallel(2)*/ * FROM test_tbl1
        INTO OUTFILE '/home/admin/test_data/dd2024'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        SINGLE = FALSE MAX_FILE_SIZE = '4MB';

      返回结果如下：

      Query OK, 9 rows affected
    信息
    • 当多个导出任务同时导出到相同路径时，可能出现报错、只导出一部分数据等问题。可以通过合理设置导出路径规避。例如：SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; 和 SELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE; 同时执行时可能由于导出文件名相同而报错，建议将导出路径设置为 test/data1 和 test/data2。
    • 当 SINGLE = FALSE，且导出因为 file already exist 等原因失败后，可以清除导出目录下所有与导出目标具有相同前缀的文件，或者删除导出目录再重建，然后再次执行导出操作。例如：SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; 失败后，可以删除 test 目录下所有 data 前缀的文件，或者直接删除 test 目录再重建，然后再次尝试执行导出操作。

  3. 登录机器，在 seekdb 节点的 /home/admin/test_data 目录下查看导出的文件信息。

ls

返回结果如下：

data_0_0_0  data_0_1_0  dd2024_0_0_0  dd2024_0_1_0  test_tbl1.csv

其中，test_tbl1.csv 是串行写单个文件示例导出的文件名；data_0_0_0 和 data_0_1_0 是并行写多个文件，不指定文件名示例导出的文件名；dd2024_0_0_0 和 dd2024_0_1_0 是并行写多个文件，指定文件名的前缀为 dd2024 示例导出的文件名。

导出数据文件到 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 和访问密钥。

相关文档​

使用 OUTFILE 语句导出数据

Contents

编辑此页