分析数据库ClickHouse在国家地球物理台网中心的应用

王军 黄经国 余丹 纪寿文 王方建

摘要：面对日益增长的地球物理观测数据，传统的Oracle数据库已经力不从心。根据地球物理历史数据可大量被读取的使用特点，通过技术选型，以具备横向扩展能力的分布式OLAP数据库ClickHouse作为数据底座，重新设计适用于ClickHouse的地球物理观测数据表结构。经过全库迁移、增量迁移和数据对比，国家地球物理台网中心的Oracle数据库中约13 TB的数据已经迁移到ClickHouse并每日更新。实际测试表明：ClickHouse显著提升数据读写性能，增强了数据统计查询能力，并且通过多副本保证了数据库的一致性和安全性。

关键词：ClickHouse；大数据；地球物理台网；观测数据

中图分类号：P315-391.2   文献标识码：A   文章编号：1000-0666（2023）01-0308-07

doi：10.20015/j.cnki.ISSN1000-0666.2023.0019

0 引言

2007年我國建成“十五”数字地震观测网络，中国地震台网中心是该网络的数据汇集与存储中心，汇集了地下流体、地电、形变、重力、地磁5个学科的全国地球物理观测数据。这些数据都存储在按“十五”数据库结构设计的Oracle10g数据库中（周克昌等，2009）。随着观测仪器的增多和高采样率仪器的入网，目前存储的数据已占用13 TB磁盘空间，如此庞大的数据库逐渐显现数据访问速度较慢、使用不方便等问题，给数据的分析应用带来挑战。一些研究表明，通过使用OCI接口（王军等，2008，2016）、CLOB字段压缩、并行化读取等技术手段进行优化（王建军等，2019；刘坚等，2019；李井冈等，2008；陈晓琳等，2020），可以在一定程度上提升数据库的读写速度。但受限于Oracle的单节点I/O瓶颈（尽管Oracle有RAC，但存储仍是共享的）（谷长勇等，2011），这些优化手段的效果随着数据量的增长会逐渐减弱，没有从根本上解决性能问题。

ClickHouse是一个开源、免费的联机分析处理（On-Line Analytical Processing，简称OLAP）数据库，相对于传统的联机事务处理（On-Line Transaction Processing，简称OLTP）数据库，如Oracle和MySQL，ClickHonse具有按列存储、使用预计算加速聚合函数查询、自带数据压缩、向量化引擎、可以在多个服务器上分布式处理等优点（朱凯，2020）；同时，也比行式数据库具有更高的性能（Wickramasekara et al，2020；Dwivedi et al，2012）。此外，ClickHouse还具备横向扩展能力，可以通过向集群增加新的节点来提升数据处理能力。ClickHouse的缺点是不支持事务、不支持严格意义上的数据删除和更新，但在实际应用中影响不大或者可以采用一些技术手段加以克服。ClickHouse集群支持数据自动复制，支持数据的完整性和最终一致性；集群中的节点没有主从之分，不会因为主服务器故障导致服务不可用。相对于Tdengine和IOTDB等分布式时序数据库（王焕涛等，2021），ClickHouse的数据类型更丰富，对SQL（结构化查询语言，Structured Query Language）语句的支持更广泛，系统也更成熟和稳定（李亚臣，2021）。经过选型对比，本文使用ClickHouse作为分析数据库进行设计，并将原始数据和预处理数据向ClickHouse进行迁移，最后对Oracle数据库和ClickHouse数据库进行对比研究。

1 数据库设计

1.1 基础信息

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

1.2“十五”观测数据表结构

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

1.3 ClickHouse的特性与表结构设计

（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，分布式表与本地表字段相同。

（5）时序数据表

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

（6）产品数据表

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

（7）日志表

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

2 历史数据迁移

考虑到Oracle中最常用的是原始数据和预处理数据，而且表结构基本相同，所以先将原始和预处理数据迁移到ClickHouse数据库。

2.1 数据目录

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

2.2 数据迁移

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

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

2.3 数据对比

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

