找不到目标用户？云原生数仓AnalyticDB MySQL秒级圈人功能大揭秘-阿里云开发者社区

【先打一波小广告】阿里云AnalyticDB MySQL升级为湖仓一体架构，支持高吞吐离线处理和高性能在线分析，可无缝替换CDH/TDH/Databricks/Presto/Spark/Hive等。

免费试用活动（5000ACU时+100GB存储）正在火热进行中，欢迎体验！免费试用链接：https://free.aliyun.com/?searchKey=AnalyticDB%20MySQL，群号：33600023146

业务挑战

营销域中的洞察分析/智能圈人/经营报表等场景是OLAP分析型数据库的重要应用场景，阿里云瑶池旗下的云原生数据仓库AnalyticDB MySQL在淘宝、饿了么、菜鸟、优酷、盒马等业务的营销场景有比较长时间的积累和沉淀，我们将通过一系列文章来介绍AnalyticDB MySQL在营销域数据产品中的落地与应用，之前文章介绍了“漏斗分析”的实现与应用，本文主要介绍“秒级圈人&画像分析”的实现与应用。

技术挑战

在营销域的“搜，广，推”业务中，对用户的运营是一个“Interest Targeting ”循环迭代的过程：对全量用户画像分析-->挖掘目标用户-->AB测试:触达目标用户-->触达效果画像分析-->挖掘目标用户。这个过程抽象出来的技术需求是：人群圈选&画像分析，技术实现的架构大致如下：

这套架构有两个问题：

成本高：用户标签数据生产好之后需要在ETL数仓和OLAP引擎存两份，这就意味着存储成本，同步作业成本和运维成本都很高，系统整体稳定性也存在潜在风险。
用户体验不好：例如现在运营同学希望对生成的人群包进行画像分析，那么他/她只能等ETL引擎将人群包计算好了并且同步回OLAP引擎之后才可以，而数据同步和作业调度往往是分钟/小时级的，运营同学不能立刻进行分析使用。

问题分析

问题的本质原因是因为圈人的计算量太大，出于稳定性和成本的妥协，传统的解决方案中圈人只能放在ETL引擎中处理。

假设所有的用户标签(N个)都在一张大宽表里，表结构大致如下：

tbl_user_tag

user_id	tag1	tag2	tag3	...	tagN
100001	x	a	3	...	...
100002	y	b	6	...	...

现在我们要圈选出满足tag1='x'并且tag2='a'的用户，分析这些用户在tag3维度的画像

圈人SQL实现：

insert overwrite into package_table_1
select user_id from tbl_user_tag where tag1='x' and tag2='a'

画像分析SQL实现

select tag3, count(distinct user_id) from 
(
select user_id from tbl_user_tag where tag1='x' and tag2='a'
)
group by tag3

这两个SQL没有问题，大部分OLAP引擎都可以完成得很好，然而，实际业务实践中我们不可能把所有的标签都放在一张表里，这里面有技术和业务维护两方面的原因：

技术方面：如果一张表里的字段很多，写时内存hold的block多，内存占用更多，理论上写延迟会更高；标签表一般默认全索引(给所有标签字段建立索引是合理的需求)的话build/load的cpu消耗也会更高。查询时元数据内存占用也会更多。
业务方面：标签表的上游业务数据来自不同的业务方，我们不可能在一个作业中完成所有标签的计算生产。

因此需要拆表，假设tag1和tag2在两张不同的标签表里：

tbl_user_tag_1

user_id	tag1	tag3	ta5	...	tagN
100001	x	2	3	...	...
100002	y	4	6	...	...

tbl_user_tag_2

user_id	tag2	tag4	tag6	...	tagM
100001	a	2	3	...	...
100002	b	4	6	...	...

圈人SQL实现：

select user_id from tbl_user_tag_1 where tag1='x'
union
select user_id from tbl_user_tag_2 where tag2='a'
或者
select user_id from tbl_user_tag_1 
  inner join tbl_user_tag_2 
  on tbl_user_tag_1.user_id = tbl_user_tag_2.user_id
  where tag1='x' and tag2='a'

这里列举了2种写法，只是写法不同，对计算引擎而言，问题都是引入了“大表JOIN”的问题，参与Join的数据规模和用户ID空间成正比，需要进行的Join计算次数和SQL条件复杂程度：需要使用到的标签表的个数成正比(此处只举了2个标签表的情况，实际中可能是10几个甚至几十个标签表进行Join)。“大表JOIN”对计算引擎的CPU资源消耗较高，执行时间也较长，因此传统解决方案中只能放在相对低成本的ETL引擎中处理。

AnalyticDB MySQL优化方法

