【DBA 实战营 课程文档】通过 select into & load data 进行数据导入导出

培训中心 DBA 实战营
#1

背景

记得几年前刚从学校毕业来 OceanBase 的时候，OceanBase 已经支撑了好几年淘宝和支付宝的双十一，正准备开始做商业化。当时商业化过程中一个非常迫切的外部用户需求，就是数据的导入导出。

在我入职时，带我的师兄刚好就正在调研和准备实现 select into & load data 功能。不久之后，我所在的那个小组就为 OceanBase 数据库内核支持了 select into & load data 功能，当时在用户中的口碑也远超 OceanBase 工具团队和蚂蚁集团 DBA 团队开发的各种导入导出工具。

image
image1536×1072 128 KB

因为这是 OceanBase 内核自带的功能，无需额外维护更多工具，并且易用性也足够好，所以在这期课程里推荐给大家！

（真怀念那个无论给团队挖了多少天坑，都有大神师兄在身边保驾护航的年代~）

image
image1536×864 136 KB

边学边练，效果拔群

正所谓 “纸上得来终觉浅，实践才能出真知”，强烈推荐大家点击下面的链接，根据在线体验页面左边的实验文档，亲手体验一把通过 select into && load data 进行数据导入导出的易用性。

  • 在线实验地址：《使用 SELECT INTO 和 LOAD DATA 进行数据导出导入》。

    • 因为 select into 和 load data 的执行计划非常简单，所以大家可以实验中 “步骤三：导出数据” 这里，通过 explain 看一下实验中这张非分区表 tbl 加或者不加 hint /*+ parallel(n) */ 时，执行计划会有哪些变化？再研究下为什么？

      explain basic SELECT *
  INTO OUTFILE '/home/admin/tb1.csv'
  FIELDS TERMINATED BY ','
  FROM tbl1;

explain basic SELECT /*+ PARALLEL(2) */ *
  INTO OUTFILE '/home/admin/tb1.csv'
  FIELDS TERMINATED BY ','
  FROM tbl1;

      image
      image1424×983 116 KB

    • 大家还可以顺带研究下：

      • 计划中的 EXCHANGE OUT / EXCHANGE IN 算子分别是在干什么（PX CODRDINATOR 是一种特殊的 EX IN 算子）？
      • 实验中这张叫做 ORDER 的分区表加或者不加 hint /*+ parallel(n) */ 时，执行计划会有哪些变化？为什么？
      • 计划中的 PX BLOCK ITERATOR 和 PX PARTITION ITERATOR 算子分别是干什么用的？有什么区别？
      • ……

      一个小提示：如果大家想了解这些问题，可以参考以下资料：

      另一个小提示：

      • 需要先登录 OceanBase 账号，才能初始化屏幕右边的实验环境进行实验。
      • 在实验环境里，干什么都可以。大家不要受限于屏幕左边的实验手册，可以天马行空地做一些你感兴趣的事情，或者验证一些你对 OceanBase 官网文档的疑问、以及自己的猜想等等（甚至可以尝试怎么搞能把这个实验环境里的 OBServer 给弄崩）。
      • 欢迎大家平时在学习 OceanBase 的过程中，也都能充分利用在线体验页面为您提供的一些实验环境，来体验 OceanBase 中您感兴趣的新特性。

  • 课后小测地址：【DBA 实战营】通过 select into & load data 进行数据导入导出。

    • 大家完成课后小测，并在小测中上传实验截图，判卷通过后就会自动获取 10 积分，并自动获得抽奖资格，有机会获得实体礼物或更高额的积分奖励。

image
image1088×960 76.2 KB

闲言少叙，正文开始。大家可以选择性地阅读自己感兴趣的部分。

通过 load data 导入数据文件

这一部分内容为大家介绍 load data 的最佳实践。

字符集和编码

在通过 load data 导入数据之前，建议了解字符集和字符的编码原理，以避免在数据导入后读数据时出现乱码。编码和解码使用不同的规则，或者字符集不匹配将导致乱码。导入数据文件出现乱码，可能是数据存储正确但是读取的编码不正确，也可能是数据存储时编码不正确。

从数据文件自身到读取数据文件、写入到 OceanBase 数据库，每个环节的字符集设置均影响最终数据的正确性。

数据文件的字符集

请确保数据文件使用正确的字符集。在导出数据文件时，推荐以 UTF8 格式保存，这样大部分的字符（特别是中文字符）都能在文件中正确保存。如果数据内容包含表情字符等，在导出数据文件时，推荐以 UTF8MB4 格式保存。

以 Linux 环境为例，查看文件的编码：

file /tmp/test.sql 
/tmp/test.sql: UTF-8 Unicode text

客户端的字符集

请确保客户端能正确读取数据文件。

注意

