Clickhouse 在大数据分析平台 - 留存分析上的应用

导语 | 本文实践了对于千万级别的用户，操作总数达万级别，每日几十亿操作流水的留存分析工具秒级别查询的数据构建方案。同时，除了留存分析，对于用户群分析，事件分析等也可以尝试用此方案来解决。

文章作者：陈璐，腾讯高级数据分析师

  背景

你可能听说过 Growingio、神策等数据分析平台，本文主要介绍实现留存分析工具相关的内容。留存分析是一种用来分析用户参与情况/活跃程度的分析模型，可考查进行初始行为后的用户中，有多少人会进行后续行为，这是衡量产品对用户价值高低的重要指标。如，为评估产品更新效果或渠道推广效果，我们常常需要对同期进入产品或同期使用了产品某个功能的用户的后续行为表现进行评估 [1]。大部分数据分析平台主要包括如图的几个功能（以神策

本文主要介绍留存分析工具的优化方案（只涉及数据存储和查询的方案设计，不涉及平台）。

我想每个数据/产品同学在以往的取数分析过程中，都曾有一个痛点，就是每次查询留存相关的数据时，都要等到天荒地老，慢！而最近采用优化方案的目的也是为了提高查询的效率和减少数据的存储，可以帮助产品快速地查询/分析留存相关的数据。优化方案的核心是在 Clickhouse 中使用 Roaringbitmap 对用户进行压缩，将留存率的计算交给高效率的位图函数，这样既省空间又可以提高查询速度。希望本实践方案可以给你带来一些帮助和启示。下面主要分 3 个部分详细介绍：Roaringbitmap 简介、思路与实现、总结与思考。

一、Roaringbitmap 简介

下面先简单介绍一下高效的位图压缩方法 Roaringbitmap。先来看一个问题:

给定含有40亿个不重复的位于[0,2^32-1]区间内的整数集合，如何快速判定某个数是否在该集合内？

显然，如果我们将这 40 亿个数原样存储下来，需要耗费高达 14.9GB 的内存，这是难以接受的。所以我们可以用位图(bitmap)来存储，即第 0 个比特表示数字 0，第 1 个比特表示数字 1，以此类推。如果某个数位于原集合内，就将它对应的位图内的比特置为 1，否则保持为 0，这样就能很方便地查询得出结果了，仅仅需要占用 512MB 的内存，不到原来的 3.4% [3]。但是这种方式也有缺点：比如我需要将 1~5000w 这 5000w 个连续的整数存储起来，用普通的 bitmap 同样需要消耗 512M 的存储，显然，对于这种情况其实有很大的优化空间。2016 年由 S. Chambi、D. Lemire、O. Kaser 等人在论文《Better bitmap performance with Roaring bitmaps》与《Consistently faster and smaller compressed bitmaps with Roaring》中提出了 roaringbitmap，主要特点就是可以极大程度地节约存储及提供了快速的位图计算，因此考虑用它来做优化。对于前文提及的存储连续的 5000w 个整数，只需要几十 KB。

它的主要思路是：将 32 位无符号整数按照高 16 位分桶，即最多可能有 2^16=65536 个桶，论文内称为 container。存储数据时，按照数据的高 16 位找到 container（找不到就会新建一个），再将低 16 位放入 container 中。也就是说，一个 roaringbitmap 就是很多 container 的集合 [3]，具体细节可以自行查看文末的参考文章。

二、思路与实现

我们的原始数据主要分为：

1.用户操作行为数据 table_oper_raw

包括时间分区(ds)、用户标识 id(user_id)和用户操作行为名称(oper_name)，如：20200701|6053002|点击首页 banner 表示用户 6053002 在 20200701 这天点击了首页 banner（同一天中同一个用户多次操作了同一个行为只保留一条）。实践过程中，此表每日记录数达几十亿行。

2.用户属性数据 table_attribute_raw

表示用户在产品/画像中的属性，包括时间分区(ds)、用户标识(user_id)及各种用户属性字段（可能是用户的新进渠道、所在省份等），如 20200701|6053002|小米商店|广东省。实践过程中，此表每日有千万级的用户数，测试属性在 20+个。

