本文實(shí)踐了對(duì)于千萬(wàn)級(jí)別的用戶，操作總數(shù)達(dá)萬(wàn)級(jí)別，每日幾十億操作流水的留存分析工具秒級(jí)別查詢的數(shù)據(jù)構(gòu)建方案。同時(shí)，除了留存分析，對(duì)于用戶群分析，事件分析等也可以嘗試用此方案來(lái)解決。

背景

你可能聽(tīng)說(shuō)過(guò)Growingio、神策等數(shù)據(jù)分析平臺(tái)，本文主要介紹實(shí)現(xiàn)留存分析工具相關(guān)的內(nèi)容。

留存分析是一種用來(lái)分析用戶參與情況/活躍程度的分析模型，可考查進(jìn)行初始行為后的用戶中，有多少人會(huì)進(jìn)行后續(xù)行為，這是衡量產(chǎn)品對(duì)用戶價(jià)值高低的重要指標(biāo)。如，為評(píng)估產(chǎn)品更新效果或渠道推廣效果，我們常常需要對(duì)同期進(jìn)入產(chǎn)品或同期使用了產(chǎn)品某個(gè)功能的用戶的后續(xù)行為表現(xiàn)進(jìn)行評(píng)估 [1]。大部分?jǐn)?shù)據(jù)分析平臺(tái)主要包括如圖的幾個(gè)功能(以神策為例)：

本文主要介紹留存分析工具的優(yōu)化方案(只涉及數(shù)據(jù)存儲(chǔ)和查詢的方案設(shè)計(jì)，不涉及平臺(tái))。

我想每個(gè)數(shù)據(jù)/產(chǎn)品同學(xué)在以往的取數(shù)分析過(guò)程中，都曾有一個(gè)痛點(diǎn)，就是每次查詢留存相關(guān)的數(shù)據(jù)時(shí)，都要等到天荒地老，慢!而最近采用優(yōu)化方案的目的也是為了提高查詢的效率和減少數(shù)據(jù)的存儲(chǔ)，可以幫助產(chǎn)品快速地查詢/分析留存相關(guān)的數(shù)據(jù)。

優(yōu)化方案的核心是在Clickhouse中使用Roaringbitmap對(duì)用戶進(jìn)行壓縮，將留存率的計(jì)算交給高效率的位圖函數(shù)，這樣既省空間又可以提高查詢速度。

希望本實(shí)踐方案可以給你帶來(lái)一些幫助和啟示。下面主要分3個(gè)部分詳細(xì)介紹：Roaringbitmap簡(jiǎn)介、思路與實(shí)現(xiàn)、總結(jié)與思考。

Roaringbitmap簡(jiǎn)介

下面先簡(jiǎn)單介紹一下高效的位圖壓縮方法Roaringbitmap。先來(lái)看一個(gè)問(wèn)題:

• 給定含有40億個(gè)不重復(fù)的位于[0,2^32-1]區(qū)間內(nèi)的整數(shù)集合，如何快速判定某個(gè)數(shù)是否在該集合內(nèi)?

顯然，如果我們將這40億個(gè)數(shù)原樣存儲(chǔ)下來(lái)，需要耗費(fèi)高達(dá)14.9GB的內(nèi)存，這是難以接受的。所以我們可以用位圖(bitmap)來(lái)存儲(chǔ)，即第0個(gè)比特表示數(shù)字0，第1個(gè)比特表示數(shù)字1，以此類推。如果某個(gè)數(shù)位于原集合內(nèi)，就將它對(duì)應(yīng)的位圖內(nèi)的比特置為1，否則保持為0，這樣就能很方便地查詢得出結(jié)果了，僅僅需要占用512MB的內(nèi)存，不到原來(lái)的3.4% [3]。但是這種方式也有缺點(diǎn)：比如我需要將1~5000w這5000w個(gè)連續(xù)的整數(shù)存儲(chǔ)起來(lái)，用普通的bitmap同樣需要消耗512M的存儲(chǔ)，顯然，對(duì)于這種情況其實(shí)有很大的優(yōu)化空間。

