LOAD DATA|V4.3.1| docs|Distributed Database

LOAD DATA

Last Updated：2025-12-03 06:58:03 Updated

Description

The statement is used to import data from an external source.

Notice

Tables with triggers are not supported.
To import data from an external file, you need to have the FILE privilege and meet the following requirements:
- When you import a file from the server, you must specify the system variable secure_file_priv in advance to indicate the path that is accessible for importing or exporting files.
- When you import a file from the client, you must add the --local-infile[=1] option when you start the MySQL client or OBClient to enable the data loading feature from the local file system.

The LOAD DATA statement in OceanBase Database supports the following input files:

Server (OBServer) node files: files stored on the OBServer nodes of OceanBase Database. You can use the LOAD DATA INFILE statement to load data from server node files to database tables.
Client (local) files: files stored in the local file system of the client. You can use the LOAD DATA LOCAL INFILE statement to load data from client local files to database tables.
OSS files: files stored in the OSS file system. You can use the LOAD DATA REMOTE_OSS INFILE statement to load data from OSS files to database tables.

The LOAD DATA statement currently supports importing CSV files. The entire import process can be divided into the following phases:

Parse the file: OceanBase Database reads data from the file based on the specified file name, and reads data from the file in parallel or in series based on the specified parallelism.
Distribute data: As OceanBase Database is a distributed database, data in different partitions may be stored on different OBServer nodes. The LOAD DATA statement calculates the destination OBServer node for each parsed data.
Insert data: After the data is received by the target OBServer node, an INSERT statement is executed locally to insert the data into the corresponding partition.

To import data from an external file, you must have the FILE privilege. You can execute the GRANT FILE ON *.* TO $user_name; statement to grant the FILE privilege to the user specified by $user_name. For example, to grant the privilege to the user named admin, you would execute the GRANT FILE ON *.* TO admin; statement.

Syntax

LOAD DATA
    [/*+ PARALLEL(N) [load_batch_size(M)] [APPEND | direct(bool, int, [load_mode])] */]
    [REMOTE_OSS | LOCAL] INFILE 'file_name'
    INTO TABLE table_name
    [{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'

Parameters

Parameter	Description
parallel(N)	The degree of parallelism for loading data. The default value of `N` is `4`.
load_batch_size(M)	The batch size for inserting data. The default value of `M` is `100`. We recommend that you set this parameter to a value within the range of [100,1000].
APPEND \| direct()	The hint to enable direct load. By default, the `APPEND` hint is equivalent to `direct(true, 0)`, and online statistics collection (the `GATHER_OPTIMIZER_STATISTICS` hint) is supported. The `direct()` parameter is described as follows: `bool`: specifies whether to sort the data to be written. The value `true` specifies to sort the data, and the value `false` specifies not to sort the data. `int`: specifies the maximum number of rows allowed to be incorrect. `load_mode`: an optional parameter that specifies the direct load mode. The value must be enclosed in single quotation marks (' ') and can be one of the following values: `full`: the default value, which specifies full direct load. `inc_replace`: incremental direct load with the semantics of the `replace` statement. Notice The incremental direct load feature is currently in the experimental stage. To avoid impacting system stability, we recommend that you do not use it in a production environment. For more information about how to use the `LOAD DATA` statement to enable direct load, see Import data or files by using the LOAD DATA statement.
REMOTE_OSS \| LOCAL	Optional. The `REMOTE_OSS` parameter specifies whether to read data from the OSS file system. Notice If you specify this parameter, you must specify `file_name` as an address in the OSS file system. The `LOCAL` parameter specifies whether to read data from the local file system of the client. If you do not specify the `LOCAL` parameter, data is read from the file system of the server, that is, the OBServer node.
file_name	The path and name of the input file. The format of `file_name` is described as follows: If the file to be imported is on an OBServer node or a client, `file_name` is in the format of `/$PATH/$FILENAME`. If the file to be imported is on Alibaba Cloud OSS, `file_name` is in the format of `oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY`. The parameters are described as follows: `$PATH`: the path of the file in the bucket, which indicates the directory where the file is stored. `$FILENAME`: the name of the file, which indicates the specific file to be accessed. `$HOST`: the host name or the CDN domain name for accelerating the access to the OSS service, which is the address to access the specified OSS service. `$ACCESS_ID`: the Access Key ID required for accessing the OSS service, which is used for authentication. `$ACCESSKEY`: the Access Key Secret required for accessing the OSS service, which is used for authentication. Note Before you import a file from Alibaba Cloud OSS, make sure that the following conditions are met: You have the permissions to access the OSS bucket and file. You must have sufficient permissions to read the specified bucket and file. This usually requires that you set the access permissions in the OSS console or by using OSS APIs, and configure the access keys (Access Key ID and Access Key Secret) as credentials with appropriate permissions. The database server can connect to the specified `$HOST` over the network to access the OSS service. If a CDN domain name is used for the OSS service, make sure that the CDN is correctly configured and the network connection is normal.
table_name	The name of the table to which the imported data is to be loaded. The table can be a partitioned or non-partitioned table.
FIELDS \| COLUMNS	The format of fields. `ENCLOSED BY`: the delimiter for the exported values. `TERMINATED BY`: the delimiter for the exported columns. `ESCAPED BY`: the character to be ignored in the exported values.
LINES STARTING BY	The starting delimiter of lines.
LINES TERMINATED BY	The ending delimiter of lines.
IGNORE number { LINES \| ROWS }	The number of rows to be ignored at the beginning of the file. `LINES` indicates rows in the file, and `ROWS` indicates rows separated by field separators. By default, the input file is parsed to match the columns in the table. If the input file does not contain all the columns in the table, the missing columns are filled with default values, which are generated based on the following rules: Character type: an empty string. Numeric type: 0. Date type: `0000-00-00`.
column_name_var	The name of the column to be imported.

Examples

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

Set the global security path.

Notice

For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
```
obclient> SET GLOBAL secure_file_priv = "/";
```
Log out.

Note

After you set the secure_file_priv variable, you must execute \q to log out for the setting to take effect.
```
obclinet> \q
```
The return result is as follows:
```
Bye
```

Reconnect to the database and use the LOAD DATA statement to import data.

Perform a regular import.

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

Use the APPEND hint to enable direct load.

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

Example 2: Import data from an OSS file

Notice

When you use an object storage path, parameters in the object storage path are separated by & characters. Make sure that the values of the parameters you enter contain only uppercase and lowercase letters, numbers, and the following characters: \/-_$+=. If you enter characters other than the preceding ones, the setting may fail.

Use the direct(bool, int) hint to enable direct load, and import the file from Alibaba Cloud 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 ',';

Example 3: Import data from a local client file

Execute the following statements to 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]>

Notice

To use the LOAD DATA LOCAL INFILE feature, use OBClient V2.2.4 or later.

In the client, execute the LOAD DATA LOCAL INFILE statement to load the local data file.

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

References

For more information about how to use the LOAD DATA statement, see Import data by using the LOAD DATA statement.
For more information about direct load using the LOAD DATA statement, see Import data by using the LOAD DATA statement.

LOAD DATA

Description

Notice

Syntax

Parameters

Notice

Notice

Note

Examples

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

Notice

Note

Example 2: Import data from an OSS file

Notice

Example 3: Import data from a local client file

Notice

References