请确保操作系统的语言包含目标字符集（如简体中文），并且 OceanBase 图形化客户端工具也支持目标字符集。推荐将编码设置为 UTF-8。

可以在 Windows、Linux 或国产操作系统使用图形化工具。如果使用的是 Linux 系统，请确保 Linux 系统支持目标字符集。使用命令 locale -m 查看系统可用的字符编码方案。GBK 或 UTF-8 均支持中文。推荐使用 UTF-8。如果操作系统不支持 GBK 或 UTF-8，需要安装简体中文对应的语言包。然后在 Shell 环境下设置环境变量 LANG 为 GBK 或 UTF-8。

终端的编码将决定终端中输入中文字符后转换的编码，以及读取中文内容时显示的编码。

使用以下方式设置会话的显示语言和编码：

## 设置编码
export LANG=en_US.UTF-8
## 检查是否设置成功
locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
...

OceanBase 租户的字符集

请确保 OceanBase 租户的字符集支持目标字符集。可以在创建租户时指定租户的字符集。推荐将字符集设置为 UTF8MB4。这样在任何一个客户端环境出现中文乱码的概率最低，因为上文已建议将客户端操作系统以及客户端工具的字符集编码均设置为 UTF-8。

如果使用的是 MySQL 租户，在创建数据库或者建表时可以改变字符集。

MySQL 的字符集参数比较复杂，推荐将 MySQL 模式的租户的字符集设置为 UTF8MB4。以下示例新建了一个 MySQL 租户，字符集选择 GBK。GBK 支持中文正常读写。

说明

此示例仅为说明原理。生产中不常用。

租户字符集选择了 GBK，上文提到客户端程序以及 Linux 环境变量中的字符集编码均设置为 UTF-8（因为 UTF-8 能兼容 GBK）。

假设还想创建一个字符集是 utf8mb4 的数据库 test2。

CREATE DATABASE test2 CHARACTER SET = utf8mb4;
Query OK, 1 row affected (0.111 sec)

USE test2;
Database changed
CREATE TABLE t1(id bigint, c1 varchar(50));
Query OK, 0 rows affected (0.163 sec)

INSERT INTO t1 VALUES(1,'中');
Query OK, 1 row affected (0.049 sec)

SHOW FULL COLUMNS FROM t1;
+-------+-------------+--------------------+------+-----+
| Field | Type        | Collation          | Null | Key |
+-------+-------------+--------------------+------+-----+
| id    | bigint(20)  | NULL               | YES  |     |
| c1    | varchar(50) | utf8mb4_general_ci | YES  |     |
+-------+-------------+--------------------+------+-----+
2 rows in set (0.004 sec)

SELECT id, c1, hex(c1) FROM t1;
+------+------+---------+
| id   | c1   | hex(c1) |
+------+------+---------+
|    1 | 中   | E4B8AD  |
+------+------+---------+
1 row in set (0.006 sec)

SHOW VARIABLES LIKE '%character%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_sets_dir       |         |
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | gbk     |
| character_set_system     | utf8mb4 |
+--------------------------+---------+
8 rows in set (0.004 sec)

从返回结果看出，虽然租户字符集是 GBK，但是有多个字符集变量值是跟随客户端环境的，值为 utf8mb4。

变量的作用如下：

  • character_set_client：客户端发送的查询数据的字符集。
  • character_set_connection：客户端与服务器连接时的数据字符集。
  • character_set_results：服务器返回给客户端结果时使用的字符集。
  • character_set_server：服务器存储数据时使用的字符集。

character_set_results 决定查询结果集中的数据编码方式。character_set_results 默认与 character_set_connection 保持一致。也可以单独设置 character_set_results，以在返回数据时使用不同的字符集。

上述示例未出现乱码。如果在导入数据过程中遇到了乱码，请参考上述示例分析哪个环节的字符集编码设置不正确，从而导致字符数据传输过程中发生了编码转换错误。

要识别是不是转换错误，需要关注显示的字符以及字符对应的编码。在 MySQL 租户中使用 hex 系统函数查看。在 Linux Shell 中使用 xxd 命令查看。

数据导入准备

为提高导入性能，请确保待导入数据的目标表除了主键外，不包含索引和其他约束。可以在数据导入成功后创建索引和约束。但是，如果数据文件是目标表的增量数据，且表包含唯一约束或索引，必须提前建好约束或索引。

数据导入方案

数据导入方案跟数据文件格式和位置有关。常见数据文件格式为 SQL 和 CSV。SQL 文件包括 DDL 和 DML。

SQL 文件导入

SQL 文件可能包含 DDL 和 DML。推荐将 DDL 和 DML 文件分开。可以通过 OceanBase 的客户端命令 obclient 调用执行 SQL 文件。如果使用的是 MySQL 租户，也可以使用 mysql 调用执行。此外，有些图形化客户端工具，例如 ODC 和 DBeaver，也可以执行 SQL 文件。

