关于数据库:如何使用ClickHouse实现时序数据管理和挖掘

ClickHouse 是一个高效的开源联机剖析列式数据库管理系统，由俄罗斯 IT 公司 Yandex 开发的，并于 2016 年 6 月发表开源。本次文章将具体解读京东城市时空数据引擎 JUST（https://just.urban-computing.cn/）是如何应用 ClickHouse 实现时序数据管理和开掘的。

时序数据全称是工夫序列（TimeSeries）数据，是依照工夫顺序索引的一系列数据点。最常见的是在间断的等工夫间隔时间点上获取的序列，因而，它是一系列离散数据 [1]。

时序数据简直无处不在，在目前单向的工夫流中，人的脉搏、空气的湿度、股票的价格等都随着工夫的流逝一直变动。时序数据是数据的一种，因为它显著而有价值的特点，成为咱们特地剖析的对象。

将时序数据能够建模为如下局部组成：

Metric： 度量的数据集，相似于关系型数据库中的 table，是固定属性，个别不随工夫而变动
Timestamp： 工夫戳，表征采集到数据的工夫点
Tags： 维度列，用于形容 Metric，代表数据的归属、属性，表明是哪个设施 / 模块产生的，个别不随着工夫变动
Field/Value： 指标列，代表数据的测量值，能够是单值也能够是多值

一个具体的多值模型时序数据案例如表 1 所示：

表 1 时序数据案例

时序数据管理的流程

所有数据的实质都是为价值服务的，获取价值的这个过程就是数据管理与剖析。从技术上来说，任何数据从产生到灭亡都会经验如图 1 所示的过程。

图 1 数据生命周期

时序数据也不例外，只是每个局部的解决不同。

（1）数据采集 。同一个场景下时序数据产生的频率个别恒定，但在不同场景下采集数据的频率是变动的，每秒一千条和每秒一千万条数据应用的技术是齐全不同的。所以，数据采集要思考的次要是频率和并发。

（2）数据存储 。数据存储是为了查问和剖析服务的。以什么格局存储、建什么索引、存储数据量大小、存储时长是时序数据存储要思考的，个别时序数据写多读少，数据具备时效性，所以存储时能够思考冷热存储拆散。

（3）数据查问和剖析 。时序数据的查问也具备显著特点，个别会依照工夫范畴读取，最近的数据读取频率高，并且依照不同的工夫粒度做聚合查问，比方统计最近一周每天的数据量。

剖析是依赖于查问的，时序数据的剖析通常是多维的，比方网页点击流量、从哪个网站、来自哪个 IP、点击频率等维度泛滥，取决于具体场景。而时序数据也非常适合数据挖掘，利用历史预测将来。

（4）数据删除 。这里的删除并不是针对单条数据的，而是对特定工夫范畴内的批量数据进行过期解决。因为时序数据具备时效性，历史数据通常不再具备价值，不论是定时删除还是手动删除，都代表着其短暂的生命周期的完结。

依据时序数据的特点和场景，咱们须要一个能满足以下指标的时序数据管理平台：

高吞吐写入：千万、上亿数据的秒级实时写入 & 继续高并发写入；
无更新操作：数据大多表征设施状态，写入后无需更新；
海量数据存储：从 TB 到 PB 级；
高效实时的查问：按不同维度对指标进行统计分析，存在显著的冷热数据，个别只会频繁查问近期数据；
高可用；
可扩展性；
易于应用；
易于保护；

说到数据库，大家第一个想到的必定是 MySQL、Oracle 等传统的曾经存在很多年的关系型数据库。当然关系模型仍然无效且实用。对于小数据量（几百万到几千万），MySQL 是能够搞定的，再大一些就须要分库分表解决了。对时序数据个别依照工夫分表，然而这对外部额定设计和运维的工作提出了高要求。显然，这不能满足大数据场景，所以简直没有人抉择这种计划。

纵观 db-engine 上排名前十的时序数据库 [2]，排除商用的，剩下开源的抉择并不多。接下来介绍几款比拟风行的时序数据库。

图 2 db-engine 时序数据库排名

