Import data by using the LOAD DATA statement|V4.4.2|OceanBase Database|docs|OceanBase Documentations

Overview

This topic describes how to use the LOAD DATA statement in OceanBase Database to write external data into internal tables, and how to enable direct load when appropriate (using hints such as APPEND or direct(...)), or by configuring the tenant-level parameter default_load_mode, to improve the throughput of large-scale data loading.

Syntax

-- FROM FILES()
LOAD DATA /*+ [APPEND | DIRECT(need_sort,max_error,{'full'|'inc'|'inc_replace'})] parallel(N) */ FROM FILES(...) INTO TABLE table_name ...;

-- Server File or Object Storage Single File
LOAD DATA /*+ [APPEND | DIRECT(need_sort,max_error,{'full'|'inc'|'inc_replace'})] parallel(N) */ [REMOTE_OSS] INFILE 'file_name' INTO TABLE table_name ...;

-- Local client files (direct load is not supported)
LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name ...;

Classification of `LOAD DATA` by data reading location and syntax relationships

Path / Syntax	Data Location	Typical Application Scenarios	Syntax relationships and description	Limitations / Configuration / Additional information
`LOAD DATA FROM FILES(...)`	A server-accessible path described by the `FILES` clause (local directory, object storage URL, HDFS, etc., as specified in the manual).	Describes `LOCATION / FORMAT / PATTERN` in a single statement, and is suitable for batch scanning and error diagnosis of files such as CSV, ORC, and Parquet	Declares the set of files to be scanned through `FROM FILES(LOCATION = ..., FORMAT = (...), [PATTERN = ...])`. The server parses the files according to the format and writes them to the target table. It can be used together with clauses such as `LOG ERRORS` for error diagnosis during import.	In a direct load scenario, you can add hints such as `/+ direct(...) parallel(N) /` after the `LOAD DATA` keyword
`LOAD DATA ... INFILE 'path'` (without `LOCAL`)	A server file system path readable by the OBServer process	The file has been distributed to the database node. This form is often used together with `secure_file_priv`	When `LOCAL` is not specified and `INFILE` is a path on the OBServer, the server directly reads the file (or a set of files matched by a wildcard).	The path must be configured within the range allowed by `secure_file_priv`. Modifying this variable typically requires a local Unix Socket connection.
`LOAD DATA REMOTE_OSS INFILE 'oss://...' / 's3://...'`	The URL of a single file in an object storage bucket	Reads data from the URL of a single file in an object storage bucket	The syntax is written as `LOAD DATA REMOTE_OSS INFILE 'oss://...'/'s3://...'`. Similar to `LOCATION` in `FROM FILES` when it points to an object storage URL, it belongs to the category of "external large-scale data sources".	The examples in this topic are organized into three types: `FROM FILES`, `INFILE`, and `LOCAL INFILE`. If you use `REMOTE_OSS INFILE`, you can apply the `direct(...)`, `parallel(N)`, and partition clause syntax in the following sections to this syntax.
`LOAD DATA ... LOCAL INFILE 'path'`	A path on the client machine that initiates the connection	The file exists only on the client. The connection must enable `--local-infile`.	The keyword `LOCAL` indicates that the file is located on the client machine. After being read by the client, the file is uploaded to the server for parsing and writing.	Use `obclient --local-infile` (or an equivalent option) when connecting to enable local loading. `LOCAL INFILE` does not support direct load.

Note

For the scope of application, differences, and a more comprehensive description of direct load capabilities between full direct load and incremental direct load, see Overview of data import.

For complete definitions and syntax details of each parameter, see LOAD DATA Syntax (MySQL-compatible mode).

Examples

Full direct load (direct(..., 'full')) is typically used for the initial loading of an empty table or for a complete baseline import.

Incremental direct load (direct(..., 'inc') or 'inc_replace') is typically used for append loading based on existing data in the target table.

The two differ in the third parameter of DIRECT(), the status of the target table, and their applicable limitations. For more information about the differences, application scenarios, and limitations between full direct load and incremental direct load, see Overview of data import.

The following examples focus on full direct load. The hint syntax for incremental direct load is the same; the only difference is changing the third parameter to 'inc' or 'inc_replace'.

