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.

In OceanBase Database, the LOAD DATA statement can load input files from OSS file systems and OBServer nodes.

Note

The current version of OceanBase Database does not support the LOCAL INFILE statement.
Do not use the LOAD DATA statement on tables with triggers.

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] 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 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 needs to be ordered. The value `true` indicates that the file must be ordered. The `int` parameter specifies the maximum number of error rows allowed.
REMOTE_OSS	Specifies whether to read data from an OSS file system. This parameter is optional. Notice If this parameter is specified, `file_name` must be the address of an OSS service.
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.
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: empty 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 bypass import.

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 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 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

Note

Syntax

Parameters

Notice

Note

Examples

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

Notice

Note

Example 2: Import data from an OSS file

References