针对上述业务痛点问题，AnalyticDB MySQL引入RoaringBitmap及相关计算函数来将圈人场景中“大表Join”问题转化成“小表Join”问题，并且在oss外表中原生支持Roaringbitmap类型，来解决多份标签数据和人群包导出的问题。

我们准备一份测试数据，来介绍一下各个场景的具体做法，数据处理整体流程大致如下：

步骤一实时构建bitmap标签表

准备一个测试用的原始标签表users_base，假设所有的用户标签都放在users_base表中，创建测试用的原始标签数据表

create table users_base(
   uid int,
   tag1 string, // tag1 取值范围是： x, y, z
   tag2 string, // tag2 取值范围是： a, b
   tag3 int // tag3 取值范围是：1-10
)

生成1亿行随机测试数据

// 生成1亿行随机数据
submit job
insert overwrite users_base
select cast(row_number() over (order by c1) as int) as uid,  substring('xyz', floor(rand() * 3) + 1, 1) as tag1, substring('ab', floor(rand() * 2) + 1, 1) as tag2, cast(floor(rand() * 10) + 1 as int) as tag3 from  
(
select A.c1 from
unnest(rb_build_range(0, 10000)) as A(c1)
  join
  (select c1 from
unnest(rb_build_range(0, 10000)) as B(c1)
))
mysql> select * from users_base limit 10;
+--------+------+------+------+
| uid    | tag1 | tag2 | tag3 |
+--------+------+------+------+
|  74526 | y    | b    |    3 |
|  75611 | z    | b    |   10 |
|  80850 | x    | b    |    5 |
|  81656 | z    | b    |    7 |
| 163845 | x    | b    |    2 |
| 167007 | y    | b    |    4 |
| 170541 | y    | b    |    9 |
| 213108 | x    | a    |   10 |
|  66056 | y    | b    |    4 |
|  67761 | z    | a    |    2 |
+--------+------+------+------+

构建bitmap标签表, 在设计标签表的时候，考虑到利用分布式计算引擎的并发能力，我们引入一个分组字段(本例中使用user_group)，将uid分组并行计算，分组的大小可以根据集群CU总数和实际业务情况进行取舍:

一般分组越多计算能力越强，但是分组过多又会导致每个bitmap包含的元素太少，不能充分利用bitmap的计算特性。
实践过程中建议保证每个分组的bitmap空间 < 1亿, 例如，原始的uid空间是100亿，可以使用100个分组，每个分组1亿。

本例子中使用16个分组， uid%16来做分组字段，offset=uid/16来记录分组后的偏移，uid = 16 * offset + user_goup , 后续使用offset来计算bitmap ，这种分组方式只是一个例子，实际业务可以根据业务需要设计分组函数

// 创建添加分组字段后的用户原始表
create table users(
   uid int,
   tag1 string,
   tag2  string,
   tag3 int,
   user_group int, // 分组字段
   offset  int //偏移
)
// 将users_base的数据写入users
submit job
insert overwrite users select uid, tag1, tag2, tag3, cast(uid%16 as int), cast(floor(uid/16) as int) from users_base
// 检查写入后的数据
mysql> select * from users limit 10;
+---------+------+------+------+------------+--------+
| uid     | tag1 | tag2 | tag3 | user_group | offset |
+---------+------+------+------+------------+--------+
|  377194 | z    | b    |   10 |         10 |  23574 |
|  309440 | x    | a    |    1 |          0 |  19340 |
|  601745 | z    | a    |    7 |          1 |  37609 |
|  753751 | z    | b    |    3 |          7 |  47109 |
|  988186 | y    | a    |   10 |         10 |  61761 |
|  883822 | x    | a    |    9 |         14 |  55238 |
|  325065 | x    | b    |    6 |          9 |  20316 |
| 1042875 | z    | a    |   10 |         11 |  65179 |
|  928606 | y    | b    |    5 |         14 |  58037 |
|  990858 | z    | a    |    8 |         10 |  61928 |
+---------+------+------+------+------------+--------+

构建roaringbitmap标签表

// 创建tag1的标签表
create Table `tag_tbl_1` (
  `tag1` string,
  `rb` roaringbitmap,
  `user_group` int) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"******",