LOAD DATA FROM FILES

MySQL-compatible mode

Oracle-compatible mode

Log in to the server where the target OBServer node is located. Prepare the data files to be scanned in the /home/admin directory (LOCATION points to a server-accessible path; the files can be in formats such as CSV, ORC, or Parquet, and this example uses CSV).

Note

The LOCATION in FROM FILES must be accessible to the OBServer node. It is recommended that the directory and files be within the scope permitted by secure_file_priv (the same as for LOAD DATA INFILE).
1. Access the server where the OBServer node is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data file tbl1.csv (with content identical to the file in the LOAD DATA INFILE example above for easy comparison).
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to configure the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the server where the target OBServer node is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the tenant mysql001 via a local Unix Socket.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use LOAD DATA FROM FILES with the direct load hint to import the data.

Create table tbl2 (the table structure is the same as the full example in LOAD DATA INFILE above).

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is data in table tbl2. The result shows the table is empty.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use direct load to scan and import tbl1.csv into table tbl2.

If you need to import data using incremental direct load, you can refer to the following syntax examples:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

or:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

In the subsequent steps, we will continue with an example of full direct load.

Specify all columns of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 (col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify a partition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0, p1);

Specify a subpartition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1);

(Optional) Combine with LOG ERRORS for error diagnostics during import (only available when the current version supports this clause).

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2
LOG ERRORS REJECT LIMIT UNLIMITED;

Use the parameter default_load_mode to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [test]> LOAD DATA FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

Verify whether data has been imported into table tbl2.

obclient [test]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Log in to the machine where the target OBServer node is located, and prepare tbl1.csv in the /home/admin directory (the procedure and data content are the same as in MySQL-compatible mode).

Note

The LOCATION of FROM FILES is a server-side path. The value and case of TYPE in FORMAT are subject to the manual for the current tenant mode. This example uses lowercase csv, consistent with the example in the manual.
1. Access the machine where the OBServer node is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to configure the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the machine where the OBServer node to connect to is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to tenant oracle001 via a local Unix Socket connection.
```
obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use LOAD DATA FROM FILES with the direct load hint to import the data.

Create table tbl2 (with the same structure as the Oracle mode table in the full LOAD DATA INFILE example above).

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is data in table tbl2; it shows the table is empty at this point.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

Use direct load to scan and import tbl1.csv into table tbl2.

If you need to import data using incremental direct load, you can refer to the syntax examples:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

or:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

The following steps will continue with an example of full direct load.

Specify all columns of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 (col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify a partition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0, p1);

(Optional) Specify a subpartition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1);

Use the parameter default_load_mode to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [SYS]> LOAD DATA FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

Verify whether data has been imported into table tbl2.

obclient [SYS]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

LOAD DATA INFILE

MySQL-compatible mode

Oracle-compatible mode

Log in to the server where the target OBServer node resides and create a test data file in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database can only load input files located locally on the OBServer node. Therefore, you must copy the file to an OBServer node before importing it.
1. Access the server where the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data file tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to specify the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the server where the target OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the mysql001 tenant via a local Unix Socket connection.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use the LOAD /*+ DIRECT */ DATA statement to import the data.

Create table tbl2.

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),                           
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is any data in table tbl2. The result shows the table is empty.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use direct load to import the data from the tbl1.csv file into table tbl2.

If you need to import data using incremental direct load, you can refer to the following syntax examples:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

or:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

The subsequent steps will continue with an example of full direct load.

Specify all columns of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify any columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify a partition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

Specify a subpartition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

Use the default_load_mode parameter to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [test]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

Verify whether data has been imported into table tbl2.

obclient [test]>  SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Log in to the server where the target OBServer node resides, and create test data named tbl1 in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database can only load input files located locally on the OBServer node. Therefore, you must copy the file to an OBServer node before importing it.
1. Access the server where the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create a test dataset named tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to configure the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the server where the OBServer node you want to connect to resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the tenant oracle001 via a local Unix Socket connection.
```
obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use the LOAD /*+ DIRECT */ DATA statement to import the data.

Create a table named tbl2.

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether the tbl2 table contains data. At this point, it shows the table is empty.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

