用户行为分析在互联网产品运营和用户增长中起着指导性的作用。例如通过对比不同渠道的用户留存率，可以针对性的调整推广策略，获取高质量用户。在贝壳找房，我们建设了自有的用户行为分析产品——罗盘(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内完成分组聚合类的简单查询，是我们做性能评估的一个简要标准。

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

使用通用查询引擎，配合自定义函数实现定制化功能；
定制用户行为分析工具，使用自定义数据结构实现。

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

与其他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事件总次数和触发用户数 
2
3 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

创作场景

ClickHouse Practice