LOAD DATA

Overview

This statement is used to import data from an external source. OceanBase Database provides two forms of the LOAD DATA statement, each with a different execution path and use case:

• LOAD DATA INFILE: This form is compatible with MySQL syntax and supports only CSV format. The file source can be specified using INFILE, LOCAL, or REMOTE_OSS to indicate whether the file is located on the server, client, or in Object Storage Service (OSS).
• LOAD DATA FROM: This form is recommended. It reads data from a URL using a URL table and supports multiple formats, including CSV, PARQUET, ORC, and ODPS. The execution path differs from the INFILE form and is better suited for batch import scenarios such as AP.

The syntax, parameters, and diagnostic methods differ significantly between the two forms. Please refer to the corresponding sections below based on the actual form you are using.

General considerations

• You cannot use the LOAD DATA statement for tables that have triggers.

• To import data from an external file, you must have the FILE privilege and the following settings:

  • When loading server-side files, you must set the system variable secure_file_priv to specify the path for importing or exporting files.
  • When loading client-side local files, you must add the --local-infile[=1] option when starting the MySQL/OBClient client to enable the feature of loading data from the local file system.

• When using partitioned table direct load, the target table cannot be a replica table, and it cannot contain auto-increment columns, identity columns, or global indexes.

• To improve import efficiency, we recommend that you create the base table first and then create indexes after the import is complete. If global indexes are involved, make sure to create them after the import is complete, otherwise, an error not support may occur.

1. LOAD DATA INFILE

This form is compatible with the LOAD DATA syntax of MySQL. It only supports CSV format. It can load files from the server (INFILE), client local files (LOCAL), or OSS files (REMOTE_OSS). The import process involves parsing the file, distributing the data, and inserting it.

Syntax of LOAD DATA INFILE

LOAD DATA
    [/*+ PARALLEL(N) [load_batch_size(M)] [APPEND | direct(bool, int, [load_mode])] | NO_DIRECT */]
    [REMOTE_OSS | LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE table_name [PARTITION(PARTITION_OPTION)]
    [CHARACTER SET 'charset_name']
    [COMPRESSION [=] {AUTO|NONE|GZIP|DEFLATE|ZSTD}]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(column_name_var
        [, column_name_var] ...)]
    [SET col_name = {expr | DEFAULT}
        [, col_name = {expr | DEFAULT}] ...]

load_mode:
    'full'
    | 'inc_replace'

PARTITION_OPTION:
    partition_option_list
    | subpartition_option_list

Support for Location objects in LOAD DATA INFILE and SELECT INTO OUTFILE

Overview

LOAD DATA INFILE and SELECT INTO OUTFILE support specifying the file path by using Location objects. If the AccessKey (AK) and SecretKey (SK) are not explicitly specified, the system automatically matches the Location object based on the longest path matching principle.

LOAD DATA INFILE

Automatic matching mechanism

If the import path is OSS and the AK and SK are not explicitly specified, the system automatically matches the path with the Location object that the current user has permissions for, and selects the corresponding AK and SK based on the longest path matching principle.

Syntax example

Specify the path by using the @location_name 'relative_path' format, where the part after @ is the name of the Location object, and the string is the subpath relative to the object:

LOAD DATA REMOTE_OSS INFILE @oss_t 'load_data_test.csv'
INTO TABLE load_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

In this example, @oss_t is the name of the Location object, and 'load_data_test.csv' is the subpath relative to the object.

SELECT INTO OUTFILE

Automatic matching mechanism

If the export path is OSS or HDFS and the AK and SK are not explicitly specified, the system automatically matches the path with the Location object that the current user has permissions for, and selects the corresponding AK and SK based on the longest path matching principle.

Syntax example

Specify the path by using the @location_name 'relative_path' format, where the part after @ is the name of the Location object, and the string is the subpath relative to the object:

SELECT *
INTO OUTFILE @oss_t 'select_into_test.csv'
FORMAT = (
    TYPE = 'CSV',
    FIELD_DELIMITER = ',',
    LINE_DELIMITER = '\n'
)
FROM export_table;

In this example, @oss_t is the name of the Location object, and 'select_into_test.csv' is the subpath relative to the object.

Parameters (applicable only to the INFILE form)

Supported format: This format only supports the CSV format.

Bypass mode and non-bypass mode: You can use the Hint to enable bypass import to improve performance.

Wildcard rules for multi-file direct load (INFILE only)

To facilitate multi-file imports, wildcard support has been introduced for both server-side and OSS file imports, but not for client-side imports.

• Server-side wildcard usage

  • Matching rules: File name matching: load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.*.csv' replace into table t1 fields terminated by '|';; directory matching is also supported, and it works similarly to file name matching.
  • Considerations: At least one matching file must exist; otherwise, an error code 4027 is returned. Only POSIX-compliant GLOB functions are supported.

