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

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 营销域中的洞察分析/智能圈人/经营报表等场景是OLAP分析型数据库的重要应用场景,阿里云瑶池旗下的云原生数据仓库AnalyticDB MySQL在淘宝、饿了么、菜鸟、优酷、盒马等业务的营销场景有比较长时间的积累和沉淀,我们将通过一系列文章来介绍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引擎都可以完成得很好,然而,实际业务实践中我们不可能把所有的标签都放在一张表里,这里面有技术和业务维护两方面的原因:

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


因此需要拆表,假设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总数和实际业务情况进行取舍:

  1. 一般分组越多计算能力越强,但是分组过多又会导致每个bitmap包含的元素太少,不能充分利用bitmap的计算特性。
  2. 实践过程中建议保证每个分组的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,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
3月前
|
数据采集 运维 Cloud Native
Flink+Paimon在阿里云大数据云原生运维数仓的实践
构建实时云原生运维数仓以提升大数据集群的运维能力,采用 Flink+Paimon 方案,解决资源审计、拓扑及趋势分析需求。
18476 54
Flink+Paimon在阿里云大数据云原生运维数仓的实践
|
2月前
|
SQL 关系型数据库 MySQL
实时数仓 Hologres操作报错合集之Flink CTAS Source(Mysql) 表字段从可空改为非空的原因是什么
实时数仓Hologres是阿里云推出的一款高性能、实时分析的数据库服务,专为大数据分析和复杂查询场景设计。使用Hologres,企业能够打破传统数据仓库的延迟瓶颈,实现数据到决策的无缝衔接,加速业务创新和响应速度。以下是Hologres产品的一些典型使用场景合集。
|
2月前
|
SQL 存储 OLAP
OneSQL OLAP实践问题之Flink SQL Gateway的功能如何解决
OneSQL OLAP实践问题之Flink SQL Gateway的功能如何解决
31 1
|
2月前
|
SQL 消息中间件 OLAP
OneSQL OLAP实践问题之实时数仓中数据的分层如何解决
OneSQL OLAP实践问题之实时数仓中数据的分层如何解决
47 1
|
2月前
|
存储 SQL 人工智能
AnalyticDB for MySQL:AI时代实时数据分析的最佳选择
阿里云云原生数据仓库AnalyticDB MySQL(ADB-M)与被OpenAI收购的实时分析数据库Rockset对比,两者在架构设计上有诸多相似点,例如存算分离、实时写入等,但ADB-M在多个方面展现出了更为成熟和先进的特性。ADB-M支持更丰富的弹性能力、强一致实时数据读写、全面的索引类型、高吞吐写入、完备的DML和Online DDL操作、智能的数据生命周期管理。在向量检索与分析上,ADB-M提供更高检索精度。ADB-M设计原理包括分布式表、基于Raft协议的同步层、支持DML和DDL的引擎层、高性能低成本的持久化层,这些共同确保了ADB-M在AI时代作为实时数据仓库的高性能与高性价比
|
2月前
|
存储 运维 Cloud Native
"Flink+Paimon:阿里云大数据云原生运维数仓的创新实践,引领实时数据处理新纪元"
【8月更文挑战第2天】Flink+Paimon在阿里云大数据云原生运维数仓的实践
241 3
|
4月前
|
Cloud Native 数据管理 OLAP
云原生数据仓库AnalyticDB产品使用合集之是否可以创建表而不使用分区
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
400 2
云原生数据仓库AnalyticDB产品使用合集之是否可以创建表而不使用分区
|
3月前
|
分布式计算 DataWorks 关系型数据库
阿里云数加-分析型数据库AnalyticDB数据导入的多样化策略
通过合理利用这些数据导入方法,用户可以充分发挥AnalyticDB的实时计算能力和高并发查询性能,为业务分析和决策提供强有力的数据支持。
|
4月前
|
Cloud Native 关系型数据库 MySQL
《阿里云产品四月刊》—云原生数据仓库 AnalyticDB MySQL 版 新功能
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代

热门文章

最新文章

相关产品

  • 云原生数据仓库AnalyticDB MySQL版
  • 云原生数据仓库 AnalyticDB PostgreSQL版
  • 推荐镜像

    更多