LOAD DATA

LOAD DATA

更新时间：2025-05-07 10:31:33

编辑

描述

该语句用于从外部导入数据。

注意

带有触发器（Trigger）的表禁止使用 LOAD DATA 语句。
要从外部文件导入数据，您需要具有 FILE 权限及以下设置：
- 加载服务器端文件时，需要提前设置系统变量 secure_file_priv，配置导入或导出文件时可以访问的路径。
- 加载客户端本地文件时，需要在启动 MySQL/OBClient 客户端时添加 --local-infile[=1] 选项来启用从本地文件系统加载数据的功能。

OceanBase 数据库 LOAD DATA 语句支持加载以下输入文件：

服务器端（OBServer 节点）文件：文件位于 OceanBase 数据库的 OBServer 节点上。可以使用 LOAD DATA INFILE 语句，将服务器端文件中加载数据到数据库表中。
客户端（本地）文件：文件位于客户端本地文件系统中。可以使用 LOAD DATA LOCAL INFILE 语句，将客户端本地文件中的数据加载到数据库表中。
OSS 文件：文件位于 OSS 文件系统。可以使用 LOAD DATA REMOTE_OSS INFILE 语句，将 OSS 文件中的数据加载到数据库表中。

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

解析文件：OceanBase 数据库会根据用户输入的文件名，读取文件中的数据，并且根据指定的并行度来决定并行或者串行解析输入文件中的数据。
分发数据：由于 OceanBase 是分布式数据库，各个分区的数据可能分布在各个不同的 OBServer 节点，LOAD DATA 会对解析出来的数据进行计算，决定数据需要被发送到哪个 OBServer 节点。
插入数据：当目标 OBServer 节点收到数据后，在本地执行 INSERT 操作将数据插入到对应的分区当中。

要从外部文件导入数据，您需要具有 FILE 权限。您可以使用 GRANT FILE ON *.* TO $user_name; 语句进行授权，其中，$user_name 是需要执行 LOAD DATA 命令的用户。

语法

LOAD DATA
    [/*+ PARALLEL(N) [load_batch_size(M)] [APPEND | direct(bool, int, [load_mode])] | NO_DIRECT */]
    [REMOTE_OSS | LOCAL] INFILE 'file_name'
    INTO TABLE table_name
    [COMPRESSION [=] {AUTO|NONE|GZIP|DEFLATE|ZSTD}]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(column_name_var
        [, column_name_var] ...)]

load_mode:
    'full' 
    | 'inc_replace'

参数解释

参数	描述
parallel(N)	加载数据的并行度，默认为 4。
load_batch_size(M)	指定每次插入的批量大小，`M` 默认为 `100`。推荐取值范围为 [100,1000]。
APPEND \| direct() \|NO_DIRECT	使用 Hint 启用旁路导入功能。注意在执行旁路导入任务的过程中，不建议同时进行 OceanBase 数据库的升级操作，因为这可能导致旁路导入任务失败。 `APPEND` Hint 默认等同于使用的 `direct(true, 0)`，同时可以实现在线收集统计信息（`GATHER_OPTIMIZER_STATISTICS` Hint）的功能。 `direct()` 参数解释如下： `bool`：表示写入的数据是否需要排序，`true` 表示需要排序，`false` 表示不需要排序。 `int`：表示最大容忍的错误行数。 `load_mode`：可选参数，表示旁路导入的模式。取值须使用英文单引号包起来，取值如下： `full`：默认值，表示全量导入。 `inc`：表示增量导入，支持 `INSERT` 和 `IGNORE` 语义。 `inc_replace`：表示增量导入，但不检查主键是否重复，相当于 `REPLACE` 语义的增量导入。 `NO_DIRECT`：控制单条 SQL 强制不走旁路导入，只要输入的 SQL 带有该 Hint，则整个语句忽略其他旁路导入的 Hint，执行普通导入。更多使用 `LOAD DATA` 旁路导入的信息，参见使用 LOAD DATA 语句旁路导入数据/文件。
REMOTE_OSS \| LOCAL	可选项， `REMOTE_OSS` 用于指定是否从 OSS 文件系统读取数据。注意如果使用了此参数，`file_name` 必须是一个 OSS 的地址，OSS 泛指对象存储 OSS（`oss://`）、COS（`cos://`）、S3（`s3://`）等。 `LOCAL` 用于指定是否从客户端的本地文件系统读取数据。如果不使用 `LOCAL` 参数，那么将从服务器端（OBServer 节点）的文件系统读取数据。
file_name	指定输入文件的路径和文件名。 `file_name` 有以下格式：导入文件在 OBServer 节点或客户端：`/$PATH/$FILENAME`。导入文件在 OSS 上：`oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY`。参数解释如下： `$PATH`：指定了存储桶中的文件路径，表示文件所在的目录。 `$FILENAME`：指定了文件的名称，表示要访问的具体文件。 `$HOST`：指定了 OSS 服务的主机名或 CDN 加速的域名，即要访问的 OSS 服务的地址。 `$ACCESS_ID`：指定了访问 OSS 服务所需的 Access Key ID，用于身份验证。 `$ACCESSKEY`：指定了访问 OSS 服务所需的 Access Key Secret，用于身份验证。说明在导入 OSS 上的文件时，需要确保以下信息：确保访问 OSS 存储桶和文件的权限。你需要拥有足够的权限来读取指定的存储桶和文件。这通常需要在 OSS 控制台或通过 OSS API 设置访问权限，并将访问密钥（Access Key ID 和 Access Key Secret）配置为具有适当权限的凭据。确保数据库服务器可以通过网络连接到指定的 `$HOST` 地址，以访问 OSS 服务。如果使用的是 OSS 服务的 CDN 加速域名，还需要确保 CDN 配置正确，并且网络连接正常。
table_name	导入数据的表的名称，支持分区表与非分区表。
COMPRESSION	指定压缩文件格式，其中 `AUTO`：根据文件名后缀自动探测压缩算法。在使用 `AUTO` 参数时，不同的后缀名有对应的压缩格式。 `.gz`：对应 GZIP 压缩文件。 `.deflate`：对应 DEFLATE 压缩文件。 `.zst/.zstd`：对应 ZSTD 压缩文件。 `NONE`：表示文件没有压缩。 `GZIP`：GZIP 压缩文件。 `DEFLATE`：不带元数据的GZIP压缩文件。 `ZSTD`：ZSTD 压缩文件。可以明确文件的压缩格式或者让程序根据文件名后缀来探测压缩格式。
FIELDS \| COLUMNS	指定字段的格式。 `ENCLOSED BY`：设置导出值的修饰符。 `TERMINATED BY`：设置导出列的结束符。 `ESCAPED BY`：设置导出值忽略的字符。
LINES STARTING BY	指定行起始符。
LINES TERMINATED BY	指定行结束符。
IGNORE number { LINES \| ROWS }	忽略前几行，`LINES` 表示文件的前几行，`ROWS` 表示由字段分隔符指定的前几行数据。缺省时，会将输入文件中的字段逐个与表中的列进行对应。如果输入文件中并没有包含所有的列，那么缺少的列按照以下的规则会被默认填充：字符类型：空字符串。数值类型：0。日期类型：`0000-00-00`。说明多文件导入时，与单文件导入行为同理。
column_name_var	指定导入的列名。