• Wildcard usage in Alibaba Cloud Object Storage Service (OSS): File name matching: load data /*+ parallel(20) direct(true, 0) */ remote_oss infile 'oss://xxx/test.*.csv?host=xxx&access_id=xxx&access_key=xxx' replace into table t1 fields terminated by '|';. Directory matching is not supported. Only * and ? are supported as file name wildcards.

2. LOAD DATA FROM (Recommended)

This method reads data from a URL table and supports various formats such as CSV, PARQUET, ORC, and ODPS. The execution path is different from the INFILE method, making it suitable for batch import scenarios like AP. This is the currently recommended method. When executed, it is equivalent to INSERT INTO ... SELECT ...: LOAD DATA FROM FILES(...) INTO TABLE table_name is equivalent to INSERT INTO table_name SELECT * FROM FILES(...). The hint is the same as that of the INSERT statement.

LOAD DATA FROM syntax

LOAD DATA
    [/*+ insert_hint */]
    [REPLACE | IGNORE]
    FROM { url_table_function_expr |
         ( SELECT expression_list FROM url_table_function_expr ) }
    INTO TABLE table_name
    [PARTITION(PARTITION_OPTION)]
    [(column_name_var [, column_name_var] ...)]
    [LOG ERRORS
        [INTO 'logfile_string']
        [REJECT LIMIT {integer | UNLIMITED}]
        [BADFILE 'badfile_string']]

url_table_function_expr:

  FILES (
    LOCATION = '<string>',
    {
      FORMAT = (
        TYPE = 'CSV',
        LINE_DELIMITER = '<string>' | <expr>,
        FIELD_DELIMITER = '<string>' | <expr>,
        PARSE_HEADER = { TRUE | FALSE },
        ESCAPE = '<character>' | <expr>,
        FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
        ENCODING = 'charset',
        NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
        SKIP_HEADER = <int>,
        SKIP_BLANK_LINES = { TRUE | FALSE },
        TRIM_SPACE = { TRUE | FALSE },
        EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
      )
      | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
    },
    [PATTERN = '<regex_pattern>']
  )
  | SOURCE (
      TYPE = 'ODPS',
      ACCESSID = '<string>',
      ACCESSKEY = '<string>',
      ENDPOINT = '<string>',
      TUNNEL_ENDPOINT = '<string>',
      PROJECT_NAME = '<string>',
      SCHEMA_NAME = '<string>',
      TABLE_NAME = '<string>',
      QUOTA_NAME = '<string>',
      COMPRESSION_CODE = '<string>'
    )

PARTITION_OPTION:
    partition_option_list
    | subpartition_option_list

Parameters (only for LOAD DATA FROM form)

Supported formats: CSV, PARQUET, and ORC (by using FILES); and ODPS (by using SOURCE).

Bypass and non-bypass: You can enable bypass import by using a hint.

Diagnostics: Use LOG ERRORS, REJECT LIMIT, and BADFILE to record failed rows. Use READ_ERROR_LOG to view the error log. For more information, see log_errors.

FILES

The FILES keyword consists of the LOCATION, FORMAT, and PATTERN clauses.

• The LOCATION clause specifies the path where the external table files are stored. Typically, the data files of the external table are stored in a separate directory, which can contain subdirectories. When a table is created, the external table automatically collects all files in this directory.

  • For a local location, the format is LOCATION = '[file://] local_file_path', where local_file_path can be a relative or absolute path. If a relative path is specified, the current directory must be the installation directory of OceanBase Database. The secure_file_priv parameter specifies the file path that the OBServer node has permission to access. local_file_path must be a subpath of the secure_file_priv path.

  • For a remote location, the format is as follows:

    • LOCATION = '{oss|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path' where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required to access OSS and S3, and s3_region is the region information selected when using S3. These sensitive access information are stored in the system tables of the database in an encrypted manner.
    • LOCATION = 'hdfs://$ {hdfs_namenode_address}:${port}/PATH.localhost' where port is the port number of HDFS, and PATH is the directory path in HDFS.
      • For Kerberos authentication: LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx'. Where:
        • principal: the user for login authentication.
        • keytab: the path to the user's authentication key file.
        • krb5conf: the path to the Kerberos environment description file.
        • configs: additional HDFS configuration items. By default, it is empty. However, in a Kerberos environment, this configuration item usually has a value and needs to be configured, for example: dfs.data.transfer.protection=authentication,privacy, which specifies the data transmission protection level as authentication and privacy.

    Notice

    When using an object storage path, the parameters of the object storage path are separated by the & symbol. Ensure that the parameter values you enter contain only uppercase and lowercase letters, numbers, \/-_$+=, and wildcards. If you enter other characters, the settings may fail.