现在我们需要根据这两类数据，求出某天操作了某个行为的用户在后续的某一天操作了另一个行为的留存率，比如，在 20200701 这天操作了“点击 banner”的用户有 100 个，这部分用户在 20200702 这天操作了“点击 app 签到”的有 20 个，那么对于分析时间是 20200701，且“点击 banner”的用户在次日“点击 app 签到”的留存率是 20%。同时，还需要考虑利用用户属性对留存比例进行区分，例如只考虑广东省的用户的留存率，或者只考虑小米商店用户的留存率，或者在广东的小米商店的用户的留存率等等。一般来说，求留存率的做法就是两天的用户求交集，例如前文说到的情况，就是先获取出 20200701 的所有操作了“点击 banner”的用户标识 id 集合假设为 S1，然后获取 20200702 的所有操作了“点击 app 签到”的用户标识 id 集合假设为 S2，最后求解 S1 和 S2 的交集：

可以看到，当 s1 和 s2 的集合中用户数都比较大的时候，join 的速度会比较慢。

在此我们考虑前文说到的 bitmap，假若每一个用户都可以表示成一个 32 位的无符号整型，用 bitmap 的形式去存储，S1 和 S2 的求交过程就是直接的一个位比较过程，这样速度会得到巨大的提升。而 Roaringbitmap 对数据进行了压缩，其求交的速度在绝大部分情况下比 bitmap 还要快，因此这里我们考虑使用 Roaringbitmap 的方法来对计算留存的过程进行优化。

1.数据构建

整个过程主要是：首先对初始的两张表——用户操作数据表 table_oper_raw 和用户筛选维度数据表 table_attribute_raw 中的 user_id 字段进行编码，将每个用户映射成唯一的 id（32 位的无符号整型），分别得到两个新表 table_oper_middle 和 table_attribute_middle。再将他们导入 clickhouse，使用 roaringbitmap 的方法对用户进行压缩存储，最后得到压缩后的两张表 table_oper_bit 和 table_attribute_bit，即为最终的查询表。流程图如下：

(1).生成用户 id 映射表

首先，需要构建一个映射表 table_user_map，包含时间分区(ds)、用户标识 id(user_d)及映射后的 id(id)，它将每个用户(String 类型)映射成一个 32 位的无符号整型。这里我们从 1 开始编码，这样每个用户的标识就转化成了指定的一个数字。

(2).初始数据转化

分别将用户操作数据表和用户筛选维度数据中的 imei 字段替换成对应的数值，生成编码后的用户操作数据:

和用户筛选维度数据:

(3).导入 clickhouse

首先在 clickhouse 中创建相同结构的表，如 table_oper_middle_ch：

同样的，在 clickhouse 中创建表 tableattributemiddle_ch。然后用 spark 将这两份数据分别导入这两张表。这一步导入很快，几十亿的数据大概 10 分多钟就可以完成

(4).Roaringbitmap 压缩

对于用户操作流水数据，我们先建一个可以存放 bitmap 的表 table_oper_bit，建表语句如下：

用户属性数据 table_attribute_bit 也类似：

这里索引粒度可设置小值，接着用聚合函数 groupBitmapState 对用户 id 进行压缩：

这样，对于用户操作数据表，原本几十亿的数据就压缩成了几万行的数据，每行包括操作名称和对应的用户 id 形成的 bitmap：

同样的，用户属性的数据也可以这样处理，得到 table_attribute_bit 表，每行包括某个属性的某个属性值对应的用户的 id 形成的 bitmap：

至此，数据压缩的过程就这样完成了。

2.查询过程

首先，简要地介绍下方案中常用的 bitmap 函数（详细见文末的参考资料）：1.bitmapCardinality

返回一个 UInt64 类型的数值，表示 bitmap 对象的基数。用来计算不同条件下的用户数，可以粗略理解为 count(distinct)2.bitmapAnd

为两个 bitmap 对象进行与操作，返回一个新的 bitmap 对象。可以理解为用来满足两个条件之间的 and，但是参数只能是两个 bitmap3.bitmapOr