"accesskey":"*************",
"url":"oss://your oss bucket/roaringbitmap/tag_tbl_1/",
"format":"parquet"
}'
// 写入tag1标签表
insert overwrite tag_tbl_1 select tag1, rb_build_agg(offset), user_group from users group by tag1, user_group
// 检查写入tag1标签表的数据, 可以看到和原始标签users表是对应的
mysql>  select tag1, user_group, rb_cardinality(rb) from tag_tbl_1;
+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| z    |          7 |            2082608 |
| x    |         10 |            2082953 |
| y    |          7 |            2084730 |
| x    |         14 |            2084856 |
.....
| z    |         15 |            2084535 |
| z    |          5 |            2083204 |
| x    |         11 |            2085239 |
| z    |          1 |            2084879 |
+------+------------+--------------------+
48 rows in set (0.66 sec)
// 创建tag2的标签表
create Table `tag_tbl_2` (
  `tag2` string,
  `rb` roaringbitmap,
  `user_group` int) 
engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"******",
"accesskey":"*************",
"url":"oss://your oss bucket/roaringbitmap/tag_tbl_2/",
"format":"parquet"
}'
// 写入tag2标签表
insert overwrite tag_tbl_2 select tag2, rb_build_agg(offset), user_group from users group by tag2, user_group
// 检查tag2标签表写入的数据
select tag2, user_group, rb_cardinality(rb) from tag_tbl_2;
mysql> select tag2, user_group, rb_cardinality(rb) from tag_tbl_2;
+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| b    |         11 |            3121361 |
| a    |          6 |            3124750 |
| a    |          1 |            3125433 |
....
| b    |          2 |            3126523 |
| b    |         12 |            3123452 |
| a    |          4 |            3126111 |
| a    |         13 |            3123316 |
| a    |          2 |            3123477 |
+------+------------+--------------------+
32 rows in set (0.44 sec)

步骤二使用bitmap标签表计算分析

场景1 圈人分析

过滤筛选分析：分析tag1 in (x, y) 的用户，在tag2维度的柱状图

// 为了方便理解，先求满足条件的用户是哪儿些
select tag2, t1.user_group as user_group, rb_cardinality(rb_and(t2.rb, rb1)) as rb from tag_tbl_2 as t2
join (
select user_group, rb as rb1 from tag_tbl_1 where tag1 in ('x', 'y'))
as t1
on t1.user_group = t2.user_group
// 求满足条件柱状图
select tag2, sum(cnt) from ( 
select tag2, t1.user_group as user_group, rb_cardinality(rb_and(t2.rb, rb1)) as cnt from tag_tbl_2 as t2
join (
select user_group, rb as rb1 from tag_tbl_1 where tag1 in ('x', 'y'))
as t1
on t1.user_group = t2.user_group
) group by tag2
+------+----------+
| tag2 | sum(cnt) |
+------+----------+
| a    | 33327868 |
| b    | 33335220 |
+------+----------+
2 rows in set (0.67 sec)

这个场景使用传统解法的等效实现如下(注：这里模拟真实场景中“大表JOIN”使用子查询的方式查了同一张表的数据)：

select
  tag2,
  count(distinct A.uid)
from
  (
    select
      uid
    from
      users
    where
      tag1 in ('x', 'y')
  ) as A
  inner join (
    select
      uid,
      tag2
    from
      users
  ) as B on A.uid = B.uid
group by
  tag2;
+------+-----------------------+
| tag2 | count(distinct A.uid) |
+------+-----------------------+
| b    |              33335220 |
| a    |              33327868 |
+------+-----------------------+
2 rows in set (2.79 sec)

从执行耗时可以看到，使用bitmap的方案性能是使用传统方案的4倍以上，而roaringbitmap的方案中我们使用的存储是oss是成本更低的方案。

场景2 交并差计算

bitmap表交并差：求同时满足条件 tag1 = 'x' or tag2 = 'y' 和 tag2 = ‘b’的用户

select user_group , rb_cardinality(rb) from (
    select
      t1.user_group as user_group,
      rb_and(rb1, rb2) as rb
    from
      (
        select
          user_group,
          rb_or_agg(rb) as rb1
        from
          tag_tbl_1
        where
          tag1 = 'x'
          or tag1 = 'y'
        group by
          user_group
      ) as t1
      join (
        select
          user_group,
          rb_or_agg(rb) as rb2
        from
          tag_tbl_2
        where
          tag2 = 'b'
        group by
          user_group
      ) as t2 on t1.user_group = t2.user_group
  group by user_group)
+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|         10 |            2083679 |
|          3 |            2082370 |
|          9 |            2082847 |
|          2 |            2086511 |
...
|          1 |            2082291 |
|          4 |            2083290 |
|         14 |            2083581 |
|         15 |            2084110 |
+------------+--------------------+
16 rows in set (0.71 sec)

场景3 交叉计算

原始标签表和bitmap标签表交叉计算，求同时满足条件 tag1 = 'x' or tag2 = 'y' 和 tag2 = ‘b’的用户，但是tag2 = ‘b‘ 这个条件的数据从users表中读取，进行交叉计算

