LOAD DATA

描述

该语句用于加载外部文件的数据到数据库表中。

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

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

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

3. 插入数据：当目标 OBServer 节点收到数据后，在本地执行 INSERT 操作将数据插入到对应的分区当中。

使用限制及注意事项

使用限制

• 带有触发器（Trigger）的表禁止使用 LOAD DATA 语句。
• 当前版本暂不支持加载本地（LOCAL）数据文件。

注意事项

为了提高数据导入速率，OceanBase 数据库在 LOAD DATA 操作中采用了并行设计。在该过程中，需要导入的数据被划分为多个子任务以并行方式执行，每个子任务都作为一个独立的事务进行处理，并且执行顺序是随机的。因此，需要注意以下事项：

• 无法保证整体数据导入的原子性。
• 对于无主键表来说，数据写入的顺序可能与文件中的数据顺序不一致。

权限要求

执行 LOAD DATA 语句，需要拥有 FILE 权限和对应表的 INSERT 权限。有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

示例如下：

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

  GRANT FILE ON *.* TO user_name;
  

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

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

  GRANT INSERT ON database_name.tbl_name TO user_name;
  

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

语法

LOAD DATA [hint_options]
    [REMOTE_OSS]
    INFILE 'file_name'
    [IGNORE | REPLACE]
    INTO TABLE table_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name_or_default]
    [field_opt]
    [line_opt]
    [{IGNORE | GENERATED} number {LINES | ROWS}]
    [(column_name_var [, column_name_var] ...)]
    [SET load_set_list]
    [load_data_extended_option_list];

hint_options:
    [/*+ PARALLEL(N) [load_batch_size(M)] [APPEND | direct(bool, int)] */]

charset_name_or_default:
    `string_charset_name`
    | BINARY
    | DEFAULT

