LOAD DATA|V4.3.1| docs|Distributed Database

LOAD DATA

Last Updated：2024-10-23 05:52:20 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 starting the MySQL client or 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 file system of the local 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.
Files in an OSS file system. You can execute the LOAD DATA REMOTE_OSS INFILE statement to load data from files in an OSS file 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 | 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 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 \| direct()	A hint for enabling the direct load feature. The `APPEND` hint is equivalent to `direct(true,0)`, and can collect statistics online like the `GATHER_OPTIMIZER_STATISTICS` hint does. Options in `direct()`: `bool`: specifies whether to sort the data written. The value `true` indicates that sorting is needed, and the value `false` indicates that sorting is not needed. `int`: the maximum number of erroneous rows allowed. `load_mode`: Optional. The direct load mode. Enclose the value with single quotation marks. Valid values: `full`: the full direct load mode. This is the default value. `inc_replace`: the incremental direct load mode based on the `replace` semantic. Notice The incremental direct load feature is currently in the experimental stage. To ensure system stability, we recommend that you do not use it in a production environment. For more information about how to use `LOAD DATA` to import data in bypass mode, see Import data in bypass mode by using the LOAD DATA statement.
REMOTE_OSS \| LOCAL	An optional parameter. `REMOTE_OSS` specifies to read data from an OSS file system. Notice If this parameter is specified, `file_name` must be an OSS address. `LOCAL` specifies to read data from the file system of the local client. If you do not specify the `LOCAL` parameter, data will be read from the file system of an OBServer node.
file_name	The path and file name of the input file. `file_name` can be in one of the following formats: For an input file on an OBServer node or client: `/$PATH/$FILENAME` For an input file in an OSS file system: `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 OSS service, that is, the address of the OSS service to be accessed. `$ACCESS_ID`: the AccessKey ID required for authentication when accessing the OSS service. `$ACCESSKEY`: the AccessKey secret required for authentication when accessing the OSS service. Note When you import a file from OSS, make sure that: You have privileges to access the corresponding OSS bucket and file. That is, you can read data from the bucket and file. Usually, you need to set access privileges on the OSS console or through OSS APIs, and configure AccessKey information (AccessKey ID and AccessKey secret) as credentials with corresponding privileges. The database server can connect to the specified `$HOST` address to access the OSS service. The CDN is configured correctly and the network connection is normal if you want to use the CDN domain name to access the OSS 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`: specifies the modifier of the exported value. `TERMINATED BY`: specifies the end character of the exported column. `ESCAPED BY`: specifies the characters ignored for the exported value.
LINES STARTING BY	The start character of the line.
LINES TERMINATED BY	The end character of the line.
IGNORE 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 data 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, 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 of the database.

Note

Because secure_file_priv is a GLOBAL variable, 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 an OSS file

Use the direct(bool,int) hint to enable direct load. The import file can be an OSS file.

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 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 of 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 examples of the LOAD DATA statement, see Import data by using the LOAD DATA statement.
For more direct load examples of the LOAD DATA statement, see Import data through direct load by using the LOAD DATA statement.

LOAD DATA

Purpose

Notice

Syntax

Parameters

Notice

Notice

Note

Examples

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

Notice

Note

Example 2: Import data from an OSS file

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

Notice

References