Import data in bypass mode by using the LOAD DATA statement

In the LOAD DATA statement, the direct keyword is used as a hint to indicate whether bypass import is used.

Limitations

• You cannot execute two statements to write the same table at a time because a lock is added to the table during import.
• The LOAD DATA statement cannot be used in triggers.
• The LOAD DATA statement supports loading LOBs but the loading performance is poor. Therefore, LOBs are still loaded to the corresponding paths by using transactions.
• The LOAD DATA statement cannot be executed in a multi-row transaction.

Considerations

To speed up data import, OceanBase Database utilizes a parallel design for LOAD DATA operations. During this process, data to be imported is divided into multiple subtasks, which are executed in parallel. Each subtask is processed as an independent transaction, and the execution sequence is random. Therefore, the following considerations should be noted:

• The atomicity of the overall data import is not guaranteed.

• For tables without primary keys, the sequence of data writes may not match the sequence of data in the file.

Syntax

LOAD DATA /*+ direct(need_sort,max_error) parallel(N) */ INFILE 'file_name' ...

For more information about the syntax of the LOAD DATA statement, see LOAD DATA.

The parameters are described as follows:

Examples

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

   Note

   In OceanBase Database, the LOAD DATA statement can load only local input files on OBServer nodes. Therefore, you must copy the to-be-imported files to an OBServer node before the import.

   [xxx@xxx /home/admin]# ssh admin@10.10.10.1
   
   [admin@xxx /home/admin]# vi tbl1.csv
   1,11
   2,22
   3,33
   

2. Set the path where the file to be imported is located.

   Set the system variable secure_file_priv to specify the path that can be accessed for file import or export.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can only connect to the database through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.

   1. Log on to the server where the OBServer node to connect to resides.

      [xxx@xxx /home/admin]# ssh admin@10.10.10.1
      

   2. Execute the following statement to connect to the mysql001 tenant through 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
      

3. After you reconnect to the database, execute the LOAD /*+ APPEND */ DATA statement to import data.

   obclient [test]> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 INT);
   Query OK, 0 rows affected
   
   obclient [test]> SELECT * FROM tbl1;
   Empty set
   
   obclient [test]> LOAD DATA /*+ direct(true,1024) parallel(16) */INFILE '/home/admin/tbl1.csv' INTO TABLE tbl1 FIELDS TERMINATED BY ',';
   Query OK, 3 rows affected
   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
   
   obclient [test]> SELECT * FROM tbl1;
   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |   11 |
   |    2 |   22 |
   |    3 |   33 |
   +------+------+
   3 rows in set
   

References

Import data by using the LOAD DATA statement