基于clickhouse做用户画像，标签圈选

clickhouse在做用户画像标签时，怎么去做圈选，表结构应该是怎么样的，我们应该怎么去处理，能够使其高性能的圈选，尽可能缩小其占用的存储空间？

这个问题，我通过代码给大家做下的演示

先在hive中对数据预处理

--最初表结构 

create table f_tag_table(
    tag Int,
    tag_name String,
    cust_num String
);

--插入数据 

INSERT INTO f_tag_table values
                             (1001, '存款客户', '1,2,3'),
                             (2001,'国债客户', '2,3,4'),
                             (3001,'卡黑名单客户', '2,4'),
                             (4001,'短信黑名单', '3,4');

现在的表是这样的： 

tag	tag_name	cust_num
1001	存款客户	1,2,3
2001	国债客户	2,3,4
3001	卡黑名单客户	2,4
4001	短信黑名单	3,4

重新建一张表

create table usr_table(
	id int,
	tag_name varchar(30),
	tag int
);

 把表f_tag_table的数据插入进去

insert into usr_table
select usr, tag_name, tag from f_tag_table 
lateral view explode(split(cust_num, ',')) tmp as usr;

 现在的表结构如下

usr	tag_name	tag
1	存款客户	1001
2	存款客户	1001
3	存款客户	1001
2	国债客户	2001
3	国债客户	2001
4	国债客户	2001
2	卡黑名单客户	3001
4	卡黑名单客户	3001
3	短信黑名单	4001
4	短信黑名单	4001

 --写入到文件中

./hive -e "
select
*
from ck.usr_table
" | tr "\t" "," > /fileName.csv

--在clickhouse中建库建表

create database ck;
use ck;
create table usr_table(
	id UInt32,
	tag_name String,
	tag UInt32
)ENGINE = MergeTree()
ORDER BY id;

--写入到CK中

clickhouse-client -m -u default -h 192.168.88.161 
--query 'INSERT INTO ck.usr_table FORMAT CSV' < /fileName.csv

-- 存储表结构

create table tag_table(
	tag UInt32,
	tag_name String,
	cust_num AggregateFunction(groupBitmap, UInt64 )
)ENGINE = AggregatingMergeTree()
ORDER BY (tag, tag_name)
SETTINGS index_granularity = 128;

insert into tag_table
select
        tag,
       tag_name,
       groupBitmapState(toUInt64(id)) as cust_num
from usr_table group by tag,tag_name;

 --查询表

select tag, tag_name, bitmapToArray(cust_num) from tag_table;

现在表是这样的：

tag	tag_name	cust_num
1001	存款客户	[1,2,3]
2001	国债客户	[2,3,4]
3001	卡黑名单客户	[2,4]
4001	短信黑名单	[3,4]

 用bitmap进行圈选

WITH
	(
	SELECT cust_num from tag_table where tag ='1001'
	) AS tag1,
	(
	SELECT cust_num from tag_table where tag ='2001'
	) AS tag2,
	(
	SELECT cust_num from tag_table where tag ='3001'
	) AS tag3,
	(
	SELECT cust_num from tag_table where tag ='4001'
	) AS tag4

select bitmapToArray(bitmapAndnot(bitmapOr(tag1, tag2),bitmapOr(tag3, tag4))) 
as customer;

至此你已经完成了对用户标签的圈选