例如，使用以下命令执行 SQL 文件：

obclient -h 1.2.3.4 -u user@tenant#cluster -P2883 -p*** -c -A test < /tmp/test.sql

或使用命令行执行 SQL 文件：

## 连接到 OceanBase 数据库
obclient -h127.0.0.1 -uroot@ob_mysql#my_cluster -P2881 -p -c -A test

# 在 OceanBase 数据库执行以下命令
source /tmp/test.sql
Query OK, 1 row affected (0.003 sec)

CSV 文件导入

可以使用 SQL 命令 load data [local] infile 导入 CSV 文件。

在导入 CSV 文件之前，请检查：

  • CSV 文件格式是否正确。

  • 文件首行是否包含列名。

    • 请确保数据导入到对应的列。如果数据错位，可能会因为类型不对而报错。如果数据文件中的列跟目标列无法映射，需要在导入命令中指定列映射（如果使用的是 load data ，需要指定列名）。

  • 导入数据时允许的最大报错行数。

    • 如果报错数量超出允许的最大报错行数，数据导入将报错。

  • 日期和时间类型数据的格式。

    • 在导入日期和时间类型的数据时，需要将 OceanBase 数据库的时间格式变量（如 MySQL 租户的 datetime_format 和 time_format）调整为与数据文件的时间列格式，以避免时间数据（字符串格式）写入到时间列（如 date、time 或 datetime 等）类型转换报错。load data 命令不支持对列使用预处理函数。

如果 CSV 文件位于 OceanBase 租户数据所在的服务器节点上，可以直连 OBServer 节点并使用 load data infile 命令从服务器端加载文件。在导入之前，还需要通过 socket 直连到 OBServer 节点并设置租户全局变量 secure_file_priv 值。这个值是一个目录，它必须包含需要导入的文件目录。

以下是通过 OceanBase 业务租户的任意一个 OBServer 节点上的 socket 文件直连的示例。

# 连接 OceanBase 业务租户
obclient -S ~/oceanbase/run/sql.sock -uroot@obmysql -P2881 -p -c -A oceanbase

-- 设置租户全局变量 `secure_file_priv`
SHOW GLOBAL VARIABLES LIKE '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.004 sec)

SET GLOBAL secure_file_priv = '/data';
Query OK, 0 rows affected (0.114 sec)

SHOW GLOBAL VARIABLES LIKE '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /data |
+------------------+-------+
1 row in set (0.002 sec)

如果 CSV 文件不在 OceanBase 租户所在的服务器节点，而在客户端所在的节点上，则可以使用命令 load data local infile 命令从客户端加载文件。obclient 在启动的时候需要带上参数 --local-infile 以启用加载本地数据的功能。

示例命令如下：

obclient --local-infile -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -Dtest

load data /*+ parallel(12) append */ 
  local infile '/data2/tpch/s100/lineitem.tbl.*'
  into table lineitem fields terminated by '|';

如果 CSV 文件位于对象存储，可以通过 ODP 连接租户并使用命令 load data remote_oss infile 命令从对象存储中加载文件。对象存储目前支持阿里云 OSS（地址协议头：oss://）、腾讯云 COS（地址协议头：cos://）和 S3 协议的对象存储（地址协议头：s3://）。需要在对象存储的路径里指定访问地址（host）、访问账户（access_id）和访问密码（access_key）。

此外，如果 CSV 文件位于对象存储，还可以在 OceanBase 数据库中创建基于这组 CSV 文件的外部表，通过外部表直接读取 CSV 内容，然后通过 INSERT INTO … SELECT … FROM … 命令将 CSV 文件读取到目标表中。

在使用 load data 导入数据时，如果目标表已有存在数据（有主键或唯一键），使用参数 replace 或 ignore 设置是替换数据还是忽略新数据。如果没有主键或唯一键，可以忽略两个参数。数据导入中报错的数据会记录到 obloaddata.log. 日志文件中，日志文件默认会在 OceanBase 软件目录下的 log 目录（/home/admin/oceanbase/log/）中。大批量的导入任务也会导致这个目录下日志文件增多。如果有大量导入报错，请留意这个日志文件的大小。

性能调优

针对大规模数据导入（>100G）的性能优化需要采取灵活的平衡策略。导入速度过慢会延长等待时间，影响业务进度，而导入速度过快可能造成 OceanBase 资源过度消耗。建议在保证系统稳定的前提下优化导入性能。此处的资源分为三类：CPU、内存和磁盘。

CPU 资源分析和优化

同一个租户内部默认所有会话的 CPU 资源使用是平等的，在 CPU 调度上，OceanBase 租户会将队列分为两个：一个用于满足快的 SQL 请求，一个用于满足慢的 SQL 请求。

