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

LOAD DATA

Last Updated：2025-12-05 03:43:26 Updated

Description

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

In OceanBase Database, the LOAD DATA statement can load input files from OceanBase Database and OBServer nodes in Alibaba Cloud OSS.

Note

The current version of OceanBase Database does not support using the LOCAL INFILE syntax to load data files.
Tables with triggers are not supported.

The LOAD DATA statement can currently import text files in the CSV format. The entire import process consists of the following steps:

Parse the file: OceanBase Database reads data from the file with the name specified by the user and parses data in the file in parallel or in series based on the specified degree of parallelism.
Distribute data: As a distributed database, OceanBase Database distributes data across OBServer nodes. After data is parsed, the LOAD DATA statement calculates where to send the data.
Insert data: After the target OBServer node receives the data, it executes the INSERT statement to insert the data into the corresponding partition.

To import data from external files, you must have the FILE privilege. You can use the GRANT FILE ON *.* TO $user_name; statement to grant the privilege to the user who needs to execute the LOAD DATA statement, where $user_name is the name of the user who needs to execute the LOAD DATA statement.

Syntax

LOAD DATA
    [ [/*+ PARALLEL(N) load_batch_size(M) APPEND */]
      | [/*+ PARALLEL(N) direct(bool, int) */] ]
    [REMOTE_OSS] 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] ...)]

Parameter description

Parameter	Description
parallel(N)	The degree of parallelism for loading data. If N is specified, it must be an integer greater than 0. The default value is `4`.
load_batch_size(M)	The number of rows to be inserted in one batch. If M is specified, it must be an integer greater than 0. The default value is `100`. We recommend that you set this parameter to a value between 100 and 1000.
APPEND	Use this hint to enable direct load, which allocates space and writes data directly in the data file. By default, the APPEND hint is equivalent to the `direct(true, 0)` hint. It also enables online collection of optimizer statistics using the `GATHER_OPTIMIZER_STATISTICS` hint.
direct	Use this hint to enable direct load. The `direct(bool, int)` parameter specifies whether to sort the given CSV file and the maximum number of allowed error rows.
REMOTE_OSS	Specifies whether to read data from the OSS file system. Notice If you specify this parameter, the `file_name` parameter must be an address in the OSS file system.
file_name	The path and name of the input file. The format of the `file_name` parameter is as follows: If the import file is stored on an OBServer node, the format is `/$PATH/$FILENAME`. If the import file is stored on OSS, the format is `oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY`. The parameters are described as follows: `$PATH` specifies the path of the file in the bucket, indicating the directory to which the file belongs. `$FILENAME` specifies the name of the file, indicating the specific file to be accessed. `$HOST` specifies the host name or CDN domain name of the OSS service or accelerated domain name, which is the address to access the OSS service. `$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 Before you import a file from OSS, make sure that: You have the permissions to access the OSS bucket and file. You must have sufficient permissions to read the specified bucket and file. This typically requires setting the permissions on the OSS console or by using OSS APIs, and configuring the access keys (Access Key ID and Access Key Secret) as credentials with appropriate permissions. Your database server can connect to the specified `$HOST` over the network to access the OSS service. If a CDN domain name is used, 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 loaded. The table can be a partitioned or non-partitioned table.
FIELDS \| COLUMNS	The formats of fields. `ENCLOSED BY`: specifies the delimiter for the exported values. `TERMINATED BY`: specifies the delimiter for the exported columns. `ESCAPED BY`: specifies the character to be ignored in the exported values.
LINES STARTING BY	The starting delimiter of a line.
LINES TERMINATED BY	The ending delimiter of a line.
IGNORE number { LINES \| ROWS }	Specifies to ignore the first several lines. `LINES` indicates several lines in the file, and `ROWS` indicates several rows separated by field delimiters. By default, the system matches each field in the input file with the corresponding column 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 described in the following example. 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 secure 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 the global variable. For more information, see secure_file_priv.
```
obclient> SET GLOBAL secure_file_priv = "";
```
Log out.

Note

Since secure_file_priv is a GLOBAL variable, you must execute \q to log out for the modification 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, make sure that the values of the parameters in the object storage path are composed of uppercase and lowercase letters, digits, and the following characters: \/-_$+=. Otherwise, the setting may fail.

Use the direct(bool, int) hint to enable direct load, where the imported file can be stored in 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 ',';