Export data using the OUTFILE statement

The SELECT INTO OUTFILE statement is a commonly used method for exporting data. It allows you to specify which fields to export, making it useful for scenarios where you don't need to export the primary key fields. When combined with the LOAD DATA INFILE statement for importing, it provides a convenient way to handle data import and export.

Background information

OceanBase Database supports this syntax in the following modes.

Privilege requirements

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

  code-placeholder-c31eb66cc-4457-4719-871e-09f261dc10ef

  Here, user_name is the user who needs to execute the SELECT INTO statement. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

• To execute the SELECT INTO statement in an Oracle tenant, you must have the SELECT privilege on the corresponding table. For more information about privileges in OceanBase Database, see Privilege types in Oracle 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 ...]

external_file_format_list:
  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',
    COMPRESSION = '<string>'
    ROW_GROUP_SIZE = '<string>' | <int>

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

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

file_name has the following format:

• When you save the exported file to an OBServer node, the format is: /\$PATH/\$FILENAME, where the parameters are described as follows:

  • \$PATH: specifies the path to save the exported file on the OBServer node.
  • \$FILENAME: specifies the name of the exported file. When SINGLE = FALSE, it indicates the prefix of the exported file. If not specified, the default prefix data is used, and the system generates the suffix automatically.

• When you save the exported file to an OSS bucket, the format is: oss://\$PATH/\$FILENAME/?host=\$HOST&access_id=\$ACCESS_ID&access_key=\$ACCESSKEY, where the parameters are described as follows:

  • \$PATH: specifies the path to save the exported file in the bucket.

  • \$FILENAME: specifies the name of the exported file. When SINGLE = FALSE, it indicates the prefix of the exported file. If not specified, the default prefix data is used, and the system generates the suffix automatically.

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

  • \$ACCESS_ID: specifies the Access Key ID required to access the OSS service for authentication.

  • \$ACCESSKEY: specifies the Access Key Secret required to access the OSS service for authentication.

    Note

    • For OceanBase Database V4.3.5, the SELECT INTO statement is supported for exporting data to S3 and object storage services that support the S3 protocol, starting from V4.3.5 BP2.
    • Aliyun OSS has a file size limit. Files larger than 5 GB are split into multiple files, each smaller than 5 GB, when exported to OSS.

field_term

• [OPTIONALLY] ENCLOSED BY string: specifies the character used to enclose field values. By default, no quotation marks are used. For example, ENCLOSED BY '"' indicates that character values are enclosed in double quotation marks. If the OPTIONALLY keyword is used, only string values are enclosed in the specified character.
• TERMINATED BY string: specifies the character used to separate field values. For example, TERMINATED BY ',' indicates that a comma is used to separate field values.
• ESCAPED BY string: specifies the escape character for handling special characters or parsing data in special formats. The default escape character is the backslash (\).

line_term

• TERMINATED BY string: specifies the ending character for each line. By default, the newline character is used. For example, ... LINES TERMINATED BY '\n' ... indicates that a newline character is used as the ending marker for a line.

file_option

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

  • SINGLE [=] TRUE: the default value, indicating that data can only be exported to a single file.

  • SINGLE [=] FALSE: indicating that data can be exported to multiple files.

    Notice

    When the parallelism is greater than 1 and SINGLE = FALSE, data can be exported to multiple files, achieving parallel read and write and improving the export speed.

• MAX_FILE_SIZE [=] {int | string}: specifies the size of a single file during export. This option is effective only when SINGLE = FALSE.

• BUFFER_SIZE [=] {int | string}: specifies the size of memory allocated to each thread for each partition during export (treated as a single partition if no partitioning is specified). The default value is 1 MB.

  Note

  • BUFFER_SIZE is used for export performance tuning. If the machine has sufficient memory and you want to improve the export efficiency, you can set a larger value (e.g., 4 MB). If the machine has insufficient memory, you can set a smaller value (e.g., 4 KB). If set to 0, all partitions in a single thread use a shared memory block.
  • For OceanBase Database V4.3.2, the BUFFER_SIZE parameter is supported starting from V4.3.2 BP1.

external_file_format_list