2016年由S. Chambi、D. Lemire、O. Kaser等人在論文《Better bitmap performance with Roaring bitmaps》與《Consistently faster and smaller compressed bitmaps with Roaring》中提出了roaringbitmap，主要特點(diǎn)就是可以極大程度地節(jié)約存儲(chǔ)及提供了快速的位圖計(jì)算，因此考慮用它來(lái)做優(yōu)化。對(duì)于前文提及的存儲(chǔ)連續(xù)的5000w個(gè)整數(shù)，只需要幾十KB。

它的主要思路是：將32位無(wú)符號(hào)整數(shù)按照高16位分桶，即最多可能有2^16 =65536個(gè)桶，論文內(nèi)稱為container。存儲(chǔ)數(shù)據(jù)時(shí)，按照數(shù)據(jù)的高16位找到container(找不到就會(huì)新建一個(gè))，再將低16位放入container中。也就是說(shuō)，一個(gè)roaringbitmap就是很多container的集合 [3]，具體細(xì)節(jié)可以自行查看文末的參考文章 。

思路與實(shí)現(xiàn)

我們的原始數(shù)據(jù)主要分為：

1. 用戶操作行為數(shù)據(jù)table_oper_raw 包括時(shí)間分區(qū)(ds)、用戶標(biāo)識(shí)id(user_id)和用戶操作行為名稱(oper_name)，如：20200701|6053002|點(diǎn)擊首頁(yè)banner 表示用戶6053002在20200701這天點(diǎn)擊了首頁(yè)banner(同一天中同一個(gè)用戶多次操作了同一個(gè)行為只保留一條)。實(shí)踐過(guò)程中，此表每日記錄數(shù)達(dá)幾十億行。
2. 用戶屬性數(shù)據(jù)table_attribute_raw 表示用戶在產(chǎn)品/畫像中的屬性，包括時(shí)間分區(qū)(ds)、用戶標(biāo)識(shí)(user_id)及各種用戶屬性字段(可能是用戶的新進(jìn)渠道、所在省份等)，如20200701|6053002|小米商店|廣東省。實(shí)踐過(guò)程中，此表每日有千萬(wàn)級(jí)的用戶數(shù)，測(cè)試屬性在20+個(gè)。

現(xiàn)在我們需要根據(jù)這兩類數(shù)據(jù)，求出某天操作了某個(gè)行為的用戶在后續(xù)的某一天操作了另一個(gè)行為的留存率，比如，在20200701這天操作了“點(diǎn)擊banner”的用戶有100個(gè)，這部分用戶在20200702這天操作了“點(diǎn)擊app簽到”的有20個(gè)，那么對(duì)于分析時(shí)間是20200701，且“點(diǎn)擊banner”的用戶在次日“點(diǎn)擊app簽到”的留存率是20%。同時(shí)，還需要考慮利用用戶屬性對(duì)留存比例進(jìn)行區(qū)分，例如只考慮廣東省的用戶的留存率，或者只考慮小米商店用戶的留存率，或者在廣東的小米商店的用戶的留存率等等。

一般來(lái)說(shuō)，求留存率的做法就是兩天的用戶求交集，例如前文說(shuō)到的情況，就是先獲取出20200701的所有操作了“點(diǎn)擊banner”的用戶標(biāo)識(shí)id集合假設(shè)為S1，然后獲取20200702的所有操作了“點(diǎn)擊app簽到”的用戶標(biāo)識(shí)id集合假設(shè)為S2，最后求解S1和S2的交集：

可以看到，當(dāng)s1和s2的集合中用戶數(shù)都比較大的時(shí)候，join的速度會(huì)比較慢。

在此我們考慮前文說(shuō)到的bitmap，假若每一個(gè)用戶都可以表示成一個(gè)32位的無(wú)符號(hào)整型，用bitmap的形式去存儲(chǔ)，S1和S2的求交過(guò)程就是直接的一個(gè)位比較過(guò)程，這樣速度會(huì)得到巨大的提升。而Roaringbitmap對(duì)數(shù)據(jù)進(jìn)行了壓縮，其求交的速度在絕大部分情況下比bitmap還要快，因此這里我們考慮使用Roaringbitmap的方法來(lái)對(duì)計(jì)算留存的過(guò)程進(jìn)行優(yōu)化。

1.數(shù)據(jù)構(gòu)建