• The FORMAT clause specifies the properties related to the file reading format and supports three file formats: CSV, PARQUET, and ORC.

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

    • LINE_DELIMITER: specifies the line delimiter of the CSV file. The default value is LINE_DELIMITER='\n'.
    • FIELD_DELIMITER: optional. Specifies the column delimiter of the CSV file. The default value is FIELD_DELIMITER='\t'.
    • PARSE_HEADER: optional. Specifies whether the first line of the CSV file is the column name for each column. The default value is FALSE, indicating that the first line of the CSV file is not specified as the column name for each column.
    • ESCAPE: specifies the escape character of the CSV file. It must be one byte. The default value is ESCAPE ='\'.
    • FIELD_OPTIONALLY_ENCLOSED_BY: optional. Specifies the symbol used to wrap field values in the CSV file. The default value is an empty string. Using this option indicates that only certain types of fields (such as CHAR, VARCHAR, TEXT, and JSON) are wrapped.
    • ENCODING: specifies the character set encoding format of the file. If not specified, the default value is UTF8MB4.
    • NULL_IF: specifies the string that is treated as NULL. The default value is an empty string.
    • SKIP_HEADER: skips the file header and specifies the number of lines to skip.
    • SKIP_BLANK_LINES: specifies whether to skip blank lines. The default value is FALSE, indicating that blank lines are not skipped.
    • TRIM_SPACE: specifies whether to remove leading and trailing spaces from fields in the file. The default value is FALSE, indicating that leading and trailing spaces in fields are not removed.
    • TRIM_SPACE: specifies whether to remove leading and trailing spaces from fields in the file. The default value is FALSE, indicating that leading and trailing spaces in fields are not removed.
    • EMPTY_FIELD_AS_NULL: specifies whether to treat empty strings as NULL. The default value is FALSE, indicating that empty strings are not treated as NULL.

  • When TYPE = 'PARQUET/ORC', there are no additional fields.

• The PATTERN clause specifies a regular expression pattern to filter files in the LOCATION directory. For each file path in the LOCATION directory, if it matches the pattern, the external table accesses the file; otherwise, it skips the file. If this parameter is not specified, the external table can access all files in the LOCATION directory by default.

SOURCE

The SOURCE keyword does not include any other clauses. In this case, TYPE = 'ODPS' and the following fields are included:

• ACCESSID: specifies the ID of the ODPS user.
• ACCESSKEY: specifies the password of the ODPS user.
• ENDPOINT: specifies the connection address of the ODPS service.
• TUNNEL_ENDPOINT: specifies the connection address of the Tunnel data transmission service.
• PROJECT_NAME: specifies the project where the table to be queried is located.
• SCHEMA_NAME: optional. Specifies the schema of the table to be queried.
• TABLE_NAME: specifies the name of the table to be queried.
• QUOTA_NAME: optional. Specifies whether to use the specified quota.
• COMPRESSION_CODE: optional. Specifies the compression format of the data source. Supported compression formats include ZLIB, ZSTD, LZ4, and ODPS_LZ4. If not specified, compression is not enabled.

log_errors

• LOG ERRORS: Enables error diagnostics during the import process, allowing failed rows to be recorded instead of terminating the entire operation at the first error. When used with the REJECT LIMIT clause, it controls the maximum number of rows that can be rejected.

• INTO 'logfile_string': Optional. Specifies the file in the target directory where error information will be written. If INTO 'logfile_string' is not specified, error information is only recorded in the warning buffer, which can be viewed using show warnings. logfile_string specifies the directory for storing error information, with the following format:

  Note

  The INTO 'logfile_string' parameter is supported starting from V4.4.0.

  • When error information is stored locally, logfile_string is in the format [file://] local_file_path, where local_file_path can be either a relative or absolute path. If a relative path is specified, the current directory must be the installation directory of OceanBase Database. secure_file_priv specifies the file paths that OBServer nodes have permission to access. local_file_path must be a subpath of secure_file_priv.

  • When error information is stored remotely (refer to the Location section in the syntax for creating external tables), logfile_string is in the following format:

    • {oss\|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path, where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access credentials required for accessing Alibaba Cloud OSS, AWS S3, and object storage compatible with the S3 protocol, respectively. s3_region specifies the region selected when using S3. These sensitive access credentials are stored in the system tables of the database in an encrypted manner.
    • hdfs://localhost:port/PATH, where localhost is the address of HDFS, port is the port number of HDFS, and PATH is the directory path in HDFS. For Kerberos authentication, the address is: hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx.

  OceanBase Database allows you to set tenant-level parameters to configure the compression algorithm and maximum size of a single diagnostic log file. For more information, see load_data_diagnosis_log_compression and load_data_diagnosis_log_max_size.

• REJECT LIMIT: Optional. Specifies the maximum number of rows that can be rejected:

  • Default value: 0. No rows can be rejected. The operation fails at the first error.
  • integer: The maximum number of rows that can be rejected on a single server. For example, 10 means that up to 10 rows can be rejected on a single server.
  • UNLIMITED: No limit on the number of rejected rows.

• BADFILE 'badfile_string': Specifies the path where error data files are stored. The format of badfile_string is the same as that of logfile_string.

  Note

  The BADFILE 'badfile_string' parameter is supported starting from V4.4.0.