快和慢的分界点是集群参数 large_query_threshold（默认值是 5s）。慢队列的 CPU 使用比例上限由集群参数 large_query_worker_percentage（默认值是 30，表示 30%）决定。在 OceanBase 数据库 V4.X 版本中，默认使用 cgroup 做租户之间的 CPU 资源隔离。在某些客户场景里，可能会关闭 cgroup 功能。此时通过集群参数 enable_cgroup 来控制。这个参数设置为 false 的时候，就退回到 OceanBase V4.0 以前的版本资源隔离方式。 数据导入会话使用的是目标租户的 CPU 资源。在不开启并行导入的情况下，就是个单线程会话，对租户 CPU 使用不会很大（跟其他业务会话公平竞争）。

内存资源分析和优化

在使用 OceanBase 数据库时，需要关注租户的内存管理机制。每个租户的所有会话分配的总内存都受限于其内存配额。当数据导入速度过快时，会导致单位时间内的内存消耗剧增，从而挤压了业务事务可用的内存空间。虽然 OceanBase 的 MemStore（写内存）管理机制具有一定的灵活性 - 当内存使用超过预设的 freeze_trigger_percentage 阈值时，系统会自动将部分数据冻结并转储到磁盘，释放内存空间。但是，如果数据写入速度远超过内存转储速度，MemStore 的使用率仍可能趋近 100%。这种情况下，业务事务和数据导入会话都可能遇到 -4002 错误。同时，数据导入任务也会报错。为了及时发现潜在问题，建议持续监控租户的 MemStore 内存使用情况。

推荐使用 OCP 监控租户性能的 MemStore 指标图。

image
image838×254 64.6 KB

OceanBase 数据库提供写入限速功能。

写入限速通过 writing_throttling_trigger_percentage 和 writing_throttling_maximum_duration（默认为 2 小时）控制。

在 V4.2 和 V4.3 版本中，由于 writing_throttling_trigger_percentage 的默认值为 100，这一保护机制实际上并未发挥作用。为了预防 MemStore 内存耗尽，推荐将该参数值调整至 90 左右。具体的参数设置可以根据租户内存大小灵活调整。内存较大的租户可以适当提高这个值，而内存较小的租户则应该相应降低。参数的最终设定应当基于对数据导入时内存消耗速度的合理评估。

数据导入性能调优的核心方法之一是优化内存 MemStore 相关参数：

  • freeze_trigger_percentage：用于设置触发冻结（转储的前置动作）的租户使用 MemStore 内存的阈值百分比。较大的转储量会增加 IO 开销，导致写入性能出现较大波动且持续时间更长。过早转储可以预留更多内存空间，但会增加转储频率造成资源浪费。推荐根据业务负载特点设置合适的阈值。
  • writing_throttling_trigger_percentage：决定写入限速的触发点。设置过低会导致过度限速，影响写入性能并浪费内存资源。设置过高可能引发内存不足错误。推荐通过压测确定最优阈值，在性能和稳定性之间取得平衡。
  • memstore_limit_percentage：用于控制租户内存中 MemStore 的占比，默认为 0，表示进行自适应调整。写密集型业务可适当提高此值。

注意

  • 参数调整会影响所有租户，需要全局评估。
  • 对于小内存（小于 8G）租户，大型查询可能挤占 MemStore 空间导致参数失效。

磁盘资源分析和优化建议

OceanBase 数据库在转储过程中会将内存数据写入磁盘文件，默认采用 lz4 压缩算法。系统每天执行一次 Major Freeze 合并操作，将最近 24 小时的内存增量数据、磁盘增量数据以及上一次合并的基线数据进行合并，生成新的基线版本。这些操作会占用磁盘空间，且由于数据多版本存储特性可能导致空间放大。虽然合并操作仅针对发生变化的数据块，但在进行大批量数据导入时，仍可能造成大量的数据合并，从而导致数据文件存储空间暂时增长。如果数据文件剩余空间不足，系统将报告空间错误。

OceanBase 数据库支持通过 datafile_maxsize 和 datafile_next 参数控制数据文件自动扩展。

在 OCP 默认部署的集群中，这两个参数未配置。因此对于初始配置较小的数据文件（datafile_size），推荐在文件系统空间充足的情况下启用自动扩展功能。而对于初始配置较大的数据文件则无需此设置。这也是企业级部署的典型特征：配置大容量初始数据文件并关闭自动扩展。

在转储和合并过程中，系统使用不同的压缩算法。转储过程生成的中间版本数据块采用 lz4 算法，而合并后的最终版本数据块使用 zstd 算法。由于 lz4 算法的压缩比相对较低（但 CPU 资源消耗较少），中间数据会占用更多存储空间。因此在评估系统所需剩余容量时，需要考虑这一因素带来的临时空间增长。