field_opt:
    {COLUMNS | FIELDS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']

line_opt:
    LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']

load_set_list:
    load_set_element [, load_set_element ...]

load_set_element:
    column_definition_ref = {expr | DEFAULT}

load_data_extended_option_list:
    load_data_extended_option [load_data_extended_option ...]

load_data_extended_option:
    LOGFILE [=] string_value
    | REJECT LIMIT [=] int_num
    | BADFILE [=] string_value

参数说明

hint_options

• PARALLEL(N)：指定加载数据的并行度，N 默认为 4。

• load_batch_size(M)：指定每次插入的批量大小，M 默认为 100。推荐取值范围为 [100, 1000]。

• APPEND | direct(bool, int)：使用 Hint 启用旁路导入功能。

  • APPEND：启用旁路导入功能，即支持直接在数据文件中分配空间并写入数据。APPEND Hint 默认等同于使用的 direct(true, 0)，同时可以实现在线收集统计信息（GATHER_OPTIMIZER_STATISTICS Hint）的功能。有关在线统计信息收集的信息，参见 在线统计信息收集。

  • direct(bool, int)：启用旁路导入功能。参数解释如下：

    • bool：表示写入的数据是否需要排序，true 表示需要排序，false 表示不需要排序。
    • int：表示最大容忍的错误行数。

  更多使用 LOAD DATA 旁路导入的信息，参见 使用 LOAD DATA 语句旁路导入数据/文件。

charset_name_or_default

• string_charset_name：指定的字符集名称，通常是一个字符串值，例如 gbk、latin1 等。
• BINARY：指定字符集为 binary 意味着不进行转换，即不进行字符集之间的转换。
• DEFAULT：指定使用数据库默认的字符集来解释文件中的字符数据。OceanBase 数据库默认的字符集是 utf8mb4。

field_opt

• {COLUMNS | FIELDS}：指定使用的关键字是 COLUMNS 还是 FIELDS，这两个关键字是同义词，表示数据文件中的列。

• TERMINATED BY 'string'：指定数据文件中字段的分隔符，即设置导出列的结束符。当读取文件的每一行时，设置的结束符表示一个字段的结束和下一个字段的开始。

• [OPTIONALLY] ENCLOSED BY 'char'：指定数据文件中字段的开头和结尾可以（可选地）被包围（引用）的字符，即设置导出值的修饰符。如果字段中包含了分隔符（例如逗号），那么这个字段常会被双引号包围。

  • OPTIONALLY：可选项，是一个修饰关键词，用于表示字段的包围字符是非强制的。在使用 LOAD DATA 命令时，这意味着某些字段可能有包围字符，而其他字段则没有。字段是否包围不影响命令的正常读取操作。

• ESCAPED BY 'char'：指定数据文件中用于转义特殊字符的转义字符，即设置导出值忽略的字符。例如，如果字段值内部含有双引号字符，该字符就需要通过在前面加一个反斜杠来进行转义（例如 "Hello \"world\""）。

例如，使用 FIELDS 关键字配置 LOAD DATA 命令的字段处理方式，使用 TERMINATED BY 指定字段的分隔符是逗号（,），使用 OPTIONALLY ENCLOSED BY 指定字段值可以用双引号（"）包围，并使用 ESCAPED BY 指定反斜杠（\）作为转义字符，用于处理字段值中的特殊字符。

...
FIELDS 
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\\'
...

line_opt

• LINES: 关键字，告诉 LOAD DATA 命令以下的参数将应用于文件中的行。
• STARTING BY 'string'：指定行起始符，即指定每一行数据的开始标识符。
• TERMINATED BY 'string'：指定行结束符，即指定每一行的结束标识符。

例如，定义每一行数据的开始标记为特定的字符串 ***，即每行中的 *** 开头，将其作为数据行的起点。定义行的结束符为换行符（\n）。

...
LINES
    STARTING BY '***'
    TERMINATED BY '\n'
...

load_set_element

column_definition_ref PARSER_SYNTAX_ERROR {expr | DEFAULT}：表示单个的设置或转换操作。

• column_definition_ref: 指定目标表的列名，在导入过程中将对这一列进行设置或转换。

• PARSER_SYNTAX_ERROR: 关键字，语法分析器错误。

• {expr | DEFAULT}:

  • expr: 指定一个表达式，它定义了如何计算或转换该列的值。表达式可以用来操作数据文件中的原始值，例如，可以通过表达式将两个字段拼接在一起，或者对一个数字字段进行数学运算。
  • DEFAULT: 指定表中该列的值将被设置为默认值。这通常是列定义时指定的默认值。

例如，对于每条导入的数据，将现有的 col_name1 列的值设置为 xxxx，将 col_name2 列的值应该被设置为它的默认值。

...
SET col_name1 = 'xxxx', col_name2 = DEFAULT;
...

load_data_extended_option

• LOGFILE [=] string_value：用来指定一个日志文件，其中会记录在数据加载过程中发生的特定事件或错误。string_value 是目标日志文件的路径和文件名。例如，LOGFILE = 'load_data.log' 会将日志信息记录到名为 load_data.log 的文件中。

• REJECT LIMIT [=] int_num：用来设置在终止 LOAD DATA 操作之前允许的最大错误数量。int_num 是一个整数，代表如果在数据加载过程中遇到的错误数量超过这个限制，整个加载操作将失败。例如，REJECT LIMIT = 10 将在遇到超过 10 个错误后停止加载数据。

• BADFILE [=] string_value：类似于 LOGFILE 参数，用来指定一个文件，该文件用来存放在加载数据过程中识别为无效或不能导入的数据行。string_value 是存放这些数据行的文件的路径和名字。例如，BADFILE = 'bad_data.txt' 将不良数据行放到 bad_data.txt 文件中。

示例

示例一：从服务器端（OBServer 节点）文件导入数据

1. 设置全局安全路径。

   注意

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

   SET GLOBAL secure_file_priv = "/";
   

2. 退出登录。

   说明

   由于 secure_file_priv 是 GLOBAL 变量，所以需要执行 \q 退出使之生效。

   obclinet> \q
   

3. 重连数据库后，使用 LOAD DATA 语句导入数据。

   • 普通导入。

     LOAD DATA 
       INFILE '/home/admin/test.csv'
       INTO TABLE t1
       FIELDS
         TERMINATED BY ','
         ENCLOSED BY '"'
         LINES TERMINATED BY '\n';
     

   • 使用 APPEND Hint 启用旁路导入。

     LOAD DATA /*+ PARALLEL(4) APPEND */
       INFILE '/home/admin/test.csv'
       INTO TABLE t1
       FIELDS
         TERMINATED BY ','
         ENCLOSED BY '"'
         LINES TERMINATED BY '\n';
     

示例二：从 OSS 文件导入数据

使用 direct(bool, int) Hint 启用旁路导入功能，导入文件可在 OSS 上。

LOAD DATA /*+ direct(true,1024) parallel(16) */
  REMOTE_OSS INFILE 'oss://antsys-oceanbasebackup/backup_rd/xiaotao.ht/lineitem2.tbl?host=***.oss-cdn.***&access_id=***&access_key=***'
  INTO TABLE tbl1
  FIELDS
    TERMINATED BY ',';

相关文档

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