3 应用效果

本次共迁移Oracle中265张表共5 355个测点的原始和预处理数据，并从以下几个方面将OLTP数据库和ClickHouse数据库进行对比。

3.1 磁盘占用

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

3.2 服务器配置

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

3.3 时序数据读取

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

3.4 表的连接

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

3.5 其它查询

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

3.6 数据的更新问题

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

3.7 数据的删除问题

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

3.8 实时数据支持

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

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

4 结论

本文使用ClickHouse作为国家地球物理台网中心的应用数据库，按照地球物理台网数据的使用特点，重新设计适合于ClickHouse的数据表结构，并将Oracle數据库的265张表（5 355个测点）的原始和预处理数据迁移到ClickHouse，主要得出以下结论：

（1）数据迁移后，大数据量场景下时序数据读取速度提升约5～10倍，占用磁盘空间仅为Oracle的三分之一，同时通过多副本增强了系统的高可用性和数据安全性。

（2）ClickHouse支持常用的表连接策略，连接性能与Oracle相当，并且支持与MySQL数据库的跨库连接。

（3）ClickHouse的数据更新问题可以使用Replacing表引擎以及数据读取时在应用端对齐的方法解决。

由于时序数据的存储结构发生较大变化，地球物理数据处理和分析软件也需要做出相应修改，ClickHouse的应用还有很多工作要完成。ClickHouse可以跟其他大数据组件如Kafka、Pulsar等消息中间件，以及Spark、Flink等计算框架紧密集成，未来可以建设大数据架构下的流批一体化地球物理数据处理平台。

参考文献：

陈晓琳，李盛乐，刘坚，等.2020.分布式数据库Greenplum在地震前兆数据存储中的应用［J］.地震研究，43（2）：412-416.

谷长勇，吴逸云，单永红，等.2011.Oracle 11g权威指南（2版）［M］.北京：电子工业出版社.

李井冈，姚运生，李胜乐，等.2008.基于Oracle 的地震前兆数据库表结构对比［J］.计算机工程与设计，29（1）：243-245.

李亚臣.2021.基于ClickHouse的用户事件分析系统的设计与实现［J］.信息与电脑：33（9）：4.

刘坚，李胜乐，王子影.2009.基于LZMA的数据库压缩存储应用研究［J］.大地测量与地球动力学，29（6）：144-147.

王焕涛，周念，陈俊彦.2021.基于时序数据库的氢能源动力监测系统的设计［J］.电子技术与软件工程，（16）：2.

王建军，赵银刚，刘高川.2019.地震前兆Oracle LOB数据压缩与交换及其访问效率研究［J］.地震研究，42（3）：447-453.

王军，方召盟，何案华，等.2016.电扰动仪数据处理软件的设计和实现［J］.震灾防御技术，11（3）：667-673.

王军，赵刚，何案华，等.2008.通过C++类封装Oracle调用接口实现地热数据库的快速建库与访问［J］.地震研究，31（3）：293-297.

周克昌，蒋春花，纪寿文，等.2010.地震前兆数据库系统设计［J］.地震，30（2）：143-151.

朱凯.2020.ClickHouse原理解析与应用实践［M］.北京：机械工业出版社.

Dwivedi A K，Lamba C S，Shukla S.2012.Performance analysis of column oriented database vs row oriented database［J］.International Journal of Computer Applications，50（14）：31-34.

Wickramasekara A，Liyanage P P，Kumarasinghe U.2020.A comparative study between the capabilities of MySQL and ClickHouse in low-performance Linux environment［C］//University of Colombo School of Computing.Conference Proceedings of the 20th International Conference on Advances in ICT for Emerging Regions（ICTer）-2020.Sri Lanka.

Application of the Analytical Database ClickHouse in the NationalGeophysical Observatory Network Center

WANG Jun，HUANG Jingguo，YU Dan，JI Shouwen，WANG Fangjian

（China Earthquake Networks Center，Beijing 100045，China）

Abstract

