clickhouse 在客群和画像中的应用

标签在客群中的应用

1. 标签明细高表建表语句

CREATE TABLE app.app_scrm_suid_flag_bigint_dt_da on cluster default_cluster
(
    `suid` String COMMENT '品牌会员id',
    `key` String COMMENT '标签编码',
    `value` Int64 COMMENT '标签值',  # 根据标签的value类型定义,可以分为int、String、Double 每个类型都对应一张标签明细高表
    `tenant_brand_code` String COMMENT '品牌商编码',
    `channel_code` String COMMENT '渠道编码'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/app/app_scrm_suid_flag_bigint_dt_da', '{replica}')  # 副本模式
PARTITION BY (tenant_brand_code, channel_code) # 分区
ORDER BY (key)
SETTINGS index_granularity = 8192
COMMENT '整型类型标签高表'

2. 基于标签明细高表建立物化视图

CREATE MATERIALIZED VIEW app.app_scrm_suid_flag_bigint_dt_da_aggregation on cluster default_cluster
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/app/app_scrm_suid_flag_bigint_dt_da_aggregation', '{replica}')   # 聚合表引擎
PARTITION BY (tenant_brand_code, channel_code)
ORDER BY (key,value)  # orderby 可以相当于主键，同一个标签的不同的枚举只会保留一份数据
# POPULATE 如果明细高表已经有数据了，加上这个就可以同步数据，对性能有影响，官方并不推荐使用populated，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。
AS SELECT key,
    value,
    groupBitmapState(toUInt64(suid)) AS suid, # 对suid进行groupBy聚合后形成一个bitmap,方便对标签做交并差的逻辑计算
    tenant_brand_code,
	channel_code
FROM app.app_scrm_suid_flag_bigint_dt_da GROUP BY key, value,tenant_brand_code,channel_code;

说明：

• 基于标签明细高表的雾化视图，以空间换时间，会额外保存一份数据。相当于标签明细高表每插入一条数据的时候都需要计算一次，并插入雾化视图里面。
• 当标签明细高表删除的时候，雾化视图的数据依然存在，所以当标签明细高表删除的时候，并不影响雾化视图的查询。

3. 基于标签的交差并操作圈选客群

• 实现类似于神策标签市场的功能
• 第一组标签（a）内部做交集，第二组标签（b）内部做并集合，第一组和第二组做交集，然后计算客群或者计算客群详情

with (
SELECT
      groupBitmapAndState(suid) AS count
from
      (
      select
             groupBitmapOrState(suid) as suid
      from
             app_scrm_suid_flag_bigint_dt_da_aggregation
      where
             tenant_brand_code = '1005'
             and key = 'sex'
             and value in ( 2 , 1 )
      group by
             key ) ) as a,
(
SELECT
      groupBitmapOrState(suid) AS count 
from
      (
      select
             groupBitmapOrState(suid) as suid
      from
             app_scrm_suid_flag_bigint_dt_da_aggregation
      where
             tenant_brand_code = '1005'
             and key = 'sex'
             and value in ( 1 , 2 )
      group by
             key
UNION ALL
      select
             groupBitmapOrState(suid) as suid
      from
             app_scrm_suid_flag_bigint_dt_da_aggregation
      where
             tenant_brand_code = '1005'
             and key = 'age_stage'
             and value in ( 4 , 3 )
      group by
             key ) ) as b
select
      bitmapCardinality(bitmapAnd(a, b)) as count -- 计算客群人数
   # bitmapToArray(bitmapAnd(a, b)) as count   -- 计算客群详情

注意：

• 即使app_scrm_suid_flag_bigint_dt_da_aggregation是聚合后的雾化视图，但是查询的时候还是要和查询标签明细表的sql大致一致需要加聚合条件（group by key），因为当标签明细表插入数据的时候，雾化视图并不一定会立马计算，所以查询的时候需要加上聚合的条件。
• bitmap函数总结：bitmapAnd参数是两个(bitmap,bitmap)，返回的是bigmap。groupBitmapAnd参数是一个（bitmap），返回的具体的个数。groupBitmapAndState参数也是一个(bitmap)，返回的是bitmap的状态（bitmap），可以用于后续的计算。bitmapCardinality参数是一个(bitmap),返回具体的个数。bitmapAndCardinality参数是两个(bitmap,bitmap)，返回具体的个数。
• 查询客群的时候bitmapToArray在jdbc中最多返回一百万数据，所以这块最好是直接使用insert into table with *** select *** 在查询数据的同时，直接将数据插入到客群详情表中。
• 参考文档：https://clickhouse.com/docs/en/sql-reference/functions/bitmap-functions/#bitmapcardinality

标签在画像中的应用

1. 基于ClickHouse标签高表的雾化视图的画像应用

• 对性别和年龄阶段计算客群画像

       select
             groupBitmapMerge(suid) as count, key, value
      from
             app_scrm_suid_flag_bigint_dt_da_aggregation
      where
             key = 'sex'
             and tenant_brand_code = '1005'
      group by
             key, value
             union all 
             select
             groupBitmapMerge(suid) as count, key, value
      from
             app_scrm_suid_flag_bigint_dt_da_aggregation
      where
             key = 'age_stage'
             and tenant_brand_code = '1005'
      group by
             key, value
返回结果案例：              
 ┌───count─┬─key─┬─value─┐
│ 13000000 │ sex │     3 │
│ 1633611 │ sex │     2 │
│  320000│ sex │     1 │
└─────────┴─────┴───────┘

3. 基于ClickHouse标签高表对客群进行画像洞察的画像应用

• 对客群进行画像洞察，求出客群下面不同的标签对应的客群画像

with (
select
       groupBitmapMergeState(suid) as count
from
       t_member_group_member_uid_aggregation  # 客群会员表的客群详情表，一个客群存一条数据，客群的会员id存成bitmap
where
       member_group_id = 137 ) as a
select
       bitmapAndCardinality(a, b) as count,
       key,
       toString(value) as value
from
       (
       select
              groupBitmapMergeState(suid) as b, key, value
       from
              app_scrm_suid_flag_bigint_dt_da_aggregation
       where
              key = 'sex'
              and tenant_brand_code = '1005'
       group by
              key, value)
union all
select
       bitmapAndCardinality(a, b) as count,
       key,
       toString(value) as value
from
       (
       select
              groupBitmapMergeState(suid) as b, key, value
       from
              app_scrm_suid_flag_bigint_dt_da_aggregation
       where
              key = 'sb_age_stage'
              and tenant_brand_code = '1005'
       group by
              key, value)
              
              
              返回结果案例：              
┌───count─┬─key─┬─value─┐
│ 1 │ sex │     3 │
│ 2 │ sex │     2 │
│  3│ sex │     1 │
└─────────┴─────┴───────┘

基于ClickHouse JDBC对BitMap的插入操作

测试 ClickHouse 版本号 21.8.12.29，经测试 在 21.8.12.1 的版本中插入失败。

• 引入Maven

<dependency>    
    <groupId>ru.yandex.clickhouse</groupId>    
    <artifactId>clickhouse-jdbc</artifactId>    
    <version>0.3.2</version>
</dependency>

<dependency>    
    <groupId>com.clickhouse</groupId>    
    <artifactId>org.roaringbitmap</artifactId>    
    <version>1.1.2</version>
</dependency>

• java 代码

1. 将客群数据的会员取出来转化为list

List<Long> collect = memberGroupMemberUidsList.stream().map(x -> 
Long.parseLong(x.getMemberUidId())).collect(Collectors.toList());

2. 将suidList转化成array

long[] longArray = 
collect.stream().mapToLong(Long::longValue).toArray();

3. 将这个参数转化为bitmap

Roaring64Bitmap roaringBitmap = Roaring64Bitmap.bitmapOf(longArray)
将roaringBitmap作为参数
insert into table values (?,?,?,?)

4. jdbc 绑定占位符和参数(中间省略了jdbc创建Connection、PreparedStatement等过程，主要聚焦于Bitmap是如何绑定参数和占位符这块。如果不做如下绑定，会报错。)

for (int i = 0; i < params.length; i++) {
当这个参数是bitmap的时候，需要强转为Roaring64Bitmap
Roaring64Bitmap param = (Roaring64Bitmap) params[i];
然后使用ClickHouseBitmap包装一下就可以
ClickHouseBitmap wrap = ClickHouseBitmap.wrap(param, ClickHouseDataType.UInt64);
使用PreparedStatement绑定参数和占位符
preparedStatement.setObject(i + 1, params[i]);               
}