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 adopts a parallel design for LOAD DATA operations. During the process, data to be imported is divided into multiple subtasks, which are executed in parallel. Each subtask is processed as an independent transaction in a random order. Therefore, observe the following considerations:

• The atomicity of the overall data import operation is not guaranteed.
• For a table without a primary key, data may be written to the table in an order different from that in the source 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.

Parameters

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 connect to the database only through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.

   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

• For more information about the LOAD DATA statement, see Import data by using the LOAD DATA statement.
• For more information about how to connect to OceanBase Database, see Overview.
• For more information about how to drop a table, see Drop a table.