如何高效处理亿级海量数据-Clickhouse速通（下）

前文： 如何高效处理亿级海量数据-Clickhouse速通（上）
接前文，本文将主要讲解如何在已有表结构的基础上进行统计。

一. 存算一体

    Clickhouse里可以直接进行实时统计。通常的数据库，一般都会进行存储和计算分离，数据库仅仅用于存储，而计算交由另外的程序执行。而Clickhouse则极大拓展了计算能力，使得可以直接在数据库里做好统计，其它程序仅仅做读取即可。

    为什么这样做？这样做有哪些好处呢？

a. 减少数据读取。
    假设数据量是10亿，如果交由外部程序进行数据分析，就需要将这10亿数据读出，而后再将计算后的数据写回数据库。这一次读写可能就需要非常长的时间。

b. 降低磁盘压力的同时，实时计算。
    Clickhouse允许使用MergeTree引擎，在数据写入的时候就进行一些统计计算，从而不需要再将数据反复读出。从而可以将统计结果以近实时的速度得出。一方面减轻了磁盘压力，也节省了整体的资源消耗，另一方面可以进行实时分析。

c. 横向扩展方便。
    还有一点就是Clickhouse是使用的shared-nothing架构，意味着每一个节点的增加都能同步等比例增加计算、存储能力。如此就省却了额外的计算节点的扩容的维护环节。
    当然有利就会有弊，在本文结尾处会讨论一些Clickhouse的弊端。

二. 如何实现？

    利用MergeTree引擎的特性，小批量统计，后台合并。

    Clickhouse的统计函数很多，在查询的时候很好用。但更好用的是结合MergeTree引擎的特性，使用统计函数进行实时统计。
    关于MergeTree和索引有很多解释很好的文章，这里仅做一个简单的解释。MergeTree的特点是，有数据就直接按照整个批量（part）写入磁盘，而后再后台按照周期进行合并每个part。MergeTree规定了一个叫颗粒度的参数，默认值是8192 （2的13次方）。后台合并的时候，Clickhouse会将各个part进行排序、归并等操作，并以8192为大小来划分颗粒度，按照颗粒进行记录索引。
    MergeTree的批量写入和定期合并特性，可以让实时统计发挥作用。在小批量写入的时候，可以做局部统计；在后台合并的时候，可以将局部统计合并成更多批量汇总的统计结果。

    用一个小例子说明：

1. 假设一个数据是用来记账的，两条数据如下：    苹果，15元；    午餐，30元;2. 这是午餐买的东西。两个数据一次性写入，那么就可以计算一下总计：    2024/7/16 支出，45元3. 到了晚上你又有一笔开支，假设还是两条：    饮料，5元；    晚餐，15元;4. 两个数据还是一次性写入，那么就可以计算一下这个批次的总计：    2024/7/16 支出，20元5. 后台有个定时程序会合并这两个数据：    2024/7/16 支出 65 元6. 当你过了一会儿，比如1分钟后去查询的时候，就会看到这个最终的结果65，而不是分开的两条数据。

    Clickhouse就是用类似的思路，来达成实时分析的，类似小计+汇总。使用的是SummingMergeTree或者AggregatingMergeTree等表引擎用来存储，都是属于MergeTree家族的。按照给定的查询语句，利用物化视图将统计数据发送到存储表，而后存储表会后台合并统计结果。一般流程如下：

原始数据表 -> 物化视图（含统计语句） -> 结果发送到存储表（SummingMergeTree/AggregatingMergeTree） -> 后台合并各个批次的统计结果 -> 查询最终统计结果

三. 利用Clickhouse统计的流程

    下文会按照以下步骤介绍如何使用Clickhouse进行统计。这个流程是比较完整的使用过程，方便产品中使用。如果只是简单统计，也可以参照官方案例，直接创建物化视图，不创建中间表。

1. 基于现有的数据表；
2. 创建物化视图的存储表；
3. 插入已有数据；
4. 创建物化视图；
5. 插入新数据查看统计结果更新情况；
6. 技巧1：多层物化视图；
7. 技巧2：对离散字段的值进行统计；

