clickhouse中bitmap在用户标签，访客去重生产中使用及clickhouse建表null值数据类型处理

       

        clickhouse中bitmap的使用对于用户标签及用户，访客跨维度去重统计是十分合适的选择，具有更快的查询效率。

1.bitmap

bitmap建表方式：

create table test.bitmap_test(
    tenant_id   String  comment '租户id',
    shop_id     String  comment '门店id',
    h           int    comment '小时段',
    uv          AggregateFunction(groupBitmap(),Int64) comment '记录访客人数',
    dt          Date   comment '日期'
)engine =AggregatingMergeTree()
partition by (dt)
order by (tenant_id,shop_id)
settings index_granularity =8192;

uv字段就是bitmap的数据类型，里面存储的元素就是bitmap对象

创建数据源表：

create table test.bitmap_source(
    tenant_id   String  comment '租户id',
    shop_id     String  comment '门店id',
    h           int    comment '小时段',
    uv          String comment '记录访客人数',
    dt          Date   comment '日期'
)engine =MergeTree()
partition by (dt)
order by (tenant_id,shop_id)
settings index_granularity =8192;

将数据源表中的数据写入bitmap表中：

    insert into test.bitmap_test
    select
        tenant_id,
        shop_id,
        h,
        groupBitmapState(metroHash64(uv)),
        dt
    from test.bitmap_test1
    group by tenant_id, shop_id, h, dt;

        其中在将数据写入bitmap表中，使用两个函数metroHash64（）将uv String类型的转换为int类型，而后在使用groupBitmapState（）函数转换为bitmap对象存入bitmap表中。

        通常在bitmap函数后加State则构造为bitmap对象数据，对于构造bitmap有两种方式：

一种是从聚合函数groupBitmapState构造的，另外一种就是使用Array对象进行构建

//构建bitmap函数
SELECT bitmapOrCardinality(bitmapBuild([1,2,3,4,5,6]), bitmapBuild([3,4,5,6,7,8]));

求取去重的uv访客量：

select groupBitmapOr(uv) from test.bitmap_test;

groupBitmap函数就是bitmap对象的聚合函数，作用就是去重统计该bitmap字段对象的的数量。

更多的bitmap聚合函数可以通过下面链接在官网中查看。

2.常用的位图函数

        除了上面所说到的聚合函数外，还用很多常用的位图操作函数，对于位图操作函数，可以在下面参考文章和官网资料中有详细的介绍就不在一一解释了。

3.bitmap表join求取去重效果

select * from
(select * from test.bitmap_test t1
left join (select * from test.bitmap_test where tenant_id='1000000007') t2
on t1.tenant_id=t2.tenant_id);

 执行结果：

 对于t2中没有关联上的数据，uv直接为空，在使用bitmap聚合函数求取uv的去重人数时候，

select groupBitmapOr(t2.uv) from
(select * from test.bitmap_test t1
left join (select * from test.bitmap_test where tenant_id='1000000007') t2
on t1.tenant_id=t2.tenant_id);

执行结果为：

该结果和下面语句的执行结果相同

select groupBitmapOr(uv) from test.bitmap_test where tenant_id='1000000007';

         这表示用bitmap聚合函数求取去重人数的时候字段中有空值并不会造成结果的不准确。

4.clickhouse建表时候null值字段设置

 建表时如果没有对字段指定可以存储null值，则在数据时候会对null值数据数值类型补为：0，字符串类型补为:''空字符串；

create table if not exists test(
    id int,
    name String
)
engine =MergeTree()
order by (id);

--没有对字段设置为可支持null值存储，则保存时候会无法存入null值，对null数据替换

解决方案：clickhouse支持null值存储语法

create table if not exists test(
    id Nullable(int),
    name Nullable(String)
)
engine =MergeTree()
order by (id);

--使用nullable语法来实现支持null值

官网资料：

1.bitmap建表数据类型指定 

2.bitmap的聚合函数

3.位图操作函数

参考文章：

1.BitMap使用在clickhouse中

2.参考