（1）OpenTSDB。OpenTSDB 开源快 10 年了，属于晚期的解决方案。因为其基于 Hadoop 和 HBase 开发的索引，所以具备海量数据的存储能力，也号称每秒百万级别的写入速度。但同样因为其依赖的 Hadoop 生态太重, 运维老本很高，不够简洁与轻量；另一个毛病就是它基于 HBase 的 key-value 存储形式，对于聚合查问并不敌对高效，HBase 存在的问题也会体现进去。

图 3 OpenTSDB 用户界面

（2）InfluxDB。InfluxDB 能够说是时序行业的榜样了，其曾经倒退成为一个平台，包含了时序数据应有的所有：从数据存储到界面展现。然而，InfluxDB 尽管开源了其外围代码，但重要的集群性能只有企业版才提供 [3], 而企业版并不是收费的。很多大公司要么间接应用，要么本人开发集群性能。

图 4 InfluxDB 各版本反对的性能

（3）TDengine。TDengine 是涛思团队开发的一个高效存储、查问和剖析时序大数据的平台，其创始人陶建辉年近 5 旬，仍然开发出了这个数据库。

TDengine 的定位是物联网、车联网、运维监测等时序数据，其设计也是专门针对每个设施。每个采集点一张表，比方空气监测站有 1000 万个，那么就建 1000 万个表，为了对多个采集点聚合查问，又提出了超表的概念，将同类型的采集点表通过标签辨别，构造一样。这种设计的确十分有针对性，尽管限度了范畴，但极大进步了效率，依据其官网的测试报告 [4], 其聚合查问速度是 InfluxDB 的上百倍，CPU、内存和硬盘耗费却更少。

图 5 涛思团队给出的不同时序数据库性能比照

TDengine 无疑是时序数据库的一朵奇葩，加上在不久前开源了其集群性能 [5]，受到了更多用户青眼。当咱们选型时其还没有开源集群性能，后续也会纳入察看之中。

（4）ClickHouse。ClickHouse（之后简称 CK）是一个开源的大数据分析数据库，也是一个残缺的 DBMS。CK 无疑是 OLAP 数据库的一匹黑马，开源不到 4 年，GitHub 上的 star 数曾经超过 12k（InfluxDB 也不过 19k+），而它们的 fork 数却相差不大。

CK 是俄罗斯的搜索引擎公司 yandex 开源的，最后是为了剖析网页点击的流量，所以叫 Click，迭代速度很快，每个月一版，开发者 500+，很多都是开源共享者，社区十分沉闷。

CK 是一个通用的剖析数据库，并不是为时序数据设计的，但只有应用切当，仍然能施展出其弱小的性能。

要利用 CK 的劣势，首先得晓得它有哪些劣势，而后了解其外围原理。依据咱们的测试后果，对于 27 个字段的表，单个实例每秒写入速度靠近 200MB，超过 400 万条数据 /s。因为数据是随机生成的，对压缩并不敌对。

而对于查问，在可能利用索引的状况下，不同量级下（百万、千万、亿级）都能在毫秒级返回。对于极限状况：对多个没有索引的字段做聚合查问，也就是全表扫描时，也能达到 400 万条 / s 的聚合速度。

能够归结为抉择和细节，抉择决定方向，细节决定成败。

CK 抉择最优的算法，比方列式压缩的 LZ4[6]；抉择着眼硬件，充分利用 CPU 和分级缓存；针对不同场景不同解决，比方 SIMD 利用于文本和数据过滤；CK 的继续迭代十分快，不仅能够迅速修复 bug，也能很快纳入新的优良算法。

（1）CK 是一个纯列式存储的数据库，一个列就是硬盘上的一个或多个文件（多个分区有多个文件），对于列式存储这里就不开展了，总之列存对于剖析来讲益处更大，因为每个列独自存储，所以每一列数据能够压缩，不仅节俭了硬盘，还能够升高磁盘 IO。

（2）CK 是多核并行处理的，为了充分利用 CPU 资源，多线程和多核必不可少，同时向量化执行也会大幅提高速度。

（3）提供 SQL 查问接口，CK 的客户端连贯形式分为 HTTP 和 TCP，TCP 更加底层和高效，HTTP 更容易应用和扩大，一般来说 HTTP 足矣，社区曾经有很多各种语言的连贯客户端。

（4）CK 不反对事务，大数据场景下对事务的要求没这么高。

