文章目录
一 数据迁移至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;
更多其他函数可以参考官网。
版权归原作者 OneTenTwo76 所有， 如有侵权，请联系我们删除。