使用 LOAD DATA 语句旁路导入数据

LOAD DATA 语句通过 Hint 使用 append/direct() 来表示是否走旁路导入。

全量旁路导入

使用限制

• 在导入过程中无法同时执行两个写操作语句（即不能同时写一个表），因为导入过程中会先加表锁，并且整个导入过程中只能进行读操作。
• 不支持在触发器（Trigger）使用。
• 不支持含有生成列的表（某些索引会产生隐藏生成列，例如 KEY idx_c2 (c2(16)) GLOBAL）。
• 不支持单行超过 2M 的数据导入。
• 不支持 Liboblog 和闪回查询（Flashback Query）。
• LOAD DATA 支持在多行事务中运行，在执行时会主动提交之前的事务。

注意事项

OceanBase 数据库通过并行处理技术优化 LOAD DATA 的数据导入速率。该操作将数据分成多个子任务并行执行，每个子任务都视为独立的事务，执行顺序是不固定的。因此对于没有主键的表，数据写入顺序可能与原文件中的顺序不同。

语法

LOAD DATA /*+ [APPEND | direct(need_sort,max_error,'full')] parallel(N) */ [REMOTE_OSS | LOCAL] INFILE 'file_name' ...

更多 LOAD DATA 语法的信息，请参见 LOAD DATA。

参数解释：

通配符方式支持多文件导入

为便于多文件导入，引入文件通配符功能，适用于 server_disk 和 OSS 类型的文件源，不适用于客户端磁盘（client_disk）。

集群内文件系统（server_disk）旁路导入使用方法

• server_disk 示例：

  • 匹配文件名：load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.*.csv' replace into table t1 fields terminated by '|';

  • 匹配目录：load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.1.csv' replace into table t1 fields terminated by '|';

  • 同时匹配目录和文件名：load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.*.csv' replace into table t1 fields terminated by '|';

• server_disk 注意事项：

  • 必须存在至少一个匹配的文件，否则返回错误码 4027。

  • 对 load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.1*.csv,/xxx/test.6*.csv' replace into table t1 fields terminated by '|'; 的输入，/xxx/test.1*.csv,/xxx/test.6*.csv 会被认为是整体匹配，若无匹配则报错 4027。

  • 只支持 POSIX 的 GLOB 函数能支持的通配符，例如 test.6*(6|0).csv 和 test.6*({0.csv,6.csv}|.csv) 尽管可通过 ls 命令查找，但 GLOB 函数无法匹配，会报错 4027。

云对象存储服务（OSS）旁路导入使用方法

• OSS 示例：

  • 匹配文件名：load data /*+ parallel(20) direct(true, 0) */ remote_oss infile 'oss://xxx/test.*.csv?host=xxx&access_id=xxx&access_key=xxx' replace into table t1 fields terminated by '|';

• OSS 注意事项：

  • 不支持目录匹配。例如：load data /*+ parallel(20) direct(true, 0) */ remote_oss infile 'oss://aa*bb/test.*.csv?host=xxx&access_id=xxx&access_key=xxx' replace into table t1 fields terminated by '|'; 会返回 OB_NOT_SUPPORTED。

  • 文件名通配符只支持 * 和 ?。其他通配符虽然允许输入，但无法匹配任何结果。

示例

1. 登录到要连接 OBServer 节点所在的机器，在 /home/admin 目录下创建测试数据。

   说明

   OceanBase 数据库中的 LOAD DATA 语句仅支持加载 OBServer 节点本地的输入文件。因此，需要在导入之前将文件拷贝到某台 OBServer 上。

   [xxx@xxx /home/admin]# ssh admin@10.10.10.1
   
   [admin@xxx /home/admin]# vi tbl1.csv
   1,11
   2,22
   3,33
   

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

   设置系统变量 secure_file_priv，配置导入或导出文件时可以访问的路径。

   注意

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

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

      [xxx@xxx /home/admin]# ssh admin@10.10.10.1
      

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

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

   3. 设置导入路径为 /home/admin。

      obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
      Query OK, 0 rows affected
      

3. 重新连接数据库后，使用 LOAD /*+ APPEND */ 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,'full') 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
   

增量旁路导入

使用限制

• 有索引（不包括主键）的表不支持增量旁路导入。
• 有外键的表不支持增量旁路导入。

注意事项

增量旁路导入的数据会触发转储，对于数据量较小且能在分钟内完成导入的情况，不建议使用增量旁路导入。

语法

LOAD DATA /*+ [APPEND | direct(need_sort,max_error,{'inc'|'inc_replace'})] parallel(N) */ [REMOTE_OSS | LOCAL] INFILE 'file_name' ...

更多 LOAD DATA 语法的信息，请参见 LOAD DATA。

参数解释：

示例

LOAD DATA 语句中增量旁路导入的操作步骤与全量旁路导入的步骤一样，只需要将 full 字段的取值替换为 inc 或 inc_replace 即可。

obclient [test]> LOAD DATA /*+ direct(true,1024,'inc_replace') 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

相关文档

• 更多有关使用 LOAD DATA 语句的示例信息，请参见 使用 LOAD DATA 语句导入数据。

• 更多有关连接数据库的详细信息，请参见 连接方式概述。

• 更多有关删除表的信息，请参见 删除表。