原Oracle数据库中保存着国家地球物理台网的基础信息,比如台站、井泉、洞体、观测室等,这些信息的完整性要求较高,而且含有多达上百兆字节的BLOB(Binary Large Object,二进制大对象)字段,如台站建设报告,仍然需要存储在关系数据库中。考虑到ClickHouse自带访问MySQL数据库的引擎,可以实现ClickHouse和MySQL的表的跨库连接,所以把基础信息迁移到MySQL中,表结构仍然保持不变。

“十五”Oracle数据库中,观测数据按测项、数据类型、采样率进行分表设计存储,表结构类似。以水位数据为例,原始数据分钟采样表名为QZ_411_DYS_01,其中“411”是水位的测项代码,“DYS”为原始数据,“01”为采样率,结构设计见表1。存储时序观测数据的字段是obsvalue,类型是CLOB(Character Large Object,字符大对象),内容是空格分隔的以ASCII码表示的字符串数据。

表1 “十五”Oracle数据库中的水位数据分钟采样表结构
Tab.1 Structure of the water-level data table(sampling rate:per minute)in the Oracle database in the 10th Five-Year Plan period

(1)本地表和分布式表

ClickHouse本地表是存储在本地磁盘上的,对它的操作只影响本节点上的数据。分布式表可以理解为集群所有分片上的本地表的合并视图,对分布式表的操作会根据分片规则映射到相应的分片节点上。分片规则必须是以数值类型定义,实际应用中使用数据的年份进行分片,即将相同年的数据放置在同一分片上。

(2)日期类型

ClickHouse的日期字段是DateTime类型,与标准的Unix时间戳一样,不能表示1970年1月1日以前的日期。据了解,Tdengine、IOTDB等数据库也不支持。由于Oracle中有1970年前的数据,所以实际使用64位的整型数来存储时间戳,含义与Unix时间戳一致,代表1970年1月1日0时以来的毫秒数(负数为1970年前),与Java语言中Date类型的getTime方法得到的时间戳相同。

(3)主键

ClickHouse的主键与传统意义上的主键概念不同,它的主键主要用来建立索引查找数据更快,但是不具备唯一性约束,即相同主键的数据可以插入到同张表中。

(4)表引擎

ClickHouse最广泛使用的表引擎是MergeTree家族,它有很多分支。比如ReplicatedMergeTree是指集群中的表,它可以自动在副本之间同步数据。ClickHouse不具备真正的更新和删除功能,它的删除和更新需通过后台的合并来间接实现,合并的时间不可预知。而在实际应用中,原始和预处理数据都有少量的更新需求,使用ReplacingMergeTree引擎引入一个版本列可以保证最终数据表里相同主键的数据只保留1条。考虑到多副本的数据安全性,最终所有的本地表都用ReplicatedReplacingMergeTree表引擎来建立。

为保持与原Oracle数据库设计的兼容,Oracle数据表中所有字段都保留,obsvalue字段则被拆分为时序数据格式单独建表存放。为利用ClickHouse的高速查询优势,将Oracle数据库中所有原始和预处理记录合并到一张表中,但不包含obsvalue字段。ClickHouse数据记录表结构见表2,分布式表与本地表字段相同。

表2 ClickHouse数据记录表结构
Tab.2 Structure of the data-record table in ClickHouse

(5)时序数据表

原Oracle中的obsvalue字段存储的是以空格分隔的字符串数据块。在ClickHouse中,该字段需要拆分成单个的数据按对应的时间戳,并以每行一个数据方式存储。为提高存储和查询效率,时序数据按测点、数据类型(原始、预处理或产品)和采样率建表。为了保证数据迁移后的精度,时序数据用Decimal类型保存。以中国地震局地质研究所白浮台(代码03002)测点3气象三要素观测仪原始分钟采样数据为例,本地表名为DYS_01_03002_3,分布式表名为DYS_01_03002_3_ALL。本地表结构见表3,分布式表字段与本地表相同。

表3 ClickHouse原始数据表结构
Tab.3 Structure of the original data table in ClickHouse

(6)产品数据表

产品数据表包括均值类产品数据和学科专业产品数据。均值类产品数据主要由预处理软件产生,在Oracle中的表结构比原始和预处理表仅缺少processingflag字段,所以均值产品数据在ClickHouse中可与原始和预处理数据一样,将均值产品的观测数据序列拆分到时序表(表3),将记录中其它信息存放在数据记录表(表2)中,用数据类型字段对均值产品类型进行区分。学科专业产品数据在Oralce中的表结构与原始或预处理数据差异很大,需要根据各自特点分别设计,此处不再赘述。

(7)日志表

日志表包括仪器运行日志表和观测日志表,这两类表在Oracle中的表结构按测项进行分表。因为日志表每行数据量较小,而且不含有LOB字段,所以在ClickHouse中可以将各测项合并到1张表。仪器运行日志表结构见表4,观测日志表结构见表5。

表4 ClickHouse仪器运行日志表结构
Tab.4 Structure of the instrument-log table in ClickHouse

表5 ClickHouse观测日志表结构
Tab.5 Structure of the observation-log table in ClickHouse