select user_group , rb_cardinality(rb) from (
    select
      t1.user_group as user_group,
      rb_and(rb1, rb2) as rb
    from
      (
        select
          user_group,
          rb_or_agg(rb) as rb1
        from
          tag_tbl_1
        where
          tag1 = 'x'
          or tag1 = 'y'
        group by
          user_group
      ) as t1
      join (
        select
          user_group,
          rb_build_agg(offset) as rb2
        from
          users
        where
          tag2 = 'b'
        group by
          user_group
      ) as t2 on t1.user_group = t2.user_group
  group by user_group)
+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|          3 |            2082370 |
|          1 |            2082291 |
|          0 |            2082383 |
|          4 |            2083290 |
|         11 |            2081662 |
|         13 |            2085280 |
...
|         14 |            2083581 |
|         15 |            2084110 |
|          9 |            2082847 |
|          8 |            2084860 |
|          5 |            2083056 |
|          7 |            2083275 |
+------------+--------------------+
16 rows in set (0.95 sec)

场景4 圈人

将满足场景2的roaringbitmap导出到oss提供给其他系统使用

// 创建计算结果的标签表
create Table `tag_tbl_3` (
  `rb` roaringbitmap) 
engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"******",
"accesskey":"*************",
"url":"oss://your oss bucket/roaringbitmap/tag_tbl_3/",
"format":"parquet"
}'
// 将计算结果导出到结果标签表
insert overwrite tag_tbl_3
    select
        rb_and(rb1, rb2) as rb,
      t1.user_group as user_group
    from
      (
        select
          user_group,
          rb_or_agg(rb) as rb1
        from
          tag_tbl_1
        where
          tag1 = 'x'
          or tag1 = 'y'
        group by
          user_group
      ) as t1
      join (
        select
          user_group,
          rb_or_agg(rb) as rb2
        from
          tag_tbl_2
        where
          tag2 = 'b'
        group by
          user_group
      ) as t2 on t1.user_group = t2.user_group
// 执行结束之后文件会以parquet格式存放在 oss://your oss bucket/roaringbitmap/tag_tbl_3

场景5 加速查询

将 tag1的标签表的数据导入内表加速查询效果

// 创建一个内表，需要将rb字段定义为varbinary格式
Create Table `tag_tbl_1_cstore` (
 `tag1` varchar,
 `rb` varbinary,
 `user_group` int
)
// 将tag1标签表的数据从oss导入到tag_tbl_1_cstore
insert into tag_tbl_1_cstore select tag1, rb_to_varbinary(rb), user_group from tag_tbl_1
// 检查导入的数据
mysql> select tag1, user_group, rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) from tag_tbl_1_cstore group by tag1, user_group;
+------+------------+---------------------------------------------------+
| tag1 | user_group | rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) |
+------+------------+---------------------------------------------------+
| y    |          3 |                                           2082919 |
| x    |          9 |                                           2083085 |
| x    |          3 |                                           2082140 |
| y    |         11 |                                           2082268 |
| z    |          4 |                                           2082451 |
...
| z    |          2 |                                           2081560 |
| y    |          6 |                                           2082194 |
| z    |          7 |                                           2082608 |
+------+------------+---------------------------------------------------+
48 rows in set (0.16 sec)

总结

本文介绍了AnalyticDB MySQL在圈人场景的实践，AnalyticDB MySQL引入RoaringBitmap及相关计算函数来将圈人场景中“大表Join”问题转化成“小表Join”问题，减少了计算量，并且在oss外表中原生支持Roaringbitmap类型，来解决多份标签数据和人群包导出的问题。Roaringbitmap的具体使用方式可以参考官方文档：https://help.aliyun.com/document_detail/600552.html?spm=a2c4g.600977.0.i2

找不到目标用户？云原生数仓AnalyticDB MySQL秒级圈人功能大揭秘

业务挑战

技术挑战

问题分析

AnalyticDB MySQL优化方法

步骤一实时构建bitmap标签表

步骤二使用bitmap标签表计算分析

场景1 圈人分析

场景2 交并差计算

场景3 交叉计算

场景4 圈人

场景5 加速查询

总结

数据仓库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像

找不到目标用户？云原生数仓AnalyticDB MySQL秒级圈人功能大揭秘

业务挑战

技术挑战

问题分析

AnalyticDB MySQL优化方法

步骤一 实时构建bitmap标签表

步骤二 使用bitmap标签表计算分析

场景1 圈人分析

场景2 交并差计算

场景3 交叉计算

场景4 圈人

场景5 加速查询

总结

数据仓库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像

步骤一实时构建bitmap标签表

步骤二使用bitmap标签表计算分析