Use direct load to import the data from the tbl1.csv file into the tbl2 table.

If you need to import data using incremental direct load, you can refer to the following syntax example:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

or:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

In the following steps, we will continue with an example of importing data using full direct load.

Specify all columns from table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify any columns from table tbl2 to import data. For example, specify columns col1 and col2.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify a partition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

(Optional) Specify a subpartition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

Use the parameter default_load_mode to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [SYS]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

Verify whether data has been imported into table tbl2.

obclient [SYS]>  SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

LOAD DATA LOCAL INFILE

MySQL-compatible mode

Oracle-compatible mode

Prepare the data file on the client machine where obclient is running (the path is the local client path, do not confuse it with the path on the OBServer). The file content is the same as the example above, still named tbl1.csv.

Note

LOCAL INFILE means the client reads a local file and uploads it to the server; the connection command must include --local-infile (or an equivalent option). LOAD DATA LOCAL INFILE does not support direct load, so do not add the APPEND / DIRECT(...) hint.
1. Create a data file on the client (example path /home/admin/client_csv/tbl1.csv, adjust the directory according to your actual client environment).
```
mkdir -p /home/admin/client_csv
vi /home/admin/client_csv/tbl1.csv
```
  The file content is as follows:
```
1,11
2,22
3,33
```
Use the local infile capability to connect to the database and switch to the target database (in this example, the test database under the mysql001 tenant).
```
obclient --local-infile -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -Dtest
```

Use LOAD DATA LOCAL INFILE to import data (not direct load).

Create table tbl2 (the table structure is the same as the full example of LOAD DATA INFILE above).

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether table tbl2 contains data; it shows the table is empty at this time.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use LOAD DATA LOCAL INFILE to import the client tbl1.csv into table tbl2.

Specify all columns of table tbl2 to import data.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify designated columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify a partition of table tbl2 to import data.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

Specify a subpartition of table tbl2 to import data.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

The direct load mode of default_load_mode does not apply to the LOCAL INFILE form. You can directly execute the following statement:
```
obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
```

Verify whether data has been imported into table tbl2.

obclient [test]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Prepare tbl1.csv locally on the client (for example, store it in /home/admin/client_csv/tbl1.csv). The content is the same as that in MySQL-compatible mode.

Note

Similar to MySQL-compatible mode, you must use the --local-infile option to connect. The INFILE path must be a local path on the client, and direct load hints are not supported.
1. Create a directory and a file on the client.
```
mkdir -p /home/admin/client_csv
vi /home/admin/client_csv/tbl1.csv
```
```
1,11
2,22
3,33
```
Connect to the database using the local infile capability (for example, tenant oracle001).
```
obclient --local-infile -h10.10.10.1 -P2881 -usys@oracle001 -p******
```

Use LOAD DATA LOCAL INFILE to import data (not direct load).

Create table tbl2 (the same as the full example of LOAD DATA INFILE in Oracle-compatible mode above).

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is data in table tbl2. The result shows the table is empty.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

Use LOAD DATA LOCAL INFILE to import the client-side tbl1.csv file into table tbl2.

Specify all columns of table tbl2 for data import.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify the columns to import from table tbl2. For example, specify columns col1 and col2.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify the partition to import from table tbl2.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

(Optional) Specify the subpartition to import from table tbl2.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

The direct load mode of default_load_mode does not apply to the LOCAL INFILE form. You can directly execute the following statement:
```
obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
```

Verify whether data has been imported into table tbl2.

obclient [SYS]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

References

Overview

Syntax

-- FROM FILES()
LOAD DATA /*+ [APPEND | DIRECT(need_sort,max_error,{'full'|'inc'|'inc_replace'})] parallel(N) */ FROM FILES(...) INTO TABLE table_name ...;

-- Server File or Object Storage Single File
LOAD DATA /*+ [APPEND | DIRECT(need_sort,max_error,{'full'|'inc'|'inc_replace'})] parallel(N) */ [REMOTE_OSS] INFILE 'file_name' INTO TABLE table_name ...;

-- Local client files (direct load is not supported)
LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name ...;

Classification of `LOAD DATA` by data reading location and syntax relationships

