Use the LOAD DATA statement to import data

OceanBase Database allows you to use the LOAD DATA statement to import data from external files to database tables.

Limitations

Do not use the LOAD DATA statement on tables with triggers.

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.

Scenarios

You can use the LOAD DATA statement to import a CSV file as follows:

1. Parse the file.

   OceanBase Database reads data from a file based on the file name that you entered and determines whether to perform parallel or serial parsing of data from the input file based on the degree of parallelism (DOP) that you specified.

2. Distribute data.

   OceanBase Database is a distributed database system. Data of each partition may be distributed across different OBServer nodes. The LOAD DATA statement is used to process the parsed data and determine the OBServer node to which the data is to be sent.

3. Insert data.

   After the target OBServer node receives the data, it executes the INSERT statement to insert the data into the corresponding partition.

Syntax

For more information about the LOAD DATA statement, see LOAD DATA (MySQL mode) or LOAD DATA (Oracle mode).

Obtain the privileges to execute the LOAD DATA statement

Before you execute the LOAD DATA statement, you must obtain the required privileges. The procedure for granting execution privileges is as follows:

1. Grant the FILE privilege.

   Execute the following statement to grant the FILE privilege to a user:

   GRANT FILE ON *.* TO user_name;
   

   Here, user_name is the user who needs to execute the LOAD DATA statement.

2. Grant other necessary privileges.

   • The INSERT privilege on the corresponding table is required in MySQL mode.

     Execute the following statement to grant the INSERT privilege to a user:

     GRANT INSERT ON database_name.tbl_name TO user_name;
     

     Here, database_name specifies the database name, tbl_name specifies the table name, and user_name specifies the user who needs to execute the LOAD DATA statement.

   • The CREATE SESSION privilege is required in Oracle mode.

     Execute the following statement to grant the CREATE SESSION privilege to a user:

     GRANT CREATE SESSION TO user_name;
     

     Here, user_name is the username of the user to which the privilege is to be granted.

Examples

Import data from a file on an OBServer node

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

   Here is an example:

   ssh admin@10.10.10.1
   

2. Create test data in the /home/admin/test_data directory.

   Here is an example:

   Run the following command to write a script named student.sql:

   vi student.sql
   

3. Enter the editing mode and add test data.

   Here is an example:

   Press the i or Insert key to enter the insert mode of the vi editor and add the following content:

   1,"lin",98
   2,"hei",90
   3,"ali",95
   

4. Specify the path of the file to import.

   Notice

   For security reasons, you can only connect to the database through a local socket to execute the SQL statement for setting secure_file_priv. For more information, see secure_file_priv.

   Here is an example:

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

      ssh admin@10.10.10.1
      

   2. Run the following command to connect to the mysql001 tenant through a local Unix socket:

      Here is an example:

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

   3. Set the file directory to /, which indicates that any path can be accessed.

      SET GLOBAL SECURE_FILE_PRIV = "/";
      

5. Reconnect to the database.

   Here is an example:

   obclient -h127.0.0.1 -P2881 -utest_user001@mysql001 -p****** -A
   

6. Create a test table.

   Here is an example:

   Execute the following statement to create a test table named student:

   obclient [test]> CREATE TABLE student (id INT, name VARCHAR(50), score INT);
   

7. Use the LOAD DATA statement to import data.

   Here is an example:

   Execute the following LOAD DATA statement to load data from the specified file to a data table. In this statement:

   • Set the path and name of the file to be loaded to /home/admin/test_data/student.sql.
   • Set the name of the target table to which the data is to be loaded to student.
   • Specify comma (,) as the delimiter for fields in the data file.
   • Specify to enclose fields of the character type in the data file by using double quotation marks (").
   • Specify to use line breaks as the end characters of lines in the data file.
   • Specify the mappings between columns in the source data file and those in the target table. The first column in the data file will be mapped to the id column in the target table, the second column to the name column, and the third column to the score column.

   obclient [test]> LOAD DATA INFILE '/home/admin/test_data/student.sql' 
   INTO TABLE student
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'  
   LINES TERMINATED BY '\n'
   (id,name,score);
   

   The return result is as follows:

   Query OK, 3 rows affected
   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
   

8. Query data in the target table.

   Here is an example:

   obclient [test]> SELECT * FROM student;
   

   The return result is as follows:

   +------+------+-------+
   | id   | name | score |
   +------+------+-------+
   |    1 | lin  |    98 |
   |    2 | hei  |    90 |
   |    3 | ali  |    95 |
   +------+------+-------+
   3 rows in set
   

Import data from a local file on the client

Perform the following steps to import data from a local file to a table in OceanBase Database:

1. Create test data in the local directory /home/admin/test_data.

   Here is an example:

   Run the following command to write a script named test_tbl1.csv:

   vi test_tbl1.csv
   

2. Enter the editing mode and add test data.

   Here is an example:

   Press the i or Insert key to enter the insert mode of the vi editor and add the following content:

   1,11
   2,22
   3,33
   

3. Start the client.

   Here is an example:

   Run the following command to use OceanBase Command-Line Client (OBClient) to connect to OceanBase Database. Add the --local-infile parameter to the command to enable the feature for loading data from local files.

   obclient --local-infile -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -Dtest
   

   Notice

   To use the LOAD DATA LOCAL INFILE feature, use OBClient V2.2.4 or later. If you do not have OBClient of the required version, you can also use a MySQL client to connect to OceanBase Database.

4. Create a test table.

   Here is an example:

   CREATE TABLE test_tbl1(col1 INT,col2 INT);
   

5. Execute the LOAD DATA LOCAL INFILE statement on the client to load data from a local file.

   Here is an example:

   obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/test_data/test_tbl1.csv' INTO TABLE test_tbl1 FIELDS TERMINATED BY ',';
   

   The return result is as follows:

   Query OK, 3 rows affected
   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
   

6. Query data in the target table.

   Here is an example:

   obclient [test]> SELECT * FROM test_tbl1;
   

   The return result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |   11 |
   |    2 |   22 |
   |    3 |   33 |
   +------+------+
   3 rows in set
   

Exception handling

Log files

If an error occurs during the import, the INSERT statement is rolled back, and the LOAD DATA statement generates a log file named obloaddata.log <XXXXXX> in the log subdirectory of the installation directory of the observer process. The following example shows the content of a log file. The log contains the basic information about the task generated by the LOAD DATA statement, including the tenant name, source file name, target table name, DOP, LOAD DATA statement used, and error details in lines.

Tenant name:    mysql
File name:  /home/admin/a.csv
Into table: `test`.`t`
Parallel:   1
Batch size: 1000
SQL trace:  YD7A20BA65670-0005AADAAA3C****
Start time: 2020-07-29 21:08:13.073741
Load query:
load data infile '/home/admin/test.csv' into table t fields terminated by ',' lines terminated by '\n'
Row ErrCode ErrMsg
1   1062    Duplicated primary key
2   1062    Duplicated primary key

References

• For more information about how to use the LOAD DATA statement to import data in direct load mode, see Use the LOAD DATA statement to import data in direct load mode.
• For more information about how to connect to a database, see Overview.
• For more information about how to drop a table, see Drop a table.