LOAD DATA|V4.2.2| docs|Distributed Database

LOAD DATA

Last Updated：2024-10-23 05:57:51 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.

Note

Starting from V4.2.2, the MySQL mode of OceanBase Database supports the LOAD DATA LOCAL INFILE syntax to load local data files. When executing LOAD DATA LOCAL INFILE, the system automatically adds the IGNORE option.
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.

Syntax

LOAD DATA
    [/*+ PARALLEL(N) load_batch_size(M) APPEND */]
   |[/*+ PARALLEL(N) direct(bool, int) */]
    [REMOTE_OSS | LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    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 bypass import feature. This feature allows you to allocate space and write data to data files. By default, this hint is equivalent to `direct(false, 0)` and can collect statistics online like the `GATHER_OPTIMIZER_STATISTICS` hint does.
direct	A hint for enabling the bypass import 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	An optional parameter. `REMOTE_OSS` specifies whether to read data from an OSS file system. Notice If this parameter is specified, `file_name` must be an OSS address. `LOCAL` specifies whether 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: `/$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.
REPLACE \| IGNORE	If a unique key conflict occurs, `REPLACE` indicates that conflicting rows are overwritten, and `IGNORE` indicates that conflicting rows are ignored. The `LOAD DATA` statement checks whether a table contains duplicate data based on its primary key. If the table does not have a primary key, the `REPLACE` and `IGNORE` options are equivalent. If duplicate data exists, the `LOAD DATA` statement records the incorrect data to a log file by default. Notice When you execute `LOAD DATA LOCAL INFILE` in the MySQL mode of OceanBase Database, the system automatically adds the `IGNORE` option. This behavior provides better compatibility with MySQL databases. If you use the `REPLACE` or `IGNORE` clause and set the DOP to a value greater than `1`, the last record inserted into the conflicting row may differ from the result of serial execution. If you need to strictly guarantee the insertion result of conflicting records, do not specify the DOP or set the DOP to `1` for the statement.
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

Import data from a file on an OBServer node

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

Set a global security path.
```
obclient> SET GLOBAL secure_file_priv = ""
Query OK, 0 rows affected
obclient> \q
Bye
```
Note

Because secure_file_priv is a GLOBAL variable, you need to run \q to exit for the settings to take effect.

After you reconnect to the database, import data from an external file.

obclient> LOAD DATA INFILE 'test.sql' INTO TABLE t1;
Query OK, 0 rows affected

Example 2: Use the APPEND hint to enable the bypass import feature.

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

Example 3: Use the direct(bool, int) hint to enable the bypass import feature. The file to be imported in bypass mode can be stored in OSS.

load data /*+ parallel(1) direct(false,0)*/ remote_oss infile 'oss://antsys-oceanbasebackup/backup_rd/xiaotao.ht/lineitem2.tbl?host=***.oss-cdn.***&access_id=***&access_key=***' into table lineitem fields terminated by '|' enclosed by '' lines starting by '' terminated by '\n';

Import data from a file on the local client

Example 4: 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 -uroot@mysql001 -p****** -A -Dtest

The return result is as follows:

Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221719526
Server version: OceanBase 4.2.2.0 (r100000022023121309-f536833402c6efe9364d5a4b61830a858ef24d82) (Built Dec 13 2023 09:58:18)

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 [test]>

Notice

To execute the LOAD DATA LOCAL INFILE statement, you must use OBClient V2.2.4 or later. If you do not have OBClient of the desired version, you can use the MySQL client to connect to the database.

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

obclient [test]> 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

Import data from an OSS file

Use the direct(bool,int) hint to enable bypass import. 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 ',';

References

For more information about how to connect to OceanBase Database, see Overview.
For more examples of the LOAD DATA statement, see Import data by using the LOAD DATA statement.
For more bypass import examples of the LOAD DATA statement, see Import data in bypass mode by using the LOAD DATA statement.

LOAD DATA

Purpose

Notice

Note

Syntax

Parameters

Notice

Note

Notice

Examples

Import data from a file on an OBServer node

Note

Import data from a file on the local client

Notice

Import data from an OSS file

References