Import data by using the LOAD DATA statement

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

Scenarios

You can use the LOAD DATA statement to import a CSV text file in the following process:

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 sent.

3. Insert data.

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

LOAD DATA

Obtain the privilege to execute the LOAD DATA statement

You need to perform the following steps to grant the access to a file on the server:

1. Set the directory where the security file resides to the directory where the file is located or to null (indicating that no check is required).

   Notice

   For security reasons, the following SQL statement can only be executed locally but cannot be executed on a remote OBClient. For more information, see secure_file_priv.

   SET GLOBAL SECURE_FILE_PRIV = "";
   

2. Grant privileges to the user.

   Execute the following statement to grant the FILE privilege to the user specified by USER_NAME, which is the user who needs to execute the LOAD DATA statement.

   GRANT FILE ON *.* TO user_name;
   

Syntax of LOAD DATA

LOAD DATA
    [/*+ parallel(N)*/]
    INFILE 'file_name'
    [REPLACE | IGNORE] // The REPLACE and IGNORE options are applicable to MySQL tenants only.
    INTO TABLE tbl_name
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']+
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_var
        [, col_name_var] ...)]

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, input file name, destination table name, DOP, LOAD DATA command 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

Examples

[root@***** /home/admin]
$cat student.sql
1,"lin",98
2,"hei",90
3,"ali",95

[root@***** /home/admin]
$obclient -h10.0.0.0 -uroot@MySQL -P2881 -p******

obclient> create table student_1 (ID int primary key,name varchar(128),score int);

obclient>  LOAD DATA INFILE '/home/admin/student.sql' INTO TABLE student_1
             FIELDS TERMINATED BY ',' ENCLOSED BY '"'  
             LINES TERMINATED BY '\n'
             (ID,name,score);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

obclient> select * from student_1
+----+------+-------+
| ID | name | score |
+----+------+-------+
|  1 | lin  |    98 |
|  2 | hei  |    90 |
|  3 | ali  |    95 |
+----+------+-------+
3 rows in set (0.00 sec)