使用 LOAD DATA 语句导入数据

更新时间：2024-01-19 15:15:41

编辑

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

使用场景

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

注意

OceanBase 数据库当前版本只支持加载位于 OSS 和服务器端（OBServer 节点）的数据文件。

解析文件。

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

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

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

LOAD DATA

获取 LOAD DATA 执行权限

用户需要授予权限才能访问机器上文件，操作步骤如下：

首先修改安全文件所在路径，设置为文件所在目录或空（即无需检查）。

注意

由于安全原因，只能使用通过本地连接的 Client 执行修改 secure_file_priv 的 SQL 语句。详细信息请参见 secure_file_priv。
```
SET GLOBAL SECURE_FILE_PRIV = "";
```
对用户进行授权。

授予用户 file 权限命令如下，USER_NAME 是需要执行 LOAD DATA 命令的用户。
```
GRANT FILE ON *.* TO user_name;
```

LOAD DATA 语法格式

LOAD DATA
    [/*+ parallel(N)*/] 
    INFILE 'file_name'
    [REPLACE | IGNORE] //REPLACE、IGNORE 选项只适用于 MySQL 租户。
    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] ...)]

参数	是否必填	说明	示例
[/+ parallel(N)/]	否	指定加载数据的并行度，建议使用的值范围是[0 - 租户的最大CPU数]	/+ parallel(4) /
INFILE 'file_name'	是	指定输入文件的路径和文件名。	infile '/home/admin/a.csv'
[REPLACE \| IGNORE]	否	指定如何处理重复的数据。LOAD DATA 通过表的主键来判断数据是否重复，如果表不存在主键，那么 LOAD DATA 语句就无法判断数据是否重复，replace 和 ignore 选项没有区别。 replace：表示将表中原有的数据替换成为输入文件中的数据。 ignore：表示忽略掉重复的数据。说明如果用户不指定这个选项，那么遇到重复数据的时候，LOAD DATA 语句会将出现把错误的数据记录到日志文件中。	replace
INTO TABLE tbl_name	是	指定目标表名称。	into table tbl_name
[{FIELDS \| COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ]	否	指定输入文件中各个字段的分隔符选项，通过 Fields \| Columns 子句来指定。 terminated By：用来指定字段的分隔符。 enclosed By：指定每个字段的开始和结束是否包含了特定的字符。 escaped By：指定字段中的通配符。说明如果用户没有指定，默认值是 teminated by '\t' enclosed by '' escaped by '\\'	fields terminated by ','
[LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ]	否	指定输入文件中每一行的开始和结束字符，通过 Lines 子句设置。 starting By：指定每一行开始的字符。 terminated By：指定每一行的结束字符。说明如果没有指定，默认执行 starting by '' terminated by '\n'	lines terminated by '\n'
[IGNORE number {LINES \| ROWS}]	否	子句指定忽略掉输入文件的前 number 行数据。	ignore 1 lines
[(col_name_var [, col_name_var] ...) ]	否	指定目标表的各列与输入文件字段之间的关系，通过 (col_name_var [, col_name_var] ...) 关键字指定。如果用户没有指定，默认会将输入文件中的字段逐个与表中的列进行对应。如果用户通过 `col_name_or_user_var` 指定输入文件中的字段与表中列的对应关系，LOAD DATA 会根据指定的列名与表中的列进行对应，没有被指定的列会取空值。如果输入文件中并没有包含所有的列，那么缺少的列按照以下的规则会被默认填充：字符类型：空字符串数值类型：0 日期类型：0000-00-00 如果用户需要添加空值，请在输入文件中使用'\N'。	(id, names)

日志文件

如果导入的过程中出现了错误，出现错误的 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

使用示例

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