Import data by using the LOAD DATA statement|V5.0.1|OceanBase Database|docs|OceanBase Documentations

Overview

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

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 and syntax relationship of `LOAD DATA` based on data read location

Path / syntax	Data location	Typical use cases	Syntax relationship and description	Restrictions / configuration / notes
`LOAD DATA FROM FILES(...)`	Server-accessible paths described in the `FILES` clause (local directory, object storage URL, HDFS, etc., subject to the manual).	Single statement describes `LOCATION / FORMAT / PATTERN`, suitable for batch scanning and error diagnosis of CSV, ORC, and Parquet files.	Declare the set of files to be scanned via `FROM FILES(LOCATION = ..., FORMAT = (...), [PATTERN = ...])`. The server parses the declaration according to the format and writes it to the target table. Can be used together with `LOG ERRORS` for error diagnosis during import.	In a direct load scenario, you can add `/+ direct(...) parallel(N) /` and other hints after the `LOAD DATA` keyword.
`LOAD DATA ... INFILE 'path'` (No `LOCAL`)	Server file system path accessible by the OBServer process.	The file has been distributed to the database node or shared storage. This 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 this file (or a set of files matched by the wildcard).	Configure the path in the `secure_file_priv` within the allowed range. Modifying this variable typically requires execution through a local Unix Socket connection.
`LOAD DATA REMOTE_OSS INFILE 'oss://...' / 's3://...'`	URL of a single file in the object bucket.	Read data from a single file URL in an object bucket.	The syntax is written as `LOAD DATA REMOTE_OSS INFILE 'oss://...'/'s3://...'`. Together with `LOCATION` in `FROM FILES`, object storage URLs also fall under the category of "external large-scale data sources".	This topic organizes examples into three types: `FROM FILES`, `INFILE`, and `LOCAL INFILE`. If you use `REMOTE_OSS INFILE`, you can apply the `direct(...)`, `parallel(N)`, and partitioning clause syntax described below.
`LOAD DATA ... LOCAL INFILE 'path'`	Path on the client where the connection is initiated.	The file exists only on the client. The connection must be enabled with `--local-infile`.	The keyword `LOCAL` indicates that the file is located on the client's local machine; after being read by the client, it is uploaded to the server for parsing and writing.	Use `obclient --local-infile` (or its 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 the 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 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 target table status, and applicable limitations. For details about the differences, application scenarios, and limitations between full direct load and incremental direct load, see Data import overview.

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

LOAD DATA FROM FILES

MySQL-compatible mode

Oracle-compatible mode

Log in to the server where the OBServer node to be connected resides. Prepare the data files to be scanned in the /home/admin directory (LOCATION points to a server-accessible path, which can be CSV, ORC, or Parquet files in this directory. This example uses CSV files).

Note

The LOCATION in FROM FILES must be a path accessible to the OBServer. It is recommended that the directory and files be within the scope allowed by secure_file_priv (the same as for LOAD DATA INFILE).
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 (with content identical to the file in the preceding LOAD DATA INFILE example 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 OBServer node to be connected resides.
```
[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 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 LOAD DATA FROM FILES with the direct load hint to import the data.

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

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 the tbl2 table contains data; it will show the table is empty at this point.
```
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 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;

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) */ 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 server where the OBServer node to be connected resides, and prepare tbl1.csv in the /home/admin directory (the procedure and data content are the same as for MySQL-compatible mode).

Note

The LOCATION of FROM FILES is the server-side path; the value and case of TYPE in FORMAT follow the current tenant mode manual. This example uses the lowercase csv consistent with the manual example.
1. Access the server where the OBServer node resides.
```
[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 server where the OBServer node to be connected 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 LOAD DATA FROM FILES with the direct load hint to import the data.

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

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 table tbl2 contains data; 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 subsequent 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 test data 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.

Configure 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 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 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 table tbl2 contains data. 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 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 following 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 specified 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 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 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 OBServer node to be connected resides, and create a test dataset 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.

Configure 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 OBServer node to be connected 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; it shows the table is empty at this point.
```
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 the explanation using an example of full direct load.

Specify to import all columns from table tbl2.

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

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.

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 enter the target database (example: the test database under tenant mysql001).
```
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 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 default_load_mode direct load mode is not applicable for the LOCAL INFILE format; 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, at /home/admin/client_csv/tbl1.csv, with the same content as in MySQL-compatible mode).

Note

Similar to MySQL-compatible mode, you must use the --local-infile connection option. The INFILE path must be a local directory on the client and does not support the direct load hint.
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 (non-direct load).

Create table tbl2 (identical to 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; it shows the table is empty.
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

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

Specify to import all columns of table tbl2.

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 LOCAL INFILE format does not support the direct load mode of default_load_mode; 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.

Overview

Syntax

Classification and syntax relationship of LOAD DATA based on data read location

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 and syntax relationship of `LOAD DATA` based on data read location