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

    LOAD DATA 语法格式如下：

    1. LOAD DATA
    2.         [/*+ parallel(N)*/]
    3.     INFILE 'file_name'
    4.     [IGNORE]
    5.     INTO TABLE tbl_name
    6.     [{FIELDS | COLUMNS}
    7.         [TERMINATED BY 'string']
    8.         [[OPTIONALLY] ENCLOSED BY 'char']
    9.         [ESCAPED BY 'char']
    10.     ]
    11.     [LINES
    12.         [STARTING BY 'string']
    13.         [TERMINATED BY 'string']
    14.     ]
    15.     [IGNORE number {LINES | ROWS}]
    16.     [(col_name_var
    17.         [, col_name_var] ...)]

    注意

    要加载的文件必须在该表的主副本所在的 OBServer 上，当前版本不支持从远程客户端加载数据。

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

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

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

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

    Load Data 提供了很多选项支持用户不同的需求，目前支持的选项有：

    • 并行度：/*+ parallel(N)*/ 选项指定加载数据的并行度，建议使用的值范围是 [0 - 租户的最大CPU数]。例如：
    1. load data /*+ parallel(4) */infile '/home/admin/a.csv' into table t

    • 输入文件：INFILE ‘file_name’ 关键字指定输入文件的路径和文件名，目前 Load Data 只支持加载 OBServer 本地的输入文件。所以，用户需要在导入之前把文件拷贝到某一个 OBServer 上，并连接文件所在的 OBServer 运行 Load Data 语句。

    • 执行权限：用户需要授予权限才能访问机器上的文件，有两步：

    1. 修改安全文件所在路径，设置为空（即无需检查）：set global secure_file_priv = "";

    2. 对用户授予目录的 read 权限：执行 grant read on directory dd to USER_NAME;

    • 重复数据处理：这部分指定如何处理重复的数据。Replace 表示将表中原有的数据替换成为输入文件中的数据；Ignore 表示忽略掉重复的数据。Load Data 通过表的主键来判断数据是否重复，如果表不存在主键，那么 Load Data 语句就无法判断数据是否重复，Replace 和 Ignore 选项没有区别。如果用户不指定这个选项，那么遇到重复数据的时候，Load Data 语句会将出现把错误的数据记录到日志文件中。

    • 目标表选项：INTO TABLE tbl_name 关键字用于指定目标表名称。Load Data 支持分区表和非分区表。

    • 字段格式：这部分指定输入文件的各个字段的分隔符选项，通过 Fields | Columns 子句来指定，其中：Terminated By 关键字用来指定字段的分隔符；Enclosed By 关键字指定每个字段的开始和结束是否包含了特定的字符；Escaped By 关键字用来指定字段中的通配符。

    • 行格式：这部分指定输入文件中每一行的开始和结束字符，通过 Lines 子句设置。 其中 Starting By 用于指定每一行开始的字符；Terminated By 用户指定每一行的结束字符。IGNORE number {LINES | ROWS} 子句指定忽略掉输入文件的前 number 行数据。

    1. load data /*+ parallel(4) */infile '/home/admin/a.csv' into table t fields terminated by ',' lines terminated by '\n';
    • 列对应关系选项：这部分用于指定目标表的各个列与输入文件的字段之间的关系，通过 (col_name_var [, col_name_var] ...) 关键字指定。如果用户没有指定，默认会将输入文件中的字段逐个与表中的列进行对应。如果用户通过 col_name_or_user_var 关键字指定输入文件中的字段与表中列的对应关系，Load Data 会根据指定的列名与表中的列进行对应，没有被指定的列会取空值，下面是一个基本的示例：
    1. load data infile '/home/admin/a.csv' into table t (id, names) set dates=CURRENT_TIMESTAMP;

    如果输入文件中并没有包含所有的列，那么缺少的列按照以下的规则会被默认填充：

    • 字符类型：空字符串

    • 数值类型：0

    • 日期类型：0000-00-00

    如果用户需要添加空值，请在输入文件中使用 ‘\N’。

    • 日志文件：如果导入的过程中出现了错误，出现错误的 INSERT 语句会被回滚，并且 Load Data 语句会在 OBServer 安装路径的 log 子目录下产生名称为 obloaddata.log.<XXXXXX> 的日志文件，以下是一个日志文件的示例：
    1. Tenant name:    mysql
    2. File name:  /home/admin/a.csv
    3. Into table: `test`.`t`
    4. Parallel:   1
    5. Batch size: 1000
    6. SQL trace:  YD7A20BA65670-0005AADAAA3CAB52
    7. Start time: 2020-07-29 21:08:13.073741
    8. Load query:
    9. load data infile '/home/admin/test.csv' into table t fields terminated by ',' lines terminated by '\n'
    10. Row ErrCode ErrMsg
    11. 1   1062    Duplicated primary key
    12. 2   1062    Duplicated primary key

    日志中会包含 Load Data 产生的任务的基本信息，包含了：租户名，输入文件名，目标表名，并行度，使用的 Load Data 命令，并且以行为单位给出具体错误的信息。

    示例：通过 Load Data 导入 csv 文件到表 ware2 中

    1. [admin@h07g12092.sqa.eu95 /home/admin/csvdata]
    2. $more ware__df8f30ac_64e0_474c_9cc4_9919d64c5e4c
    3. 2,1200,.0862,L6xwRsbDk,xEdT1jkENtbLwoI1Zb0,NT0j4RCQ4OqrS,vlwzndw2FPrO,XR,063311111
    4. 1,1200,.1868,n1P4zYo8OH,jTNkXKWXOdh,lf9QXTXXGoF04IZBkCP7,srRq15uvxe5,GQ,506811111
    6. $obclient  -h192.168.1.101 -utpcc@obbmsql -P2881 -p123456
    8. obclient> load data infile '/home/admin/csvdata/ware__df8f30ac_64e0_474c_9cc4_9919d64c5e4c' into table ware2 fields terminated by ',' lines terminated by '\n';
    9. Query OK, 2 rows affected (0.02 sec)
    10. Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
    12. obclient> select * from ware2\G
    13. *************************** 1. row ***************************
    14.       W_ID: 2
    15.      W_YTD: 1200
    16.      W_TAX: .0862
    17.     W_NAME: L6xwRsbDk
    18. W_STREET_1: xEdT1jkENtbLwoI1Zb0
    19. W_STREET_2: NT0j4RCQ4OqrS
    20.     W_CITY: vlwzndw2FPrO
    21.    W_STATE: XR
    22.      W_ZIP: 063311111
    23. *************************** 2. row ***************************
    24.       W_ID: 1
    25.      W_YTD: 1200
    26.      W_TAX: .1868
    27.     W_NAME: n1P4zYo8OH
    28. W_STREET_1: jTNkXKWXOdh
    29. W_STREET_2: lf9QXTXXGoF04IZBkCP7
    30.     W_CITY: srRq15uvxe5
    31.    W_STATE: GQ
    32.      W_ZIP: 506811111
    33. 2 rows in set (0.00 sec)