（5）不倡议按行更新和删除，CK 的删除操作也会转化为减少操作，粒度太低重大影响效率。

生产环境中通常是应用集群部署，CK 的集群与 Hadoop 等集群略微有些不一样。如图 6 所示，CK 集群共蕴含以下几个要害概念。

图 6 CK 集群示例

（1）CK 实例 。能够一台主机上起多个 CK 实例，端口不同即可，也能够一台主机一个 CK 实例。

（2）分片 。数据的程度划分，例如随机划分时，图 5 中每个分片各有大概一半数据。

（3）正本 。数据的冗余备份，同时也可作为查问节点。多个正本同时提供数据查问服务，可能放慢数据的查问效率，进步并发度。图 5 中 CK 实例 1 和示例 3 存储了雷同数据。

（4）多主集群模式 。CK 的每个实例都能够叫做正本，每个实体都能够提供查问，不辨别主从，只是在写入数据时会在每个分片里长期选一个主正本，来提供数据同步服务，具体见下文中的写入过程。

要实现分片的性能，须要分布式引擎。在集群状况下，CK 里的表分为本地表和分布式表，上面的两条语句可能创立一个分布式表。留神，分布式表是一个逻辑表，映射到多个本地表。

create table t_local on cluster shard2_replica2_cluster(t Datetime, id UInt64)  
ENGINE=ReplicatedMergeTree('/clickhouse/tables/{shard}/t_local','{replica}')
PARTITION BY toYYYYMM(t)
ORDER BY id
create table t on cluster shard2_replica2_cluster  (t Datetime, id UInt64) 
ENGINE=Distributed(shard2_replica2_cluster,default,t_local,id)

这里的 t_local 就是本地表，t 就是分布式表。ReplicatedMergeTree 是实现正本同步的引擎，参数能够先疏忽。Distributed 引擎就是分布式引擎，参数别离为：集群名，数据库名，本地表名，分片键（能够指定为 rand() 随机数）。

分布式引擎在写入和查问过程中都充当着重要的角色，具体过程见上面。

依据应用的表引擎不同，写入过程是不同的，上文的建表形式是比拟惯例的做法，依照下面的建表语句，须要同时开启外部复制项。

<shard2_replica2_cluster>
       <shard>
               <weight>1</weight>
               <internal_replication>true</internal_replication>
               <replica>
                        …
               </replica>
               <replica>
                        …
                </replica>
       </shard>

写入 2 条数据：insert into t values(now(), 1), (now(),2)，如图 7 所示，写入过程分为 2 步：分布式写入和正本同步。

图 7 CK 写入过程

（1）分布式写入

1）客户端会抉择集群里一个正本建设连贯，这里是实例 1。写入的所有数据先在实例 1 实现写入，依据分片规定，属于 01 分片的写入实例 1 本地，属于 02 分片的先写入一个长期目录，而后向实例 2（shard02 的主正本）建设连贯，发送数据到实例 2。

2）实例 2 接管到数据，写入本地分区。

3）实例 1 返回写入胜利给客户端（每个分片写入一个正本即可返回，能够配置）。

（2）正本同步

同步的过程是须要用到 ZK 的，下面建表语句的 ReplicatedMergeTree 第一个参数就是 ZK 上的门路。创立表的时候会有一个正本选举过程，个别先起的会成为主正本，正本的节点信息会注册到 ZK，ZK 的作用只是用来保护正本和工作元数据以及分布式通信，并不传输数据。正本一旦注册胜利，就开始监听 /log 下的日志了，当正本上线，执行插入时会通过以下过程：

1）实例 1 在写入本地分区数据后，会发送操作日志到 ZK 的 /log 下，带上分区名称和源主机（实例 1 的主机）。

2）01 分区的其余正本，这里就实例 3，监听到日志的变动，拉取日志，创立工作，放入 ZK 上的执行队列 /queue（这里都是异步进行），而后再依据队列执行工作。

3）执行工作的过程为：抉择一个正本（数据量最全且队列工作起码的正本），建设到该正本（实例 1）的连贯，拉取数据。

留神，应用正本引擎却不开启外部复制是不明智的做法，因为数据会反复写，尽管数据校验能够保证数据不反复，但减少了无畏的开销。

