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

简介: 基于clickhouse做用户画像,标签圈选

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

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

先在hive中对数据预处理

--最初表结构

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

image.gif

--插入数据

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

image.gif

现在的表是这样的:

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
);

image.gif

把表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;

image.gif

现在的表结构如下

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

image.gif

--在clickhouse中建库建表

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

image.gif

--写入到CK中

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

image.gif

-- 存储表结构

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;

image.gif

--查询表

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

image.gif

现在表是这样的:

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;

image.gif

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

目录
相关文章
用户画像系列—如何从0到1建设用户画像
用户画像系列—如何从0到1建设用户画像
286 0
【云栖实录】Hologres3.0全新升级:一体化实时湖仓平台
2024年云栖大会,Hologres 3.0全新升级为一体化实时湖仓平台,通过统一数据平台实现湖仓存储一体、多模式计算一体、分析服务一体、Data+AI 一体,发布 Dynamic Table、External Database、分时弹性、Query Queue、NL2SQL 等众多新的产品能力,实现一份数据、一份计算、一份服务,极大提高数据开发及应用效率。同时,Hologres 的预付费实例年付折扣再降15%,仅需7折,不断帮助企业降低数据管理成本,赋能业务增长。
One ID中的核心技术ID-Mapping究竟是怎么实现的?by彭文华
One ID中的核心技术ID-Mapping究竟是怎么实现的?by彭文华
一文教会你使用强大的ClickHouse物化视图
在现实世界中,数据不仅需要存储,还需要处理。处理通常在应用程序端完成。但是,有些关键的处理点可以转移到ClickHouse,以提高数据的性能和可管理性。ClickHouse中最强大的工具之一就是物化视图。在这篇文章中,我们将探秘物化视图以及它们如何完成加速查询以及数据转换、过滤和路由等任务。 如果您想了解更多关于物化视图的信息,我们后续会提供一个免费的培训课程。
28547 10
一文教会你使用强大的ClickHouse物化视图
如何构建标签画像工程体系及实现方案
本文将按总分的结构进行展开:首先对标签画像的基本概念做简单的说明;其次会从业务需求的角度出发,阐述如何构建一个可用的最小标签画像系统单元;而后会以这个最小单元为基础,对部分重点模块进行扩展介绍;最后进行总结,并对文中未涉及的发展方向简要说明。
65201 1
如何构建标签画像工程体系及实现方案
VSCode找不到自定义模块ModuleNotFoundError
VSCode找不到自定义模块ModuleNotFoundError
701 0
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问