Clickhouse 在自助分析场景中的探索及实践
导读 转转平台有海量的用户数据需要进行分析计算，根据业务需要输出各类报表，但巨大数据量加上复杂的数据模型，和个性化的分析维度，让数仓预计算的汇总数据也难以支持，转转平台通过引入 ClickHouse 作为分析引擎来对平台做支撑。本文将分享 ClickHouse 在自助分析场景中的探索及实践。
文章将从以下4个方面展开：
-
转转自助分析场景下 OLAP 选型
-
高斯平台自助分析场景
-
ClickHouse 优化实践
-
ClickHouse 未来在转转的规划与展望
分享嘉宾｜王鹏哲 转转 架构师
编辑整理｜张玮
出品社区｜DataFun
01/转转自助分析场景下 OLAP 选型
1. 背景
转转平台主要对用户的行为数据进行分析，也就是埋点数据。埋点数据包含用户的曝光、点击、展现等事件，覆盖数量很大，且在部分分析场景需要支持精确去重。大数据量加上去重、数据分组等计算使得指标在统计过程中运算量较大。
除此之外，在一些数据分析场景中需要计算留存率、漏斗转化等复杂的数据模型，虽然转转在离线数仓的数仓分层和汇总层对通用指标做了预计算处理，但由于这些模型的分析维度通常是不确定的，因此预计算无法满足产品或者运营提出的个性化报表的需求，需要提给分析师或后端去开发，使得数据链路较长，数据价值也随着时间的推移而减少。
作为分析平台，既需要保证数据和架构的高可用，也要做到任意维度、任意指标的秒级反馈。基于以上情况，需要一个即席查询的引擎来做支持。
2. OLAP 选型考量
转转对 OLAP 引擎选型考量有三个方面：性能，灵活性，复杂性。
- **性能：**OLAP 引擎所能承受的数据量级（亿级/百亿级/千亿级等），以及数据反馈时效性（毫秒级/秒级/分钟级），结合业务情况考虑 OLAP 能否支撑业务实现。
- **灵活性：**考虑 OLAP 引擎能支持的查询场景，是能否支持聚合结果或明细数据的查询，还是两者都支持。从数据链路来看，能否支持离线数据和实时数据的摄取，以及在查询支撑上是不是能支持高并发的即席查询。
- **复杂性：**系统的架构需要尽可能的简单，使用门槛低、运维难度低，扩展性强。
根据这三个方面的考量，转转对如下开源 OLAP 引擎进行了调研。
OLAP 引擎主要有两大类：一种是基于预计算的 MOLAP，另一种是基于 MPP 架构的 ROLAP 引擎。
- 基于预计算的 MOLAP 引擎的优势是对整个计算结果做了预计算，查询比较稳定，可以保证查询结果亚秒级或者是秒级响应。但由于依赖预计算，查询的灵活性比较弱，无法统计预计算外的数据，代表是 Kylin 和 Druid。
- 基于 MPP 架构的引擎可以支持实时数据的摄入和实时分析，查询场景灵活，但查询稳定性较弱，取决于运算的数据量级和资源配比，基于 MPP 架构的 OLAP 一般都是基于内存的，代表是 Impala 和 Presto。
Kylin 采用的技术是完全预聚合立方体，能提供较好的 SQL 支持以及 join 能力，查询速度基本上都是亚秒级响应。同时，Kylin 有良好的 web 管理界面，可以监控和使用立方体。但当维度较多，交叉深度较深时，底层的数据会爆炸式的膨胀。而且 Kylin 的查询灵活性比较弱，这也是 MOLAP 引擎普遍的弱点。
Druid 采用位图索引、字符串编码和预聚合技术，可以对数据进行实时摄入，支持高可用高并发的查询，但是对 OLAP 引擎的分析场景支持能力比较弱，join 的能力不成熟，无法支持需要做精确去重计算的场景。
Impala 支持窗口函数和 UDF，查询性能比较好，但对内存的依赖较大，且 Impala 的元数据存储在 Hive MetaStore 里，需要与 Hadoop 主键紧密的结合，对实时数据摄入一般要结合 Kudu 这类存储引擎做 insert、update、delete 操作，多系统架构运维也比较复杂。
Presto 可跨数据源做联邦查询，能支持多表的 join，但在高并发的场景下性能较弱的。
相比较而言，ClickHouse单机性能很强，基于列式存储，能利用向量化引擎做到并行化计算，查询基本上是毫秒级或秒级的反馈，但 ClickHouse 没有完整的事务支持，对分布式表的 join 能力较弱。
Doris运维简单，扩缩容容易且支持事务，但 Doris 版本更新迭代较快且成熟度不够，也没有像 ClickHouse 自带的一些函数如漏斗、留存，不足以支撑转转的业务场景。
基于以上考量，转转选择了 ClickHouse 作为分析引擎。
3. ClickHouse
ClickHouse 是面向实时联机分析处理的基于列式存储的开源分析引擎，是俄罗斯于2016年开源的，底层开发语言为 C++，可以承受的 PB 数据量级的分析。ClickHouse 有以下特性：
- 具有完备的 DBMS 功能，SQL 支持较为完善。
- **基于列式存储和数据压缩，支持索引。**列式存储就是相同列的数据类型是一致的，可以拥有更好的压缩比。ClickHouse 也支持索引的，如主键索引，包括二级索引，稀疏索引等。
- 向量化引擎与 SIMD 提高 CPU 的利用率，多核多节点并行执行，可基于较大的数据集计算，提供亚秒级的查询响应。
- **支持数据复制和数据完整性。**节点的写入的数据 ClickHouse 会在后台同步数据到其他副本里。
- **多样化的表引擎。**ClickHouse 对外部的 Kafka、HDFS 的这些数据查询引擎，以及 MergeTree 系列的引擎、Distribute 这种分布式表引擎的支持都比较完善。
ClickHouse 的查询场景主要分为四大类：
- **交互式报表查询。**可基于 ClickHouse 构建用户行为特征的宽表，对于多维度，多指标的计算能秒级的给出反馈。
- **用户画像系统。**在 ClickHouse 里面构建用户特征的宽表，可基于某一个标签或者是某一个用户做用户画像的用户细查、人群圈选等。
- **AB测试。**利用 ClickHouse 的高效存储和它提供的一些自带的函数，如 grouparray 函数，可以做到秒级给出 AB 实验的效果数据。
- **监控系统。**通过 Flink 或者是 Spark Streaming 去实时的采集业务指标、系统指标数据，实时写到 ClickHouse 里面，后期可以结合 Grafana 做指标的显示。
--
02/高斯平台自助分析场景
1. 系统介绍
转转高斯平台的核心功能主要包含两个部分：
- 埋点数据管理：埋点元数据纳管，埋点元数据质量统一监控和告警。
- 自助分析：基于业务特点和多部门复合需求，提供多维度、多指标的交叉分析能力，可以支持用户画像标签选择、人群圈选，AB TEST 等分析功能，全面支撑日常数据分析需求。
下图展示了高斯平台的系统架构，总共分为四层：
**数据采集层：**转转的数据主要是业务库和埋点数据。业务库数据存在 MySQL 里，埋点数据通常是 log 的日志。MySQL 业务库的数据通过 Flink-CDC 实时抽取到 Kafka，log 日志由 Flume 去采集，并分为实时和离线两条通道，实时埋点日志会将数据写入到Kafka，离线的日志存在 HDFS 里面。
数据存储层： 主要是对数据采集层过来的数据进行存储，存储采用的是 Kafka 和 HDFS，ClickHouse 基于底层数据清洗和数据接入，实现宽表存储。在 ClickHouse 的数据清洗中，离线数据采用的 Hive 或 Spark-SQL 做宽表，实时数据基于 Flink 读取 Kafka 的数据并关联维表做表拉宽。ClickHouse 的数据接入也分为实时数据链路和离线数据两条链路。离线数据 用 SeaTunnel 和自研的调度平台每天定时的做 T+1日的数据摄入。实时数据采用自研 Flink ClickHouseSink 写入。
**数据服务层：**对外统一封装的 HTTP 服务，由外部系统调用，对内提供了 SQL 化的客户端工具。
**数据应用层：**主要是基于 ClickHouse 的高斯自助分析平台和用户画像平台两大产品。高斯分析平台可以对于用户去做事件分析，计算 PV、UV 等指标以及做留存 LTV 漏斗分析、行为分析等，用户画像平台提供了人群的圈选、用户细查等功能。
如图，是高斯平台基于 ClickHouse 的高可用架构，该架构采用了复制表引擎 和分布式表引擎两种表引擎。
通常一个客户端发送请求过来之后，由代理服务器转发到某一台具体的客户端节点做分布式查询。但分布式表引擎是不存放任何数据的，只根据查询请求分发到对应的本地表里面去做数据的查询，再由发起分布式表查询引擎的节点做数据的汇总最后统一返回给客户端。
这种架构的优势是数据副本是由表引擎管理 （依赖于 Zookeeper），不需要人工或者是运维的去配置这种数据副本，缺点是在建集群的时候配置复杂，后期维护的成本较大。
2. 高斯平台集群现状
高斯平台的业务支撑主要包含在线报表的查询和数据分析两部分。报表型业务查询可以毫秒级的响应，分析型查询的大部分可以秒级响应。
高斯平台目前的服务器数量是20个，双副本设置，承载存量数据是40 T+，日增量20亿+，在满足业务场景的前提下，TTL 设置186天，平台包含的核心看板500+，日活数量是200+。
3. ClickHouse 在高斯平台的业务场景
（1）行为分析
**业务背景：**APP 上线活动专题页，业务方想查看活动页面上线后各个坑位的点击的效果。
**技术实现：**采用 ClickHouse 的物化视图、聚合表引擎，以及明细表引擎。ClickHouse 的物化视图可以做实时的数据累加计算，POPULATE 关键词对物化视图的更新的策略起作用。在建物化视图时使用 POPULATE 关键词会对底层表的历史数据做物化视图的计算，但如果在做物化视图计算过程中底层表有数据新增，那么新增的这部分数据不会参与计算，计算会缺少一部分数据导致结果不准确，因此官方并不推荐在创建物化视图的时候使用该关键词。
（2）AB-TEST 分析
**业务背景：**转转早期 AB-TEST 采用传统的 T+1日数据，但 T+1日数据已无法满足业务需求。
**技术方案：**利用 ClickHouse 做实时数据采集和实时指标的计算。通过 Flink 读取 Kafka 数据实时写入 ClickHouse 本地表做实时指标预计算。
技术难点： 在数据写入的过程中，会抛出如上图的报错。
**原因：**表分区设置粒度小，实时数据写入时频繁有小批量数据插入，导致目录生成速度、merge 速度跟不上写入速度。
**解决方案：**在 Sink 端降低并发，配置批次写入的时间、大小，Server 端调大系统承受力参数。
（3）亿级数据 JOIN
**业务背景：**在做匹配圈选人群和一些用户行为的数据做指标预计算时，需要对用户行为数据表和用户特征表两张亿级数据进行 join 计算。
**技术方案：**数据导入时，对数据的连接字段分桶，比如，在 SeaTunnel 导入的时候，将关联字段（如，UID）做分桶后写入到某一个具体的本地分片里面。这种操作和分布式表写入时指定 sharding key 策略是一样的。
但是分布式表写入在转转是禁止的 。分布式表写入涉及到写入放大的概念，分布表写入时，通常会把所有的数据落地到某一个节点上，在到达一定的数据体积或者时间之后才会把这些数据分发到各个数据分片上，所以最好是做到本地表的写入。
**技术原理：**在做用户画像数据和行为数据导入的时候，数据已经预分区了，相同的 JOIN KEY 其实是在同一节点上，因此不需要去做两个分布式表去跨节点的 join。在一条 SQL 查询的时候，不需要去 join 业务表的分布式表，只需要去 join 本地表就行，执行过程中会把具体的查询逻辑改为两个表，join 本地表之后再汇总最后的计算结果，这样就能得到正确的结果。
4. 高斯平台使用痛点
在使用 ClickHouse 时遇到了一些痛点：
- ClickHouse 的高并发能力特别弱，官方建议的 QPS 是每秒100左右。随着高斯平台的不断推广以及业务的不断接入，部分业务场景是需要高并发的查询支持的，当高并发的查询上来之后，ClickHouse 性能下降比较明显。
- ClickHouse 不支持事务性的 DDL 和其他的分布式事务，复制表引擎的数据同步的状态和分片的元数据管理都强依赖于 Zookeeper。
- 部分应用场景需要做到实时的行级数据 update 和 delete 操作，ClickHouse 缺少完整的这类操作。
- ClickHouse 缺少自动的 rebalance 机制，扩缩容比较麻烦。
--
03/ClickHouse 在转转的优化实践
1. 内存优化
在数据分析过程中常见的问题大都是和内存相关的。如上图所示，当内存使用量大于了单台服务器的内存上限，就会抛出这个报错。
针对这个问题，需要对 SQL 语句和 SQL 查询的场景进行分析，如果是在进行 count 和 disticnt 计算时内存不足，可以使用一些预估函数减少内存的使用量来提升查询速度；如果 SQL 语句进行了 group by 或者是 order by 操作，可以配置 max_bytes_before_external_group_by 和 max_bytes_before_external_sort 这两个参数去调整。
2. Zookeeper 优化
在 ClickHouse 里面，Zookeeper 的作用主要分为两大类：分布式 DDL 的执行 和复制表引擎之间数据同步状态的依赖。随着复制表的数据规模的增加，Zookeeper 会成为 ClickHouse 集群的瓶颈，需要调整一些相关的参数对 Zookeeper 进行优化，比如，尽可能增大会话超时的时间。
另外，Zookeeper 的事务日志和日志目录最好做到目录分离，用更好的 I/O 速度的存储磁盘。在 ClickHouse 建表时也可以添加一些参数对元数据进行压缩的存储，减缓 Zookeeper 的压力。
3. 性能调优参数
上图是转转实践过的一些优化的参数，主要是限制并发处理的请求数和限制内存相关的参数。
- **max_concurrent_queries：**限制每秒的并发请求数，默认值100，转转调整参数值为150。使用这个参数需根据集群性能以及节点的数量来调整参数值。
- **max_memory_usage：**设置单个查询单台机器的最大内存使用量，建议设置值为总内存的80%，因为需要预留一些内存给系统 OS 使用。
- **max_memory_usage_for_all_queries：**设置单服务器上查询的最大内存量，建议设置为总内存的80%~90%。
- **max_memory_usage_for_user & max_bytes_before_external_sort：**group by 和 order by 使用超出内存的阈值后，预写磁盘进行 group by 或 order by 操作。
- **background_pool_size：**后台线程池的大小，默认值为16，转转调整为32。这个线程池大小包含了后台 merge 的线程数，增大这个参数值是有利于提升 merge 速度的，但是能否提升这个值也取决于机器的配置和 CPU 的个数。
4. 建表/查询规范
- 建表规范
在一些场景下，Hive 建表字段的数据类型可以都是 String 类型，但在 ClickHouse 建表时，字段是数值类型或者是日期类型的尽量用相应的类型，因为 ClickHouse 对这些数据类型底层是做了优化的，数值类型 group by 的时候最快。
另外，建表的时候尽量不使用 Nullable，Nullable 列无法被索引，一般对数据做清洗的时候，遇到有 Null 值的列，也会给 Null 值去写入一个默认值后再处理。
- 查询规范
列裁剪和分区裁剪，这个应该是大部分的查询引擎都需要做的，查询时尽量减少查询的数据集。此外，一些经常需要被用来关联做分析的业务表，可以在 ClickHouse 创建成小的字典表用来进行 join 操作，这样可以加速查询效率。
在一些固定的业务场景下，SQL 的查询逻辑是比较固定的，指标和维度也是固化的，这种情况就可以用物化视图以及 ClickHouse 后续更新的 Projection 做到预计算，这样处理可以避免查询时做重复的计算，减少浪费集群的 CPU 或内存资源。
在分布式表进行 in 或 join 操作时尽量使用 Global 关键词，避免查询级数放大。虽然 ClickHouse 里面分布式 join 能力是比较弱的，但还是尽量避免做这类操作。
--
04/未来规划及展望
对上述的痛点转转将持续进行优化，主要为四个方向：
- **服务平台化，故障规范化。**ClickHouse 目前在转转已经有多个业务接入使用，因此，后续会提高一些业务方的应用性，业务的治理，比如，资源的多租户隔离，异常用户的限流熔断，以及对 ClickHouse 精细化监控报警，包括一些慢查询的指标等的计算。
- **ClickHouse 容器化的部署。**支持数据的存算分离，更好的、弹性的做到集群的扩缩容，扩缩容之后能够对数据能做自动的数据均衡服务架构智能化。
- **服务架构智能化。**针对之前提到的部分业务场景做高并发的查询，ClickHouse 本身的支持高并发能力比较弱，引入 Doris 基于特定的业务场景去自适应的选择 ClickHouse 或者是 Doris 引擎来支持这类业务场景。
- **ClickHouse 内核级的优化。**包括实时写入一致性保证、分布式事务支持、移除 Zookeeper 的服务依赖。目前 Zookeeper 在 ClickHouse 数据达到一定量级是会有瓶颈的，所以之后会去做移除 Zookeeper 服务依赖的开发。
--
05/问答环节
Q1：当前高斯平台画像和自助分析宽表之间的 join 场景性能是什么样的？表的量级大概是多少？是本地 join 去解决的吗？以及行为数据精准的去重能不能达到秒级反馈？
A1：转转对于宽表之间 join 的场景主要是涉及到用户画像的用户表和高斯平台的一张用户行为表，这两部分数据的量级都达到亿级之上。关于 join 目前的优化手段就是本地 join 和 SQL 的调优，尽量减少数据列和分区的扫描等。行为数据 uv 的精确去重目前是可以做到秒级输出的，目前转转的数据运算，亿级的数据都是可以做到秒级的输出的。
今天的分享就到这里，谢谢大家。
分享嘉宾
王鹏哲｜转转 大数据平台&实时计算架构师
目前主要负责大数据平台体系建设，架构设计与优化。
《数据智能知识地图》下载
上下滑动⬆️⬇️，查看《数据智能知识地图》数据湖模块，完整版请关注公众号"大话数智"下载
点击链接，快速了解知识地图详情，并下载：https://sourl.cn/zeC3pZ
DataFun新媒体矩阵
关于DataFun
专注于大数据、人工智能技术应用的分享与交流。发起于2017年，在北京、上海、深圳、杭州等城市举办超过100+线下和100+线上沙龙、论坛及峰会，已邀请超过2000位专家和学者参与分享。其公众号 DataFunTalk 累计生产原创文章900+，百万+阅读，16万+精准粉丝。
