一文读懂：画像平台人群包产出进阶之路

本文将以规则人群为例，完整地描述人群创建耗时从十几分钟降低到秒级响应的优化进阶过程。

人群圈选需求

数据工程师给了下面一张Hive宽表userprofile_demo.user_label_table，其表结构及数据示例如表9-2所示。

                 表9-2 userprofile_demo.user_label_table表结构及数据示例

上述表中包含了海量用户的200多个标签数据；数据表以日期作为分区，单个分区下有一亿多行数据。随着时间推移，数据行数和列数都会逐渐增加。

产品需求是基于这张宽表可以实现人群圈选功能：用户通过可视化的页面选择标签并配置筛选条件，系统可以快速找到满足条件的用户并生成人群。以上述表userprofile_demo.user_label_table为例，其可能存在的最复杂的查询需求：筛选出2022年1月1日到1月7日期间每日评论次数都大于2次、 粉丝数范围属于[200，800]且喜欢军事的北京市男性用户。

简单直接的解决思路

实现上述需求的核心是构建如下SQL语句并找到所有满足条件的UserId，其中WHERE条件是什么取决于用户在画像平台上的标签选择和筛选配置。

SELECT
DISTINCT userId
FROM
userprofile_demo.user_label_table
WHERE
sex = '男'
AND province = '北京市'
AND (
fans_count >= 200
AND fans_count <= 800
)
AND...

如何执行上述Hive SQL语句？可以通过Hive JDBC连接HiveServer并提交SQL语句，这种开发模式和使用传统的MySQL数据库比较相似，工程上可以快速上手且开发效率较高。解决了SQL执行问题，那查询结果如何存储为人群？可以通过下面的SQL语句将用户查询结果insert到人群结果表中。

INSERT OVERWRITE userprofile_demo.crowd_result_table
SELECT
DISTINCT userId
FROM
userprofile_demo.user_label_table...

其中人群结果Hive表为userprofile_demo.crowd_result_table，其表结构及数据示例如表9-3所示。该表中crowd_id作为分区键，方便按人群crowd_id获取所有user_id。

                表9-3 userprofile_demo.crowd_result_table表结构及数据示例

当人群应用到第三方平台时，需要拉取指定人群下的所有UserId数据。如果通过人群结果Hive表传递数据不仅涉及数据权限与安全问题，而且传递效率低且服务不稳定。为了解决这个问题引入了BitMap（Java代码中使用的是RoaringBitmap），可以将人群中的所有UserId存储到BitMap并持久化存储到阿里云OSS中，通过BitMap和第三方平台之间进行人群数据交互可以实现秒级完成。

到目前为止，用户可以通过可视化的方式创建人群，人群数据最终存储在Hive表和OSS中，主要借助BitMap对外提供人群数据，其架构如图9-6所示。

图片

ClickHouse作为缓存

随着人群创建数目的增加，完全基于Hive表圈选人群的问题逐渐暴露出来：当人群集中创建时其产出效率较低。这个问题的主要原因是所有人群创建任务都集中在一个离线队列中，而且任务间没有优先级划分，不同任务抢占资源从而造成人群产出延迟。

针对上述问题有两个主要解决方法：

• 人群任务划分优先级。人群创建任务区分不同优先级，高优先级任务放到高优队列执行，保证高优任务优先执行。
• 更换执行引擎。将大数据任务执行引擎从默认的MapReduce切换至Spark，提高计算速度。

按上述思路修改后运行效果不如预期。在资源有限的情况下，任务优先级与人群产出时间没有明显正相关关系，优先级高的队列资源虽然充裕，但是资源饱和度可能也高，最终人群产出整体时间也可能较长。执行引擎切换到Spark后速度提升明显，但是对资源的消耗也相应增加。

ClickHouse主要应用在OLAP场景下，工程上考虑将作为Hive表的“缓存”来加速人群圈选的速度。人群圈选的初衷是找到所有满足条件的用户，可以把用户筛选语句直接交由ClickHouse引擎执行。当满足条件的用户比较少时可以一次性查询出所有用户结果；当用户量级比较大时，直接通过单条SQL语句查出所有结果很容易超过ClickHouse集群的内存和IO限制，此时可以通过下述两种方式来解决。

• 借助offset与limit分批次查询UserId并最终汇总查询结果。其核心代码如下所示，通过offset调整偏移量，通过limit值调整每一批次的人群数目，limit的数值大小影响了分批次查询的次数并最终影响人群的产出时间。

SELECT
userId
FROM
userprofile_demo.user_label_ch_table
WHERE
dt = '2022-01-01'
AND sex = '男'
AND province = '北京市'
AND (
fans_count >= 200
AND fans_count <= 800
)
ORDER BY
userId
LIMIT
-- 分批次查询数据 --
0, 10000