在进行数据迁移之前,需要掌握Oracle中的数据量、数据分布等。由于Oracle中的表按照数据类型、测项代码、采样率进行分表,所以通过编写PL/SQL过程来扫描数据库中所有仪器的数据目录并保存到Oracle中,数据目录表结构见表6。扫描过程为:①读取数据库中所有的原始和预处理数据表名。②对每张原始或预处理表,按照台站代码、测点编码、测项分量代码进行分组,查询每个测项分量的数据起止时间、行数、字节数、时间戳最大值,将这些信息保存到数据目录表。

表6 Oracle数据目录表结构
Tab.6 Structure of the data-catalog table in Oracle

数据迁移分为两个阶段:第一阶段一次性迁移全量数据; 第二阶段每天迁移增量数据。迁移的基本单位是ClickHouse中的时序数据表,即某测点的某采样率的原始或预处理数据,采用多线程方式并行提高迁移速度。

由于“九五”向“十五”并网等历史原因,Oracle数据库中的部分数据时间戳字段为0,所以要进行一次全表数据迁移,完成全量数据迁移后,再针对每天Oracle增加的数据定时进行复制。全量数据迁移和增量数据迁移的流程相似,主要区别在于是否对数据按时间戳进行筛选。增量数据迁移的流程见图1。

为保证数据迁移的准确性,笔者编写程序将Oracle与ClickHouse的数据进行对比。如果有错误就将信息输出到日志文件,排查原因后重新迁移。分析得出,除程序自身的BUG外,大多为数据格式或数据精度造成的错误。

迁移前Oracle占用约13 TB磁盘空间,迁移后ClickHouse单个副本占用约4 TB节的三分之二空间。原因是Oracle数据库同一观测对象的时序数据重复部分较多,而ClickHouse是按列存储并自带数据压缩。

ClickHouse是分布式分析数据库,根据实际资源和数据安全需求,在中国地震台网中心使用4台服务器来部署ClickHouse,即2个分片2个副本的节点模式,节点服务器配置见表7。Oracle服务器为单节点。为减少网络传输开销,测试程序在服务器上运行,服务器配置与ClickHouse节点相同。

以地磁、形变、流体3个学科的各1套仪器对Oracle和ClickHouse时序数据读取进行对比,结果见表8。由表8可知,ClickHouse读取速度为Ovalle的5～6倍; 对于相同的查询任务,ClickHouse的CPU占用率较Oracle稍高,IO等待率较Oracle低,内存占用两者相当。可见,数据量越大,ClickHouse的性能优势越明显。

图1 增量数据迁移流程图
Fig.1 Flowchart of incremental data migration

表7 服务器配置
Tab.7 Configuration of servers

表8 时序数据读取对比
Tab.8 Comparison of the time series data reading in Oracle and ClickHouse

ClickHouse支持表连接查询,并提供ALL、ANY、ASOF 3种连接策略。考虑这样的需求,查询所有仪器的测项分量名称、仪器名称、台站名称和机构名称。该查询需要连接台站信息表、机构信息表、仪器信息表、台站仪器运行信息表、台站测项分量信息表、测项分量信息表。该查询Oracle用时约350 ms,ClickHouse用时约280 ms。此外,对表连接查询性能要求较高的场合,ClickHouse可以使用Join和Dictionary表引擎将数据常驻内存来加速查询。

有些查询需求,如查询天津市2021年的数据条数,在Oracle中需要遍历所有测项相关的数据表,而在ClickHouse中仅需要一条SQL语句就可以完成; 如查询某时间段的数据均值,ClickHouse采用预计算技术因而可以毫秒级返回结果,而Oracle由于数据存储在CLOB,无法完成此类查询。

对Oracle这样的OLTP系统,事务完成就标志着数据达到一致性状态。而ClickHouse是后台合并实现更新,合并时间不可预知。在合并完成前读取数据有可能出现同一主键对应多条记录的情况,对此有两种处理方式:①表名后使用final关键字表示要读取最终一致的状态,即合并后的状态,但这会带来较大的时间开销。②应用程序读取时序数据时按数据时间戳和插入时间这两列进行排序,将数据按时间戳对齐放到内存时,如果存在同一主键多条记录的情况,程序会使用最新的数据覆盖掉之前的数据,从而保证内存的数据是最新的。从实际读取测试的效果来看,与不加排序相比,这种方式带来的时间开销可以忽略。使用final关键字的开销较大,大数据量时更为显著,读取时序数据时推荐使用第二种方法来解决最新数据问题。

ClickHouse没有事务的概念,不支持真正意义上的删除,其删除操作也是通过后台合并实现,这个过程是异步的。在业务场景中,预处理软件删除数据时可以通过将数据置空的方式,将删除操作转化为更新操作,由Replacing表引擎实现版本更新。极少数场景下,如果确实需要删除数据,可以手动触发强制合并数据涉及的分区,总体延时比OLTP系统高,但在可接受的范围内。

近年来对地球物理实时数据的应用需求逐渐增加,而现有Oracle原始数据表每行只能按块存储1 d的数据,不能支持实时数据的持久化。在ClickHouse中可以将所有仪器的实时数据按行存储到一张表,充分发挥ClickHouse每秒百万行的读写速度优势。还可以设置TTL(time to live)自动删除过期实时数据。

此外,迁移后的ClickHouse集群经历过一次磁盘故障的考验,体现出良好的健壮性。