Traditional Oracle database has been unable to cope with the rapidly-growing seismic geophysical observation data.To meet the need of mass data processing of the historical geophysical data from the National Geophysical Observatory Network Center，through technology selection，the distributed OLAP database ClickHouse with horizontal expansion capability is used to redesign the structure of the data table which is suitable for ClickHouse.By full database migration，incremental migration，and data comparison，about 13 TB data in the Oracle database have been migrated to the ClickHouse database and have been keeping daily updating.Tests show that in ClickHouse，the performance of data reading and writing is significantly improved，the capability of data query is enhanced，and the consistency and security of the database is guaranteed through multiple copies.

Keywords：ClickHouse；big data；geophysical networks；observation data

JOURNAL OF SEISMOLOGICAL RESEARCH

Vol.46  No.2  Series No.206   April，2023

CONTENTS

Discussion on the Importance of the Features for the Judgement of Earthquake Sequence Types Applicable to Machine LearningJIANG Haikun，WANG Jinhong（172）

Research Progress in Field of Earthquake Prediction by Machine Learning Based on Seismic DataWANG Jinhong，JIANG Haikun（187）

Analysis of the Present-day Activity of the Qingchuan Fault and Its VicinityWU Weiwei，LIANG Mingjian，LONG Feng，SU Jinrong，CHEN Xuefen（203）

Comparative Analysis of the Activity Characteristics and the Aftershock Forecasting Efficiency of Two Earthquake Sequencesin LushanBI Jinmeng，SONG Cheng，MA Yong（215）

Determination of Three Parameters of the 2021 Maduo MW7.4 Earthquake Using High-rate BDS/GPSZHANG Huai，NIE Zhaosheng，LIU Gang，XIONG Wei，NI Yipeng，HUANG Jun（225）

Retrospective Study on the Forecast of the 2021 Maduo MS7.4 Earthquake by PI MethodSONG Cheng，ZHANG Yongxian，ZHOU Shaohui，BI Jinmeng，XU Xiaoyuan（236）

Analysis of Soil Hydrogen Anomaly in the Huoshan Seismic Window AreaFANG Zhen，HUANG Xianliang，TAO Yuechao，LI Shenliang，  TAO Fangyu，YANG Yuanyuan，ZHU Houlin，LU Dongliang（244）

Characteristics of 3D Velocity Structure in the Arcuate Tectonic Belt of Southeastern YunnanCAO Ying，FU Hong，QIAN Jiawei（260）

The Holocene Activity Evidence of Paleo-earthquakes in the Northwestern Segment of the Deqin-Zhongdian FaultCHANG Yuqiao，LI Xi，ZHOU Qingyun，BAI Xianfu，RAN Hua，LUO Weidong（270）

Seismic Resilience Analysis of Water Distribution Networks with Multi-components and Resilience Improvement StrategiesLIU Wei，WU Qianxiang（279）

Seismic Resilience Evaluation of the Traffic System Based on the Dynamic Bayesian NetworkCHEN Yiqin，HUANG Shuping（290）

Study on Intensity Parameters of the Monopole Communication Tower Subjected to the Pulse-like Ground MotionLI Bo，LI Xiaofei，WANG Zhijiang（299）

Landslide Recognition After the 2021 Haiti MS7.2 Earthquake Based on the Improved YOLOv4 AlgorithmFU Rao，HE Jing，LIU Gang（307）

Application of the Analytical Database ClickHouse in the National Geophysical Observatory Network CenterWANG Jun，HUANG Jingguo，YU Dan，JI Shouwen，WANG Fangjian（314）

收稿日期：2022-02-15.

基金項目：公共安全信息化工程（中国地震局建设项目）（12151013401）.

第一作者简介：王 军（1979-），高级工程师，主要从事地震监测数据管理与软件开发.E-mail：wangjun825@163.com.

王军，黄经国，余丹，等.2023.分析数据库ClickHouse在国家地球物理台网中心的应用［J］.地震研究，46（1）：308-314，doi：10.20015/j.cnki.ISSN1000-0666.2023.0019.