【用户画像】将数据迁移到ClickHouse（源码实现）、位图的介绍（bitmap）、位图在用户分群中的应用、位图的使用

文章目录

一 数据迁移至Clickhouse

1 为何要迁移

标签计算完成后保存在hive虽然可以查询但是性能非常糟糕。而标签的使用往往是即时的。最常见的场景就是“用户分群”，也称“人群圈选”、“圈人”等等。

分群操作就是根据多个标签组合，产生一个用户集合，供营销、广告等部门使用。而这些操作计算量大，产生结果需要时效性高。

2 方案选型

选择方案最重要的依据就是数据量和时效性要求。
时效性数据量分群方案****能接受隔天无所谓HIVE宽表即时产生千万以下，标签百级OLAP宽表(Elasticsearch,Clickhouse,Tidb…)即时产生亿级，标签千级Bitmap方式(Clickhouse，doris)
适合的才是最好的，此任务选择用Clickhouse实现Bitmap方式存储。

3 任务目标

把hive中标签宽表数据，写入至Clickhouse的宽表。

4 设计分析

• 读取hive的宽表，在clickhouse中建立对应的宽表。因为并不是hive表到hive表，所以并不能够直接用insert select 解决。
• 先通过把数据查询成为Dataframe ，再通过行动算子写入至Clickhouse的宽表。

5 代码实现

搭建模块 – task-export-ck

（1 ）pom.xml

在poml文件中添加配置

<dependencies><dependency><groupId>com.hzy.userprofile</groupId><artifactId>task-common</artifactId><version>1.0-SNAPSHOT</version></dependency></dependencies><build><plugins><!-- 该插件用于将Scala代码编译成class文件 --><plugin><groupId>net.alchim31.maven</groupId><artifactId>scala-maven-plugin</artifactId><version>3.4.6</version><executions><execution><!-- 声明绑定到maven的compile阶段 --><goals><goal>compile</goal><goal>testCompile</goal></goals></execution></executions></plugin><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-assembly-plugin</artifactId><version>3.0.0</version><configuration><descriptorRefs><descriptorRef>jar-with-dependencies</descriptorRef></descriptorRefs></configuration><executions><execution><id>make-assembly</id><phase>package</phase><goals><goal>single</goal></goals></execution></executions></plugin></plugins></build>

（2）配置文件

core-site.xml、hdfs-site.xml、hive-site.xml、hive-site.xml、log4j.properties与之前模块无异,主要用于本地调试。

config.properties

#mysql配置
mysql.url=jdbc:mysql://hadoop101:3306/user_profile_manage2022?characterEncoding=utf-8&useSSL=false
mysql.username=root
mysql.password=123456

clickhouse.url=jdbc:clickhouse://hadoop101:8123/user_profile2022

ClickHouse有两个对外开放的端口号：8123和9000

8123，适用于JDBC，短连接，HTTP协议

9000，适用于Client，长连接，TCP协议

添加执行clickhouse的Sql语句的工具类。

因为该工具也会被其他模块使用，所以放在task-common下的util层

packagecom.hzy.userprofile.utilimportjava.sql.{Connection, DriverManager, Statement}importjava.util.Properties

object ClickHouseUtil {privateval properties: Properties = MyPropertiesUtil.load("config.properties")val CLICKHOUSE_URL = properties.getProperty("clickhouse.url")def executeSql(sql:String):Unit={
    Class.forName("ru.yandex.clickhouse.ClickHouseDriver");val connection: Connection = DriverManager.getConnection(CLICKHOUSE_URL,null,null)val  statement: Statement = connection.createStatement()
    statement.execute(sql)
    connection.close()}}

（3）创建库

clickhouse-client -m

create database user_profile1009;

完成测试后，可以在ClickHouse中user_profile1009中看到对应的数据。

6 打包发布

添加流程任务，因为要在合并宽表任务之后执行，所以级别设为300，调度任务之后，再次测试执行，等待结果。

注意注释掉

//.setMaster("local[*]")

，打包，上传。

启动远程任务提交器，内网穿透。

二 在clickhouse中宽表转换为Bitmap表

1 为什么用位图（Bitmap）?

（1）存储成本

假设有个1,2,5的数字集合，如果常规的存储方法，要用3个Int32空间。其中一个Int32就是32位的空间。三个就是3*32Bit，相当于12个字节。

如果用Bitmap怎么存储呢，只用8Bit（1个字节）就够了。每一位代表一个数，位号就是数值，1标识有，0标识无。如下图：
7654321000100110
这样的一个字节可以存8个整数，每一个数的存储成本实质上是1Bit。

也就是说Bitmap的存储成本是Array[Int32]的1/32,是Array[Int64]的1/64。

