基于AnalyticDB PostgreSQL + OSS + SLS构建面向应用内行为数据的分析全链路

作者：茏城

行业综述

在泛互联网行业中，企业投入了大量的精力用于追踪用户的行为日志，尝试更好的了解用户在应用内的核心行为。企业追踪了海量的日志，清洗后获得了核心用户的行为事件并于其他业务行为进行关联分析，如市场营销触达，订单支付，用户信息等。这些分析可以帮助企业建立用户行为与结果的倾向关联，对用户生命在应用内的行为进行市场，运营等手段的有效干预，在待转换，流失，沉寂等关键生命周期点进行介入。同时建立用户的分析模型和目标转换漏斗，进行整体应用的运营指导。

客户场景

AnalyticDB PostgreSQL助力某互联网企业完成数仓建设和行为日志的数据采集，入库，清洗，分析和洞察的全链路。将重点行为事件进行提取并于用户信息，订单信息，运营推广等维度和事实表进行关联分析，甄别关键链路并打造可视化大屏；于此同时，通过小批高频的入库能力，提供了近实时的业务追踪和运营分析手段。本次，引入Serverless版本的弹性能力和单点计算能力增强，对现有架构进行改造升级，大幅缩短了单次计算的效率，提高了的业务实时性并最终实现了10+的性价比增长。

架构设计

（脱敏后）

链路解析：

1. 对于日志的采集用户可以自建工具，同时也可以使用阿里云提供的SLS产品进行用户的站内行为事件的采集；具体可参考SLS的数据采集；
   
2. 将行为日志的采集数据落在OSS上进行存储；参考SLS的数据存储；
   
3. 通过定时任务将OSS的数据以外表的形式导入AnalyticDB PostgreSQL数据仓库中，可用自建任务调度系统或使用DMS进行任务触发；
   
4. 对导入的数据进行结构化的清洗，过滤掉无用的数据，并将核心的事件行为结构化后添加如表；
   
5. 提供企业内业务的数据分析师，对行为数据的目标进行关联分析； 例如

1. 分析整体的用户行为趋势，观测核心指标的健康度；
   
2. 行为数据+用户数据来建立不同的用户群组及行为特征；
   
3. 针对历史行为的在用户上，建模来预测是否用户存在潜在的流失风险；
   
4. 行为数据+订单数据来进行哪些行为关联用户行为及付费转换，可为之后的运营活动进行目标客群的定位；
   

6. 同时对于成熟的分析模型持久化，配合主流的BI工具如Tableau和QuickBI等，搭建数据看板，AnalyticDB PostgreSQL支持秒级的高频刷新，可实现近实时的数据分析。AnalyticDB的BI工具集成可点击查询：
   
7. 若选用Serverless实例，在工作时段和运营的高峰的时端存在着并发和较大的查询密集度。 此时AnalyticDB PostgreSQL支持在线扩容，在秒级即可完成产品的横向扩容，对交互式查询的业务影响极低。在业务闲时可将资源释放，确保极高的资源使用效率。
   

性能测试 

以下是我们在服务用户使用Serverless版本与OLAP海外头部友商在同等价位上进行POC的性能对比；

测试数据集

用户的性能测试涉及了3张核心表，以下所有的数据字段均脱敏，不指代任何业务意义；

表A（50+列，19亿行数据，压缩后88GB），date这一列是sortkey的第一列，客户会根据date做各种where条件筛选。

Column    |          Type          |           Modifiers           
--------------+------------------------+-------------------------------
 apple        | bigint                 | 
 banana       | character varying(32)  | 
 cherry       | character varying(64)  |
 durian       | integer                | 
 date         | integer                | 
Distributed by: (cherry)
Order by: (date, cherry, apple, durian, banana)

表B（30+列，7000W行数据，压缩后1.2GB）

Column    |          Type          |           Modifiers           
--------------+------------------------+-------------------------------
 apple        | bigint                 | 
 cherry       | character varying(64)  | default ''::character varying
 durian       | integer                | 
 date         | integer                | 
Distributed by: (cherry)
Order by: (date, cherry, apple, durian)

表C（20+列，5亿行数据，压缩后7.8GB）

Column        |         Type          |           Modifiers           
---------------------+-----------------------+-------------------------------
 apple               | bigint                | 
 durian              | integer               | 
 cherry              | character varying(64) | not null
 date                | integer               | 
Distributed by: (cherry)
Order by: (date, cherry, apple, durian)

测试业务SQL

客户本次测试选择包含272条业务常用SQL，对于数据的扫描范围范围为30天。

客户的分析SQL大多数是针对上述三个表中的两个或者三个做join和sum/group by的场景，然后通过join key和where条件做进一步筛选。

Sample Query如下：

select apple,
       durian,
       date,
       sum(col1)
from(
select A.apple, A.durain, A.date, max(NVL(NULLIF(A.colx, ''), '0') ::float) as col1
 from A, C
 where A.cherry= C.cherry
  and A.apple= C.apple
  and A.durain= C.durain
  and A.apple in (balabala)
  and A.date>= xxxx
  and A.date<= yyyy
  and A.durain> 0
  and A.banana= 'balabala'
  and A.colx in (balabala)
  and C.apple in (balabala)
  and C.durain> 0
 group by 1, 2, 3, 4) C
group by 1,2,3

其中，join key为分布列，所以SQL所做的join就是local join。where条件列大多数是sortkey中的列，所以可以做高效统计信息过滤筛选和二分查找优化。

性能结果

本次比对，用户对两款现有产品做了性能对比测试，一个是海外友商的A OLAP产品，一个是AnalyticDB PostgreSQL最新发布的serverless版本。两款产品均具有行业领先的扩缩容能力和头部厂商的性能领先性。 用户希望能够选择稳定且高性能的OLAP产品以支撑更高的数据实时性和一致性。

由于海外友商的计算资源通过CU封装，单位CU的参数未公开，故本次性能比较选择在同等价位下，为对272个业务SQL进行的总耗时对比，性能结果如下：

测试结果显示，AnalyticDB PostgreSQL Serverless版本在多表join的数据分析性能上，领先海外友商A约40%。

总结

面对海量的行为数据，以AnalyticDB PostgreSQL 为中心的数据解决方案提供了国际领先的数据分析性能。同时可覆盖数据采集，整合，分析，洞见，配合深度集成的阿里云产品生态，可以快速助力企业完成从0到1的业务部署和落地，一站式完成场景化的深度分析；

新用户可点击购买9.9首月试用；

需要进行类似部署或申请AnalyticDB PostgreSQL Serverless版本试用，欢迎点击咨询；