Path / Syntax	Data Location	Typical Application Scenarios	Syntax relationships and description	Limitations / Configuration / Additional information
`LOAD DATA FROM FILES(...)`	A server-accessible path described by the `FILES` clause (local directory, object storage URL, HDFS, etc., as specified in the manual).	Describes `LOCATION / FORMAT / PATTERN` in a single statement, and is suitable for batch scanning and error diagnosis of files such as CSV, ORC, and Parquet	Declares the set of files to be scanned through `FROM FILES(LOCATION = ..., FORMAT = (...), [PATTERN = ...])`. The server parses the files according to the format and writes them to the target table. It can be used together with clauses such as `LOG ERRORS` for error diagnosis during import.	In a direct load scenario, you can add hints such as `/+ direct(...) parallel(N) /` after the `LOAD DATA` keyword
`LOAD DATA ... INFILE 'path'` (without `LOCAL`)	A server file system path readable by the OBServer process	The file has been distributed to the database node. This form is often used together with `secure_file_priv`	When `LOCAL` is not specified and `INFILE` is a path on the OBServer, the server directly reads the file (or a set of files matched by a wildcard).	The path must be configured within the range allowed by `secure_file_priv`. Modifying this variable typically requires a local Unix Socket connection.
`LOAD DATA REMOTE_OSS INFILE 'oss://...' / 's3://...'`	The URL of a single file in an object storage bucket	Reads data from the URL of a single file in an object storage bucket	The syntax is written as `LOAD DATA REMOTE_OSS INFILE 'oss://...'/'s3://...'`. Similar to `LOCATION` in `FROM FILES` when it points to an object storage URL, it belongs to the category of "external large-scale data sources".	The examples in this topic are organized into three types: `FROM FILES`, `INFILE`, and `LOCAL INFILE`. If you use `REMOTE_OSS INFILE`, you can apply the `direct(...)`, `parallel(N)`, and partition clause syntax in the following sections to this syntax.
`LOAD DATA ... LOCAL INFILE 'path'`	A path on the client machine that initiates the connection	The file exists only on the client. The connection must enable `--local-infile`.	The keyword `LOCAL` indicates that the file is located on the client machine. After being read by the client, the file is uploaded to the server for parsing and writing.	Use `obclient --local-infile` (or an equivalent option) when connecting to enable local loading. `LOCAL INFILE` does not support direct load.

Note

For the scope of application, differences, and a more comprehensive description of direct load capabilities between full direct load and incremental direct load, see Overview of data import.

For complete definitions and syntax details of each parameter, see LOAD DATA Syntax (MySQL-compatible mode).

Examples

Full direct load (direct(..., 'full')) is typically used for the initial loading of an empty table or for a complete baseline import.

Incremental direct load (direct(..., 'inc') or 'inc_replace') is typically used for append loading based on existing data in the target table.

The following examples focus on full direct load. The hint syntax for incremental direct load is the same; the only difference is changing the third parameter to 'inc' or 'inc_replace'.

LOAD DATA FROM FILES

MySQL-compatible mode

Oracle-compatible mode

Log in to the server where the target OBServer node is located. Prepare the data files to be scanned in the /home/admin directory (LOCATION points to a server-accessible path; the files can be in formats such as CSV, ORC, or Parquet, and this example uses CSV).

Note

The LOCATION in FROM FILES must be accessible to the OBServer node. It is recommended that the directory and files be within the scope permitted by secure_file_priv (the same as for LOAD DATA INFILE).
1. Access the server where the OBServer node is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data file tbl1.csv (with content identical to the file in the LOAD DATA INFILE example above for easy comparison).
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to configure the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the server where the target OBServer node is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the tenant mysql001 via a local Unix Socket.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use LOAD DATA FROM FILES with the direct load hint to import the data.

Create table tbl2 (the table structure is the same as the full example in LOAD DATA INFILE above).

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is data in table tbl2. The result shows the table is empty.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use direct load to scan and import tbl1.csv into table tbl2.

If you need to import data using incremental direct load, you can refer to the following syntax examples:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

or:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

In the subsequent steps, we will continue with an example of full direct load.

Specify all columns of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 (col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify a partition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0, p1);

Specify a subpartition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1);

