ClickHouse中的 Bitmap

ClickHouse Bitmap

一、bitmap本身

bitmap（位图）是一种利用比特位来进行数据存储的结构，非0即1。

简单举例：存储1-8的整数，如果我们用整数数组的话，至少需要4（1个int型整数按4字节）*8=32个字节的存储空间，但是如果用bitmap的话，我们只需要1个字节（8bit），从低位到高位，每一位是否为1即可表示该数是否存在。显然，使用bitmap能够显著节省用户存储空间。

二、clickhouse中的bitmap

2.1 定义

bitmap在clickhouse中是一种AggregateFunction的数据类型。

clickhouse使用RoaringBitmap实际存储位图对象，当基数小于或等于32时，它使用Set保存。当基数大于32时，它使用RoaringBitmap保存。这也是为什么低基数集的存储更快的原因。

2.2 构建方法

(来自官网：https://clickhouse.com/docs/zh/sql-reference/functions/bitmap-functions)

• 通过对整形数组进行转换得到
  • bitmapBuild(array)：将array转换为bitmap，示例：

SELECT bitmapBuild([1, 2, 3, 4, 5]) AS res

• 通过聚合函数groupBitmapState来构造
  • groupBitmapState(arrayJoin(array))：将列值转换成bitmap，示例:

SELECT groupBitmapState(arrayJoin([1, 2, 3, 4, 5]))

2.3 创建bitmap表

create table user_tag_value_bitmap
 ( 
    tag_code String,
    tag_value String ,
    us AggregateFunction(groupBitmap,UInt64)
)engine=AggregatingMergeTree()
 partition by  (tag_code) 
 order by (tag_value);

Bitmap表必须选择AggregatingMergeTree引擎。

对应的Bitmap字段，必须是AggregateFunction(groupBitmap,UInt64)，groupBitmap标识数据的聚合方式，UInt64标识最大可存储的数字长度。

2.4 操作bitmap

ClickHouse实现了大量的BitMap的函数，获取属性，集合运算等。包括但不限于：（所有函数可参照官网介绍）

• bitmapToArray(bitmap) 将位图转换为整数数组
• bitmapSubsetInRange(bitmap, range_start, range_end) 将位图指定范围（不含range_end）转换为另一个位图。
• bitmapContains(haystack, needle) 检查位图是否包含指定元素。
• bitmapAndnot(bitmap1,bitmap2) 计算两个位图的差异，返回一个新的位图对象。
• bitmapCardinality(bitmap) 返回一个UInt64类型的数值，表示位图对象的基数。
• groupBitmapState 将聚合列的数字值聚合成bitmap
• groupBitmapMergeState 将一列中的bitmap值进行聚合
• ……

2.5 应用场景

用户画像、行为分析（漏斗分析，留存分析）

1.用户画像：

• 存储结构：按标签+用户ID列表的方式来进行存储，其中用户ID列表使用bitmap类型来节省存储空间。
• 使用方法：通过标签可以快速找到相关人群进行推荐；对于多个标签查找共性用户的需求，可以通过bitmap集合运算函数来快速得到。
  在用户画像中，利用bitmap储存用户Id的人群包，同时利用位运算的与或非操作来计算多个人群包的交集并集，从而得到新人群。

2.漏斗分析：

• 存储结构：uid collection 使用bitmap存储用于快速过滤数据 ，存储uid的sequence编码用于序列和时间窗口的匹配。
• 使用方法：对动作的人员列表做预聚合，比如每天对前一天的元数据执行一次，将结果（Bitmap）保存到预聚合表中，查询时只对 Bitmap 做取交集获取基数。

3.留存分析：

• 存储结构：uid collection 使用bitmap存储用于快速过滤数据 。
• 使用方法：每天将结果保存到bitmap表中，查询时更新这个bitmap表并与今天的表进行关联并补全。

2.6 使用示例

1.用户画像：

使用groupArray:

SELECT
    tag_code_value.1 AS tag_code,
    tag_code_value.2 AS tag_value,
    groupArray(id) AS us
FROM (
SELECT
    arrayJoin([('browser', browser ),
    ('phone_model',phone_model ),
    ('isFirstUse',isFirstUse )]) AS tag_code_value,
    id
FROM users_all
) AS tv
GROUP BY
    tag_code_value.1,
    tag_code_value.2;

这里需要说明一下，聚合成bitmap的列没有显示是正常的，因为bitmap的结构本身无法用正常文本显示。

但是将聚合的值插入bitmap表（建表部分参照前面2.3部分），可以通过bitmapToArray函数验证。

select tag_code, tag_value , bitmapToArray(us) from user_tag_value_bitmap ;

最后bitmap对browser(浏览器版本)，phone_model(手机型号)，isFirstUse(是否初次使用)这三个用户属性的聚合结果如下：

这样之后，通过标签可以快速找到相关人群；非常满足对于多个标签查找共性用户的需求

select
bitmapToArray(
    bitmapAnd(
        (select   us from user_tag_value_bitmap
           where tag_value='Chrome' and tag_code='browser'),
        (select   us from user_tag_value_bitmap
            where tag_value='否' and tag_code='isFirstUse')
    )
)as res

select
bitmapToArray(
    bitmapAnd(
        (select groupBitmapMergeState(us) us from user_tag_value_bitmap
            where tag_value='Chrome' and tag_code='browser'),
        (select groupBitmapMergeState(us) from user_tag_value_bitmap
            where tag_value in ('iPhone 12','iPhone 13 Pro') and tag_code='phone_model')
    )
)as res

因为查询时，有可能需要针对某一个标签，取多个值，甚至是一个区间范围，那就会涉及多个值的userId集合，因此需要在子查询内部用groupBitmapMergeState进行一次合并，多个集合取并集。

另外，如果有多张tag表，进行交并计算（要比普通的用户表进行JOIN或者IN计算要高效很多）：

with
(
    SELECT groupBitmapOrState(users) FROM tag_users WHERE tag_id = 'f'
) as user_group1,
(
    SELECT groupBitmapOrState(users) FROM tag_users2 WHERE tag_id = 'ff'
) as user_group2
select bitmapToArray(bitmapAnd(user_group1, user_group2));

2.漏斗分析：

使用windowFunnel:

with result as (select event.user_id as user_id, windowFunnel(86400)(time, (event.`event` = 'appEnter') and date >= '2023-06-14' and date <= '2023-06-20',
(event.`event` = 'beginLogModule'),
(event.`event` = 'buttonClick'),
(event.`event` = 'appExit')) AS level from app_events_all  event
 where date >= '2023-06-14' and date <= '2023-06-21' GROUP BY user_id)
select transform(level_index, [1, 2, 3, 4],['打开APP', '进入模块', '打开APP', '退出APP'], '其他' ) as event, count(distinct user_id) from (
select user_id,
arrayJoin(arrayEnumerate(arrayWithConstant(level, 1))) level_index
      FROM result
          )
group by level_index
order by level_index

对于实时漏斗分析查询来说，查询条件是变化多端的，做预聚合的前提是基于查询条件（WHERE 或 GROUP BY子句） 中的维度的，而对于 windowFunnel 函数，步骤条件、步骤执行顺序等条件均在函数内设置，无法外置，因此预聚合实施无法完成。

使用bitmap：

with
        (select groupBitmapState(sipHash64(user_id)) from app_events_all where event = 'appEnter' and date >= '2023-06-14' and date <= '2023-06-20') as appEnter,
        (select groupBitmapState(sipHash64(user_id)) from app_events_all where event = 'beginLogModule' and date >= '2023-06-14' and date <= '2023-06-21') as beginLogModule,
        (select groupBitmapState(sipHash64(user_id)) from app_events_all where event = 'buttonClick' and  date >= '2023-06-14' and date <= '2023-06-21') as buttonClick,
        (select groupBitmapState(sipHash64(user_id)) from app_events_all where event = 'appExit' and date >= '2023-06-14' and date <= '2023-06-21') as appExit

select bitmapCardinality(appEnter)   as num1,
       bitmapAndCardinality(appEnter, beginLogModule)  as num2,
       bitmapAndCardinality(bitmapAnd(appEnter, beginLogModule), buttonClick)  as num3,
       bitmapAndCardinality(bitmapAnd(bitmapAnd(appEnter, beginLogModule), buttonClick), appExit) as num4;

with 子句中分别获取了 4 个行为动作的人员列表（ uid 列表（ bitmap 类型）），在 select 中依次对 4 个动作的人员列表取交集，然后计算基数。

这样，我们就可以将 with 中的内容预聚合，比如每天对前一天的元数据执行一次，将结果（Bitmap）保存到预聚合表中，真正查询时只需要对 Bitmap 做取交集获取基数的操作就可以了，而且预聚合后数据量会急剧减少，性能方面有极大的提升；

但是！！！这里不得不提及关于漏斗分析，bitmap无法做到的点，Bitmap 的与（And）操作是不区分前后顺序的，也就是说，bitmapAndCardinality(appEnter, beginLogModule) 与 bitmapAndCardinality(beginLogModule, appEnter) 是等价的。由此可见，如果想要用 Bitmap 来进行操作，元数据必须要从用户操作和业务上保证严格意义上的顺序，也就是说，用户只有这一条行为操作路径，否则统计会产生严重的误差。另外，Bitmap无法进行窗口期限制，不能保证动作链路在某一规定的时间内完成。

所以，总体来说，在做漏斗分析时，bitmap在无窗口期限制的无序漏斗上是非常适合的，而有序漏斗需要确保动作链路的严格顺序一致性，这里仅仅使用bitmap是无法实现的。

至于有序漏斗该如何有效利用bitmap，则可以先使用bitmap结构存储相关用户行为数据，在此基础上通过UUID集合做快速的过滤，再对过滤后的UUID分别做时间戳的匹配，同时上一层节点输出也作为下一层节点的输入，设计一套流程算法，由此达到快速过滤的目的 。

详细的设计流程在美团点评的一篇技术文章，可以用来参考：

https://tech.meituan.com/2018/03/20/user-funnel-analysis-design-build.html

3.留存分析：

使用rentention:

  SELECT
        sum(r[1]) AS r1,
        sum(r[2]) AS r2,
        r2/r1
    FROM
    (
    select user_id,
           retention(date='2021-05-01' and event = '启动',date='2021-05-03' and event = '首页') AS r
           from  analyse_testb where   date >= '2021-05-01' and  date  <= '2021-05-03'
               group by user_id );

  #案例:用户留存
SELECT

    SUM(r[1]) AS "第1天活跃用户",
    SUM(r[2])/SUM(r[1]) AS "次日留存",
    SUM(r[3])/SUM(r[1]) AS "3日留存",
    SUM(r[4])/SUM(r[1]) AS "7日留存"
FROM
    (
        WITH
            first_day_table AS ( SELECT TIMESTAMP '2021-05-01 00:00:00' AS first_day)
        SELECT
            user_id,
            retention(
                date = (SELECT DATE(first_day) FROM first_day_table),
                date = (SELECT DATE(first_day +  INTERVAL 1 DAY) FROM first_day_table),
                date = (SELECT DATE(first_day +  INTERVAL 2 DAY) FROM first_day_table),
                date = (SELECT DATE(first_day +  INTERVAL 6 DAY) FROM first_day_table)
                ) AS r
            FROM    analyse_testb
            WHERE   (date >= TIMESTAMP '2021-05-01 00:00:00')
            AND     (date <= TIMESTAMP '2021-05-01 00:00:00' + INTERVAL '6 day')
            GROUP BY user_id
        );

使用bitmap:

select event,bitmapAndCardinality(
    neighbor(bmp,-1), bmp
) retetion
FROM (
    select event, groupBitmapState(sipHash64(user_id)) bmp FROM analyse_testb
    where  date >= '2021-05-01' and  date  <= '2021-05-03'
    GROUP BY event order by event
)