• TYPE = 'CSV' contains the following fields:

  • LINE_DELIMITER : specifies the row delimiter for the CSV file. The default is LINE_DELIMITER='\n' for a MySQL tenant and LINE_DELIMITER=chr(10) for an Oracle tenant.
  • FIELD_DELIMITER: Optional. The column delimiter for the CSV file. The default is FIELD_DELIMITER='\t'.
  • ESCAPE specifies the escape symbol in the CSV file, and it must be 1 byte. The default value is ESCAPE ='\'.
  • FIELD_OPTIONALLY_ENCLOSED_BY : Optional. The symbol that wraps the values of fields in a CSV file. The default value is an empty string. Use this option to wrap only the values of fields of some types (such as CHAR, VARCHAR, TEXT, and JSON).
  • FIELD_ENCLOSED_BY : optional. The character that encloses field values in the CSV file. The default value is empty, indicating that no enclosure is used. This option applies the enclosure character to all types of fields.
  • ENCODING specifies the character set encoding of the file. If you do not specify this option, the default value is UTF8MB4.
  • COMPRESSION: Optional. Specify the file compression format. Supported values: NONE, GZIP, DEFLATE, and ZSTD.
    • GZIP/DEFLATE: GZIP-compresses files.
    • ZSTD - Compressed ZSTD files.
    • NONE indicates the file is not compressed (default).
  • FILE_EXTENSION: Optional. Specifies a user-defined file extension, which is used only during multi-file export and is supported only for the CSV format. If this parameter is not specified, the file extension is determined based on the format type. The default file extension for the CSV format is .csv.

• TYPE = 'PARQUET' contains the following fields:

  • COMPRESSION: specifies the compression format of the Parquet file. The default value is UNCOMPRESSED, and the compressed file extension is .parquet. Valid values are UNCOMPRESSED, SNAPPY, GZIP, BROTLI, ZSTD, LZ4, and LZ4_HADOOP.
  • ROW_GROUP_SIZE: Specifies the size of the ROW GROUP in the PARQUET file in bytes. You can specify the size by writing a numeric value or by writing a string such as '64MB'. The default is 256MB if you do not specify a value. We recommend that you use the default value.

• TYPE = 'ORC' contains the following fields:

  • COMPRESSION: specifies the compression format of the ORC file, with the default compression suffix being .orc. The supported compression formats are UNCOMPRESSED (indicating no compression), SNAPPY, ZLIB, LZ4, and ZSTD. If not specified, the default value is UNCOMPRESSED.
  • COMPRESSION_BLOCK_SIZE: specifies the size of the data blocks that are compressed, in bytes. You can specify a numeric value or a string in the format '64KB'. The default value is 256KB. We recommend that you retain the default value.
  • STRIPE_SIZE: Specify the stripe size of the ORC file, in bytes. This option can be specified as a number or a string such as '64MB'. The default value is 64MB if this option is not specified. We recommend that you use the default value.
  • ROW_INDEX_STRIDE: A parameter that controls the frequency of index records, specifying how often to record an index. If not specified, it defaults to 10000. We recommend using the default value.

Considerations

• When multiple export tasks are exporting to the same path simultaneously, errors or partial data exports may occur. You can avoid this by setting the export path appropriately. For example:

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

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

  When multiple export tasks are executed simultaneously, errors may occur due to the same export file name. We recommend that you set the export paths to test/data1 and test/data2.

• When SINGLE = FALSE, if an export fails due to reasons such as file already exist, you can delete all files with the same prefix as the export target in the export directory, or delete the export directory and recreate it, and then retry the export. For example:

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

  After a 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 retry the export.

• The compression of files in PARQUET and ORC formats is performed at the data block level within the file, not at the file level. Therefore, the compression suffix is only applicable to CSV files and not to PARQUET or ORC files.

• When exporting compressed CSV files, the naming conventions for single-file and multi-file exports differ slightly:

  • For multi-file exports (SINGLE = FALSE), if a compression algorithm is specified, the corresponding compression suffix is appended to the file name. For example, if the export file name is data_1_0_1 and the compression algorithm is specified as gzip, the file name becomes data_1_0_1.gz.

  • For single-file exports (SINGLE = TRUE), the output file name is identical to the user-specified file name. If a compression suffix is specified, it is included in the output file name. If no compression suffix is specified, it is omitted.

• The logic for the FILE_EXTENSION field when exporting CSV files is as follows:

  • If both COMPRESSION and FILE_EXTENSION are specified, the suffix order is .file_extension.compression. For example, if the export file name is data_0_0_1, the FILE_EXTENSION is specified as xls, and the COMPRESSION is specified as gzip, the file name for multi-file export becomes data_0_0_1.xls.gz.

  • Regardless of whether the FILE_EXTENSION is specified with a leading . or not, the output file extension will contain only one .. For example, if the export file name is data, and the FILE_EXTENSION is specified as xls or .xls, the file name will be data.xls.

Example

Export data file to a local drive

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

Example 2: Export data as a PARQUET file using the SELECT INTO OUTFILE statement

Example 3: Export data to an ORC file by using the SELECT INTO OUTFILE statement

Export data files to OSS

Use the SELECT INTO OUTFILE statement to export data from the test_tbl2 table to the specified OSS storage location by partition. The partitioning is based on the combination of the col1 and col2 columns. Rows with the same values in these columns are grouped into 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 OSS host address, Access ID, and Access Key.

More information

You can import a file exported by using the SELECT INTO OUTFILE method by using the LOAD DATA statement. For more information, see Use the LOAD DATA statement to import data.