Import data by using the LOAD DATA statement

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

Limitations

A table with triggers is not allowed to use the LOAD DATA statement.

Considerations

OceanBase Database optimizes the data import rate of the LOAD DATA statement by using parallel processing technology. The statement divides data into multiple subtasks for parallel execution. Each subtask is an independent transaction, and the execution order of the subtasks is not fixed. Therefore:

• The global atomicity of the data import process cannot be guaranteed.
• For tables without a primary key, the data write order may differ from the order in the original file.

Use cases

You can use LOAD DATA to import CSV files. The entire import process is as follows:

1. Parse the file.

   OceanBase reads data from the file based on the specified file name. It then parses the file data in parallel or in series based on the specified parallelism.

2. Distribute the data.

   Once OceanBase parses the data, the data is distributed to the corresponding OBServer node. The distribution is done based on the locations of data partitions.

3. Insert the data.

   Once a data row is received by the destination OBServer node, a local INSERT statement is executed to insert the data row into the corresponding partition.

LOAD DATA syntax

For more information about the LOAD DATA syntax, see LOAD DATA (MySQL mode) and LOAD DATA (Oracle mode).

Obtain the privilege to execute LOAD DATA

Before you execute the LOAD DATA statement, you must obtain the required privilege. Perform the following steps to grant the required privilege:

1. Grant the FILE privilege to the user.

   Here is an example:

   Use the following syntax to grant the FILE privilege:

   GRANT FILE ON *.* TO user_name;
   

   Here, user_name is the username of the user to which you want to grant the FILE privilege.

2. Grant other necessary privileges.

   • In MySQL mode, you need to grant the INSERT privilege on the corresponding table to the user.

     Here is an example:

     Use the following syntax to grant the INSERT privilege:

     GRANT INSERT ON database_name.tbl_name TO user_name;
     

     Here, database_name is the name of the database, tbl_name is the name of the table, and user_name is the username of the user to which you want to grant the INSERT privilege.

   • In Oracle mode, you need to grant the CREATE SESSION privilege to the user.

     Here is an example:

     Use the following syntax to grant the CREATE SESSION privilege:

     GRANT CREATE SESSION TO user_name;
     

     Here, user_name is the username of the user to which you want to grant the privilege.

Examples

Import data from a server file

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. Go to insert 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. Set the path of the import file.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local Unix socket to execute the SQL statement that modifies the global variable. 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. 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:

   • Specify the path and name of the file to be loaded as /home/admin/test_data/student.sql.
   • Specify the name of the target table to which the data is to be loaded as student.
   • Specify the field separator in the data file as a comma.
   • Specify that the fields (of the character type) in the data file are enclosed in double quotation marks (").
   • Specify that the rows in the data file are terminated with a line break.
   • Specify the mappings between the columns in the source file and those in the target table. The first column in the source 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. View table information.

   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 client (local) file

Use the following procedure to import data from a local file to a table in OceanBase Database.

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

   Here is an example:

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

   vi test_tbl1.csv
   

2. Enter edit 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 Client (OBClient) to connect to OceanBase Database. Add the --local-infile parameter in 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 the database.

4. Create a test table.

   Here is an example:

   CREATE TABLE test_tbl1(col1 INT,col2 INT);
   

5. In the client, execute the LOAD DATA LOCAL INFILE statement to load the local data 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. View the table information.

   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 will be rolled back, and the LOAD DATA statement will generate a log file named obloaddata.log.<XXXXXX> in the log subdirectory of the observer process installation path. The following is an example of a log file content. The log contains basic information about the task generated by the LOAD DATA statement, including the tenant name, input file name, target table name, degree of parallelism, used LOAD DATA command, and specific error information for each line.

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

Exception diagnostics

In OceanBase Database V4.3.5 BP2, the LOAD DATA statement for importing data from a URL now supports specifying an error diagnostics mode. This mode allows the system to record failed rows. In the current version, these rows are stored in warning buffer. In MySQL-compatible mode, you can use the show warnings statement to view these failed rows, with a maximum of 64 rows recorded. This prevents the entire operation from being terminated due to the first error. Combined with the REJECT LIMIT clause, you can specify the maximum number of allowable error rows.

For more information about the syntax of the LOAD DATA statement for importing data from a URL with error diagnostics enabled, see LOAD DATA (MySQL-compatible mode) or LOAD DATA (Oracle-compatible mode).

References

• For more information about how to use the LOAD DATA statement to import data through direct load, see Direct load.
• For more information about how to connect to a database, see Overview of connection methods.
• For more information about how to drop a table, see Drop a table.