基于clickhouse的Bitmap实现任意时间段的去重求基数操作

 

 

1、什么是bitmap

bitmap的介绍：https://www.cnblogs.com/cjsblog/p/11613708.html

2、Clickhouse的RoaringBitmap介绍

https://blog.csdn.net/yizishou/article/details/78342499

3、基于bitmap实现业务需求

我们的需求场景是：任意时间段, 求关注的人群基数（去重）

如果使用传统数仓按天分区做法，成本会非常高，而且无法做到在线查询出结果的

简单解释下：

2021-01-01这一天  关注的iphone12的人是 : [张三、王五、赵六]

2021-01-02这一天 关注的iphone12的人是 : [王五、赵六、陈七]

求这两天关注过iphone12的人数量：

[张三、王五、赵六]
or
[王五、赵六、陈七]
然后去重，得到：
[[张三、王五、赵六、陈七]

 

求2021-01-01到2021-01-02的留存人数：

[张三、王五、赵六]
     and
[王五、赵六、陈七]
相交得到：
[王五、赵六]

 

求2021-01-02 相比 2021-01-01的新增人数

[王五、赵六、陈七]
     andNot
[张三、王五、赵六]
在2021-01-02出现过，但没有在2021-01-01出现过的人
[陈七]

 

以上的操作如果要通过Clickhouse实现，需要如下几步：

第一步：在clickhouse形成满足需求的宽表

第二步：基于宽表实现bitmap表

第三步：实现需求查询的sql

3.1、实现业务宽表

我们实现宽表的方式是：Hive加工处理,得到业务宽表,然后经过Datax将数据同步到Clickhouse里面

比如，按照上面的逻辑，实现的宽表就是（clickhouse的表）：

{
   dt string 时间
   brand  品牌  手机品牌
   phone_model  手机型号
   device_id String  设备号      
}

【注意】：这一步后续是可优化的，要结合bitmap的特性和clickhouse的表引擎来优化

3.2、基于宽表实现bitmap表

clickhouse的bitmap函数页面：https://clickhouse.com/docs/zh/sql-reference/functions/bitmap-functions/

简单分析下需求，我们要取品牌、型号、天维度下的人群基数

那么Clickhouse的bitmap表可以做成如下结构：

{
     dt  LowCardinality(String)   时间
     dim_type  LowCardinality(String)  维度类型(1:品牌维度、2:手机型号维度)
     dim_value LowCardinality(String) 每个dim_type维度下对应的品牌或者手机型号值
     bitmap_dvid  AggregateFunction(groupBitmap, UInt32) 明细
}

然后通过jdbc或者sparkSQL，实现如下操作，向clickhouse的bitmap表插入数据：

1）：插入数据前，删除分区

alter table bitmap的表 on cluster default_cluster drop partition 时间分区

2）：分别插入不同维度的bitmap

插入品牌维度：
INSERT INTO bitmap表 select dt , 1 , brand , groupBitmapState(toUInt32(dvid)) as dvid from 宽表 where dt = 日期 
group by brand

插入型号维度
INSERT INTO bitmap表 select dt , 2 , phone_model , groupBitmapState(toUInt32(dvid)) as dvid from 宽表 where dt = 日期 
group by phone_model

3）：查询新增、留存等

查询iphone12品牌在当前周期（2021-07-01~2021-09-29）相比上周期(2021-05-01 ~ 2021-06-30)的留存人数
select bitmapCardinality(
    bitmapAnd(
       select groupBitmapOrState(devid_bmp) from(
          select devid_bmp from bitmap表 where dt >= '2021-07-01' and dt <= '2021-09-29' and dim_type = 1 and dim_value = 'iphone12'
       ) , -- 当期周期关注过iphone12的人
       select groupBitmapOrState(devid_bmp) from(
          select devid_bmp from bitmap表 where dt >= '2021-05-01' and dt <= '2021-06-30' and dim_type = 1 and dim_value = 'iphone12'
       )  -- 上周期关注过iphone12的人
    )
)

经过测验：一个月的人群数量大约是：1亿

通过bitmap查询留存,一个月的情况，不到2s

4、优化bitmap在clickhouse上的使用

4.1、引擎上的选择

首先是引擎上的选择，实际压测，发现两种引擎在bitmap场景下效率最高，分别是：

1、MergeTree

2、ReplicatedMergeTree

其中使用MergeTree性能会更好

 

4.2、缩减bitmap的大小

我们的设备明细一般是64位的，经过hash散列后，占用的bitmap空间其实是非常大的

所以我们对所有的设备明细维护了一套数字（其实就是hive的row_number）

这样bitmao大大缩减，极大提升查询速度，减小了每次查询使用的CPU和内存