Export data using the OUTFILE statement

The SELECT INTO OUTFILE statement is a common method for exporting data. It allows you to specify the fields to be exported, which is useful in scenarios where you don't want to export the primary key fields. When used in conjunction with the LOAD DATA INFILE statement for importing data, it provides a convenient way to import and export data.

Background information

OceanBase Database is compatible with this syntax.

Privilege requirements

• To execute the SELECT INTO statement in a MySQL tenant, you must have the FILE privilege and the SELECT privilege on the table. You can use the following command to grant the FILE privilege to a user:

   GRANT FILE ON *.* TO user_name;
  

  In the statement, user_name is the username of the account that executes the SELECT INTO statement. For more information about privileges in OceanBase Database in MySQL mode, see Privilege types in MySQL-compatible mode.

• To execute the SELECT INTO statement in an Oracle tenant, you must have the SELECT privilege on the table. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.

Syntax

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>

Parameters

file_name

The file_name parameter has the following format:

• When the export file is stored on an OBServer node, specify the path and file name in the format of \$PATH/\$FILENAME. The parameters are described as follows:

  • $PATH: specifies the path to save the exported file, which is the path of the export file on the OBServer node.
  • \$FILENAME: the name of the file to be exported. When SINGLE = FALSE, it specifies the prefix of the exported file. If you do not specify this parameter, the system uses the default prefix data. The system automatically generates the suffix.

• When an exported file is saved to OSS, the file path is in the oss://\$PATH/\$FILENAME/?host=\$HOST&access_id=\$ACCESS_ID&access_key=\$ACCESSKEY format, where:

  • \$PATH: the path of the bucket to which the exported file is saved.

  • \$FILENAME: the name of the exported file.

  • \$HOST: the address of the OSS endpoint.

  • \$ACCESS_ID: the Access ID of the ApsaraDB for OSS account.

  • \$ACCESSKEY: the Access Key of the ApsaraDB for OSS account.

    Note

    • For OceanBase Database V4.3.5, SELECT INTO has been adapted to support S3 and S3 protocol-based object storage as the destination for data export starting from V4.3.5 BP2.
    • Due to file size limitations in Alibaba Cloud OSS, files larger than 5 GB will be split into multiple files, with each file being smaller than 5 GB when exported to OSS.

field_term

• [OPTIONALLY] ENCLOSED BY string: Specifies the symbol that encloses field values. By default, there is no enclosing symbol. For example, ENCLOSED BY '"' means that character values are enclosed in double quotes. If the OPTIONALLY keyword is used, the specified enclosing character is applied only to string-type values.
• TERMINATED BY string: Specifies the delimiter between field values. For example, TERMINATED BY ',' specifies a comma as the delimiter between two field values.
• ESCAPED BY string: Specifies the escape character for handling special characters or parsing specially formatted data. The default escape character is a backslash (\).

line_term

• TERMINATED BY string: Specifies the end character for each line. By default, a newline character is used. For example, ... LINES TERMINATED BY '\n' ... indicates that each line ends with a newline character.

file_option

• SINGLE [=] {TRUE | FALSE}: specifies whether to export data to a single file or multiple files.

  • SINGLE [=] TRUE: specifies to export data to a single file. This is the default value.

  • SINGLE [=] FALSE: specifies to export data to multiple files.

    Notice

    When the degree of parallelism is greater than 1 and SINGLE = FALSE, data can be exported to multiple files to achieve parallel reading, parallel writing, and improved export efficiency.

• MAX_FILE_SIZE [=] {int | string}: specifies the maximum size of a file when data is exported to multiple files. This parameter is effective only when SINGLE = FALSE.

• BUFFER_SIZE [=] {int | string}: specifies the size of memory applied for each thread for each partition when data is exported. This parameter is not effective for non-partitioned tables. The default value is 1 MB.

  Note

  • BUFFER_SIZE is used for export performance tuning. If sufficient memory is available on the server and you want to improve the export efficiency, you can set a large value (for example, 4 MB). If the memory is insufficient, you can set a small value (for example, 4 KB). If you set it to 0, all partitions of a thread share a public memory.
  • Starting from OceanBase Database V4.3.2 BP1, the BUFFER_SIZE parameter is supported.

external_file_format_list