好处一： 如果有一个超大的无序且不重复的整数集合，用Bitmap的存储成本是非常低的。

（2）天然去重

好处二： 因为每个值都只对应唯一的一个位置，不能存储两个值，所以Bitmap结构可以天然去重。

（3）快速定位

如果有一个需求，比如想判断数字“3”是否存在于该集合中。若是传统的数字集合存储，那就要逐个遍历每个元素进行判断，时间复杂度为O(N)。

但是若是Bitmap存储只要查看对应的下标数的值是0还是1即可，时间复杂度为O(1)。

查询3
7654→321000100110
好处三：非常方便快速的查询某个元素是否在集合中。

（4）集合间计算

如果有另一个集合2、3、7，想查询这两个集合的交集。

传统方式[1,2,5]与[2,3,7] 取交集就要两层循环遍历。

而Bitmap只要把00100110和10001100进行与操作就行了。而计算机做与、或、非、异或 等等操作是非常快的。

如下：
7654321010001100
&
7654321000100110
=
6543210****00000100
好处四：集合与集合之间的运行非常快。

（5）优势场景

综上，Bitmap非常适合的场景：

• 海量数据的压缩存储
• 去重存储
• 判断值存在于集合
• 集合之间的交并差

（6）局限性

当然这种方式也有局限性：

• 只能存储正整数字而不是字符串
• 存储的值必须是无序不重复
• 不适合存储稀疏的集合，比如一个集合存了三个数[5,1230000,88880000] 这三个数，用Bitmap存储的话其实就不太划算。（但是clickhouse使用的RoaringBitmap，优化了这个稀疏问题。）> RoaringBitmap是一种混合的结构，将整个的数据空间分成一段一段的，如0-1000,1000-2000等，这样就可以将每一段去独立的管理。RoaringBitmap中有两种存储方式，使用Bitmap或者使用数组存储，如果数据很稀疏则使用数组存储十分划算，反之使用Bitmap存储划算。于是，在RoaringBitmap中会存在一个阈值，超过阈值使用Bitmap存储，最终将这两种数据结构组合起来，以解决数据稀疏的问题。

2 Bitmap在用户分群中的应用

（1）现状

首先，如下是用户的标签宽表
用户性别年龄偏好1男90后数码2男70后书籍3男90后美食4女80后书籍5女90后美食
如果想根据标签划分人群，比如：年龄：90后 + 偏好：美食。

（2）传统解决方案

那么无非对列值进行遍历筛选，如果优化也就是列上建立索引，但是当这张表有1000个标签列时，如果要索引生效并不是每列有索引就行，要每种查询组合建一个索引才能生效，索引数量相当于1000个列排列组合的个数，这显然是不可能的。

（3）更好的方案

那么更好的办法是按字段重组成Bitmap。

将年龄和偏好分别提取出来。
年龄ArrayBitmap****90后1,3,50010101080后40001000070后200000100性别ArrayBitmap****男1,2,300001110女4,500110000偏好ArrayBitmap****数码100000010美食3,500101000书籍2,400010100
如果能把数据调整成这样的结构，想进行条件组合，就简单了。

比如: [美食] + [90后] = Bitmap[3,5] & Bitmap[1,3,5] = 3,5 这个计算速度相比宽表条件筛选是非常非常快的。

3 在clickhouse中使用Bitmap表

最终想得到的结果如下图：

现原始表结构为：

转换过程如下图：

（1） SQL实现

-- 将两列值拼成数组select[1as a,2as b];-- 将两列值拼成元组select(1as a,2as b);-- 元组外面再嵌套数组select[(1as a,2as b),(3,4)];-- 再炸开select arrayJoin([(1as a,2as b),(3,4)]);-- 切开select rs_col.1, rs_col.2from(select arrayJoin([(1as a,2as b),(3,4)]) rs_col ) rs_t;

（2） 在clickhouse中使用Bitmap表

以上面的表举例：

建表和数据

createtable user_tag_merge 
(   uid UInt64,
    gender String,
    agegroup String,
    favor String
)engine=MergeTree()orderby(uid);

模拟数据

insertinto user_tag_merge values(1,'M','90后','sm');insertinto user_tag_merge values(2,'M','70后','sj');insertinto user_tag_merge values(3,'M','90后','ms');insertinto user_tag_merge values(4,'F','80后','sj');insertinto user_tag_merge values(5,'F','90后','ms');

原始数据如下：

数据转换

现依据上图流程，依次对数据进行处理