整個(gè)過(guò)程主要是：首先對(duì)初始的兩張表——用戶操作數(shù)據(jù)表table_oper_raw和用戶篩選維度數(shù)據(jù)表table_attribute_raw中的user_id字段進(jìn)行編碼，將每個(gè)用戶映射成唯一的id(32位的無(wú)符號(hào)整型)，分別得到兩個(gè)新表table_oper_middle和table_attribute_middle。再將他們導(dǎo)入clickhouse，使用roaringbitmap的方法對(duì)用戶進(jìn)行壓縮存儲(chǔ)，最后得到壓縮后的兩張表table_oper_bit和table_attribute_bit，即為最終的查詢表。流程圖如下：

(1).生成用戶id映射表 首先，需要構(gòu)建一個(gè)映射表table_user_map，包含時(shí)間分區(qū)(ds)、用戶標(biāo)識(shí)id(user_d)及映射后的id(id)，它將每個(gè)用戶(String類型)映射成一個(gè)32位的無(wú)符號(hào)整型。這里我們從1開(kāi)始編碼，這樣每個(gè)用戶的標(biāo)識(shí)就轉(zhuǎn)化成了指定的一個(gè)數(shù)字。

(2).初始數(shù)據(jù)轉(zhuǎn)化 分別將用戶操作數(shù)據(jù)表和用戶篩選維度數(shù)據(jù)中的imei字段替換成對(duì)應(yīng)的數(shù)值，生成編碼后的用戶操作數(shù)據(jù):和用戶篩選維度數(shù)據(jù):

(3).導(dǎo)入clickhouse 首先在clickhouse中創(chuàng)建相同結(jié)構(gòu)的表，如table_oper_middle_ch。

同 樣的，在clickhouse中 創(chuàng)建表table_attribute_middle_ch。 然后用spark將這兩份數(shù)據(jù)分別導(dǎo)入這 兩張表。 這一步導(dǎo)入很快，幾十億的數(shù)據(jù)大概10分多鐘 就可以完成。

(4).Roaringbitmap壓縮 對(duì)于用戶操作流水?dāng)?shù)據(jù)，我們先建一個(gè)可以存放bitmap的表table_oper_bit，建表語(yǔ)句如下：用戶屬性數(shù)據(jù)table_attribute_bit也類似：這里索引粒度可設(shè)置小值，接著用聚合函數(shù)groupBitmapState對(duì)用戶id進(jìn)行壓縮：這樣，對(duì)于用戶操作數(shù)據(jù)表，原本幾十億的數(shù)據(jù)就壓縮成了幾萬(wàn)行的數(shù)據(jù)，每行包括操作名稱和對(duì)應(yīng)的用戶id形成的bitmap：同樣的，用戶屬性的數(shù)據(jù)也可以這樣處理，得到table_attribute_bit表，每行包括某個(gè)屬性的某個(gè)屬性值對(duì)應(yīng)的用戶的id形成的bitmap：至此，數(shù)據(jù)壓縮的過(guò)程就這樣完成了。

2. 查詢過(guò)程

首先，簡(jiǎn)要地介紹下方案中常用的bitmap函數(shù)(詳細(xì)見(jiàn)文末的參考資料)：

• bitmapCardinality 返回一個(gè)UInt64類型的數(shù)值，表示bitmap對(duì)象的基數(shù)。用來(lái)計(jì)算不同條件下的用戶數(shù)，可以粗略理解為count(distinct)
• bitmapAnd 為兩個(gè)bitmap對(duì)象進(jìn)行與操作，返回一個(gè)新的bitmap對(duì)象。可以理解為用來(lái)滿足兩個(gè)條件之間的and，但是參數(shù)只能是兩個(gè)bitmap
• bitmapOr 為兩個(gè)bitmap對(duì)象進(jìn)行或操作，返回一個(gè)新的bitmap對(duì)象?？梢岳斫鉃橛脕?lái)滿足兩個(gè)條件之間的or，但是參數(shù)也同樣只能是兩個(gè)bitmap。如果是多個(gè)的情況，可以嘗試使用groupBitmapMergeState

舉例來(lái)說(shuō)，假設(shè)20200701這天只有[1,2,3,5,8]這5個(gè)用戶點(diǎn)擊了banner，則有：

