LOAD DATA|V4.4.2|OceanBase Database| docs|Distributed Database

LOAD DATA

Last Updated：2026-07-17 03:39:41 Updated

OceanBase Database supports the LOAD DATA statement for loading the following types of input files:
- Server-side files (on OBServer nodes): These files are stored on the OBServer nodes of OceanBase Database. You can use the LOAD DATA INFILE or LOAD DATA FROM URL statement to load data from these server-side files into a database table.
- Client-side files (local files): These files are stored on the local file system of the client. You can use the LOAD DATA LOCAL INFILE or LOAD DATA FROM URL statement to load data from these client-side files into a database table.
  
  Note
  
  When you execute the LOAD DATA LOCAL INFILE statement, the system automatically adds the IGNORE option.
- OSS files: These files are stored in the OSS file system. You can use the LOAD DATA REMOTE_OSS INFILE statement to load data from these OSS files into a database table.
The LOAD DATA statement can currently import data from CSV-formatted text files. The entire import process can be divided into the following steps:
1. File parsing: OceanBase Database reads the data from the specified file and parses it in parallel or sequentially based on the specified parallelism.
2. Data distribution: Since OceanBase Database is a distributed database, data in different partitions may be stored on different OBServer nodes. The LOAD DATA statement calculates the parsed data and determines which OBServer node to send the data to.
3. Data insertion: After the target OBServer node receives the data, it executes the INSERT statement to insert the data into the corresponding partition.

Considerations

You cannot use the LOAD DATA statement for a table that contains triggers.
To import data from an external file, you must have the FILE privilege and the following settings:
- When you load a server-side file, you must set the system variable secure_file_priv to specify the path for importing or exporting files.
- When you load a client-side local file, you must add the --local-infile[=1] option when you start the MySQL/OBClient client to enable the data loading feature from the local file system.
When you use partitioned table bypass import, the target table cannot be a replicated table, and cannot contain auto-increment columns, identity columns, or global indexes.

To import data from an external file, you must have the FILE privilege. You can use the GRANT FILE ON *.* TO $user_name; statement to grant this privilege, where $user_name is the user who executes the LOAD DATA command.

Syntax

-- Import a regular file
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)]
    [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] ...)]

load_mode:
    'full'
    | 'inc_replace'

PARTITION_OPTION:
    partition_option_list
    | subpartition_option_list