查问的是分布式表，但要定位到理论的本地表，也就是正本的抉择，这里有几种抉择算法，默认采纳随机抉择。响应客户端查问申请的只会有一个正本，然而执行过程可能波及多个正本。比方：select count(*) from t。因为数据是散布在 2 个分片的，只查一个正本不能失去全副后果。

图 8 CK 多实例查问过程

CK 外围的引擎就是 MergeTree，在此之上产生了很多附加引擎，上面介绍几种比拟罕用的。

（1）ReplacingMergeTree。为了解决 MergeTree 主键能够反复的特点，能够应用 ReplacingMergeTree，但也只是肯定水平上不反复：仅仅在一个分区内不反复。应用形式参考：https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replacingmergetree/

（2）SummingMergeTree。对于确定的 group by + sum 查问，若比拟耗时，那么能够建 SummingMergeTree, 依照 order by 的字段进行聚合或自定义聚合字段，其余字段求和。

（3）AggregatingMergeTree。聚合显然是剖析查问的重点，个别应用聚合 MergeTree 都会联合物化视图，在插入数据时主动同步到物化视图里，这样间接查问物化视图中聚合的后果即可。

（4）ReplicatedXXXMergeTree。在所有引擎前加一个 Replicated 前缀，将引擎降级为反对正本性能。

（5）物化视图。物化视图就是将视图 SQL 查问的后果存在一张表里，CK 里非凡的一点是：只有 insert 的数据能力进入触发视图查问，进入视图表，分布式状况下同步过来的数据是不会被触发的，为了在分布式下应用物化视图，能够将物化视图所依赖的表指定为分布式表。

在理解了 CK 的基本原理后，咱们看看其在时序数据方面的解决能力。

（1）工夫：工夫是必不可少的，依照工夫分区可能大幅升高数据扫描范畴；

（2）过滤：对条件的过滤个别基于某些列，对于列式存储来说劣势显著；

（3）降采样：对于时序来说十分重要的性能，能够通过聚合实现，CK 自带工夫各个粒度的工夫转换函数以及弱小的聚合能力，能够满足要求；

（4）剖析开掘：能够开发扩大的函数来反对。

另外 CK 作为一个大数据系统，也满足以下根底要求：

（1）高吞吐写入；

（2）海量数据存储：冷热备份，TTL；

（3）高效实时的查问；

（4）高可用；

（5）可扩展性：能够实现自定义开发；

（6）易于应用：提供了 JDBC 和 HTTP 接口；

（7）易于保护：数据迁徙不便，复原容易，后续可能会将依赖的 ZK 去掉，内置分布式性能。

因而，CK 能够很不便的实现一个高性能、高可用的时序数据管理和剖析零碎。上面是关键点的具体介绍。

时序查问场景会有很多聚合查问，对于特定场景，如果应用的十分频繁且数据量十分大，咱们能够采纳物化视图进行预聚合，而后查问物化视图。然而，对于一个通用的剖析平台，查问条件能够随便扭转的状况下，应用物化视图的开销就太大了，因而咱们目前并没有采纳物化视图的形式，而是采纳原始的表。物化视图的计划后续将会进一步验证。

上面给出的是 JUST 建时序表的语法格局：第一个括号为 TAG 字段，第二个括号为 VALUE 字段（必须是数值型），大括号是对底层存储的非凡配置，这里次要是 CK 的索引和参数。除了用户指定的字段外，还有一个隐含的 time 字段，专为时序保留。

create table my_ts_table as ts (
    tag1 string,
    tag2 String [:primarykey][:comment=’形容’]
)
(
    value1 double,
    value2 double
)

在 JUST 底层，对应了 CK 的 2 张表（一张本地表，一张分布式表），默认会依据 time 分区和排序，如上面的一个例子：

create table my_ts_table as ts (
    tag1 string,
    tag2 String [:primarykey][:comment=’形容’]
)
(
    value1 double,
    value2 double
)

理论对应的 CK 建表语句为：

