ClickHouse 数据存储架构优化
一、背景
我们最初当时的ClickHouse的版本还是在1.x的时代，partition还不支持自定义，只能按月来划分。我们组内的数据存储还是按天来分表设计，例如“XXXX_20200612”。在后面升级了新版本的ClickHouse以后，新业务才开始慢慢使用上parition by day的模式，但是使用的ENGINE也只有MergeTree。 在今年年初我们对几份数据的存储架构进行了改造，记录一下改造过程。
二、改造
1、数据A
（1）旧架构
最早的表engine还是使用MergeTree的，并且因为不同的查询时间跨度有不同的数据时间粒度表，所以会在流式计算落地5min时间粒度的表后，再启一个离线计算从5min聚合计算出1h、1h聚合计算出12h的得到另外2张表。 这样子带来的问题就是由于有一个离线计算的存在，导致每次都需要从CH拉取一小时、十二小时的数据进行计算，离线查询的时候对于集群的稳定性挑战很大。 （2）新架构
新架构其实改造不大，只是简单的把所有的表引擎改造成SummingMergeTree，然后在计算层面对三张表进行三次写入，这样既可把离线计算的逻辑去掉，简化了架构的逻辑，并且降低了计算机器的消耗。
2、数据B
（1）旧架构
这份数据是比较新接入的数据，所以从一开始的设计上就使用了比较新、完善的存储架构，例如已经是partition by day、使用SummingMergeTree引擎，所以对于不同粒度的数据直接多写来实现。但是还是有问题，因为三种时间粒度的缘故，一份数据需要写入三次，对于数据库层面其实是冗余的，并且三倍的写入量对于集群的开销也是很大的，对于zk上的znode也不友好。 （2）新架构
当5min的粒度写入时，利用物化视图让ClickHouse实现对1h、12h的表的拷贝。从以前的三次写入到现在的一次写入，集群的负载降低，并且物化视图的开销很小，得到的收益很大。 数据A的改造为什么不适用物化视图呢？由于是旧有数据，表名还是按天划分，5min的表名是YYYYMMDD，1H跟12H的表名是YYYYMM，为了避免前端查询的改造，所以按天分表的模式没有改动，也没办法使用物化视图来替代三写。
对应建表SQL
CREATE MATERIALIZED VIEW {DATABASE}.{TABLE}_1h_local (
`host` String,
`all_count` Int64,
`time` Int64,
`day` Date) ENGINE = SummingMergeTree PARTITION BY day
ORDER BY
(host,
time) SETTINGS index_granularity = 8192 AS
SELECT
host,
all_count,
intDiv(statistic_time,
3600000) * 3600000 AS time,
day
FROM
{DATABASE}.{TABLE}_5min_local3、数据C
（1）旧架构
这份数据由于QPS高，所以不适合直接查询ClickHouse；并且由于量大，也不适合直接写入到ElasticSearch。所以我们旧有的架构就是先落地1min粒度的数据到ClickHouse中，再从这张表离线聚合出其他时间粒度的数据，完全聚合降低数据量以后再写入到es中。 那看样子好像存储在ClickHouse的5min、1h、12h的数据没什么用处？其实ch中的数据都看成是中间过渡表，不直接用es来聚合是因为量太大如果要完全聚合出结果集对于es的压力很大。那是不是可以直接从1min的数据聚合出1h、12h的数据呢？这样子也省去了再在ch中存储这2个维度的数据。其实还是考虑到数据量太大，直接聚合风险比较高，所以才采用了层层聚合的模式。 这个存储架构的弊端就是中间过滤表就是多余的，占写入、占存储。 （2）新架构 之前的数据存储是直接存时间戳，现在把时间解析拆解，改成存储hour_in_12,hour,minute_in_5,minute这4个字段，并时间粒度由大到小按顺序创建索引。 即把1591950180000（2020-06-12 16:23:00）拆解，改成存储下面4个字段
修改离线查询语句： 例如12h的查询
WHERE hour_in_12 = 1例如1h的查询
WHERE hour_in_12 = 1 and hour = 16例如5min的查询
WHERE hour_in_12 = 1 and hour = 16 and minute_in_5 = 20即通过这样拆解，使得每种离线查询都能命中对应的索引，加快查询以及扫盘的效率。 改造后的新架构图：
三、操作
由于涉及表引擎修改，所以需要重建表、迁移数据。如何稳妥保证线上服务不中断是需要考虑的点。 这次我们的操作步骤是
（1）新建表，表名是旧表名_new。 （2）从旧表拷贝今天之前的历史数据到新表中。拷贝方式有很多，这次我们使用的是上每台节点上执行
INSERT INTO TABLE_new SELECT * FROM TABLE where {partition column} = ''这种方式比较麻烦，需要上去每台执行，并且需要手动查看复制进度。但是胜在简单。 （3）在复制完所以历史数据后，先rename旧表名到旧表名_origin,再麻烦rename 旧表名_new 到 旧表名。这样子数据就完成了表的切换工作。由于rename的操作很快，所以基本可以等同服务正常提供。 （4）rename完以后，新的数据就会写入到新表中。但是对于查询而言新表上今天的数据都是空的，所以再执行下今天数据的拷贝，即可完成整个过程。