-- Import URL File
LOAD DATA
    [/*+ PARALLEL(N) [load_batch_size(M)] [APPEND | direct(bool, int, [load_mode])] | NO_DIRECT */]
    [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']]

load_mode:
    'full'
    | 'inc_replace'

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
|                Parameter               |        Description                     |
|----------------------------------|---------------------------------|
| parallel(N)                      | The degree of parallelism for data loading. Default value: 4.    |
| load_batch_size(M)               | The size of the batch for each insert. The default value of `M` is `100`. We recommend that you set the value to a number in the range [100, 1000].  |
| APPEND \| direct() \|NO_DIRECT   | Specifies the hint to enable direct load. <main id="notice" type='notice'><h4>Notice</h4><p>Do not upgrade OceanBase Database while a direct load task is in progress, because the upgrade may cause the direct load task to fail. </p></main> <ul><li>`APPEND` is equivalent to `direct(true, 0)` by default. It also enables the `GATHER_OPTIMIZER_STATISTICS` hint for online statistics collection. </li><li>`direct()` specifies the direct load mode. The parameters of `direct()` are described as follows: <ul><li><code>bool</code>: specifies whether the data to be written needs to be sorted. <code>true</code> indicates that the data needs to be sorted. <code>false</code> indicates that the data does not need to be sorted. <main id="notice" type='explain'><h4>Note</h4><p>When you import data from an ordered file or from backup data (which is sorted by the primary key), you can set <code>need_sort</code> to <code>false</code> to skip the sorting process. If the data is not sorted by the primary key and the <code>need_sort</code> parameter is set to <code>true</code>, an error is returned. </p></main></li><li><code>int</code>: specifies the maximum number of error rows that can be tolerated. </li><li><code>load_mode</code>: an optional parameter that specifies the direct load mode. The value of this parameter must be enclosed in single quotation marks. Valid values: <ul><li><code>full</code>: the default value. Indicates full direct load. </li><li><code>inc</code>: incremental direct load. Supports the <code>INSERT</code> and <code>IGNORE</code> semantics. </li><li><code>inc_replace</code>: incremental direct load. Does not check whether the primary key is duplicated. This is equivalent to incremental direct load with the <code>REPLACE</code> semantics. </li></ul></li></ul></li><li><code>NO_DIRECT</code>: specifies that a single SQL statement must not use direct load. If an SQL statement contains this hint, the statement ignores other direct load hints and executes normal load. </li></ul> For more information about direct load, see [Use the LOAD DATA statement for direct load](../../../../../../620.obap/300.obap-data-collection/300.obap-import-data/20.bypass-import/200.full-bypass-import.md). |
| REMOTE_OSS \| LOCAL | An optional parameter. <ul><li> <code>REMOTE_OSS</code>: specifies whether to read data from an object storage service. If you use this parameter, you must specify an object storage service endpoint. Supported object storage services: Alibaba Cloud Object Storage Service (OSS) (<code>oss://</code>), Amazon Simple Storage Service (S3) (<code>s3://</code>), and other object storage services that are compatible with S3, such as OBS and Google Cloud Storage (GCS) (both use <code>s3://</code>).  </li><li> <code>LOCAL</code>: specifies whether to read data from the local file system of the client. If you do not use the `LOCAL` parameter, data is read from the file system of the server (OBServer node). </li></ul> For more information about this parameter, see [Use the LOAD DATA statement for direct load](../../../../../../620.obap/300.obap-data-collection/300.obap-import-data/20.bypass-import/200.full-bypass-import.md). |
| file_name                        | The path and name of the input file. <code>file_name</code> can be in the following formats: <ul><li>When the import file is stored on an OBServer node or client: <code>/\$PATH/\$FILENAME</code>. </li><li>When the import file is stored on an object storage service: <code>oss://\$PATH/\$FILENAME/?host=\$HOST&access_id=\$ACCESS_ID&access_key=\$ACCESSKEY</code>. </li></ul>    The parameters are described as follows: <ul><li><code>\$PATH</code>: the path of the file in the bucket. </li><li><code>\$FILENAME</code>: the name of the file to be accessed. </li><li><code>\$HOST</code>: the host name or the domain name of the CDN-accelerated endpoint of the object storage service. This parameter specifies the endpoint of the object storage service. </li><li><code>\$ACCESS_ID</code>: the Access Key ID required to access the object storage service. This parameter is used for identity verification. </li><li><code>\$ACCESSKEY</code>: the Access Key secret required to access the object storage service. This parameter is used for identity verification. </li></ul>    <main id="notice" type='explain'> <h4>Note</h4> <p>When you import a file from an object storage service, make sure that the following conditions are met: <ul><li>You have the required permissions to read the bucket and file. You must have sufficient permissions to read the specified bucket and file. This usually requires you to set the access permissions in the OSS console or by using the OSS API and configure the Access Key ID and Access Key secret as credentials with appropriate permissions. </li><li>The database server can connect to the specified <code>$HOST</code> endpoint to access the object storage service. If you use the CDN-accelerated endpoint of the object storage service, make sure that the CDN is configured correctly and that the network connection is normal. </li></ul></p> </main> |
| table_name                       | The name of the table to which the data is imported. Supports partitioned and non-partitioned tables.  |
| PARTITION_OPTION                   | The name of the partition when you perform direct load on a partition. <ul><li>partition_option_list: the list of partitions to be inserted. Separate multiple partitions with commas (,). </li> <li>subpartition_option_list: the list of subpartitions to be inserted. Separate multiple subpartitions with commas (,). </li></ul><main id="notice" type='explain'><h4>Note</h4><p>Partitioning is supported only for direct load. If you do not add the direct load hint or set the direct load configuration, the partitioning option is invalid when you execute the LOAD DATA statement. </p></main>|
| COMPRESSION                      | Specifies the compression format of the file. <ul><li><code>AUTO</code>: Automatically detects the compression algorithm based on the file name suffix. <br>When using the <code>AUTO</code> parameter, different suffixes correspond to different compression formats. <ul><li><code>.gz</code>: GZIP-compressed file.</li><li><code>.deflate</code>: DEFLATE-compressed file.</li><li><code>.zst/.zstd</code>: ZSTD-compressed file.</li></ul></li><li><code>NONE</code>: Indicates that the file is not compressed.</li><li><code>GZIP</code>: GZIP-compressed file.</li><li><code>DEFLATE</code>: GZIP-compressed file without metadata.</li><li><code>ZSTD</code>: ZSTD-compressed file.</li></ul> You can explicitly specify the compression format of the file or let the program detect the compression format based on the file name suffix. |
| FIELDS \| COLUMNS                | Specifies the format of the fields. <ul><li> `ENCLOSED BY` : Specifies the modifier for the exported value.  </li> <li> `TERMINATED BY` : Specifies the delimiter for the exported column. </li> <li>`ESCAPED BY` : Specifies the character to be ignored in the exported value.</li></ul>   |
| LINES STARTING BY                | Specifies the starting character of a line.   |
| LINES TERMINATED BY              | Specifies the ending character of a line. |
| IGNORE number { LINES \| ROWS } | Specifies the number of lines to be ignored. `LINES` specifies the number of lines at the beginning of the file. `ROWS` specifies the number of rows at the beginning of the file, as specified by the field delimiter. By default, the system maps the fields in the input file to the columns of the table. If the input file does not contain all the columns, the missing columns are filled with the following values: <ul><li> For character types: empty string.   </li> <li> For numeric types: 0.    </li> <li> For date types: `0000-00-00`.</li></ul><main id="notice" type='explain'><h4>Note</h4><p>When you import data from multiple files, the behavior is the same as when you import data from a single file. </p></main>    |
| column_name_var                  | Optional. Specifies the name of the imported column.   |

| LOG ERRORS                       | Optional. Enables error diagnostics during the import of data from a URL. For more information, see [log_errors](#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 an external table are stored in a separate directory, which can contain subdirectories. When you create an external table, it automatically collects all files in this directory.

    * For a local location, the format is `LOCATION = '[file:// | sfile://] local_file_path'`.
    * `file://`: This protocol header indicates that when encountering files with the same logical path across multiple OBServer nodes, they are treated as different files and processed separately. Each node independently reads the file from its local path.

      * `sfile://`: This protocol header indicates that when encountering files with the same logical path across multiple OBServer nodes, the system deduplicates them based on the logical path to ensure only one copy of the data is read (supporting multi-server load balancing).
        <main id="notice" type='notice'>
        <h4>Note</h4>
      <p><ul><li>When using <code>sfile://</code>, the system performs consistency verification of file sizes. If files under the same logical path have inconsistent sizes across different OBServer nodes, the system reports the <code>OB_INVALID_EXTERNAL_FILE</code> error to avoid result errors caused by data inconsistency.</li> <li>For version V4.4.2, support for the <code>sfile://</code> protocol header was added to the local location path format in the <code>LOAD DATA</code> syntax starting from V4.4.2 BP2.</li></ul></p>

    </main>

  * `local_file_path`: Can be a relative or absolute path. If you specify a relative path, the current directory must be the installation directory of OceanBase Database. The `secure_file_priv` parameter specifies the file paths that OBServer nodes have 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 for accessing 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 and 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, this parameter is empty, but in a Kerberos environment, it usually has a value and needs to be configured. For example, `dfs.data.transfer.protection=authentication,privacy` specifies the data transfer protection level as `authentication` and `privacy`.
      <main id="notice" type='notice'>
      <h4>Notice</h4>
    <p>When using an object storage path, the parameters of the object storage path are separated by the <code>&</code> symbol. Please ensure that your input parameter values contain only uppercase and lowercase letters, numbers, <code>\/-_$+=</code>, and wildcards. If you input other characters, the settings may fail.</p>

</main>

  * The `FORMAT` clause specifies properties related to the file reading format, supporting CSV, PARQUET, and ORC file formats.

    * When **TYPE = 'CSV'**, the following fields are included:
    * `LINE_DELIMITER`: specifies the line delimiter for CSV files, defaulting to `LINE_DELIMITER='\n'`.
    * `FIELD_DELIMITER`: optional, specifies the column delimiter for CSV files, defaulting to `FIELD_DELIMITER='\t'`.
    * `PARSE_HEADER`: optional, specifies whether the first row of the CSV file is the column names. By default, `FALSE` indicates that the first row is not specified as the column names.
    * `ESCAPE`: specifies the escape character for CSV files, which must be one byte, defaulting to `ESCAPE ='\'`.
    * `FIELD_OPTIONALLY_ENCLOSED_BY`: optional, specifies the symbol for enclosing field values in CSV files, defaulting to an empty string. This option applies only to specific field types (e.g., CHAR, VARCHAR, TEXT, JSON).
    * `ENCODING`: specifies the character set encoding format of the file. If not specified, the default value is `UTF8MB4`.
    * `NULL_IF`: specifies the string to be treated as `NULL`. The default value is empty.
    * `SKIP_HEADER`: skips the file header and specifies the number of rows to skip.
    * `SKIP_BLANK_LINES`: specifies whether to skip blank lines. By default, `FALSE` indicates that blank lines are not skipped.
    * `TRIM_SPACE`: specifies whether to remove leading and trailing spaces from fields in the file. By default, `FALSE` indicates that leading and trailing spaces are not removed.

  * `EMPTY_FIELD_AS_NULL`: specifies whether to treat empty strings as `NULL`. By default, `FALSE` indicates 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 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, supporting `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 error rows allowed.

  * `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:
    <main id="notice" type='explain'>
    <h4>Note</h4>
  <p>The <code>INTO 'logfile_string'</code> parameter is supported starting from V4.4.0. </p>

  </main>

  * When error information is stored locally, `logfile_string` is in the format `[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. `secure_file_priv` specifies the file paths that OBServer nodes are allowed to access. `local_file_path` must be a subpath of the `secure_file_priv` path.

    * When error information is stored remotely (refer to the Location section in the syntax for creating an external table), `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 information 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 details 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 individual diagnostic log files. For more information, see [load_data_diagnosis_log_compression](../../../../../800.configuration-items-and-system-variables/100.system-configuration-items/400.tenant-level-configuration-items/4220.load_data_diagnosis_log_compression.md) and [load_data_diagnosis_log_max_size](../../../../../800.configuration-items-and-system-variables/100.system-configuration-items/400.tenant-level-configuration-items/4230.load_data_diagnosis_log_max_size.md).

  * `REJECT LIMIT`: optional. Specifies the maximum number of error rows allowed:
  * Default value: 0. No error rows are allowed. The operation fails at the first error.
  * `integer`: the maximum number of error rows allowed on a single server. For example, 10 means that up to 10 error rows can be encountered on one server.

* `UNLIMITED`: allows an unlimited number of error rows.

  * `BADFILE 'badfile_string'`: specifies the path where error data files are stored. `badfile_string` follows the same format as `logfile_string`.
    <main id="notice" type='explain'>
    <h4>Note</h4>
  <p>The <code>BADFILE 'badfile_string'</code> parameter is supported starting from V4.4.0. </p>

</main>
  <main id="notice" type='notice'>
  <h4>Notice</h4>
<p><ul><li>When <code>LOG ERRORS</code> is not specified, the default behavior is to fail the operation at the first error. </li><li>If <code>LOG ERRORS</code> is specified but <code>REJECT LIMIT</code> is not, it is equivalent to setting <code>LIMIT</code> to 0. The operation will fail at the first error, but the first error will be recorded, and the error code will be related to the diagnostic, such as "reject limit reached".</p>

</main>

#### View error logs

OceanBase Database supports the following SQL statement to view the error logs:
```sql
SELECT * FROM READ_ERROR_LOG('diagnosis_log_path');

In this statement, diagnosis_log_path specifies the path of the error log. When executed, this SQL statement is equivalent to the following URL external table query:

SELECT *
    FROM FILES (
    LOCATION = 'diagnosis_log_path/'
        FORMAT(
        TYPE = 'csv'
        FIELD_DELIMITER = ','
        FIELD_OPTIONALLY_ENCLOSED_BY = '\',
        PARSE_HEADER  = true
    )
[, PATTERN = 'filename']
);

Here are some examples:

The specified log path is a file name (not ending with /).

SELECT * FROM READ_ERROR_LOG('diagnosis/log/path/filename');

The corresponding URL external table query is as follows (the file name is used as a pattern to filter files):

SELECT *
    FROM FILES (
    LOCATION = 'diagnosis/log/path/',
        FORMAT (
        TYPE = 'csv'
        FIELD_DELIMITER = ','
        FIELD_OPTIONALLY_ENCLOSED_BY = '\',
        PARSE_HEADER = true
    ),
PATTERN = 'filename'
);

The specified log path is a folder (ending with /).

SELECT * FROM READ_ERROR_LOG('diagnosis/log/path/');

The corresponding URL external table query is as follows (no pattern is used):

SELECT *
    FROM FILES (
    LOCATION = 'diagnosis/log/path/',
        FORMAT (
        TYPE = 'csv'
        FIELD_DELIMITER = ','
        FIELD_OPTIONALLY_ENCLOSED_BY = '\',
        PARSE_HEADER = true
)
);

Rules for using wildcards in multi-file direct load

To facilitate multi-file import, the wildcard feature is introduced for server-side and OSS file imports, but not for client-side file imports.

Server-side wildcard usage
- Matching rules:
- Matching a file: load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.*.csv' replace into table t1 fields terminated by '|';
- Matching a directory: load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.1.csv' replace into table t1 fields terminated by '|';
Matching both a directory and a file: load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.*.csv' replace into table t1 fields terminated by '|';
- Considerations:
- At least one matching file must exist; otherwise, an error code 4027 is returned.
- For the input load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.1*.csv,/xxx/test.6*.csv' replace into table t1 fields terminated by '|';, /xxx/test.1*.csv,/xxx/test.6*.csv is considered a whole match. If no match is found, an error code 4027 is returned.
Only POSIX-compatible GLOB functions are supported. For example, test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv) can be found using the ls command but cannot be matched by the GLOB function, resulting in an error code 4027.
- Wildcard usage in Cloud Object Storage Service (OSS)
  - Matching rules:
Matching a file: 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 '|';
- Considerations:
- Directory matching is not supported. For example, load data /*+ parallel(20) direct(true, 0) */ remote_oss infile 'oss://aa*bb/test.*.csv?host=xxx&access_id=xxx&access_key=xxx' replace into table t1 fields terminated by '|'; will return OB_NOT_SUPPORTED.
Only * and ? are supported as filename wildcards. Other wildcards, although allowed in input, cannot match any results.

Examples

Import data from a file on the server (OBServer node)

Example 1: Import data from a file on the server.

Set the global secure path.

Notice

Due to security reasons, when you set the system variable secure_file_priv, you can only modify the global variable by using a local socket connection. For more information, see secure_file_priv.

```shell obclient> SET GLOBAL secure_file_priv = "/";


   2. Exit the login.
     <main id="notice" type='explain'>
     <h4>Note</h4>
   <p>Because <code>secure_file_priv</code> is a <code>GLOBAL</code> variable, you need to execute <code>\q</code> to exit and make it effective.</p>

   </main>
   ```shell
   obclinet> \q

The return result is as follows:

Bye

After reconnecting to the database, use the LOAD DATA statement to import data.

Normal import. ```shell obclient> LOAD DATA INFILE '/home/admin/test.csv' INTO TABLE t1;


  * Enable direct load by using the `APPEND` hint.
  ```shell
  LOAD DATA /*+ PARALLEL(4) APPEND */ INFILE '/home/admin/test.csv' INTO TABLE t1;

Example 2: Enable direct load by using the APPEND hint.

   LOAD DATA /*+ PARALLEL(4) APPEND */
   INFILE '/home/admin/a.csv'
INTO TABLE t;

Example 3: Import a CSV file.

Import all columns from the test1.csv file.

load data  /*+ direct(true,0) parallel(2)*/
  from files(
  location = "data/csv",
    format = (
    type = 'csv',
    field_delimiter = ',',
    parse_header = true,
  skip_blank_lines = true
  ),
pattern = 'test1.csv')
into table t1;

Read the c1 and c2 columns from the test1.csv file in the data/csv directory and import them to the col1 and col2 columns of the t1 table.

load data  /*+ direct(true,0) parallel(2)*/
  from (
      select c1, c2 from files(
        location = 'data/csv'
        format = (
        type = 'csv',
        field_delimiter = ',',
        parse_header = true,
      skip_blank_lines = true
      ),
pattern = 'test1.csv'))
into table t1 (col1, col2);

Example 4: Import a PARQUET file.

load data  /*+ direct(true,0) parallel(2)*/
  from files(
  location = "data/parquet",
  format = ( type = 'PARQUET'),
pattern = 'test1.parquet')
into table t1;

Example 5: Import an ORC file.

load data  /*+ direct(true,0) parallel(2)*/
  from files(
  location = "data/orc",
  format = ( type = 'ORC'),
pattern = 'test1.orc')
into table t1;

Example 6: Import an ODPS file.

load data  /*+ direct(true,0) parallel(2)*/
  from source (
  type = 'ODPS',
  accessid = '$ODPS_ACCESSID',
  accesskey = '******',
  endpoint= '$ODPS_ENDPOINT',
  project_name = 'example_project',
  schema_name = '',
  table_name = 'example_table',
  quota_name = '',
compression_code = '')
into table t1;

Import data from a file on the client (local)

Example 1: Import data from a local file to a table in OceanBase Database.

Open the terminal or command prompt window and enter the following command to start the client.

obclient --local-infile -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p******

The return result is as follows:

Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221548006

Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [SYS]>

 ```
   <main id="notice" type='notice'>
   <h4>Notice</h4>
 <p>To use the <code>LOAD DATA LOCAL INFILE</code> feature, use OBClient V2.2.4 or later.</p>

</main>

2. In the client, execute the `LOAD DATA LOCAL INFILE` statement to load the local data file.
```shell
obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/test_data/tbl1.csv' INTO TABLE tbl1 FIELDS TERMINATED BY ',';

The return result is as follows:

Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Example 2: Directly import a compressed file by setting COMPRESSION.

    LOAD DATA LOCAL INFILE '/your/file/lineitem.tbl.gz'
    INTO TABLE lineitem
    COMPRESSION GZIP
FIELDS TERMINATED BY '|';

Example 3: Specify a partition for direct load by using PARTITION.

Specify a partition for direct load.

load data /*+ direct(true,0) parallel(2) load_batch_size(100) */
infile "$FILE_PATH"
into table t1 partition(p0, p1)
fields terminated by '|' enclosed by '' lines starting by '' terminated by '\n';

Specify a subpartition for direct load.

load data /*+ direct(true,0) parallel(2) load_batch_size(100) */
infile "$FILE_PATH"
into table t1 partition(p0sp0, p1sp1)
fields terminated by '|' enclosed by '' lines starting by '' terminated by '\n';

Import data from an OSS file

Example 1: Enable direct load by using the direct(bool, int) hint. The direct load file is stored on OSS.

LOAD DATA /*+ direct(true,1024) parallel(16) */ REMOTE_OSS INFILE 'oss://antsys-oceanbasebackup/backup_rd/xiaotao.ht/lineitem2.tbl?host=***.oss-cdn.***&access_id=***&access_key=***' INTO TABLE tbl1 FIELDS TERMINATED BY ',';

Import data from a server-side file as a URL external table

Notice

The commands in the examples have been desensitized. When you verify the commands, replace the IP addresses with the actual IP addresses of your machines.

The following example shows how to import data from a server-side file as a URL external table. The external file is stored in the server (OBServer node) and in OceanBase Database in Oracle mode. The steps are as follows:

Create directories on the OBServer node. The /home/admin/test_csv directory stores the external data, the /home/admin/test_into directory stores the error log, and the /home/admin/test_badfile directory stores the error data file.

[admin@xxx /home/admin]# mkdir -p /home/admin/{test_csv,test_into,test_badfile}

Prepare an external file. In the /home/admin/test_csv directory, create a file named type_cast.csv.

[admin@xxx /home/admin/test_csv]# vi type_cast.csv

The content of the file is as follows:

1,2,3
2,4,af
3,4,5
ds,6,32
4,5,6
5,2,3
6,v4,af
7,4,5
kj,a6,32
8,5,6

3. Set the file path for import.
  <main id="notice" type='notice'>
  <h4>Notice</h4>
<p>For security reasons, when you set the system variable <code>secure_file_priv</code>, you can only modify the global variable by using a local socket connection. For more information, see <a href="../../../../../800.configuration-items-and-system-variables/200.system-variable/300.global-system-variable/12000.secure_file_priv-global.md">secure_file_priv</a>. </p>

  1. Run the following command to log in to the machine where the OBServer node is located.
  ```shell
  ssh admin@10.10.10.1


   2. Run the following command to connect to the `oracle001` tenant by using a local Unix socket connection.
   ```shell
   obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******

  3. Run the following SQL statement to set the import and export to unlimited.
  ```sql
  SET GLOBAL secure_file_priv = "/";


   4. Reconnect to the `oracle001` tenant.

   Here is an example:
   ```shell
   obclient -h10.10.10.1 -P2881 -usys@oracle001 -p****** -A

Create a table named test_tbl1.

CREATE TABLE test_tbl1(col1 INT, col2 INT, col3 INT);

Set the compression algorithm for diagnostic logs to AUTO.

ALTER SYSTEM SET load_data_diagnosis_log_compression = 'AUTO';

For more information about setting the compression algorithm for diagnostic logs, see load_data_diagnosis_log_compression.

Set the maximum size of a single diagnostic log file to 1 KB. If the size of the exported log exceeds 1 KB, a second file will be automatically generated and the export will continue.

ALTER SYSTEM SET load_data_diagnosis_log_max_size = '1K';

For more information about setting the size of a single diagnostic log file, see load_data_diagnosis_log_max_size.

Use the LOAD DATA statement to import data into the test_tbl1 table from a URL external table, specify error diagnostics, store the error log in the /home/admin/test_into/ directory, and store the error data files in the /home/admin/test_badfile/ directory.

    LOAD DATA FROM FILES(
    LOCATION = '/home/admin/test_csv/',
        FORMAT = (
        TYPE = 'csv',
    FIELD_DELIMITER = ','),
    PATTERN = 'type_cast.csv')
    INTO TABLE test_tbl1
        LOG ERRORS
        INTO '/home/admin/test_into/'
        REJECT LIMIT UNLIMITED
BADFILE '/home/admin/test_badfile/';

The return result is as follows:

Query OK, 6 rows affected, 4 warnings
Records: 6  Deleted: 0  Skipped: 0  Warnings: 4

Run the read_error_log statement to view the error log content.

SELECT * FROM READ_ERROR_LOG('/home/admin/test_into');

The return result is as follows:

+------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
| ERROR CODE | FILE NAME     | LINE NUMBER | ERROR MESSAGE                                                                                         |
+------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
| -5114      | type_cast.csv | 4           | fail to scan file type_cast.csv at line 4 for column "SYS"."TEST_TBL1"."COL1", error: Invalid numeric |
| -5114      | type_cast.csv | 9           | fail to scan file type_cast.csv at line 9 for column "SYS"."TEST_TBL1"."COL1", error: Invalid numeric |
| -5114      | type_cast.csv | 7           | fail to scan file type_cast.csv at line 7 for column "SYS"."TEST_TBL1"."COL2", error: Invalid numeric |
| -5114      | type_cast.csv | 2           | fail to scan file type_cast.csv at line 2 for column "SYS"."TEST_TBL1"."COL3", error: Invalid numeric |
+------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
4 rows in set

10. View the data in the `test_tbl1` table.
```sql
SELECT * FROM test_tbl1;

```

The return result is as follows:
```shell
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |    2 |    3 |
|    3 |    4 |    5 |
|    4 |    5 |    6 |
|    5 |    2 |    3 |
|    7 |    4 |    5 |
|    8 |    5 |    6 |
+------+------+------+
6 rows in set


    11. View the content of the bad file through the URL external table.
    ```sql
    SELECT *
        FROM FILES (
        LOCATION = '/home/admin/test_badfile',
            FORMAT (
            TYPE = 'csv',
        FIELD_DELIMITER = ','),
    PATTERN = 'data.bad');

    ```

    The return result is as follows:
    ```shell
    +------+------+------+
    | C1   | C2   | C3   |
    +------+------+------+
    | ds   | 6    | 32   |
    | kj   | a6   | 32   |
    | 6    | v4   | af   |
    | 2    | 4    | af   |
    +------+------+------+
    4 rows in set

References

For more information about using the LOAD DATA statement, see Import data by using the LOAD DATA statement.

OceanBase

Customer Stories

Documentation

LOAD DATA

Purpose

Note

Considerations

Syntax

Rules for using wildcards in multi-file direct load

Examples

Import data from a file on the server (OBServer node)

Notice

Import data from a file on the client (local)

Import data from an OSS file

Import data from a server-side file as a URL external table

Notice

References