多文件旁路导入时通配符的规则

为便于多文件导入，引入文件通配符功能，适用于服务器端和 OSS 文件导入，不适用于客户端文件导入。

服务器端通配符使用
- 匹配规则：
  - 匹配文件名：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 '|';
- 注意事项：
  - 必须存在至少一个匹配的文件，否则返回错误码 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）中通配符使用
- 匹配规则：
  
  匹配文件名：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 '|';
- 注意事项：
  - 不支持目录匹配。例如：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。
  - 文件名通配符只支持 * 和 ?。其他通配符虽然允许输入，但无法匹配任何结果。

示例

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

示例 1：服务器端文件导入数据。

设置全局安全路径。

注意

由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Socket 连接数据库执行修改该全局变量的 SQL 语句。更多信息，请参见 secure_file_priv。
```
obclient> SET GLOBAL secure_file_priv = "/";
```
退出登录。

说明

由于 secure_file_priv 是 GLOBAL 变量，所以需要执行 \q 退出使之生效。
```
obclinet> \q
```
返回结果如下：
```
Bye
```

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

普通导入。

obclient> LOAD DATA INFILE '/home/admin/test.csv' INTO TABLE t1;

使用 APPEND Hint 启用旁路导入。

LOAD DATA /*+ PARALLEL(4) APPEND */ INFILE '/home/admin/test.csv' INTO TABLE t1;

示例 2：使用 APPEND Hint 启用旁路导入功能。

LOAD DATA /*+ PARALLEL(4) APPEND */
   INFILE '/home/admin/a.csv'
   INTO TABLE t;

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

示例 1：从本地文件导入数据至 OceanBase 数据库表中。

打开终端或命令提示符窗口，并输入以下命令来启动客户端。

obclient --local-infile -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p******

返回结果如下：

Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221548006
Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [SYS]>

注意

为了使用 LOAD DATA LOCAL INFILE 功能，请使用 V2.2.4 或之后的版本的 OBClient 客户端。

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

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

返回结果如下：

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

示例 2：通过设置 COMPRESSION 直接导入压缩文件。

LOAD DATA LOCAL INFILE '/your/file/lineitem.tbl.gz' 
    INTO TABLE lineitem 
    COMPRESSION GZIP 
    FIELDS TERMINATED BY '|';

从 OSS 文件导入数据

注意

使用对象存储路径时，对象存储路径的各项参数由 & 符号进行分隔，请确保您输入的参数值中仅包含英文字母大小写、数字、\/-_$+= 以及通配符。如果您输入了上述以外的其他字符，可能会导致设置失败。

示例 1：使用 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 ',';

描述

注意

语法

参数解释

注意

注意

说明

说明

多文件旁路导入时通配符的规则

示例

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

注意

说明

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

注意

从 OSS 文件导入数据

注意

相关文档