# 返回5  
select bitmapCardinality ( user_bit )  
from tddb . table_oper_bit  
where ds = 20200701 AND oper_name =  
'點(diǎn)擊banner'  
又如果20200701從小米商店新進(jìn)的用戶是[1,3,8,111,2000,100000]，則有：  
# 返回3,因?yàn)閮烧叩闹睾嫌脩糁挥?,3,8這3個(gè)用戶  
select bitmapCardinality ( bitmapAnd (  
( SELECT user_bit  
FROM tddb . table_oper_bit  
WHERE ( ds = 20200701 ) AND ( oper_name = '點(diǎn)擊banner' )),  
( SELECT user_bit  
FROM tddb . table_attribute_bit  
WHERE ds = 20200701 and ( attr_id = 'first_channel' ) and ( attr_value IN ( '小米商店'  
))))) 

有了以上的數(shù)據(jù)生成過(guò)程和bitmap函數(shù)，我們就可以根據(jù)不同的條件使用不同的位圖函數(shù)來(lái)快速查詢，具體來(lái)說(shuō)，主要是以下幾種情況：

a. 操作了某個(gè)行為的用戶在后續(xù)某一天操作了另一個(gè)行為的留存：

• 如“20200701點(diǎn)擊了banner的用戶在次日點(diǎn)擊app簽到的留存人數(shù)”，就可以用以下的sql快速求解:

b. 操作了某個(gè)行為并且?guī)в心硞€(gè)屬性的用戶在后續(xù)的某一天操作了另一個(gè)行為的留存：

• 如“20200701點(diǎn)擊了banner且來(lái)自廣東/江西/河南的用戶在次日點(diǎn)擊app簽到的留存人數(shù)”：

c. 操作了某個(gè)行為并且?guī)в心硯讉€(gè)屬性的用戶在后續(xù)的某一天操作了另一個(gè)行為的留存：

• 如“20200701點(diǎn)擊了banner、來(lái)自廣東且新進(jìn)渠道是小米商店的用戶在次日點(diǎn)擊app簽到的留存人數(shù)”：

3. 實(shí)踐效果

根據(jù)這套方案做了實(shí)踐，對(duì)每日按時(shí)間分區(qū)、用戶、操作名稱去重后包括幾十億的操作記錄，其中包含千萬(wàn)級(jí)別的用戶數(shù)，萬(wàn)級(jí)別的操作數(shù)。最后實(shí)現(xiàn)了：

• 存儲(chǔ) 原本每日幾十G的操作流水?dāng)?shù)據(jù)經(jīng)壓縮后得到的表table_oper_bit為4GB左右/天。而用戶屬性表table_attribute_bit為500MB左右/天
• 查詢速度 clickhouse集群現(xiàn)狀：12核125G內(nèi)存機(jī)器10臺(tái)。clickhouse版本:20.4.7.67。查詢的表都存放在其中一臺(tái)機(jī)器上。測(cè)試了查詢?cè)?0200701操作了行為oper_name_1(用戶數(shù)量級(jí)為3000+w)的用戶在后續(xù)7天內(nèi)每天操作了另一個(gè)行為oper_name_2(用戶數(shù)量級(jí)為2700+w)的留存數(shù)據(jù)(用戶重合度在1000w以上)，耗時(shí)0.2秒左右
• 反饋 最后和前端打通，效果也是有了明顯的優(yōu)化，麻麻再也不用擔(dān)心我會(huì)轉(zhuǎn)暈~

總結(jié)與思考

總的來(lái)說(shuō)，本方案的優(yōu)點(diǎn)是：

• 存儲(chǔ)小，極大地節(jié)約了存儲(chǔ);
• 查詢快，利用bitmapCardinality、bitmapAnd、bitmapOr等位圖函數(shù)快速計(jì)算用戶數(shù)和滿足一些條件的查詢，將緩慢的join操作轉(zhuǎn)化成位圖間的計(jì)算;
• 適用于靈活天數(shù)的留存查詢;
• 便于更新，用戶操作數(shù)據(jù)和用戶屬性數(shù)據(jù)分開(kāi)存儲(chǔ)，便于后續(xù)屬性的增加和數(shù)據(jù)回滾。

另外，根據(jù)本方案的特點(diǎn)，除了留存分析工具，對(duì)于用戶群分析，事件分析等工具也可以嘗試用此方案來(lái)解決。