(Optional) Combine with LOG ERRORS for error diagnostics during import (only available when the current version supports this clause).

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2
LOG ERRORS REJECT LIMIT UNLIMITED;

Use the parameter default_load_mode to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [test]> LOAD DATA FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

Verify whether data has been imported into table tbl2.

obclient [test]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Log in to the machine where the target OBServer node is located, and prepare tbl1.csv in the /home/admin directory (the procedure and data content are the same as in MySQL-compatible mode).

Note

The LOCATION of FROM FILES is a server-side path. The value and case of TYPE in FORMAT are subject to the manual for the current tenant mode. This example uses lowercase csv, consistent with the example in the manual.
1. Access the machine where the OBServer node is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to configure the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the machine where the OBServer node to connect to is located.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to tenant oracle001 via a local Unix Socket connection.
```
obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use LOAD DATA FROM FILES with the direct load hint to import the data.

Create table tbl2 (with the same structure as the Oracle mode table in the full LOAD DATA INFILE example above).

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is data in table tbl2; it shows the table is empty at this point.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

Use direct load to scan and import tbl1.csv into table tbl2.

If you need to import data using incremental direct load, you can refer to the syntax examples:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

or:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

The following steps will continue with an example of full direct load.

Specify all columns of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 (col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify a partition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0, p1);

(Optional) Specify a subpartition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1);

Use the parameter default_load_mode to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [SYS]> LOAD DATA FROM FILES(
    LOCATION = '/home/admin',
    FORMAT = (TYPE = 'csv', FIELD_DELIMITER = ','),
    PATTERN = 'tbl1.csv'
) INTO TABLE tbl2;

Verify whether data has been imported into table tbl2.

obclient [SYS]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

LOAD DATA INFILE

MySQL-compatible mode

Oracle-compatible mode

Log in to the server where the target OBServer node resides and create a test data file in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database can only load input files located locally on the OBServer node. Therefore, you must copy the file to an OBServer node before importing it.
1. Access the server where the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create the test data file tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to specify the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the server where the target OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the mysql001 tenant via a local Unix Socket connection.
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use the LOAD /*+ DIRECT */ DATA statement to import the data.

Create table tbl2.

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),                           
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is any data in table tbl2. The result shows the table is empty.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use direct load to import the data from the tbl1.csv file into table tbl2.

If you need to import data using incremental direct load, you can refer to the following syntax examples:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

or:

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

The subsequent steps will continue with an example of full direct load.

Specify all columns of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify any columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify a partition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

Specify a subpartition of table tbl2 to import data.

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

Use the default_load_mode parameter to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [test]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

Verify whether data has been imported into table tbl2.

obclient [test]>  SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Log in to the server where the target OBServer node resides, and create test data named tbl1 in the /home/admin directory.

Note

The LOAD DATA statement in OceanBase Database can only load input files located locally on the OBServer node. Therefore, you must copy the file to an OBServer node before importing it.
1. Access the server where the OBServer node resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Create a test dataset named tbl1.csv.
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
Set the import file path.

Set the system variable secure_file_priv to configure the accessible paths for import or export files.

Notice

For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix Socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.
1. Log in to the server where the OBServer node you want to connect to resides.
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. Execute the following command to connect to the tenant oracle001 via a local Unix Socket connection.
```
obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
```
3. Set the import path to /home/admin.
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

After reconnecting to the database, use the LOAD /*+ DIRECT */ DATA statement to import the data.

Create a table named tbl2.

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether the tbl2 table contains data. At this point, it shows the table is empty.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

Use direct load to import the data from the tbl1.csv file into the tbl2 table.

If you need to import data using incremental direct load, you can refer to the following syntax example:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

or:

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'inc_replace') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

In the following steps, we will continue with an example of importing data using full direct load.

Specify all columns from table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify any columns from table tbl2 to import data. For example, specify columns col1 and col2.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify a partition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

(Optional) Specify a subpartition of table tbl2 to import data.

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

Use the parameter default_load_mode to import data.

Set the value of default_load_mode to FULL_DIRECT_WRITE.

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

Do not specify hints for the LOAD DATA statement.

obclient [SYS]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

Verify whether data has been imported into table tbl2.

obclient [SYS]>  SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

LOAD DATA LOCAL INFILE

MySQL-compatible mode

Oracle-compatible mode

Prepare the data file on the client machine where obclient is running (the path is the local client path, do not confuse it with the path on the OBServer). The file content is the same as the example above, still named tbl1.csv.

Note

LOCAL INFILE means the client reads a local file and uploads it to the server; the connection command must include --local-infile (or an equivalent option). LOAD DATA LOCAL INFILE does not support direct load, so do not add the APPEND / DIRECT(...) hint.
1. Create a data file on the client (example path /home/admin/client_csv/tbl1.csv, adjust the directory according to your actual client environment).
```
mkdir -p /home/admin/client_csv
vi /home/admin/client_csv/tbl1.csv
```
  The file content is as follows:
```
1,11
2,22
3,33
```
Use the local infile capability to connect to the database and switch to the target database (in this example, the test database under the mysql001 tenant).
```
obclient --local-infile -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -Dtest
```

Use LOAD DATA LOCAL INFILE to import data (not direct load).

Create table tbl2 (the table structure is the same as the full example of LOAD DATA INFILE above).

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether table tbl2 contains data; it shows the table is empty at this time.
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

Use LOAD DATA LOCAL INFILE to import the client tbl1.csv into table tbl2.

Specify all columns of table tbl2 to import data.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify designated columns of table tbl2 to import data. For example, specify columns col1 and col2.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify a partition of table tbl2 to import data.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

Specify a subpartition of table tbl2 to import data.

obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

The direct load mode of default_load_mode does not apply to the LOCAL INFILE form. You can directly execute the following statement:
```
obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
```

Verify whether data has been imported into table tbl2.

obclient [test]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

Prepare tbl1.csv locally on the client (for example, store it in /home/admin/client_csv/tbl1.csv). The content is the same as that in MySQL-compatible mode.

Note

Similar to MySQL-compatible mode, you must use the --local-infile option to connect. The INFILE path must be a local path on the client, and direct load hints are not supported.
1. Create a directory and a file on the client.
```
mkdir -p /home/admin/client_csv
vi /home/admin/client_csv/tbl1.csv
```
```
1,11
2,22
3,33
```
Connect to the database using the local infile capability (for example, tenant oracle001).
```
obclient --local-infile -h10.10.10.1 -P2881 -usys@oracle001 -p******
```

Use LOAD DATA LOCAL INFILE to import data (not direct load).

Create table tbl2 (the same as the full example of LOAD DATA INFILE in Oracle-compatible mode above).

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

Query whether there is data in table tbl2. The result shows the table is empty.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

Use LOAD DATA LOCAL INFILE to import the client-side tbl1.csv file into table tbl2.

Specify all columns of table tbl2 for data import.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Specify the columns to import from table tbl2. For example, specify columns col1 and col2.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

(Optional) Specify the partition to import from table tbl2.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

(Optional) Specify the subpartition to import from table tbl2.

obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

The direct load mode of default_load_mode does not apply to the LOCAL INFILE form. You can directly execute the following statement:
```
obclient [SYS]> LOAD DATA LOCAL INFILE '/home/admin/client_csv/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
```

Verify whether data has been imported into table tbl2.

obclient [SYS]> SELECT * FROM tbl2;

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

The result shows that data has been imported into table tbl2.

OceanBase

Customer Stories

Documentation

Import data by using the LOAD DATA statement

Overview

Syntax

Classification of LOAD DATA by data reading location and syntax relationships

Note

Examples

LOAD DATA FROM FILES

Note

Notice

Note

Notice

LOAD DATA INFILE

Note

Notice

Note

Notice

LOAD DATA LOCAL INFILE

Note

Note

References

Import data by using the LOAD DATA statement

Overview

Syntax

Classification of LOAD DATA by data reading location and syntax relationships

Note

Examples

LOAD DATA FROM FILES

Note

Notice

Note

Notice

LOAD DATA INFILE

Note

Notice

Note

Notice

LOAD DATA LOCAL INFILE

Note

Note

References

Classification of `LOAD DATA` by data reading location and syntax relationships

Classification of `LOAD DATA` by data reading location and syntax relationships