• When TYPE = 'CSV', the fields included are as follows:

  • LINE_DELIMITER: specifies the line delimiter of the CSV file. The default value is '\n'.
  • FIELD_DELIMITER: specifies the field delimiter of the CSV file. The default value is '\t'.
  • ESCAPE: specifies the escape character of the CSV file. The value must be 1 byte in length. The default value is '\'.
  • FIELD_OPTIONALLY_ENCLOSED_BY: specifies the characters that enclose the field values in the CSV file. This parameter is optional. The default value is an empty string. This option allows you to add enclosing characters only to specific types of fields, such as CHAR, VARCHAR, TEXT, and JSON.
  • FIELD_ENCLOSED_BY: specifies the characters that enclose the field values in the CSV file. This parameter is optional. The default value is an empty string, which specifies not to add enclosing characters. This option adds enclosing characters to all types of fields.
  • ENCODING: specifies the character set encoding format of the file. If this parameter is not specified, the default value UTF8MB4 takes effect.
  • COMPRESSION: specifies the compression format of the file. This parameter is optional. The supported compression formats are NONE, GZIP, DEFLATE, and ZSTD.
    • GZIP/DEFLATE: specifies the GZIP compression format.
    • ZSTD: specifies the ZSTD compression format.
    • NONE: specifies that the file is not compressed. This is the default value.
  • FILE_EXTENSION: specifies the user-defined file extension. This parameter is optional. It is supported only for multi-file export and applies only to CSV files. If this parameter is not specified, the file extension is determined by the file format. The default file extension for CSV files is .csv.

• When TYPE = 'PARQUET', the following fields are included:

  • COMPRESSION: specifies the compression format of the PARQUET file. The default value is .parquet. The supported compression formats are UNCOMPRESSED (which specifies that the file is not compressed), SNAPPY, GZIP, BROTLI, ZSTD, LZ4, and LZ4_HADOOP. If this parameter is not specified, the default value UNCOMPRESSED takes effect.
  • COMPRESSION: specifies the compression format of the ORC file. The default value is .orc. The supported compression formats are UNCOMPRESSED (which specifies that the file is not compressed), SNAPPY, ZLIB, LZ4, and ZSTD. If this parameter is not specified, the default value UNCOMPRESSED takes effect.

• COMPRESSION_BLOCK_SIZE: specifies the size of the block into which the data is compressed, in bytes. You can specify a numeric value or a string in the format of '64KB'. If this parameter is not specified, the default value 256KB takes effect. We recommend that you use the default value.

  • STRIPE_SIZE: specifies the size of a stripe in the ORC file, in bytes. You can specify a numeric value or a string in the format of '64MB'. If this parameter is not specified, the default value 64MB takes effect. We recommend that you use the default value.

Considerations

• If multiple export tasks are exporting data to the same path at the same time, errors may occur and only a part of the data may be exported. You can avoid this issue by properly setting the export path. For example:

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

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

  If the export files have the same name, errors may occur. We recommend that you set the export path to test/data1 and test/data2.

• If SINGLE = FALSE and the export fails because the file already exists, you can delete all files in the export directory that have the same prefix as the export target, or delete the export directory and recreate it, and then re-export the data. For example

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

  After the failure, you can delete all files with the data prefix in the test directory, or directly delete the test directory and recreate it, and then try to export the data again.

• Compression applies to data blocks in Parquet and ORC files, not to the entire files. Therefore, only the CSV format supports compression suffixes.

• When you export a compressed CSV file, the file names are different whether you export single files or multiple files.

  • If you export multiple files (SINGLE = FALSE), and a compression algorithm is specified, use the suffix corresponding to the compression algorithm. The suffix is placed at the end of the file name. For example, if the file name of the exported file is data_1_0_1 and the specified compression algorithm is gzip, the file name is data_1_0_1.gz.

  • If you export a single file (SINGLE = TRUE), the output file name is the same as the specified file name. That is, if a compression suffix is specified, it is also included in the output file name. If no compression suffix is specified, the suffix is not output.

• The logical order of the FILE_EXTENSION field when you export a CSV file is as follows:

  • If you specify both the COMPRESSION and FILE_EXTENSION parameters, the suffix order is .file_extension.compression. For example, if the file name of the exported file is data_0_0_1, the FILE_EXTENSION parameter is set to xls, and the COMPRESSION parameter is set to gzip, the file name is data_0_0_1.xls.gz when multiple files are exported.

  • Regardless of whether the value of the FILE_EXTENSION parameter starts with a ., the output file name contains only one .. For example, if the file name of the exported file is data and the FILE_EXTENSION parameter is set to xls or .xls, the file name is data.xls.

Example

Export data to a local file

Example 1: Use the SELECT INTO OUTFILE statement to export a CSV file

Example 2: Use the SELECT INTO OUTFILE statement to export a PARQUET file

Example 3: Use the SELECT INTO OUTFILE statement to export an ORC file

Export data to OSS

You can execute the SELECT INTO OUTFILE statement to export data from the test_tbl2 table to a specified OSS storage location based on partitions. The partitions are determined by the combination of the col1 and col2 columns. Rows with the same values in these two columns are considered to be in the same partition and exported to the same directory.

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

The storage location is specified by the $DATA_FOLDER_NAME variable. You also need to provide the host address, access ID, and access key of the OSS service.

More information

A file exported by using the SELECT INTO OUTFILE method can be imported by using the LOAD DATA statement. For more information, see Import data by using the LOAD DATA statement.