可以使用 OCP 查看 OceanBase 的租户空间，但是 OCP 有延迟。也可以在集群的 sys 租户下，使用以下 SQL 实时查看一个表在所有节点上所有版本的数据大小。

WITH table_locs AS (
SELECT
    t.tenant_id,
    t.database_name,
    t.table_id,
    t.table_name,
    t.table_type tablet_type,
    t.tablet_id,
    REPLACE(concat(t.table_name,':',
                   t.partition_name,':',
                   t.subpartition_name),':NULL','') tablet_name,
    t.tablegroup_name,
    t.ls_id,
    t.ZONE, 
    t.ROLE,
    t.svr_ip
FROM
    oceanbase.CDB_OB_TABLE_LOCATIONS t
WHERE
    t.data_table_id IS NULL 
UNION
SELECT
    i.tenant_id,
    i.database_name,
    i.table_id,
    t.table_name,
    i.table_type tablet_type,
    i.tablet_id,
    REPLACE(
        REPLACE(concat(i.table_name,':',
                       i.partition_name,':',
                       i.subpartition_name) ,
                concat('__idx_', i.data_table_id, '_'),'')
        ,':NULL',''
    ) tablet_name,
    i.tablegroup_name,
    i.ls_id,
    i.ZONE, 
    i.ROLE,
    i.svr_ip
FROM
    oceanbase.CDB_OB_TABLE_LOCATIONS i
INNER JOIN oceanbase.__all_virtual_table t ON
    ( i.tenant_id = t.tenant_id
        AND i.data_table_id = t.table_id  )
WHERE i.data_table_id IS NOT NULL 
)
SELECT
    t.database_name,
    t.ls_id,
    t.ROLE,
    t.svr_ip,
    t.table_name,
    t.tablet_name,
    -- group_concat(s.table_type,',') tablet_types,
    round(sum(s.size)/1024/1024/1024,2) size_gb
FROM
    table_locs t JOIN oceanbase.GV$OB_SSTABLES s 
        ON (t.tenant_id=s.tenant_id
            AND t.ls_id=s.ls_id
            AND t.svr_ip=s.svr_ip
            AND t.tablet_id=s.tablet_id)
WHERE
    t.tenant_id = 1004
    AND t.database_name IN ('tpccdb')
    AND t.table_name IN ('bmsql_stock2')
    AND s.table_type NOT IN ('MEMTABLE')
    -- AND t.ROLE IN ('LEADER')
GROUP BY
    t.database_name,
    t.ls_id,
    t.ROLE,
    t.svr_ip,
    t.table_name,
    t.tablet_name 
WITH ROLLUP
ORDER BY 
    t.database_name,
    t.ls_id,
    t.ROLE,
    t.svr_ip,
    t.table_name,
    t.tablet_name
;

还需要关注数据导入对磁盘性能的影响。这种影响体现在两个方面：内存数据转储和合并过程中的顺序写 IO，这种操作是间歇性的。 OceanBase 事务日志（clog）的持续化也是顺序写小 IO。当数据文件和事务日志文件部署在 NVMe SSD 存储上时，可以忽略 IO 影响。如果发现 IO 压力过大，可以通过降低数据导入速度来缓解。OCP 提供实时磁盘 IO 性能监控，可以据此进行精确评估和调整。

参数建议

OceanBase 集群参数经验值：

-- 必设的系统变量和参数
set global max_allowed_packet=1073741824; -- 设置为 1GB
set global ob_sql_work_area_percentage=30; -- 默认值：5
alter system set freeze_trigger_percentage=30; -- 默认值：70

-- 选设的系统变量和参数
alter system set enable_syslog_recycle='True'; -- 默认值：false
alter system set max_syslog_file_count=100; -- 默认值：0
alter system set minor_freeze_times=500; -- 默认值：5
alter system set minor_compact_trigger=5; -- 默认值：5
alter system set merge_thread_count=45; -- 默认值：0
alter system set minor_merge_concurrency=20; -- 默认值：0
alter system set writting_throttling_trigger_percentage=85; -- 默认值：10
alter system set flush_log_at_trx_commit=0;  -- 默认值：1
alter system set syslog_io_bandwidth_limit=100;  -- 默认值：30MB

说明

在数据导入后，将系统变量和参数务必重新修改为默认值。

并行导入

提升数据导入效率的主要方法是增加并发度，load data 命令支持通过 hint /*+ PARALLEL(n) /设置并行度为 n，可以与旁路导入的 hint /+ APPEND */ 配合使用。

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

旁路导入