四. 实践步骤

1. 基于现有的数据表；
现有表结构在 如何高效处理亿级海量数据-Clickhouse速通（上） 已经介绍。这里不再赘述。基于现有表创建一个统计表。

2. 创建物化视图的存储表；

CREATE TABLE log_agg_minute
(
`minute` DateTime CODEC(Delta(4), ZSTD(3)),
`domain` LowCardinality(String),
`all_request` SimpleAggregateFunction(sum, UInt64),
`package_size_sum` SimpleAggregateFunction(sum, UInt64),
`client_ip_Map` SimpleAggregateFunction(sumMap, Map(IPv4, UInt64)) CODEC(ZSTD(3)),
`client_region_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(3)),
`method_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(3)),
`response_statuscode_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(3)),
`request_ua_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6)),
`request_accept_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6)),
`request_accept_encoding_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6)),
`request_accept_language_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(minute)
PRIMARY KEY (domain, minute)
ORDER BY (domain, minute)
TTL minute + toIntervalYear(1)
SETTINGS index_granularity = 1440;

其中：

• 表引擎：
  SummingMergeTree/AggregatingMergeTree  这两种表引擎都可以用来进行日常的统计，都能自动按照主键聚合。所不同的是前者用于处理简单的求和类的聚合；后者可以处理更多类型的聚合，包括各种AggregateFunction、SimpleAggregateFunction类型的字段。

• 主要有两种字段：
  SimpleAggregateFunction(sum, UInt64) 用于统计数字型的聚合，也就是简单求和。
  SimpleAggregateFunction(sumMap, Map(String, UInt64)) 用于统计字段取值的计数，例如：统计性别里男女各有多少。
  AggregateFunction、SimpleAggregateFunction类型字段主要区别在于，后者不需要记录中间值，其特性是[1]：

  f(S1 UNION ALL S2) = f(f(S1) UNION ALL f(S2))

  例如：sum(list1+list2) = sum(list1) + sum(list2)

表创建完成后插入数据。

3. 插入已有数据；
如果没有已存在数据，可以跳过这段。

INSERT INTO log_agg_minute
SELECT
   toStartOfMinute(time) AS minute,
   domain,
   count() AS all_request,
   sum(package_size) AS package_size_sum,
   sumMap([client_ip], [1]) AS client_ip_Map,
   sumMap([client_region], [1]) AS client_region_Map,
   sumMap([method], [1]) AS method_Map,
   sumMap([response_statuscode], [1]) AS response_statuscode_Map,
   sumMap([request_headers['User-Agent']], [1]) AS request_ua_Map,
   sumMap([request_headers['Accept']], [1]) AS request_accept_Map,
   sumMap([request_headers['Accept-Encoding']], [1]) AS request_accept_encoding_Map,
   sumMap([request_headers['Accept-Language']], [1]) AS request_accept_language_Map
FROM log
GROUP BY (minute, domain);

4. 创建物化视图；

CREATE MATERIALIZED VIEW log_agg_minute_mv TO log_agg_minute
AS SELECT
   toStartOfMinute(time) AS minute,
   domain,
   count() AS all_request,
   sum(package_size) AS package_size_sum,
   sumMap([client_ip], [1]) AS client_ip_Map,
   sumMap([client_region], [1]) AS client_region_Map,
   sumMap([method], [1]) AS method_Map,
   sumMap([response_statuscode], [1]) AS response_statuscode_Map,
   sumMap([request_headers['User-Agent']], [1]) AS request_ua_Map,
   sumMap([request_headers['Accept']], [1]) AS request_accept_Map,
   sumMap([request_headers['Accept-Encoding']], [1]) AS request_accept_encoding_Map,
   sumMap([request_headers['Accept-Language']], [1]) AS request_accept_language_Map
FROM log
GROUP BY (minute, domain);

    物化视图里的统计语句实际上和插入已有数据（3）的语句是一致的。其中：sumMap([method], [1]) AS method_Map 这部分的意思是给每个值都设置一个计数值，出现就记为1，sumMap会自动归并相同的key，同一个key对应value累加。

5. 插入新数据查看统计结果更新情况
构造一个假数据进行测试，可以重复插入来测试统计效果。

INSERT INTO log VALUES('1.123.1.2',22880,'广东','abc.com','','GET',2423,{},{},200,'https:\/\/abc.com\/m\/r\/?id=1&from=web','2024-05-20 02:02:00');

插入几条之后，可以查询统计表看是否生效。查询语句如下：​​​​​​​

SELECT  minute,  domain,  sum(all_request),  sum(package_size_sum),  sumMap(client_ip_Map),  sumMap(client_region_Map),  sumMap(method_Map),  sumMap(response_statuscode_Map),  sumMap(request_ua_Map),  sumMap(request_accept_Map),  sumMap(request_accept_encoding_Map),  sumMap(request_accept_language_Map)FROM log_agg_minutegroup by  (`domain`,`minute`) format JSON;

查询结果如下：​​​​​​​

{"minute": "2024-05-20 02:02:00","domain": "abc.com","sum(all_request)": "221","sum(package_size_sum)": "535483","sumMap(client_ip_Map)": {"1.123.1.2":"221"},"sumMap(client_region_Map)": {"广东":"221"},"sumMap(method_Map)": {"GET":"221"},"sumMap(response_statuscode_Map)": {"200":"221"},"sumMap(request_ua_Map)": {"":"221"},"sumMap(request_accept_Map)": {"":"221"},"sumMap(request_accept_encoding_Map)": {"":"221"},"sumMap(request_accept_language_Map)": {"":"221"}}

这里可能会有一些困惑。

• 为何插入的是log表，而查询却是log_agg_minute表？
      这是因为，物化视图会自动将数据统计并写入到统计表里。也就是这部分：

原始数据表 -> 物化视图（含统计语句） -> 结果发送到存储表（SummingMergeTree/AggregatingMergeTree）

• 为何查询还要使用sum/sumMap这样的方式进行统计，而不是直接查出结果？
      如果是SimpleAggregateFunction完全可以直接查询对应的各个字段，不使用聚合函数查询。在大部分情况下，这些结果都是正确的。但在少数情况下，如果你要查询的结果不幸分布在了两个不同的part里，那么合并就不会很彻底，也就是相同的主键会出现两个，结果就会出现两条了。使用聚合函数就可以避免这个问题，并且不会影响太多性能。
      如果是AggregateFunction直接查询对应的字段，则会无法直接现实结果。一般要使用sumMerge/sumMapMerge 这样的xxxMerge函数来合并统计结果，相应的统计也要使用xxxState的函数进行统计。
   

6. 技巧1：多层物化视图；

    物化视图是可以逐层叠加的。我们可以制作出类似“分钟->小时->天->月->年”这样的多层物化视图。当然也可以同样应用于其他层级系统，例如：组织层级、行政区域划分、文件夹、树形结构等等。

    以时间的层级为例。其方式是，在分钟统计的表基础上，创建小时统计表及对应的物化视图。这也就是前面为什么要单独创建分钟统计表，而不是使用默认的物化视图自动创建的表的原因。

    小时统计表，实际上只有时间字段和粒度变化，其他都和分钟的一致：

CREATE TABLE log_agg_hour
(
`hour` DateTime CODEC(Delta(4), ZSTD(3)),
`domain` LowCardinality(String),
`all_request` SimpleAggregateFunction(sum, UInt64),
`package_size_sum` SimpleAggregateFunction(sum, UInt64),
`client_ip_Map` SimpleAggregateFunction(sumMap, Map(IPv4, UInt64)) CODEC(ZSTD(3)),
`client_region_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(3)),
`method_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(3)),
`response_statuscode_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(3)),
`request_ua_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6)),
`request_accept_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6)),
`request_accept_encoding_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6)),
`request_accept_language_Map` SimpleAggregateFunction(sumMap, Map(String, UInt64)) CODEC(ZSTD(6))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(hour)
PRIMARY KEY (domain, hour)
ORDER BY (domain, hour)
TTL hour + toIntervalYear(1)
SETTINGS index_granularity = 1024;

    创建物化视图的基础表是分钟统计表，而不是log表。其统计语句，与前面的分钟表的查询语句基本是一致的：​​​​​​​

CREATE MATERIALIZED VIEW log_agg_hour_mv TO log_agg_hourAS SELECT        toStartOfHour(minute) AS hour,        domain,        sum(all_request) AS all_request,        sum(package_size_sum) AS package_size_sum,        sumMap(client_ip_Map) AS client_ip_Map,        sumMap(client_region_Map) AS client_region_Map,        sumMap(method_Map) AS method_Map,        sumMap(response_statuscode_Map) AS response_statuscode_Map,        sumMap(request_ua_Map) AS request_ua_Map,        sumMap(request_accept_Map) AS request_accept_Map,        sumMap(request_accept_encoding_Map) AS request_accept_encoding_Map,        sumMap(request_accept_language_Map) AS request_accept_language_MapFROM  log_agg_minuteGROUP BY (domain, hour);

    可以插入数据进行测试，查询语句也是分钟表类推的，不再赘述。
依次类推，可以往上叠加更高的层级。

7. 技巧2：对离散字段的值进行统计；

    sumMap函数 [2]是用于统计Map（字典）的。这里主要用于了字段的不同值的计数或者说分布统计。这样可以减少很多表，比如对状态码的统计，单个response_statuscode_Map字段就等价于 group by (domain, minute, response_statuscode)查询。

测试多次插入不同的状态码的数据：

INSERT INTO log VALUES('1.123.1.2',22880,'广东','abc.com','','GET',2423,{},{},500,'https:\/\/abc.com\/m\/r\/?id=1&from=web','2024-05-20 02:02:00');

再次查询分钟统计的结果，会得到如下的内容：​​​​​​​

{  ...  "sumMap(response_statuscode_Map)": {"200":"231","500":"10"},  ...}

五. clickhouse总结

基于以上步骤，至此，就建立起了一个从数据存储，到数据统计的数据存算系统。外部程序可以基于此数据库进行查询。其特点有：
1. 善于存储时序特性的亿级甚至十亿级海量数据，并具有极高的压缩率；
2. 充分利用硬件资源，包括硬盘、CPU、内存；
3. 近乎实时的统计，并且无需额外的统计程序模块；
4. 自动管理数据过期（TTL）；
5. 支持分布式扩展；
6. 极其高效的聚合查询；
7. 常见业务一台高性能机器就能搞定；
8. 大部分语法兼容MySQL，从传统数据库迁移成本较低。

当然，clickhouse还有自己的弱点：
1. 硬件利用率高，在查询并发过高的时候容易吃满硬件资源导致自身服务崩掉重启；
2. 不擅长整条数据查询，尤其大量原始数据查询并offset的时候，查询会很慢；
3. 功能琐碎，基本上每一个问题都有对应的特定方法，需要对应地学；
4. 如果要投入生产环境，大概率要一个高性能服务器，成本较高；
5. 直接对用户功能的查询，只建议应用在低频的，例如统计分析页面；
6. 当前国内成熟的应用和最佳实践，较少被分享出来，更多资料在国外。一些国内大厂都会开发自己的大数据数据库。

clickhouse速通，旨在提供一个快速用起来clickhouse到实际应用场景中的引导。后续看情况，会陆续分享一些踩过的坑，包括clickhouse的一些弱点如何优化。如果有感兴趣的点，也可以留言提出，我会考虑加快更新。有问题也可以留言交流。

引用：

1.https://clickhouse.com/docs/en/sql-reference/data-types/simpleaggregatefunction
2.https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/summap
3. https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/