CREATE TABLE just.username_dbname_airquality_local
(
    `id` Int32,
    `oid`Int32,
    `name`String,
    `city`String,
    `time`DateTime,
    `PM10`Float64,
    `PM25`Float64
)
ENGINE =ReplicatedMergeTree('/clickhouse/tables/{shard}/24518511-2939-489b-94a8-0567384d927d','{replica}')
ORDER BY (time)
SETTINGS index_granularity = 8192
PARTITION BY toYYYYMM(time)

CREATE TABLE just.wangpeng417_test_airquality
(
    `id` Int32,
    `oid`Int32,
    `name`String,
    `city`String,
    `time`DateTime,
    `PM10`Float64,
    `PM25`Float64
)
ENGINE = Distributed('just_default', 'just', 'username_dbname_airquality_local',rand())

这样保障在应用工夫范畴查问时能够利用到索引，如果还有其余依照 TAG 的查问条件，还能够自定义索引和排序字段 [LL1]（CK 规定索引字段肯定是排序字段的前缀）。

在不同场景下，还是须要依据数据量和数据特点来抉择索引分区和索引粒度。依据试验测试，如果在咱们环境里 CK 每秒能够扫描 1GB 数据量，再乘以 1 -10 倍的压缩比，那么一个分区的数据量应该大于千万到亿级别能够保障较优的速度，CK 自身是多线程查问的，能够保障同时对每个分区查问的隔离性。然而依据查问的场景，比方最多查到一个月，但大部分状况都是查一周，那么分区准确到周可能更好，这是个综合衡量的过程。

在 JUST 中，高可扩展性和高可用性是咱们的谋求。为实现高可扩展性，咱们对数据进行程度分片；为了实现高可用性，咱们对每个分片存储至多两个正本。

对于集群部署，最小化的状况是 2 台机器，这会产生 2 种状况 1）穿插正本；2）一主一备；如图 9 所示：

图 9 两种正本的情景

这两种计划对查问和写入影响的试验后果如图 10 所示:

图 10 两种正本的写入和查问后果比照

试验结果表明：写入速度（横坐标为写入示例数，纵坐标为速度 MB/s）在达到极限时是差不多的，而查问性能（横坐标为 SQL 编号,SQL 语句见附录 1，纵坐标为耗时，单位为秒）对于简略查问差异不大，然而对于占用大量资源的简单查问，一主一备更加高效。因为 CK 的强悍性能是建设在充分利用 CPU 的根底上，在咱们的测试中，裸机状况下 CPU 达到 90% 以上十分频繁，如果有独自的机器部署 CK，那么无可非议可能充分利用机器资源。但在咱们的环境中，与其余大数据平台共有机器，就须要防止 CK 占用过多资源，从而影响其余服务，于是咱们抉择 docker 部署。docker 容器部署也有开源的基于 k8s 的实现：clickhouse-operator，对于小型环境，能够抉择手动配置，通过简略的脚本即可实现自动化部署。

基于以上测试论断，为了保障服务高可用，CK 集群和数据冗余是必不可少的，咱们的计划是保障至多 2 个正本的状况下，分片数尽量多，充分利用机器，且每个机器有且仅有一个 CK 实例。于是就有了以下分片数与正本数的公式：

其中_f_(_n_) 代表当有_n_台机器时，部署的散布状况，n>=2。_f_(2) = (1, 2) 示意 2 台机器采纳 1 个分片 2 个正本部署的策略，_f_(3)=(1, 3) 示意 3 台机器时 1 个分片 3 个正本部署策略，_f_(4)=(2, 2) 示意 4 台机器应用 2 个分片，每个分片 2 个正本，以此类推。

随着数据量减少，须要扩大节点时，能够在不停机的状况下动静扩容，次要利用的是分片之间的权重关系。

这里扩容分为两种状况：

（1）减少正本：只须要批改配置文件，减少正本实例，数据会主动同步，因为 CK 的多主个性，正本也能够当作查问节点，所以能够分担查问压力；

（2）减少分片：减少分片要麻烦点，须要依据以后数据量、减少数据量计算出权重，而后在数据量达到平衡时将权重批改回去

如果开始时咱们只有 1 个分片，曾经有 100 条数据了。

<test_extend>
       <shard>
              <weight>1</weight>
              <internal_replication>true</internal_replication>
              <replica>
                     <host>10.220.48.106</host>
                     <port>9000</port>
              </replica>
              <replica>
                     <host>10.220.48.105</host>
                     <port>9000</port>
              </replica>
       </shard>
