使用 LOAD DATA 语句导入数据

OceanBase 数据库支持通过 LOAD DATA 命令加载外部文件的数据到数据库表中。

使用限制

带有触发器（Trigger）的表禁止使用 LOAD DATA 语句。

注意事项

OceanBase 数据库通过并行处理技术优化 LOAD DATA 的数据导入速率。该操作将数据分成多个子任务并行执行，每个子任务都视为独立的事务，执行顺序是不固定的。因此：

• 不能保证数据导入过程的全局原子性。
• 对于没有主键的表，数据写入顺序可能与原文件中的顺序不同。

使用场景

LOAD DATA 目前可以对 CSV 格式的文本文件进行导入，整个导入的过程如下：

1. 解析文件。

   OceanBase 会根据用户输入的文件名，读取文件中的数据，并且根据用户输入的并行度来决定并行或者串行解析输入文件中的数据。

2. 分发数据。

   由于 OceanBase 是分布式数据库系统，各个分区的数据可能分布在各个不同的 OBServer 节点上，LOAD DATA 会对解析出来的数据进行计算，决定数据需要被发送到哪个 OBServer 节点。

3. 插入数据。

   当目标 OBServer 节点收到了发送过来的数据之后，在本地执行 INSERT 操作把数据插入到对应的分区当中。

LOAD DATA 语法

有关 LOAD DATA 语法的详细信息，请参见 LOAD DATA（MySQL 模式） 和 LOAD DATA（Oracle 模式）。

获取 LOAD DATA 执行权限

在执行 LOAD DATA 语句之前，您需要先获得相应的权限。以下是授予执行权限的操作步骤：

1. 对用户进行授权 FILE 权限。

   示例如下：

   要为用户授予 FILE 权限，可以使用以下命令：

   GRANT FILE ON *.* TO user_name;
   

   其中，user_name 是需要执行 LOAD DATA 命令的用户。

2. 授予其他必要权限。

   • MySQL 模式需要拥有对应表的 INSERT 权限。

     示例如下：

     要为用户授予 INSERT 权限，可以使用以下命令格式：

     GRANT INSERT ON database_name.tbl_name TO user_name;
     

     其中，database_name 是数据库名称，tbl_name 是表名，user_name 是需要执行 LOAD DATA 命令的用户。

   • Oracle 模式需要拥有 CREATE SESSION 权限。

     示例如下：

     要为用户授予 CREATE SESSION 权限，可以使用以下命令格式：

     GRANT CREATE SESSION TO user_name;
     

     其中，user_name 是要授予权限的用户名。

示例

从服务器端文件导入数据

1. 登录到要连接 OBServer 节点所在的机器。

   示例如下：

   ssh admin@10.10.10.1
   

2. 在 /home/admin/test_data 目录下创建测试数据。

   示例如下：

   执行以下命令，编写一个名为 student.sql 的脚本。

   vi student.sql
   

3. 进入编辑模式并添加测试数据。

   示例如下：

   按下 i 键或者 Insert 键进入 vi 编辑器的插入模式，在插入模式下添加以下内容。

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

4. 设置导入的文件路径。

   注意

   由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Socket 连接数据库执行修改该全局变量的 SQL 语句。更多信息，请参见 secure_file_priv。

   示例如下：

   1. 登录到要连接 OBServer 节点所在的机器。

      ssh admin@10.10.10.1
      

   2. 执行以下命令，通过本地 Unix Socket 连接方式连接租户 mysql001。

      示例如下：

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

   3. 设置为文件所在目录为 /，表示没有限制，任意路径均可访问。

      SET GLOBAL SECURE_FILE_PRIV = "/";
      

5. 重新连接数据库。

   示例如下：

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

6. 创建测试表。

   示例如下：

   执行下面 SQL 语句，创建测试表 student。

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

7. 使用 LOAD DATA 语句导入数据。

   示例如下：

   使用以下 LOAD DATA 语句将数据从文件加载到数据库表中，其中：

   • 指定要加载的文件的路径和文件名为 /home/admin/test_data/student.sql。
   • 指定要加载数据的目标表名为 student。
   • 指定数据文件中的字段分隔符为逗号。
   • 指定数据文件中的字段（字符类型）将使用双引号封闭。
   • 指定数据文件中的行将使用换行符作为结束符。
   • 指定要加载的数据文件中的列与目标表中的列的映射关系。数据文件中的第一列将映射到目标表的 id 列，第二列映射到 name 列，第三列映射到 score 列。

   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);
   

   返回结果如下：

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

8. 查看表信息。

   示例如下：

   obclient [test]> SELECT * FROM student;
   

   返回结果如下：

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

从客户端（本地）文件导入数据

使用以下语句，从本地文件导入数据至 OceanBase 数据库表中。

1. 在本地 /home/admin/test_data 目录下创建测试数据。

   示例如下：

   执行以下命令，编写一个名为 test_tbl1.csv 的脚本。

   vi test_tbl1.csv
   

2. 进入编辑模式并添加测试数据。

   示例如下：

   按下 i 键或者 Insert 键进入 vi 编辑器的插入模式，在插入模式下添加以下内容。

   1,11
   2,22
   3,33
   

3. 启动客户端。

   示例如下：

   执行以下语句，使用 OBClient 命令行工具连接到 OceanBase 数据库。通过添加 --local-infile 参数启用从本地文件加载数据的功能。

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

   注意

   为了使用 LOAD DATA LOCAL INFILE 功能，请使用 V2.2.4 或之后的版本的 OBClient 客户端。如果您没有要求版本的 OBClient 客户端，也可以使用 MySQL 客户端来连接数据库。

4. 创建测试表。

   示例如下：

   CREATE TABLE test_tbl1(col1 INT,col2 INT);
   

5. 在客户端中，执行 LOAD DATA LOCAL INFILE 语句来加载本地数据文件。

   示例如下：

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

   返回结果如下：

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

6. 查看表信息。

   示例如下：

   obclient [test]> SELECT * FROM test_tbl1;
   

   返回结果如下：

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

异常处理

日志文件

如果导入的过程中出现了错误，出现错误的 INSERT 语句会被回滚，并且 LOAD DATA 语句会在 observer 进程安装路径的 log 子目录下产生名称为 obloaddata.log.<XXXXXX> 的日志文件。以下是一个日志文件的内容示例，日志中会包含 LOAD DATA 产生的任务的基本信息，包含租户名、输入文件名、目标表名、并行度、使用的 LOAD DATA 命令，并且以行为单位给出具体错误的信息。

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

异常诊断

OceanBase 数据库 LOAD DATA 语句导入 URL 外表语法开始新增指定错误诊断模式。允许将失败的行记录下来，而不是让整个操作因第一个错误而终止。配合 REJECT LIMIT 子句，可以控制允许的错误行数。

更多关于 LOAD DATA 语句导入 URL 外表语法开始支持指定错误诊断的信息，参见 LOAD DATA（MySQL 模式） 或 LOAD DATA（Oracle 模式）。

相关文档

• 更多有关使用 LOAD DATA 语句旁路导入数据的信息，请参见 旁路导入。
• 更多有关连接数据库的详细信息，请参见 连接方式概述。
• 更多有关删除表的信息，请参见 删除表。