LOAD DATA|V4.2.5| docs|Distributed Database

LOAD DATA

Last Updated：2025-11-19 10:08:13 Updated

Purpose

You can use this statement to import data from an external file.

Notice

Do not use the LOAD DATA statement on tables with triggers.
To import data from an external file, you must have the FILE privilege and configure the following settings:
- To load files from an OBServer node, you must configure the system variable secure_file_priv to specify the path that can be accessed during file import or export.
- To load local files on a client, you must add the --local-infile[=1] option when you start the MySQL client or OceanBase Command-Line Client (OBClient), to enable data loading from the local file system.

OceanBase Database supports the following input files for the LOAD DATA statement:

Files on an OBServer node: You can execute the LOAD DATA INFILE statement to load data from files on an OBServer node into database tables.
Files in the local file system of the client: You can execute the LOAD DATA LOCAL INFILE statement to load data from files in the file system of the local client into database tables.

Note

Starting from V4.2.2, OceanBase Database in Oracle mode supports the LOAD DATA LOCAL INFILE syntax to load local data files.
Files in an object storage service system: You can execute the LOAD DATA REMOTE_OSS INFILE statement to load data from files in an object storage service system into database tables.

You can use the LOAD DATA statement to import a CSV text file in the following process:

Parse the file: OceanBase Database reads data from a file based on the file name that you enter and determines whether to perform parallel or serial parsing of data from the input file based on the specified degree of parallelism (DOP).
Distribute the data: OceanBase Database is a distributed database. Data of each partition may be distributed across different OBServer nodes. The LOAD DATA statement is used to process the parsed data and determine to which OBServer node the data is sent.
Insert the data: After the destination OBServer node receives the data, it executes the INSERT statement to insert the data into the corresponding partition.

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 the privilege, where $user_name is the user that executes the LOAD DATA statement.

Syntax

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

Parameters

Parameter	Description
parallel(N)	The DOP for loading data. The default value of `N` is `4`.
load_batch_size(M)	The batch size of each insertion. The default value of `M` is `100`. Recommended value range: [100,1000].
APPEND	A hint for enabling the direct load feature. This feature allows you to directly allocate space and write data to data files. The `APPEND` hint is equivalent to `direct(true, 0)`, and can collect statistics online like the `GATHER_OPTIMIZER_STATISTICS` hint does.
direct	A hint for enabling the direct load feature. The `bool` parameter in `direct(bool, int)` specifies whether the given CSV file is ordered. The value `true` indicates that the file is ordered. The `int` parameter specifies the maximum number of error rows allowed.
REMOTE_OSS \| LOCAL	Optional. Valid values: `REMOTE_OSS`: specifies to read data from an object storage service system. Notice If this parameter is specified, the value of `file_name` must be the address of an OSS system. `LOCAL`: specifies to read data from the local file system on the client. If you do not specify the `LOCAL` parameter, data will be read from the file system on an OBServer node.
file_name	The path and file name of the file to import. You can specify a value in either of the following formats: If you want to import a file on an OBServer node or client, specify `/$PATH/$FILENAME`. If you want to import a file in an object storage service system, specify the value in the following format: `oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY`. The parameters are described as follows: `$PATH`: the file path in the bucket, which represents the directory where the file is located. `$FILENAME`: the name of the file to be accessed. `$HOST`: the host name or CDN domain name of the object storage service, that is, the endpoint of the object storage service to be accessed. `$ACCESS_ID`: the AccessKey ID for accessing the object storage service. `$ACCESSKEY`: the AccessKey secret for accessing the OSS service. Note When you import a file from an object storage service system, make sure that the following conditions are met: You have permissions to access the corresponding bucket and file in the object storage service system. In other words, you can read data from the bucket and file. Usually, you need to grant required access permissions to the AccessKey pair in the object storage service console or through object storage service APIs. The database server can connect to the endpoint specified by `$HOST` to access the object storage service. A CDN domain name is configured correctly and the network connection is normal, if you want to use the CDN domain name to access the object storage service.
table_name	The name of the table from which data is imported. Partitioned and non-partitioned tables are supported.
FIELDS \| COLUMNS	The format of the field. `ENCLOSED BY`: the modifier of an imported value. `TERMINATED BY`: the end character of an imported column. `ESCAPED BY`: the characters ignored for an imported value.
LINES STARTING BY	The start character of the line.
LINES TERMINATED BY	The end character of the line.
IGNORES number { LINES \| ROWS }	Specifies to ignore the first few lines. `LINES` indicates the first few lines of the file. `ROWS` indicates the first few rows of data specified by the field delimiter. By default, fields in the input file are mapped to columns in the destination table one by one. If the input file does not contain all the columns, the missing columns are filled based on the following mappings: Character type: null string. Numeric data type: 0 Date data type: `0000-00-00`
column_name_var	The name of the imported column.

Examples

Example 1: Import data from a file on an OBServer node

Set a global security path.

Notice

For security reasons, you can connect to the database only through a local socket to execute the SQL statement for setting secure_file_priv. For more information, see secure_file_priv.
```
obclient> SET GLOBAL secure_file_priv = "/";
```
Log out of the database.

Note

secure_file_priv is a GLOBAL variable. Therefore, you need to run \q to exit for the settings to take effect.
```
obclient> \q
```
The return result is as follows:
```
Bye
```

Connect to the database again. Execute the LOAD DATA statement to import data.

Perform normal 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 a file in an object storage service system

Use the direct(bool, int) hint to enable direct load. Import data from a file in an object storage service system in direct load mode.

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 file on the client

Perform the following steps to import data from a local file to a table in OceanBase Database:

Open a terminal or Command Prompt 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.

Execute the LOAD DATA LOCAL INFILE statement on the client to load data from a local 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 examples about using the LOAD DATA statement for direct load, see Import data through direct load by using the LOAD DATA statement.

LOAD DATA

Purpose

Notice

Note

Syntax

Parameters

Notice

Note

Examples

Example 1: Import data from a file on an OBServer node

Notice

Note

Example 2: Import data from a file in an object storage service system

Example 3: Import data from a local file on the client

Notice

References