</test_extend>

当初要再退出一个分片，那么权重的计算过程如下（为了简化疏忽这个期间插入的数据）：

如果咱们打算再插 n 条数据时，集群数据可能平衡，那么每个 shard 有 (n+100)/2 条，当初 shard01 有 100 条，设权重为 w1、w2，那满足公式：n * (w2/(w1+w2)) = (n+100)/2，其中 n >100, 所以，如果 w1=1，n=200，那么 w2=3。

所以，将配置批改如下：

<test_extend>
       <shard>
              <weight>1</weight>
              <internal_replication>true</internal_replication>
              <replica>
                     <host>10.220.48.106</host>
                     <port>9000</port>
              </replica>
              <replica>
                     <host>10.220.48.105</host>
                     <port>9000</port>
              </replica>
       </shard>
       <shard>
              <weight>3</weight>
              <internal_replication>true</internal_replication>
              <replica>
                     <host>10.220.48.103</host>
                     <port>9000</port>
              </replica>
       </shard>
</test_extend>

等到数据同步平均后再改回 1：1。

JUST 时序剖析底层应用了 CK 作为存储查问引擎，并开发了可复用的可视化剖析界面，欢送拜访 https://just.urban-computing.cn/ 进行体验。

图 11 JUST 时序剖析模块示意图

用户能够应用对立的查问界面建设时序表，而后导入数据，切换到时序剖析模块进行可视化查问。

图 12 JUST 建设时序示意用意

目前提供的查问性能次要有：按工夫查问、按 TAG 过滤，在数据量很多的状况下，能够依照大一些的工夫粒度进行降采样，查看整个数据的趋势，同时提供了线性、拉格朗日等缺失值填补性能。

剖析开掘局部次要是按找特定值和百分比过滤，以及一些简略的函数转换。

目前时序模块的性能还比拟简陋，对于时序数据的 SQL 查问反对还不够齐备。将来还有集成以下性能：

（1）接入实时数据；

（2）针对简单查问，面板性能能够采纳聚合引擎事后聚合；

（3）更欠缺的剖析和开掘性能；

（4）对数据的容错与校验解决；

（5）与 JUST 统一的 SQL 查问反对。

[1]https://en.wikipedia.org/wiki…

[2]https://db-engines.com/en/ran…

[3]https://www.influxdata.com/bl…

[4]https://www.taosdata.com/down…

[5]https://www.taosdata.com/blog…

[6]lz4.LZ4[EB/OL].https://lz4.github.io/lz4/,20…

[7]https://clickhouse.tech/docs/…

比 MySQL 快 839 倍！揭开剖析型数据库 JCHDB 的神秘面纱
亿级数据库毫秒级查问？看完这一篇，海量数据赋能你也行
乘风破浪，云时代的 DBA 须要做好哪些筹备？

欢送点击 【京东智联云】，理解开发者社区

更多精彩技术实际与独家干货解析

欢送关注【京东智联云开发者】公众号

关于数据库:如何使用ClickHouse实现时序数据管理和挖掘

1，时序数据简介

2，时序数据管理概述

2. 时序数据管理系统指标

3. 技术选型

3，CK 原理介绍

1. CK 为什么快？

2. CK 根底

3. CK 集群

4. CK 分布式引擎

5. CK 写入过程

6. CK 查问过程

7. CK 中重要的索引引擎

4，CK 与时序的联合

1. 时序索引与分区

2. 部署与高可用

3. 动静扩容

4. 零碎介绍与有余

参考链接：

举荐浏览：

Just My Socks（注册教程内含优惠码）

关于数据库:如何使用ClickHouse实现时序数据管理和挖掘

1，时序数据简介

2，时序数据管理概述

2. 时序数据管理系统指标

3. 技术选型

3，CK 原理介绍

1. CK 为什么快？

2. CK 根底

3. CK 集群

4. CK 分布式引擎

5. CK 写入过程

6. CK 查问过程

7. CK 中重要的索引引擎

4，CK 与时序的联合

1. 时序索引与分区

2. 部署与高可用

3. 动静扩容

4. 零碎介绍与有余

参考链接：

举荐浏览：

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）