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.

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.

   Log on to the tenant as an administrator (root for a MySQL tenant and SYS for an Oracle tenant), and set the system variable secure_file_priv to specify the path to be accessed during file import or export.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can use only the local client 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. Use the OBClient to directly connect to the mysql001 tenant.

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

      Alternatively, 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
      

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
   

More information

Import data by using the LOAD DATA statement