为两个 bitmap 对象进行或操作，返回一个新的 bitmap 对象。可以理解为用来满足两个条件之间的 or，但是参数也同样只能是两个 bitmap。如果是多个的情况，可以尝试使用 groupBitmapMergeState 举例来说，假设 20200701 这天只有[1,2,3,5,8]这 5 个用户点击了 banner，则有：

# 返回5select bitmapCardinality(user_bit)from tddb.table_oper_bitwhere ds = 20200701 AND oper_name = '点击banner'

又如果 20200701 从小米商店新进的用户是[1,3,8,111,2000,100000]，则有：

# 返回3,因为两者的重合用户只有1,3,8这3个用户select bitmapCardinality(bitmapAnd(	(SELECT user_bit         FROM tddb.table_oper_bit         WHERE (ds = 20200701) AND (oper_name = '点击banner')), 	(SELECT user_bit	 FROM tddb.table_attribute_bit	 WHERE ds = 20200701 and (attr_id = 'first_channel') and (attr_value IN ('小米商店')))))

有了以上的数据生成过程和 bitmap 函数，我们就可以根据不同的条件使用不同的位图函数来快速查询，具体来说，主要是以下几种情况：

a. 操作了某个行为的用户在后续某一天操作了另一个行为的留存：

如“20200701 点击了 banner 的用户在次日点击 app 签到的留存人数”，就可以用以下的 sql 快速求解:

b. 操作了某个行为并且带有某个属性的用户在后续的某一天操作了另一个行为的留存：

如“20200701 点击了 banner 且来自广东/江西/河南的用户在次日点击 app 签到的留存人数”：

c. 操作了某个行为并且带有某几个属性的用户在后续的某一天操作了另一个行为的留存：

如“20200701 点击了 banner、来自广东且新进渠道是小米商店的用户在次日点击 app 签到的留存人数”：

3.实践效果

根据这套方案做了实践，对每日按时间分区、用户、操作名称去重后包括几十亿的操作记录，其中包含千万级别的用户数，万级别的操作数。最后实现了：

存储

原本每日几十 G 的操作流水数据经压缩后得到的表 table_oper_bit 为 4GB 左右/天。而用户属性表 table_attribute_bit 为 500MB 左右/天

查询速度

clickhouse 集群现状：12 核 125G 内存机器 10 台。clickhouse 版本:20.4.7.67。查询的表都存放在其中一台机器上。测试了查询在 20200701 操作了行为 oper_name_1(用户数量级为 3000+w)的用户在后续 7 天内每天操作了另一个行为 oper_name_2(用户数量级为 2700+w)的留存数据(用户重合度在 1000w 以上)，耗时 0.2 秒左右

反馈

最后和前端打通，效果也是有了明显的优化，麻麻再也不用担心我会转晕~

三、总结与思考

总的来说，本方案的优点是：

• 存储小，极大地节约了存储

• 查询快，利用 bitmapCardinality、bitmapAnd、bitmapOr 等位图函数快速计算用户数和满足一些条件的查询，将缓慢的 join 操作转化成位图间的计算

• 适用于灵活天数的留存查询

• 便于更新，用户操作数据和用户属性数据分开存储，便于后续属性的增加和数据回滚

另外，根据本方案的特点，除了留存分析工具，对于用户群分析，事件分析等工具也可以尝试用此方案来解决。PS : 作者初入坑 ch，对于以上内容，有不正确/不严谨之处请轻拍~ 欢迎交流~

参考文献：

[1] 解析常见的数据分析模型——留存分析：https://www.sensorsdata.cn/blog/jie-xi-chang-jian-de-shu-ju-fen-xi-mo-xing-liu-cun-fen-xi/

[2] RoaringBitmap 数据结构及原理：https://blog.csdn.net/yizishou/article/details/78342499

[3] 高效压缩位图 RoaringBitmap 的原理与应用：https://www.jianshu.com/p/818ac4e90daf

[4] 论文：Better bitmap performance with Roaring bitmaps：https://arxiv.org/abs/1402.6407v9?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DanielLemiresArticlesOnArxiv+(Daniel+Lemire%27s+articles+on+arXiv)

[5] Clickhouse 文档-位图函数：https://clickhouse.tech/docs/zh/sql-reference/functions/bitmap-functions/

关注“腾讯云大数据”公众号，技术交流、最新活动、服务专享一站 Get~