Clickhouse系列之BitMap函数知识点详解与案例实操使用

阿杰笔记
2,378 阅读4分钟

本文正在参加「技术专题19期 漫谈数据库技术」活动

前言

什么是Bitmap? bitmap是为了节省数据的存储空间而产生的。bitmap可以认为是一个key-value数据库，它通过一个bit来纪录数据的值(0,1)，而这个数据的值对于的键就是这个数据。

image.png

在clickhouse中，为了提高运行速度以及减少代码对与bitmap的重复实现，将其嵌入到数据库中。在clickhouse中Bitmap函数是对两个bitmap对象值进行计算，它会返回一个新的bitmap或者一个新的cardinality，例如与 、或、非等操作。构建一个Bitmap有两种方法。一个是通过带有-State的aggregation function进行Bitmap的构建；两外一种方式是通过Array(数组)进行构建。

正文

image.png

函数详解与操作

安装我们就不讲解了，现在我们直接对Clickhouse中bitmap常用的几个函数进行讲解，如下面表格所示。

函数讲解
arrayJoin宽表转Bitmap表需要行转列，要用arrayJoin把多列数组炸成行。
bitmapAnd 求两个Bitmap值的交集
bitmapOr求两个Bitmap值的并集
bitmapXor求两个Bitmap值的差集(异或)
bitmapToArray把Bitmap转换成数值数组
bitmapToArray把Bitmap转换成数值数组
bitmapCardinality返回一个bitmap数据的个数
  • arrayJoin讲解 ArrayJoin，它是通过将一个Array转换为另一个Array,这个函数使用的会比较频繁，现在我们看看它的一个使用。
  1. 创建库表并插入数据 image.png

2.执行sql

SELECT s, arr  FROM arrays_test  ARRAY JOIN arr;

image.png

  • bitmapAnd讲解 bitmapAnd是两个bitmap进行与运算，然后返回一个新的bitmap结果，执行sql如下：
SELECT bitmapToArray(bitmapAnd(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;

image.png

  • bitmapOr讲解 bitmapOr是两个bitmap进行或然后返回一个新的bitmap结果，行sql如下：
SELECT bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;

image.png

  • bitmapXor讲解 bitmapXor是两个bitmap进行差集然后返回一个新的bitmap结果，行sql如下：
SELECT bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS res;

image.png

  • bitmapToArray讲解 bitmapToArray是将一个bitmap转为一个新的array结果，行sql如下：
SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;

image.png

  • bitmapCardinality讲解 bitmapCardinality是将一个bitmap中的个数返回，行sql如下：
SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS res;

image.png

案例实操

现在我们通过一个案例将上面的函数进行一个案例实操，这个背景是对于在电商中，尤其是私域中十分重要，它是对人群进行一个分群，对于分群之后的数据，运营人员可以对用户进行一个点对点的精准投放。

  • 准备库表以及数据，数据会包含每个用户的多个标签
use cktest;
create table user_tag_merge
(   uid UInt64,
    gender String,
    agegroup String,
    favor String
)engine=MergeTree()
order by (uid);


insert into user_tag_merge values(1,'M','90后','sm');
insert into user_tag_merge values(2,'M','70后','sj');
insert into user_tag_merge values(3,'M','90后','ms');
insert into user_tag_merge values(4,'F','80后','sj');
insert into user_tag_merge values(5,'F','90后','ms');


create table user_tag_value_string
 (
    tag_code String,
    tag_value String ,
    us AggregateFunction(groupBitmap,UInt64)
)engine=AggregatingMergeTree()
 partition by  (tag_code)
 order by (tag_value);
  • 查询插入的数据
select agegroup , gender  , favor   ,uid
from user_tag_merge;
  • 把数据转为一个数组
select  ('agegroup', agegroup ) ,
   ('gender',gender ) , ('favor',favor )    ,uid
    from user_tag_merge;

image.png

  • 把数组转为一个列表
select  [ ('agegroup', agegroup  ) ,
     ('gender',gender ) ,
     ('favor',favor )]  tag_code_value ,uid
    from user_tag_merge

image.png

  • 把数据炸裂(arrayJoin)
SELECT
    arrayJoin([('agegroup', agegroup), ('gender', gender), ('favor', favor)]) AS tag_code_value,
    uid
FROM user_tag_merge;

image.png

  • 将us转为bitmap
    tag_code_value.1 AS tag_code,
    tag_code_value.2 AS tag_value,
    groupBitmapState (uid) AS us
FROM
(
    SELECT
        arrayJoin([('agegroup', agegroup), ('gender', gender), ('favor', favor)]) AS tag_code_value,
        uid
    FROM user_tag_merge
) AS tv
GROUP BY
    tag_code_value.1,
    tag_code_value.2;

image.png

  • 将上入bitmap表中，将arra数据转换为bitmap数据，它是不可见的，需要使用bitmap array去显示。
insert into user_tag_value_string
select tag_code_value.1 as tag_code,tag_code_value.2 as tag_value ,
groupBitmapState( uid ) us
from (
SELECT
    arrayJoin([('agegroup', agegroup), ('gender', gender), ('favor', favor)]) AS tag_code_value,
     uid
FROM user_tag_merge
)tv
 group by tag_code_value.1,tag_code_value.2;
  • 交集查询，这个就是对分组的用户进行一个查询，从sql我们可以看出对于标签为ms,以及90后的标签它的用户id是3，5。那么运营人员就可以对于这两个标签进行一个精准筛选，通过筛选的用户id，给到后端人员进行用户的进一步挖掘。
select  bitmapToArray(
     bitmapAnd(  (  select   us from user_tag_value_string
                   where tag_value='ms' and tag_code='favor'  )
               , ( select   us from user_tag_value_string
                    where tag_value='90后' and tag_code='agegroup'  )
     )
 )as res;

image.png 好了，案例结束啦！

总结

本节主要是讲解clickhouse中Bitmap的使用，对于新手而言，可能对与bitmap不是很熟悉，因此我们会比bitmap进行一个讲解，什么是bitmap。然后对于Clickhouse中的各个常用的bitmap进行讲解与使用，其实操作是十分简单，在工作中都是要对业务的一个理解和使用。最后我们通过一个小的demo对bitmap进行进一步认识和了解，这个demo在电商领域十分常见，因为它对于用户的价值挖掘十分有用，如有问题可以留言讨论。

avatar
文章
阅读
粉丝