ClickHouse Practice

用户行为分析在互联网产品运营和用户增长中起着指导性的作用。例如通过对比不同渠道的用户留存率，可以针对性的调整推广策略，获取高质量用户。在贝壳找房，我们建设了自有的用户行为分析产品——罗盘(compass.data.lianjia.com)。在开发过程中，我们使用 ClickHouse 作为查询引擎，支持了十亿级日志数据上的秒级自定义分析。下面结合近半年的实践经验，与大家分享一下 ClickHouse 这款很棒的查询引擎。

介绍

ClickHouse 是什么

ClickHouse(https://clickhouse.yandex/)是一个面向 OLAP 的列式数据库，源自俄罗斯最大的搜索公司 Yandex，是 Yandex 的工程师在实现其 Web 分析服务 Metrica(类似 google analytics)时设计开发的。2016 年开源后，凭借优秀的查询速度吸引了众多用户，国内也有新浪等公司在使用。官方 benchmark 秒天秒地，比 Hive 快 100 倍，比 vertica、greenplum 等商业化数据库快 6-10 倍[1]。

关键特性

• 支持 SQL&丰富的数据和聚合函数

• 作为一个 DBMS，肯定是要支持 SQL 的。虽然不能完全支持 ANSI SQL，但是 ClickHouse 提供的数组和聚合函数，更适用于分析型场景。

• 列式存储

• 列式存储特别适用于在分析型场景下

• 大部分分析场景下，只用到了数据集中少量的列。例如，如果查询需要 100 列中的 5 列，在面向列的数据库中，通过只读取所需的数据，I/O 可能会减少 20 倍；

• 同样类型的数据也更容易压缩，这进一步减少了 I/O 量；

• 由于 I/O 减少，更多的数据可以存放在系统缓存中。

官网对行式存储和列式存储的可视化对比如下：

上图为行式存储，下图为列式存储，通过只加载所需的数据可以有效加速查询。

• 向量引擎

• 在列式存储基础上，ClickHouse 使用了向量引擎。区别与传统数据库一次一 tuple 的 pipleline 执行模式，ClickHouse 中所有操作都是基于向量，参考了 VectorWise 的思想。这可以有效减少调用次数，并更适用于现代处理器的指令，比如 JIT 可以生成 SIMD 指令来处理向量。参考[1]

• Blazing Fast

• 受益于列存及向量化引擎，ClickHouse 可以提供极快的查询速度。抛开官方的 benchmark，在我们的测试中，10 亿级别数据分组聚合耗时约 1 秒，主要瓶颈在与磁盘读取速度。

• 实时接入

• ClickHouse 提供了多种表引擎，其中 MergeTree 相关引擎支持实时写入，实时查询。

• 没有事务、低并发、对长文本支持差

• ClickHouse 没有事务，因此不适用与 OLTP 类需求；另外没有对大并发量的简单查询做优化，因此不适用于做高 QPS 的 kv 存储(HBase 更合适)；同时不适用于长文本的存储(可以考虑 ES)。

上述为我觉得值得关注的一些特性，其他特性可以参考官方文档[3]。文档写得好也可以算是一个特性。

实践-用户行为分析

Why always me

• 项目需求

• 在为“用户行为分析”的项目进行技术选型时，我们有性能、实时性和可扩展性三方面的要求。

1b in 1s 指 10 亿行日志，能够在 1s 内完成分组聚合类的简单查询，是我们做性能评估的一个简要标准。

针对这些需求，有两类解决方案：

1. 使用通用查询引擎，配合自定义函数实现定制化功能；

2. 定制用户行为分析工具，使用自定义数据结构实现。

根据公司的日志规模，对比两类方案的适用场景和开发成本，决定用一个通用查询引擎来承载这些功能。

• 与其他 OLAP 引擎的对比

• 我们调研了各种开源的查询引擎，包括聚合预计算类的 Kylin、Druid，SQL on Hadoop 类的 tez、 SparkSQL、Impala 和 ClickHouse。

首先预聚合类的引擎，Kylin 适用于预定义维度上的分析，对于固定的转化漏斗可以支持，对于用户自定义的转化漏斗则无法支持或者退化基于明细数据的 sql 查询。Druid 适用于时间序列数据的统计性分析，经过类似 Kylin 的预计算处理数据可以实现固定漏斗分析，配合 sketch 等插件可以实现近似的留存分析和转化漏斗功能,无法精确计算。

通用查询引擎都可以通过开发自定义函数满足需求，因此我们主要测试了查询性能。基于 400G、4 亿条日志数据的测试结果如下：

tez、SparkSQL 测试使用集群组提供的线上环境，数据使用 parquet 格式。ClickHouse 使用 5 台 24 核腾讯云机器。

ClickHouse 性能明显优于其他方案，且支持数据的实时集成，即时可查询，因此选用 ClickHouse。

神策采用的 impala+kudu 也是一种解决方案，调研后发现要达到接近 ClickHouse 的速度，优化成本较高，没有选用[4]。我们没有对比百度近期开源的 palo，参考第三方测试数据，palo 性能不亚于 ClickHouse[5]。

Show me the code

• 数据接入 &表设计

• 用户行为日志上报后，使用 Spark Streaming 进行清洗，清洗后数据写入 Kafka 中，此后通过离线与实时两条流接入 ClickHouse。

• 实时流使用 Hangout 读取 Kafka 数据，经过类似 logStash 的 filter 解析，写入 ClickHouse；

• 离线流通过 flume 落地 Kafka 数据至 HDFS，使用 Hive 关联其他维表后，由 Spark 任务读取 HDFS 数据，写入 ClickHouse。离线任务每天例行执行，覆盖实时数据。

ClickHouse 中有使用 ReplicatedMergeTree 引擎的本地表 logs 存储数据，以事件类型(event)和时间戳(timestamp)为主键，按用户 id(eventuuid)做 shard，保证每个用户的日志只分配到一个节点上。在本地表基础上创建了分布式表 logsall，查询基于分布式表，由 ClickHouse 负责分发查询至各个节点并汇总结果数据。

• 配合 UDF 实现功能

• 1.事件触发次数/人数统计和留存分析

对于统计某时间段，某些事件类型的发生次数、触发人数，可以概括为一个分组聚合 SQL。

1--统计8月20号的不同操作系统的IMClick事件总次数和触发用户数 23 select os,count(*),count(distinct event_uuid) from compass.logs_all where date = '2018-08-20' and event = 'IMClick' group by os 

通过按用户 ID 分组，统计不同时间段该用户对应的事件数，即可获得该用户的留存情况。基于单个用户的留存情况，聚合即可统计不同维度下的留存率。

2.转化漏斗

转化漏斗是一个相对复杂的计算，主要用于分析一个多步骤过程中每一步的转化与流失情况。

对转化漏斗的定义参考神策相关定义[6]

假设一个漏斗中包含了 A、B、C、D、E 五个步骤,选择的时间范围是 2015 年 1 月 1 日到 2015 年 1 月 3 日，窗口期是 1 天，那么，如果用户在 2015 年 1 月 1 日到 2015 年 1 月 3 日触发了步骤 A，并且在步骤 A 发生的 1 天内，依顺序依次触发了 B、C、D、E，则视作该用户完成了一次成功的漏斗转化。

我们开发了一个 ClickHouse 聚合函数 FunnelEvtNameTag 来实现该功能。实现方式包括以下三步：

• 对日志数据按用户 ID 分组，调用聚合函数。聚合函数接收输入的乱序数据，按照时间戳进行排序。通过类似 LCS 的方法进行匹配，获取该用户行为中符合转化漏斗的步骤，以数组的形式返回；

• 使用 ARRAY JOIN 语法对数组进行列转行，筛选获得该转化漏斗对应的维度信息；

• 基于维度和用户完成的转化层级，聚合统计每个维度下，完成不同转化步骤的用户数。

结合 sql 的说明如下

 1--以一个按照系统类型查看 搜索请求->搜索结果点击->房源详情页->400点击->400确认点击 转化率的demo,说明下整个计算流程  2SELECT  3system_type,  4countIf(1, steps >= 1) AS step1_cnt, --完成漏斗第一步的用户数  5countIf(1, steps >= 2) AS step2_cnt, --同上  6countIf(1, steps >= 3) AS step3_cnt,  7countIf(1, steps >= 4) AS step4_cnt,  8countIf(1, steps >= 5) AS step5_cnt  9FROM 10( 11SELECT 12event_uuid, 13max(funnel) AS steps, 14arrayFilter(system_type -> isNotNull(system_type), groupArray(system_type))[1] AS system_type 15FROM 16--该子查询对应上述第二步,使用ARRAY JOIN进行行列转换,并获取对应维度信息 17( 18SELECT 19event_uuid, 20funnel, 21splitByChar('\u0001', dim)[2] AS system_type 22FROM 23--该子查询对应上述第一步,跟进限制条件查询日志表,按用户id分组调用聚合函数funnelEvtNameTag,输入固定参数为窗口期86400000ms即一天,漏斗开始时间戳,漏斗结束时间戳,漏斗个步骤；可变参数为 日志时间戳以及 事件类型和系统类型拼接的字符串 24( 25SELECT 26event_uuid, 27funnelEvtNameTag(86400000, 1534521600000, 1534607999999, 'SearchRequst', 'SearchClick', 'HouseDetailView', 'Click400', 'ConfirmClick400')(client_timestamp, concat(event, '\u0001', system_type, '\u0001')) AS funnels 28FROM compass.logs 29WHERE ((event = 'SearchRequst') OR (event = 'SearchClick') OR (event = 'HouseDetailView') OR (event = 'Click400') OR (event = 'ConfirmClick400')) AND (client_timestamp >= 1534521600000) AND (client_timestamp <= 1534608000000) AND (data_date >= toDate('2018-08-18')) AND (data_date <= toDate('2018-08-19')) 30GROUP BY event_uuid 31) 32ARRAY JOIN 33funnels.1 AS funnel, 34funnels.2 AS dim 35WHERE funnel > 0 36) 37GROUP BY event_uuid 38) 39GROUP BY system_type 

在实现该功能过程中，经过函数内排序、按用户 id 分片本地化计算等优化，最终结果查询近 7 天的复杂转化情况(约 20 亿数据)耗时 5S 左右。

3.路径分析，Session 分析等

用户行为分析还包括路径分析，Session 分析等功能，这些功能的实现方式与上述两个功能类似，均可通过分组聚合，配合自定义函数实现，不做赘述。感兴趣的同学可以联系我们或者查阅数据智能部增长策略组相关 Wiki。

• 服务部署

• 后端 ClickHouse 使用 3 台 80 核，200G 内存的腾讯云机器搭建，使用 ReplicateMergeTree + ZK 实现数据两备份。

其他

对其他项目的支持

ClickHouse 除支撑了罗盘自定义分析功能，还在 DMP，搜索行为回放，MerLin 等项目中有应用，目前每天写入数据量超过 6 亿条，每天约 5 万次查询请求。

相关组件 &后续方向

为了更好的使用和维护 ClickHouse，我们维护了以下组件

• 数据接入工具，目前提供了 HDFS 接入工具类，MySQL 数据接入以及实时 kafka 接入方式；

• 基于 tabix/superset 搭建了可视化查询页面；

• 使用 Grafana 和 ClickHouse DashBoard 搭建了慢查询监控页面 ；

• 提供了定制化及通用 SQL API；

后续我们将在性能瓶颈发现，集群扩容(reshard)，提高并发查询速度以及数据权限控制方面持续投入。

总结

ClickHouse 是一个快速的查询引擎，适用于千万级以上数据的分析及交互式查询。在贝壳找房，用来支撑了用户行为分析，DMP 等系统。

作者介绍：

作者阿苏勒（企业代号名），目前负责贝壳找房增长策略方向的相关工作。

本文转载自公众号贝壳产品技术（ID：gh_9afeb423f390）。

原文链接：

https://mp.weixin.qq.com/s/Q76SChqZQ3R1uPkYnGgjSA