-- 拼select uid,[('gender',gender),('agegroup',agegroup),('favor',favor)]from user_tag_merge;-- 炸：用arrayJoin炸开，类似于hive中的explodeselect uid, arrayJoin([('gender',gender),('agegroup',agegroup),('favor',favor)]) tv
from user_tag_merge;-- 切select tv.1,tv.2,uid
from(select uid, arrayJoin([('gender',gender),('agegroup',agegroup),('favor',favor)]) tv
from user_tag_merge) user_tag;-- 聚select tv.1,tv.2,groupArray(uid)from(select uid, arrayJoin([('gender',gender),('agegroup',agegroup),('favor',favor)]) tv
from user_tag_merge) user_tag
groupby tv.1,tv.2;-- 聚（bitmap）select tv.1,tv.2,groupBitmapState(uid)from(select uid, arrayJoin([('gender',gender),('agegroup',agegroup),('favor',favor)]) tv
from user_tag_merge) user_tag
groupby tv.1,tv.2;-- bitmap的结构本身无法用正常文本显示，为看出效果，再嵌套一层数组select tv.1,tv.2,bitmapToArray(groupBitmapState(uid))from(select uid, arrayJoin([('gender',gender),('agegroup',agegroup),('favor',favor)]) tv
from user_tag_merge) user_tag
groupby tv.1,tv.2;

创建Bitmap表

createtable user_tag_value_string
 ( 
    tag_code String,
    tag_value String ,
    us AggregateFunction(groupBitmap,UInt64))engine=AggregatingMergeTree()partitionby(tag_code)orderby(tag_value);

Bitmap表必须选择AggregatingMergeTree引擎。

对应的Bitmap字段，必须是AggregateFunction(groupBitmap,UInt64)，groupBitmap标识数据的聚合方式，UInt64标识最大可存储的数字长度。

业务结构上，稍作了调整。把不同的标签放在了同一张表中，但是因为根据tag_code进行了分区，所以不同的标签实质上还是物理分开的。

插入数据

groupBitmapState()：将多行的值聚合成一个bitmap值。

insertinto user_tag_value_string
select tv.1,tv.2,groupBitmapState(uid)from(select uid, arrayJoin([('gender',gender),('agegroup',agegroup),('favor',favor)]) tv
from user_tag_merge) user_tag
groupby tv.1,tv.2;-- 查看数据是否正确，再转成数组select tag_code, tag_value, bitmapToArray(us)from user_tag_value_string;

结果如下：

以上操作就是通过一句sql，将在ClickHouse处理的宽表变成一个位图表。

对Bitmap进行查询

对Bitmap进行查询

使用这个Bitmap表进行查询。

比如想查询[90后]+[美食]的用户条件组合查询

bitmapAnd(bitmapa,bitmapb)：求交集

select bitmapToArray(
bitmapAnd ((select us from user_tag_value_string where tag_value='90后'and tag_code='agegroup'),(select us from user_tag_value_string where tag_value='ms'and tag_code='favor')));

首先用条件筛选出us, 每个代表一个Bitmap结构的uid集合，找到两个Bitmap后用bitmapAnd函数求交集。 然后为了观察结果用bitmapToArray函数转换成可见的数组。

范围值查询

• 比如要取 [90后]或者[80后] + [美食]
• 或者消费金额大于1000 + [女性]

groupBitmapState 和groupBitmapMergeState区别

前者 把普通值聚合成bitmap ，后者 是bitmap之间进行并集的聚合

select bitmapToArray(
bitmapAnd ((select groupBitmapMergeState(us)from user_tag_value_string where tag_value in('90后','80后')and tag_code='agegroup'),(select us from user_tag_value_string where tag_value='ms'and tag_code='favor')));

先对多个年龄组取并集，然后去交集。

询时，有可能需要针对某一个标签，取多个值，甚至是一个区间范围，那就会涉及多个值的userId集合，因此需要在子查询内部用groupBitmapMergeState进行一次合并，其实就多个集合取并集。

比如要取 [90后]或者[80后] + [美食]或者[书籍]

select bitmapToArray(
bitmapAnd ((select groupBitmapMergeState(us)from user_tag_value_string where tag_value in('90后','80后')and tag_code='agegroup'),(select groupBitmapMergeState(us)from user_tag_value_string where tag_value in('ms','sj')and tag_code='favor')));

函数总结

函数****arrayJoin宽表转Bitmap表需要行转列，要用arrayJoin把多列数组炸成行。groupBitmapState把聚合列的数字值聚合成Bitmap的聚合函数bitmapAnd求两个Bitmap值的交集bitmapOr求两个Bitmap值的并集bitmapXor求两个Bitmap值的差集(异或)bitmapToArray把Bitmap转换成数值数组groupBitmapMergeState把一列中多个bitmap值进行并集聚合。 （连续值）bitmapCardinality求Bitmap包含的值个数

select tag_value,bitmapCardinality(us) from user_tag_value_string;

更多其他函数可以参考官网。