海量数据下的OLAP实践_蚂蚁集团

海量数据下的OLAP引擎实战 余志鹏-蚂蚁集团-⾼级技术专家 DataFunSummit#2023 ⾃我介绍 余志鹏（花名百恼），蚂蚁⼗年⽼兵 2013~2018年，阿⾥速卖通（Aliexpress），主要负责营销平台、卡券平台、积分平台的建设 2018~Now，蚂蚁⼤数据DeepInsight性能负责⼈ ⽬录CONTENT 01为什么需要通过ADM来解决OLAP场景下⼤表性能问题 02通过Cube解决OLAP场景下报表性能问题 03通过采样解决OLAP场景下分析性能问题 04我们的团队 01 通过ADM解决OLAP场景下的报表性能问题 DataFunSummit#2023 为了⽅便理解后续的内容，数据分析中的⼀些基本概念需要提前理解 OLTPOLAP 数据量 View ADM：ApplicationDataModel ⾯向具体业务应⽤的数据集合 ⼩于1亿 Controller DWS：DataWarehouseSummary 轻度汇总层：⽐如按照user_id进⾏汇总 ⼤于10亿 Model DWD：DataWarehouseDetail 明细层：⽐如交易流⽔ ⼤于100亿 LOG … filen file1 DB Tablen … Table2 Table1 ODS：OperationDataSource 这⼀层基直接把在线的数据库同步到离线数仓 ⼤于100亿 蚂蚁场景下的数据 数据驱动决策链路中，制作报表和分析的效率就决定了数据驱动决策效率 经营决策 把分析和看板中得到的洞⻅进⾏经营决策，提⾼决策效率和质量 通过业务看板中的数据变化，指导经营决策把分析得到的洞⻅运⽤在经营决策中 ①DAU/MAU/AAU ①各城市 ②各企业 ③趋势增⻓ ②还款率 ③… 业务看数数据分析 当业务数据出现问题时需要找到问题的原因 建⽴分析的假设树，沿着分析思路不断分析，找到洞⻅ 第⼀步：制作业务看板，核⼼耗时在研发ADM 报表制作流程 提出数据分析需求 业务BI⼈员 选择数据 数据集 选择图表拖拽式分析 草稿报表 报表发布 正式报表 运营&PD 反复确认⼝径 简化数据需求 查看结果 过滤筛选 ADM ETL数据研发⼈员 DWD 制作⼀张报表平均耗时1.5天，其中ADM研发耗时1天 （如果⼀年⽣产⼀万张报表，则需要1万⼈⽇） 当业务出现下跌，需要找到原因是什么? 是否某产品余额下降? 是否受新/⽼客的影响? 1 各产品余额均等幅下降 3 新客的余额⽉环⽐下降明显 2 ⽼客余额⽉环⽐略有上升 4 *客群新客余额下降 10 新客户数量⽉环⽐明显下降 是否受其他因素影响? 5 是否仍受不同地域影响? 运营反馈近期拉新策略调整 涉农/监管等⽆明显影响 6*地域*客群余额⽉环⽐下降明显 流量 *地域*客群还款总额环⽐持平 7 *地域*客群贷款总额环⽐下降 11 活动权益⽤户量同⽐减少 权益曝光量少？ 9 *客群的授信额度下调 12 13 活动权益曝光量环⽐减少 活动权益点击量环⽐减少 *地区*客群新客户贷款政策收紧 结论1 结论2 拉新活动权益投放减少 其他原因? 是否授信额度少了? 是否仍受不同客群影响? ⽤户 未探索 *地域*客群贷款少了? *地域*客群还款多了？ 权益点击量少？ 什么类型的流量减少？ 是否受新客数量影响? 产品 *⽉份“贷款余额”下降，为什么? 建⽴假设树，不断在各个空间上进⾏探索，找到贡献度最⼤的原因 8 业务理解 【5⼩时】 实现前⾯假设树分析的流程：写SQL，等运⾏，查质量 主流程 *⽉份“贷款余额”下降，为什么? 【时间消耗】 定义问题 步骤分解 分析论证 检查运⾏结果质量，数据不对则重新跑 【40⼩时，占整个过程时间消耗的80%】 规范的资产模型可减少这 ⼀步的开销 定义是个度量，依次分析 报告产出 【5⼩时】 写报告 【2⼩时】 找到对应的数据 写SQL 质量检查 【单次数⼗分钟】 设计分析思路 运⾏脚本 洞⻅获得 未找到洞⻅，则重新设计分析思路，⼀般需要循环⼏次 设计了3个分析思路 进⾏尝试⼀下 等待ODPS返回 ⼀般都是周级时间消耗 理解业务闭环 做报表 【2⼩时】 问题洞⻅ 1.80%的探索时间都是在做度量的清洗，持续4天，产出⼤量的临时ADM脚本（⼀次性）或者ADM数据（固化后每天都跑） 2.运营不会通过SQL做度量清洗，不会做数据探索，⽽BI资源有限，所以解决⽅案是技术团队的同学做：数据探索（分析+ETL） 探索式分析流程：写SQL，等运⾏，查质量 SQL脚本 背后的缺点 技术和业务沟通成本⾼ 缺点说明 当技术同学不了解业务需求的时候，要能够定义出适合data-exploration的度量是不容易的， 可以拿上⾯的SQL脚本举例，于是产⽣⼤量沟通成本，如果 是本业务线的技术还好 度量定义 【了解业务的情况下，分钟级】 写SQL代码 【单次1⼩时，时间占⽐15%】 等SQL运⾏结果 【得到正确结果5⼩时（单个SQL耗时1-2⼩时，跑2-3次），时间占⽐80%】 10个探索式度量，产⽣4个ADM 6个临时任务 技术的同学每次都要写SQL，产⽣ ⼤量的临时表，还有ADM，有4个是data-exploration产出的，⼤⼤ 增加了资产管理的复杂度 出错概率⼤，纠错代价⾼。每⼀步都需要去check⾃⼰算的数据是否正确，写SQL稍 微不注意就出错。错了过后，重新跑需要消耗时间太⻓。 在ODPS上的等待时间还是⽐较⻓的，基本都要⼏⼗分钟乃⾄数⼩时 在新引擎上的等待时间还未可知，由于data-exploration有很⼤的随机性，所以提前做物化收效⼀般 ⼀般都是多种技术⼿段配合，⽽且需要智能调优，才能⾃动的根据场景来进⾏性能优 化 时间消耗细分 除了效率外，随着需求的增⻓，ADM快速增⻓，治理成本⾼，计存成本⾼ 2020年研发报 表xx万个 2019年研发 报表xx万个 2018年研发报 表xx万个 数据需求不但增加，报表数量和ADM的数量持续增⻓ 报表和ADM的数量 •2019年ADM数量从xxx万增加到xxx万,增⻓xx%；报表数量从xx万增加到xx万，增⻓xx% •2020年ADM数量增加到xx万,增⻓xx%，两年增⻓xx%;报表数量从xx万增加到xx万，增⻓xx%，两年增⻓xx% 数据研发⼈数中，技术占⽐xx%以上，数据需求吞吐量不⾜，供给侧靠⼤量的技术同学补位 数仓研发员⼯数量 •2019年研发⼈数从xx⼈增加到xx⼈,增⻓xx% •2020年研发⼈数增加到xx⼈,增⻓xx%，两年增⻓ xx% 存储量两年增⻓xx%，成本不断增加 ADM的存储量 •2019年ADM存储从xxPB增加到xxPB,增⻓xx% •2020年ADM存储增加到xxPB,增⻓xx%，两年增⻓ xx% 那么，为什么需要ADM来解决这些问题呢？ 原因分析：①⼤表的分析性能问题；②解决分析中复杂度量定义的问题 传统ETL过程 ETL解决上⼀代数据分析平台的两个问题： 1.直接基于DWD分析，带来性能问题，可以通过ETL提前做ADM 2.直接基于DWD分析，分析SQL太复杂，可以通过ETL消化复杂SQL ODS 1.ETL DWD （维度模型） 1.ETL 1.ETL DWSADM报表 ETL+DWS+ADM的⽅案 ER模型转维度模型 解决ER模型分析复杂度⾼的问题 公司内部场景 ETL+DWS+ADM的⽅案带来三个问题： 1.加⼯层次多，链路⻓，ETL同学紧缺，完⼯时间周期⻓ 2.规范不易落地，复⽤问题解决的不好，导致重复加⼯，浪费各种资源 3.数据多了，要找起来都麻烦了，链路多了，找到也不敢⽤了 然后我们开始设计⽅案，再把这三个问题给解决了？于是我们做了资产平台 我们做了资产治理我们做了其他等等 我们可以回到原点思考 在数据驱动决策的过程上，制作报表和分析的效率会决定了公司最终的决策效率。 接下来主要讲如何通过⼤表性能优化来解决ADM带来的性能问题 报表 探索式分析 度量和维度确定， ⽤户在⼀个固定的空间内看数 度量和维度不确定， ⽤户探索的空间⾮常⼤ 通过「Cube预聚合」减少查询时数据量，本质上是⾃动化ADM 通过「采样的⽅式」减少即时分析时的数据量 02 通过Cube 解决报表的性能问题 DataFunSummit#2023 对应的问题 我们举⼀个实际的例⼦来说明Cube是如何解决性能问题 报表配置信息 对应的SQL查询 展示不全 固定筛选：2个 步⻓：最近7天 度量：6个 固定维度：4个 动态维度：1个 cbcn_jk_seller_trd：单分区1亿 SELECTseller_name,seller_main_category,short_code,SUM(gmt_usd),SUM(ipv),SUM(orders) FROMcbcn_jk_seller_trd 可变 ANDindustry_name='xxx' ANDseller_name='xxx' 可变 GROUPBY 可变 seller_name,serller_main_category,short_code ANDbusiness_type=‘all’ WHEREfulfillment_crete_date>='2023-05-24' ANDfulfillment_crete_date<='2023-05-30' 查询数据量⼤，性能慢 Cube的构建SQL 基于Cube的查询 CREATETABLEcube_xxxxas SELECT'[biz_date]'asdt,seller_name,seller_main_category,short_code,business_typeSUM(gmt_usd)sum_gmt_usd,SUM(ipv),SUM(orders) FROMcbcn_jk_seller_trd ANDindustry_name='xxx'ANDseller_name='xxx' GROUPBYseller_name,serller_main_category,short_code,business_type WHEREfulfillment_crete_date>='[biz_date]-7'ANDfulfillment_crete_date<='[biz_date]' SELECT seller_name,seller_main_category,short_code,business_typesum_gmt_usd,sum_ipv,sum_orders FROMcube_xxxx wheredt='2023-05-30'andbusiness_type='all' 查询数据量从10亿级别到百万级别，性能提升⾮常明显 Cube的系统执⾏流程 寻找和查询匹配的Cube selecttime,product,location,sum(gmv)fromTablegroupby selecttime,product,location,gmv’fromTable’ 报表查询查询匹配查询改写 CubeData 函数⽀持的丰富度影响改写成功率它占⽐很⼩，不是主要⽭盾 覆盖率越⾼ 被cube匹配上的查询越多 命中 CubeData 加速 查询能被cube匹配的前提是先精准定义出cube OB/Explorer ODPS ⽴⽅体数据 建⽴模型cube定义cube优化cube构建 星型模型 or 雪花模型 1 2 3 …4 优先级任务队列 构建引擎 层级维度 必要维度 联合维度 聚合组 全量构建 增量构建 Spark 构建 MR 构建 维度(Dimension)、度量(Measure) MAX、MIN、SUM、COUNT、COUNT_D 把Cube转化为数学问题，Cube的核⼼指标 y1慢查询场景Cube覆盖率= n ∑ i=1 应该