分析數據庫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.