Overview
This topic describes how to use the LOAD DATA statement in OceanBase AI Database to write external data into internal tables, and to enable direct load when conditions are met (for example, by using hints such as APPEND or direct(...)) or by configuring the tenant-level parameter default_load_mode, to improve throughput for 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 ...;
-- Local client file (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 application scenarios |
Syntax and description |
Limitations / configuration / additional information |
|---|---|---|---|---|
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. This clause 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 readable 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 ... 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. The client reads the file and then uploads it 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 Data import overview.
For the complete definitions and syntax details of each parameter, see LOAD DATA.
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 the 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
Log in to the server where the OBServer node to be connected resides. Prepare the data files to be scanned in the
/home/admindirectory (LOCATIONpoints to a server-accessible path), which can be CSV, ORC, or Parquet files in this directory. This example uses CSV files.Note
The
LOCATIONinFROM FILESmust be a path accessible to the OBServer. It is recommended that the directory and files be within the scope permitted bysecure_file_priv(the same as forLOAD DATA INFILE).Access the server where the OBServer node resides.
[xxx@xxx /home/admin]# ssh admin@10.10.10.1Create the test data file
tbl1.csv(with content identical to the file in the precedingLOAD DATA INFILEexample 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_privto 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.Log in to the server where the OBServer node to be connected resides.
[xxx@xxx /home/admin]# ssh admin@10.10.10.1Execute the following command to connect to the tenant
mysql001via a local Unix Socket connection.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******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 FILESwith the direct load hint to import the data.Create table
tbl2(with the same structure as the full example in the precedingLOAD DATA INFILE).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 affectedQuery whether there is data in table
tbl2. The result shows the table is empty.obclient [test]> SELECT * FROM tbl2; Empty setUse direct load to scan and import
tbl1.csvinto tabletbl2.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;The subsequent steps will continue with an example of full direct load.
Specify all columns of table
tbl2to 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: 0Specify columns of table
tbl2to import data. For example, specify columnscol1andcol2.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: 0Specify a partition of table
tbl2to 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
tbl2to 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 ERRORSfor 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_modeto import data.Set the value of
default_load_modetoFULL_DIRECT_WRITE.obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';Do not specify hints for the
LOAD DATAstatement.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 setThe result shows that data has been imported into table
tbl2.
LOAD DATA INFILE
Log in to the server where the OBServer node to be connected resides, and create test data in the
/home/admindirectory.Note
The
LOAD DATAstatement in OceanBase AI Database only supports loading input files local to the OBServer node. Therefore, you must copy the file to an OBServer node before importing it.Access the server where the OBServer node is located.
[xxx@xxx /home/admin]# ssh admin@10.10.10.1Create 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_privto 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.Log in to the server where the OBServer node to be connected resides.
[xxx@xxx /home/admin]# ssh admin@10.10.10.1Execute the following command to connect to the tenant
mysql001via a local Unix Socket.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******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 */ DATAstatement 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 affectedQuery whether table
tbl2contains data. The result shows the table is empty.obclient [test]> SELECT * FROM tbl2; Empty setUse direct load to import the data from the
tbl1.csvfile into tabletbl2.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 ',';In the subsequent steps, we will continue with an example of full direct load.
Specify all columns of table
tbl2to 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: 0Specify any columns of table
tbl2to import data. For example, specify columnscol1andcol2.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: 0Specify a partition of table
tbl2to import.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
tbl2to import.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_modeto import data.Set the value of
default_load_modetoFULL_DIRECT_WRITE.obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';Do not specify hints for the
LOAD DATAstatement.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 setThe result shows that data has been imported into table
tbl2.
LOAD DATA LOCAL INFILE
Prepare the data file on the client where obclient is running. This path is a client-side local path, which is different from the path on the OBServer. The file content is the same as in the previous example, and the file is still named
tbl1.csv.Note
LOCAL INFILEspecifies that the client reads a local file and uploads it to the server. The connection command must include the--local-infileoption (or its equivalent).LOAD DATA LOCAL INFILEdoes not support direct load, so do not add theAPPENDorDIRECT(...)hint.Create a data file on the client. In this example, the path is
/home/admin/client_csv/tbl1.csv; adjust the directory based on your actual client environment.mkdir -p /home/admin/client_csv vi /home/admin/client_csv/tbl1.csvThe file content is as follows:
1,11 2,22 3,33
Connect to the database with the local infile capability enabled and access the target database (in this example, the
testdatabase under themysql001tenant).obclient --local-infile -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -DtestUse
LOAD DATA LOCAL INFILEto import data (without direct load).Create table
tbl2(the table structure is the same as the full example in the previousLOAD DATA INFILEsection).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 affectedQuery whether there is data in table
tbl2. The result shows the table is empty.obclient [test]> SELECT * FROM tbl2; Empty setUse
LOAD DATA LOCAL INFILEto import the client filetbl1.csvinto tabletbl2.Specify all columns of table
tbl2to 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: 0Specify columns of table
tbl2to import data. For example, specify columnscol1andcol2.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: 0Specify a partition of table
tbl2to 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
tbl2to 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
LOCAL INFILEformat does not use the direct load mode specified bydefault_load_mode. 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 setThe result shows that data has been imported into table
tbl2.