OceanBase 数据库支持旁路导入。旁路导入有如下特点：

  1. 绕过常规 SQL 层的大部分接口。
  2. 数据直接写入数据文件，而不经过租户的 MemStore 内存。
  3. 通过 KV Cache 中的内存缓冲区写入数据文件。
  4. 由于写入路径更短，在大批量数据插入时性能更好。

当执行旁路导入时，客户端程序（obclient 或 Java 程序等）需要通过 OBServer 的 RPC 端口（默认 2882）发送 SQL。当使用 OBProxy 转发连接时，需要同时开通 OBProxy 的 RPC 端口（默认 2885）。虽然也可以通过负载均衡设备访问并开通 OBProxy 的 RPC 端口转发，但由于旁路导入会产生大量网络流量，可能影响其他应用的网络带宽使用，因此推荐大批量数据导入时直接连接固定的 OBProxy。

为 obclient 命令行下 INSERT SQL 或 load data 开启旁路导入，需要添加以下 Hint：

  • append：等效于 direct(true,0)。同时可以在线收集统计信息，相当于 GATHER_OPTIMIZER_STATISTICS。
  • direct(bool, int, [load_mode]：bool 表示数据写入时是否排序（true：数据排序；false：数据不排序）。int 表示最大容忍报错行数。load_mode 可选，表示导入模式，默认值 full 表示全量导入，新增功能值 inc 表示增量导入（支持 insert ignore），新增功能值（inc_replace）表示增量导入，但不检查主键重复，相当于 replace（跟 ignore 冲突）。
  • enable_parallel_dml parallel(N)：可选，表示加载数据的并行度。

以下是使用旁路导入示例：

LOAD DATA /*+ PARALLEL(4) DIRECT(true, 0, 'full') */
   INFILE '/home/admin/a.csv'
   INTO TABLE t;

在 V4.3.5 版本中，OceanBase 数据库支持在租户全局级别设置默认数据加载模式，这样上面 SQL 就不用写旁路导入相关的 Hint。

ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

重新登录，再执行以下 SQL，就会自动进行旁路导入。

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

旁路导入不使用 MemStore 而是使用独立的内存空间，因此与普通数据导入的内存使用特点有所不同。在使用旁路导入时，需要确保分配足够的 KV Cache 内存，以防出现内存不足。另外，由于旁路导入会直接写入数据文件且数据压缩比较低，因此数据文件会快速增长，建议提前预留充足的数据文件存储空间。

注意：

旁路导入要求会话 SQL 不能在事务中。

通过 select into 导出 Parquet 和 ORC 格式的文件

OceanBase 从 4.3.5 开始，支持了通过 select Into 导出 Parquet 和 ORC 格式文件的功能，select into 这一部分内容，重点为大家介绍除了 CSV 以外的这两种新格式的导出方式。

导出 Parquet

数据类型映射

目前 OceanBase 通过 select Into 导出 Parquet 格式支持了 MySQL 的数据类型，数据类型的映射表如下：

Parquet物理类型 Parquet 逻辑类型 Hive数据类型 OB-MySQL数据类型
INT32 INT(8,TRUE) TINYINT TINYINT
INT32 INT(16,TRUE) SMALLINT SMALLINT
INT32 INT(32,TRUE) INT INT
INT64 INT(64,TRUE) BIGINT BIGINT
INT32 INT(8,FALSE) TINYINT（超限为null） TINYINT UNSIGNED
INT32 INT(16,FALSE) SMALLINT（超限为null） SMALLINT UNSIGNED
INT32 INT(32,FALSE) INT（超限为null） INT UNSIGNED
INT64 INT(64,FALSE) BIGINT（超限为null） BIGINT UNSIGNED
FLOAT NONE FLOAT FLOAT
DOUBLE NONE DOUBLE DOUBLE
FIXED_LEN_BYTE_ARRAY DECIMAL DECIMAL DECIMAL, DECIMAL UNSIGNED

（必须指定 precision 和 scale）|
|BYTE_ARRAY|STRING|CHAR|CHAR, BINARY

（Parquet 的 string 类型都是 utf8 编码）|
|BYTE_ARRAY|STRING|VARCHAR|VARCHAR, VARBINARY|
|BYTE_ARRAY|STRING|STRING|TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB|
|INT64|TIMESTAMP(is_adjusted_to_utc=true, parquet::LogicalType::TimeUnit::MICROS)|TIMESTAMP|TIMESTAMP|
|INT64|TIMESTAMP(is_adjusted_to_utc=false, parquet::LogicalType::TimeUnit::MICROS)|TIMESTAMP|DATETIME|
|INT96|NONE|TIMESTAMP||
|INT32|DATE|DATE|DATE|
|INT64|TIME||TIME|
|INT32|INT(8,FALSE)||YEAR|

语法

使用新增的 FORMAT 语法导出 Parquet 文件，指定 type 为 Parquet 即可。

SELECT * FROM t1
INTO outfile 'outfiles/data1' 
FORMAT(
  TYPE = 'PARQUET',
  COMPRESSION = '<string>'
  ROW_GROUP_SIZE = '<string>' | <int>
);
  • COMPRESSION 用于指定 Parque t文件的压缩格式，ROW_GROUP_SIZE 用于指定 Parquet 文件的 ROW GROUP 大小。生成的 Parquet 文件后缀名为.parquet 。
  • COMPRESSION 选项支持的参数：‘UNCOMPRESSED’（表示文件没有压缩）, ‘SNAPPY’, ‘GZIP’, ‘BROTLI’, ‘ZSTD’, ‘LZ4’, ‘LZ4_HADOOP’。不指定时默认值为 ‘UNCOMPRESSED’。
  • ROW_GROUP_SIZE 选项可以写数字，单位为字节，或者写形如 ‘64MB’ 的字符串。不指定时默认值为 ‘256MB’。建议使用默认值。

使用示例

-- 将 t1 表导出为 Parquet 格式的文件，文件名为 data1.parquet
SELECT * FROM t1 into outfile 'data1.parquet' format = (TYPE = 'PARQUET');

-- 将 t1 表导出为 Parquet 格式的文件并指定压缩算法为 snappy
SELECT * FROM t1 into outfile 'data1.parquet'
    format = (TYPE = 'PARQUET' COMPRESSION = 'SNAPPY');

-- 将 t1 表导出为 Parquet 格式的文件并指定 row_group 大小为 128MB
SELECT * FROM t1 into outfile 'data1.parquet'
    format = (TYPE = 'PARQUET' ROW_GROUP_SIZE = '128MB');

-- 将t1表导出为Parquet格式的多个文件
-- 导出的文件形如：data1_0_0_0.parquet, data1_0_1_0.parquet
SELECT /*+parallel(2)*/ * FROM t1 into outfile 'data1' 
  format = (TYPE = 'PARQUET')
  single = false;

-- 将 t1 表按分区导出，以 c1 列的值作为分区依据
-- 导出的文件形如：
-- oss://bucket_name/test_parquet/1/data_0_0_0.parquet
-- oss://bucket_name/test_parquet/2/data_0_1_0.parquet
-- oss://bucket_name/test_parquet/3/data_0_0_0.parquet
-- oss://bucket_name/test_parquet/4/data_0_1_0.parquet
SELECT /*+parallel(2)*/ * FROM t1
  into outfile 'oss://bucket/parquet/?host=**&access_id=**&access_key=**'  
  partition by cast(c1 as char)  
  format = (TYPE = 'PARQUET')   
  single = false;

导出 ORC

数据类型映射

目前 OceanBase 通过 select Into 导出 ORC 格式支持了 MySQL 的数据类型，数据类型的映射表如下：

ORC类型 Hive数据类型 OB-MySQL数据类型
BYTE TINYINT TINYINT
SHORT SMALLINT SMALLINT
INT INT INT
LONG BIGINT BIGINT
FLOAT FLOAT FLOAT
DOUBLE DOUBLE DOUBLE
DECIMAL DECIMAL DECIMAL
CHAR CHAR CHAR
VARCHAR VARCHAR VARCHAR
STRING STRING TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT
BINARY BINARY TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB/BINARY/VARBINARY
DATE DATE DATE
TIMESTAMP TIMESTAMP DATETIME/TIMESTAMP

语法

使用新增的 FORMAT 语法导出 ORC 文件，指定 type 为 ORC 即可。

SELECT * FROM t1
INTO outfile 'outfiles/data1' 
FORMAT = (
    TYPE = 'ORC'
    COMPRESSION = '<string>'
    COMPRESSION_BLOCK_SIZE = '<string>' | <int>
    STRIPE_SIZE = '<string>' | <int>
    ROW_INDEX_STRIDE = <int>
  )
  • COMPRESSION 用于指定 ORC 文件的压缩格式。
  • COMPRESSION 选项支持的参数：‘UNCOMPRESSED’（表示文件没有压缩）, ‘SNAPPY’, ‘ZLIB’，‘LZ4’，‘ZSTD’。不指定时默认值为 ‘UNCOMPRESSED’。
  • COMPRESSION_BLOCK_SIZE 指的是数据在压缩时被分割成的块大小，可以写数字（单位为字节），或者写形如 ‘64KB’ 的字符串。不指定时默认值为 256 KB。建议用户使用默认值。
  • STRIPE_SIZE 用于指定 ORC 文件的 stripe 大小，可以写数字（单位为字节），或者写形如 ‘64MB’ 的字符串。不指定时默认值为 64 MB。建议用户使用默认值。
  • ROW_INDEX_STRIDE 是控制索引记录的频率的参数，定义了每隔多少行记录一次索引，不指定时默认值为10000。建议用户使用默认值。
  • 生成的 ORC 文件后缀名为.orc 。

使用示例

-- 将 t1 表导出为 ORC 格式的文件，文件名为 data1.orc
SELECT * FROM t1 into outfile 'data1.orc' format = (TYPE = 'ORC');

-- 将 t1 表导出为 ORC 格式的文件并指定压缩算法为 snappy
SELECT * FROM t1 into outfile 'data1.orc' format = (TYPE = 'ORC' COMPRESSION = 'SNAPPY');

-- 将 t1 表导出为 ORC 格式的文件并指定 STRIPE_SIZE 大小为 128MB
SELECT * FROM t1 into outfile 'data1.orc' format = (TYPE = 'ORC' STRIPE_SIZE = '128MB');

-- 将 t1 表导出为 ORC 格式的文件并指定 COMPRESSION_BLOCK_SIZE 大小为 256KB
SELECT * FROM t1 into outfile 'data1.orc' format = (TYPE = 'ORC' COMPRESSION_BLOCK_SIZE = '256KB');

-- 将 t1 表导出为 ORC 格式的文件并指定 ROW_INDEX_STRIDE 大小为 10000
SELECT * FROM t1 into outfile 'data1.orc' format = (TYPE = 'ORC' ROW_INDEX_STRIDE = 10000);

-- 将 t1 表导出为 ORC 格式的多个文件
-- 导出的文件形如：data1_0_0_0.orc, data1_0_1_0.orc
SELECT /*+parallel(2)*/ * FROM t1 into outfile 'data1' 
  format = (TYPE = 'ORC')
  single = false;

-- 将 t1 表按分区导出，以 c1 列的值作为分区依据
-- 导出的文件形如：
-- oss://bucket_name/test_orc/1/data_0_0_0.orc
-- oss://bucket_name/test_orc/2/data_0_1_0.orc
-- oss://bucket_name/test_orc/3/data_0_0_0.orc
-- oss://bucket_name/test_orc/4/data_0_1_0.orc
SELECT /*+parallel(2)*/ * FROM t1
  into outfile 'oss://bucket/orc/?host=**&access_id=**&access_key=**'  
  partition by cast(c1 as char)  
  format = (TYPE = 'ORC')   
  single = false;

附录

CSV / Parquet / ORC 格式简介

CSV

image
image1760×310 99.6 KB

Parquet / ORC

image
image1500×462 131 KB

嵌套数据类型的 Schema

Parquet 是一个由 Google Dremel 格式启发而来的列存格式，在大数据领域通常作为存储格式，被 iceberg 等湖、各种查询引擎能够合理的使用。

Parquet schema模型能比较简洁处理嵌套和重复。嵌套关系用“属性组”（groups）表示，重复属性用"重复度"（repeated field）表示。一个属性的"重复度"可以有几种定义：

  • required：刚好出现一次
  • optional：出现 0 或 1 次
  • repeated：出现 0 或多次

Parquet 存储数据的例子

假如有 schema 是：

country_tab {
  required string continent,
  required group country {
    repeated string city,
    optional int population
  },
  required string name
}

country_tab 记录包含 continent、country、name三个属性，其中country是一个属性组，包含 city、population两个属性。city 是一个 repeated 字段，因此可以有多个值，从而表达了数组。

如何存储下面一行数据 ？

country_tab {
  "continent": "Europe",
  "country": {
    "city": [
      "Paris",
      "Nice",
      "Marseilles",
      "Cannes"
    ],
    "population": 1000000
  }
  "name": "France"
}

其中，city 是个数组，数组中元素可以是任意个，假设 population 可能是未知的，可以被设置为 NULL，其他属性都是不能为 NULL 的。

Parquet 认为以上 schema 有 4 列（仅存储叶子结点）

continent
Europe

city
Paris
Nice
Marseilles
Cannes

population
1000000

France
Paris

假如有 2 行数据，因为一行中的数据可以 repeat，也可以为 null，如何区分一个 column chunk 中的数据到底是属于哪一行的？

通过数据上定义的 defination level 和 repetitive level。

类似地，Maps, List or Sets 都可以用重复和属性组（groups + repeated field）表达出来。

相比之下，ORC 使用一个单独的 bitmap 来存储节点是否存在，比较容易理解。

  1. Struct 只记录 Struct 本身的 PRESENT。

  2. List = Present Stream + Length Stream。

  3. Map = Map 等价于一种很奇怪的 List 和 List 表现一样，但后面有一组 Key 一组 Value。

  4. Union 分成 PRESENT + Tag，后面接不同种类的 Stream。

Parquet / Orc 对比示例

image
image2282×404 133 KB

参考

实时查看一个表在所有节点上所有版本的数据大小的SQL是否可分享