本文分享自华为云社区《基于MRS-ClickHouse构建用户画像系统方案介绍》,作者:hourongqi 。
1. 业务场景
用户画像是对用户信息的标签化。用户画像系统通过对收集的各维度数据,进行深度的分析和挖掘,给不同的用户打上不同的标签,从而刻画出客户的全貌。通过用户画像系统,可以对各个用户进行精准定位,从而将其应用于个性化推荐、精准营销等业务场景中。用户画像系统已经被各个企业广泛采用,是大数据落地的重要方式之一。
在移动互联网时代,用户数量庞大,标签数量众多,用户标签的数据量巨大。用户画像系统中,对于标签的存储和查询,不同的企业有不同的实现方案。当前主流的实现方案采用ElasticSearch方案。但基于ElasticSearch构建用户画像平台,往往面临灵活性不足、资源开销大、无SQL接口开发不便等问题。为此,本文提供了一种基于华为MRS ClickHouse构建用户画像系统的方法。
2. 为什么基于MRS-ClickHouse构建标签查询系统
2.1 MRS-ClickHouse简介
MRS-ClickHouse是一款面向联机分析处理的列式数据库。其最核心的特点是极致压缩率和极速查询性能。MRS-ClickHouse支持SQL查询,且查询性能好,特别是基于大宽表的聚合分析查询性能非常优异,比其他分析型数据库速度快一个数量级。
位图索引是一种使用位图的特殊索引,主要针对大量相同值的列而创建。位图中位置编码中的每一位表示对应的数据行的有无。位图索引适合固定值的列,如性别、婚姻状况、行政区等等。而不适合像身份证号、消费金额这种离散值的列。用户画像场景中,每一个标签,对应大量的人群。标签的数量是有限的枚举值,这一特点非常适合位图索引。
举例:
假设有两个标签,一个是标签1-持有贵金属,另一个是标签2-持有保险。各个持卡人拥有的标签情况如下表所示。
从中,我们可以看到,有标签1-持有贵金属的持卡人ID集合是:[0,1,4,5,6,7,9,10,13,14]。有标签2-持有保险的持卡人ID集群是:[2,3,5,7,8,11,12,13,15]。
在ClickHouse出现之前,如果要将位图索引应用于用户画像场景,需要自己构建位图数据结构、管理位图索引,使用门槛较高。好消息是,MRS-ClickHouse原生提供了对位图数据结构和位置索引的支持,将位图的构建及维护封装在ClickHouse内部。使用者基于ClickHouse构建位图索引变成非常的简单。
ClickHouse位图构造函数:
ClickHouse位图运算结果获取函数:
综上,为什么是选择基于ClickHouse构建标签查询系统?
- ClickHouse查询速度快,最快可达亚秒级响应;
- ClickHouse内置位图数据结构,方便构建位图索引,提升标签查询性能;
- 基于JDBC/SQL接口,开发更简单;
- 基于MPP架构,可横向扩展;
3. 如何基于MRS-ClickHouse构建标签查询系统
在ClickHouse中创建一张原始标签表,将原始标签数据导入其中。然后基于标签原始表构建标签位图表,并创建对应的分布式表。上层标签查询应用基于标签位图表(分布式表)进行标签查询。
流程如下图所示:
创建一张标签位图表,先创建本地表。本地表用于保存标签位图数据。其创建语句如下:
-- 创建位图表,先创建本地表CREATE TABLE IF NOT EXISTS tbl_tag_bitmap ON CLUSTER default_cluster( tagname String, --标签名称 tagvalue String, --标签值 tagbitmap AggregateFunction(groupBitmap, UInt64 ) --userid集合)ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/default/tables/{shard}/ tbl_tag_bitmap ','{replica}')PARTITION BY tagnameORDER BY (tagname, tagvalue)SETTINGS index_granularity = 128;
然后再创建对应的分布式表。分布式表用于上层应用查询标签。其建表语句如下:
CREATE TABLE IF NOT EXISTS default.tbl_tag_bitmap_all ON CLUSTER default_cluster( tagname String, --标签名称 tagvalue String, --标签值 tagbitmap AggregateFunction(groupBitmap, UInt64 ) --userid集合)ENGINE = Distributed(default_cluster, default, tbl_tag_bitmap, rand());
将标签原始表的数据导入标签位图表中。并在导入过程中,使用groupBitmapState()函数构建位图。SQL语句如下:
-- 导入数据, 将同一个标签的所有userid使用groupBitmapState函数合并成一个bitmapINSERT INTO tbl_tag_bitmap_allSELECT tagname,tagvalue,groupBitmapState(userid)FROM tbl_tag_src_allGROUP BY tagname,tagvalue;
Step 3:基于分布式表快速检索标签
查询持有贵金属产品,并且性别是男的userid列表:
WITH ( SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '持有产品' AND tagvalue = '贵金属' LIMIT 1 ) AS bitmap1, ( SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '性别' AND tagvalue = '男' LIMIT 1 ) AS bitmap2SELECT bitmapToArray(bitmapAnd(bitmap1, bitmap2)) AS res
分别统计持有保险的客户中,男性和女性的总人数:
---- 查询持有保险的客户中,男性人数:WITH ( SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '持有产品' AND tagvalue = '保险' LIMIT 1 ) AS bitmap1, ( SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '性别' AND tagvalue = '男' LIMIT 1 ) AS bitmap2SELECT bitmapCardinality(bitmapAnd(bitmap1, bitmap2)) AS res---- 查询持有保险的客户中,女性人数:WITH ( SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '持有产品' AND tagvalue = '保险' LIMIT 1 ) AS bitmap1, ( SELECT tagbitmap FROM tbl_tag_bitmap_all WHERE tagname = '性别' AND tagvalue = '女' LIMIT 1 ) AS bitmap2SELECT bitmapCardinality(bitmapAnd(bitmap1, bitmap2)) AS res
4.总结
针对用户画像场景中的海量标签查询,传统的方案存在灵活性不足、资源消耗大、缺少SQL接口开发难度大等问题。基于华为MRS-ClickHouse,可以非常方便的构建位图索引,实现海量标签数据的实时检索。MRS-ClickHouse让开发成本大幅降低,标签查询更快响应,让精准营销更便捷。
华为云FusionInsight MRS云原生数据湖已广泛应用于政府、金融、运营商、大企业、互联网等行业,携手800+合作伙伴,服务于全球60+国家和地区3000+政企客户。