• 通过ClickHouse原生BitMap查询UserId。ClickHouse支持将查询出的数字类型结果封装成BitMap直接返回客户端，该方式可以节约网络传输带宽，从而单批次可以支持更大量的用户查询需求，最终可以降低人群的产出时间。

SELECT
groupBitmapState(userId) as bitmap
FROM
userprofile_demo.user_label_ch_table
WHERE
dt = '2022-01-01'
AND sex = '男'
AND province = '北京市'
AND (
fans_count >= 200
AND fans_count <= 800
)
ORDER BY
userId
LIMIT
0, 10000

通过以上两种方式从ClickHouse查询出的UserId可以在内存中直接写入BitMap，有了人群BitMap便可以直接向第三方提供人群数据。为了满足Hive表形式的人群使用需求，后续还可以将人群BitMap落盘到人群结果Hive表中。如图9-7所示，人群圈选功能的实现已经从单纯的Hive查询转变为ClickHouse查询优先、失败后Hive兜底的方式，人群圈选速度提升明显，人群产出时间从几十分钟降低到几分钟。

图片

SQL优化与BitMap使用

随着人群数目继续增加，ClickHouse查询语句并发度不断提高，集群偶尔出现资源过载的情况。扩大集群规模是最简单直接的方式，但其性价比较低。在资源有限的情况下，首先想到的方法是设置人群创建任务优先级，借助任务调度降低查询并发量来减轻集群压力，从而提高高优人群的产出速度。虽然这种方式降低了高峰期的集群压力，但是增大了所有人群的平均产出时间。其次考虑从优化SQL语句入手，在资源量固定的情况下提高SQL执行效率。下面将以实际案例介绍SQL语句的优化方式。

查询2022年1月1日到1月7日期间，开直播（live_or_not）天数超过3次且收礼数量（receive_gift_count）超过10个的北京市男性用户，其核心SQL语句如下所示。

(
SELECT
userId
FROM
userprofile_demo.user_label_table
WHERE
province = '北京市'
AND dt = '2022-01-07'
)
INNER JOIN (
SELECT
userId
FROM
userprofile_demo.user_label_table
WHERE
sex = '男'
AND dt = '2022-01-07'
)
INNER JOIN (
SELECT
userId
FROM
(
SELECT
userId,
count(live_or_not) AS totalCount
FROM
userprofile_demo.user_label_table
WHERE
   dt >= '2022-01-01'
AND dt <= '2022-01-07'
AND live_or_not = 1
GROUP BY
userId
HAVING
totalCount >= 3
)
)
INNER JOIN (
SELECT
userId
FROM
(
SELECT
userId,
sum(receive_gift_count) AS totalCount
FROM
userprofile_demo.user_label_table
WHERE
dt >= '2022-01-01'
AND dt <= '2022-01-07'
GROUP BY
userId
HAVING
totalCount >= 10
)
)

很容易看出，上述SQL语句存在一些优化点：北京市和男性两个条件可以合并成一条筛选语句，开播次数和收礼个数涉及的统计语句也可以合并到一起。最终结合标签类型以及筛选时间范围是否相同等因素对SQL语句进行了整合优化，优化后的语句如下所示，相比原始语句其执行时间缩短40%左右。

(
SELECT
userId
FROM
userprofile_demo.user_label_table
WHERE
province = '北京市'
AND sex = '男'
AND dt = '2022-01-07'
)
INNER JOIN (
SELECT
userId
FROM
(
SELECT
userId,
count(live_or_not) AS num1, sum(receive_gift_count) AS num2
FROM
userprofile_demo.user_label_table
WHERE
dt >= '2022-01-01'
AND dt <= '2022-01-07'
AND live_or_not = 1
GROUP BY
userId
HAVING
(
num1 >= 3
AND num2 >= 10
)
)
)

以上人群创建思路是从宽表中正向查询出满足条件的UserId，是否可以换个思路进行反向查询？比如先计算出所有性别标签值是男性的人群和省份标签值是北京市的人群，那么两个人群做交集就是北京市男性用户。ClickHouse支持基于BitMap的人群创建，可以将画像宽表中的数据转换成不同标签的BitMap数据，灌入ClickHouse中之后可以借助BitMap的交并差操作实现人群创建。BitMap并不适用于所有的标签类型，对于性别、年龄段等标签值可枚举的用户属性类标签，引入BitMap之后，亿级人群圈选时间由3分钟降低到20秒左右。图9-8展示了基于BitMap进行人群圈选的实现逻辑。

图片

本文节选自《用户画像：平台构建与业务实践》，转载请注明出处。

图片