ToB企服应用市场:ToB评测及商务社交产业平台

标题: 阿里CCO：基于 Hologres 的亿级明细 BI 探索分析实践 [打印本页]
作者: 宁睿    时间: 2024-6-9 13:33
标题: 阿里CCO：基于 Hologres 的亿级明细 BI 探索分析实践
CCO是Chief Customer Officer的缩写，也是阿里巴巴团体客户体验奇迹部的简称。随着业务的多元化发展以及行业竞争的深入，用户体验题目越来越受到关注。CCO体验业务运营小二一样平常会大量投入在体验洞察分析中，旨在通过用户的声音数据联合生意业务、物流、退款等业务数据，洞察发现消耗者/商家体验链路上的卡点题目并推进优化，带给消耗者和商家更好服务体验。
以今年3月为例，通过统计日志数据发现，共有80+业务同砚提交了22000+个Query，都是围绕着用户心声和业务数据的多维度交错分析，假如按照每个Query小二平均投入10分钟进行编写、执行、检查等操作来盘算的话，共计投入458工作日，也就意味着这80+业务同砚人均每个月至少有1周的时间全部投入在数据处置惩罚、运行上。业务侧大量的洞察分析诉求也使得体验洞察的数字化和智能化能力建设势在必行，我们需要有能支持到业务复杂场景Ad-hoc查询的数据能力和产物能力。
通过对数据产物的不断迭代，我们采用Hologres+FBI支撑CCO体验小二全部数据探索需求，月均50亿+明细数据聚合查询秒级返回，支持100+业务小二大促、一样平常的体验运营洞察分析，助力业务小二单次洞察分析提效10倍以上，解放业务同砚的生产力。在文本中，我们也将会介绍CCO数据洞察产物基于Hologres在BI查询场景的最佳实践。
体验洞察各阶段方案演变

联合业务，我们梳理了当前CCO体验洞察数据应用的几个特点：

因此在整体数据方案落地的过程中，如何快速相应业务不断变化的需求，同时考虑业务上的数据特点，选择相对稳固且高可用的方案是我们需要面临的题目。这里重要经历了三个阶段。
阶段一：预盘算聚合Cube（MOLAP）+ADB加快查询

这个阶段还未支持实时的洞察能力，采用的方式是比较通例的预盘算聚合Cube结果集，即在MaxCompute侧将所需要的交错维度指标预盘算好，形成一个ADS层的聚合指标结果宽表，通过外表或者DataX工具将聚合结果写入到OLAP引擎加快查询。此阶段CCO较为主流的OLAP引擎选型重要是ADB、MySQL等。这种方案在应对较少且相对稳固的维度和指标组适时较为实用，由于结果已经预盘算好，只需要针对结果表进行简单聚合盘算，ADB也提供了稳固的查询加快能力。以下为整体数据链路结构的简单表现图：

但是随着业务场景的更加复杂化，存在的题目也极为明显：

阶段二：实体ID轻度汇总事实表+维度表关联查询

这个阶段实时化洞察已经在很多场景有较强的诉求，故需要同时联合实时链路来考虑方案。方案一不适合实时链路的建设，重要在于预盘算的多维汇总宽表难以确定PK，一旦维度组合发生变化，PK需要重新界说，无法稳固的支持upsert/update操作。
所以在这个阶段重要针对扩展性机动性等题目重新设计了方案。重要的思路是不做维度的预盘算，而是抽取洞察场景内事实表的实体对象ID，构建基于这些实体对象ID的轻度汇总DWS指标层，然后将DWS指标事实表和实体对象的DIM表直接写入到OLAP引擎，在数据服务或者FBI数据集这一层直接join查询。
以共享零售为例，业务的本质是买家下单，货从卖家流转到买方。这里的参与的对象有商品、商家、买家、骑手等，我们构建以商品ID+商家ID+买家ID+骑手ID的联合主键，盘算在这个主键下的各业务模块的指标汇总事实表，然后利用OLAP引擎的强大的交互分析能力直接关联事实表和维表做多维分析查询。数据链路结构的简单表现图如下：

这种方案对比方案一解决了扩展性题目，提拔了机动度，维度的扩展只需要简单调整维表即可，遇上行业的调整乃至无需做任何处置惩罚；同时PK稳固，也能支持到实时upsert。但也由于数据显现端关联查询逻辑复杂，性能上对OLAP引擎要求较高。存在的题目可以总结为以下几点：

阶段三：基于Hologres明细宽表的即席查询

为了能支持到更加丰富的场景以及支持到实时离线联邦查询下机动的窗口应用，我们方案的考虑方向转向为不再做指标的预盘算，直接将明细数据写入到OLAP引擎，在数据集/数据服务等服务层直接关联DIM表即席查询。同样这对OLAP引擎的性能要求极高，CCO在客岁实时架构升级之后，参见CCO x Hologres：实时数仓高可用架构再次升级，双11大规模落地，借助Hologres列存强大的OLAP能力及实时离线联邦查询能力使该方案落地变为大概。
没有最好的方案，只有在对应场景下做出取舍后相对实用的方案。在这个阶段，我们捐躯了一定的查询性能，选择了对场景支持更丰富、实时离线联邦查询以及扩展机动度更支持更佳的方案。当然在淘系这类较大数据量的业务场景中，我们也做了一定的优化和取舍。如在实际处置惩罚中，对于相对稳固的维度我们在MaxCompute/Flink处置惩罚写入了明细，只对于行业类目等这类易调整且相对敏感的维度直接在数据集/数据服务关联查询。
三种方案对比：
场景方案一：预聚合方案二：轻度汇总方案三：明细即席查询
查询性能（较大数据量）较好一般一般
维度支持支持丰富但数据量易爆炸支持范围固定维度支持丰富
扩展性较差好较好
去重盘算存在膨胀存在膨胀可精确盘算
实时离线联邦查询窗口对比不支持不支持机动支持
行业回刷需要回刷无需回刷无需回刷
Hologres+FBI一体化体验洞察数据实践

联合CCO体验业务在数据洞察应用场景中数据量大、周期长、链路范围广、维度特征多、实时离线对比窗口及快照特征诉求多等需求特点，我们利用Hologres+FBI的各种特性不断在实践中设计优化整体的解决方案。从数据应用诉求来说，用户可以接受一定时间的返回延迟，涉及较大数据量读写但同时查询QPS较低，因而我们选择捐躯一定的查询RT，选择利用基于Hologres明细的即席查询的方案，整体流批两条链路结构如下：

如上所示，整体的方案是相对范例的Lambda结构：

以下为我们针对上面提到的前阶段数据利用存在的各种题目，在实践应用中的一些具体的技能方案。
表设计、Table Group及索引选择


重要查询场景是基于明细按时间范围的OLAP查询，数据规模单日分区超数十亿，同时也需要按天更新回刷数据，所以Hologres表的属性选择上，是列存+业务主键PK+日期分区表。

Table Group的设置一般根据利用场景、数据量大小、Join频次综合考虑。需要关联的表放入同一个Table Group，通过Local Join减少数据的Shuffle，可极大提拔查询效率。
Shard Count根据数据量选择合适的大小。Shard数过小数据的读写会存在瓶颈，而Shard数过大会导致一样平常固定的开销以及查询启动的开销增大造成浪费，大量的Shard数过大的表同时启动查询也轻易给集群的负载造成压力，影响利用性能。现在体验洞察实践中，日增量亿级的生意业务类明细结果Shard Count设置为128，退款、咨询求助等日增量千万左右的明细表Shard Count设置为32。

Hologres提供了Distribution Key、Clustering Key、Segment Key、Bitmap Columns等一系列的索引方式对表进行优化，合理的利用各类索引，可以大幅提拔利用性能。分布建Distribution Key只能是PK或PK的部分字段，选择基于PK来设定；对于商家、类目、行业等经常用在Filter和Range场景的字段，我们对应的设置了聚簇索引Clustering Key。而对于大量的二分类的维度特征以及枚举较少的字段，如是否直播订单、商家分层等，我们对应设置了位图索引Bitmap Columns等。
  1. BEGIN;
  2. CREATE TABLE "public"."ads_case_di"
  3. (
  4. "date_id" TEXT NOT NULL,
  5. "case_id" INT8 NOT NULL,
  6. "industry_name"  TEXT NOT NULL,
  7. "seller_id"    INT8 NOT NULL,
  8. "seller_nick"  INT8 NOT NULL,
  9. "is_presale_order" TEXT,
  10. "is_live_order"    TEXT,
  11.   XXX ,
  12. PRIMARY KEY ("date_id","case_id")
  13. )
  14. PARTITION BY LIST (date_id);
  15. CALL SET_TABLE_PROPERTY('"public"."ads_case_di"', 'orientation', 'column');
  16. CALL SET_TABLE_PROPERTY('"public"."ads_case_di"', 'segment_key', '"date_id"');
  17. CALL SET_TABLE_PROPERTY('"public"."ads_case_di"', 'clustering_key', '"industry_name","seller_nick"');
  18. CALL SET_TABLE_PROPERTY('"public"."ads_case_di"', 'bitmap_columns','"is_presale_order","is_live_order"');
  19. CALL SET_TABLE_PROPERTY('"public"."ads_case_di"', 'dictionary_encoding_columns', '"industry_name","seller_nick","is_presale_order","is_live_order"');
  20. CALL SET_TABLE_PROPERTY('"public"."ads_case_di"', 'time_to_live_in_seconds', '17280000');
  21. COMMIT;
复制代码
T+1分区覆盖方案

在Flink作业界说Hologres Sink表时，需要配置`partitionRouter`和`createPartTable`参数来保证流作业数据Sink到实时的分区以及在路由不到分区时自动创建分区。
  1. partitionRouter = 'true'
  2. createPartTable = 'true'
复制代码
Holo的分区表是子母表结构，子表的当日分区作为流作业的Sink表，T+1及之前的分区为离线任务Batch写入，在每天上午离线任务调度结束数据天生后覆盖实时作业写入的数据。而在T+1的离线数据写入的时候，如何制止写入时出现空分区或者查询抖动，现在的方案是批写入临时子表然后rename并挂载到母表，可以瞬间完成T+1分区的数据切换，制止影相应用端利用体验。以下以某个表现例。
  1. BEGIN;
  2. --线上表分区子表，如果不存在分区，就创建该分区
  3. create table if not exists ads_tb_di_${bizdate} partition of ads_tb_di
  4.   for values in ('${bizdate}');
  5. --批数据写入的中间表子表
  6. create table if not exists ads_tb_di_batch_${bizdate} partition of ads_tb_di_batch
  7.   for values in ('${bizdate}');
  8.   
  9. --解除线上表依赖关系
  10. ALTER TABLE ads_tb_di DETACH PARTITION ads_tb_di_${bizdate};
  11. --解除中间表依赖关系
  12. ALTER TABLE ads_tb_di_batch DETACH PARTITION ads_tb_di_batch_${bizdate};
  13. --名称互换
  14. ALTER TABLE ads_tb_di_${bizdate} RENAME to ads_tb_di_temp_${bizdate};
  15. ALTER TABLE ads_tb_di_batch_${bizdate} RENAME to ads_tb_di_${bizdate};
  16. --挂依赖
  17. ALTER TABLE ads_tb_di ATTACH PARTITION ads_tb_di_${bizdate} FOR VALUES in ('${bizdate}');
  18. --删除临时批表
  19. drop TABLE ads_tb_di_temp_${bizdate};
  20. commit;
复制代码
FBI的Velocity语法和Fax函数裁剪SQL优化查询

在BI的利用上，我们选择FBI（阿里团体内部的一款BI分析产物）。现在FBI一个组件只支持一个数据集，为了支持多维交错分析应用，我们比较常见的方案是在数据集SQL中将全部大概用到的表拼接起来以备查询。但实际的即席查询场景中，用户选择的指标和维度大概只利用到了数据会合的部分表，假如全量查询数据集，会造成浪费同时也会影响查询性能。
联合FBI的 Velocity语法和Fax函数等特性配置动态查询可以实现根据用户的选择动态路由裁剪，在数据会合如下利用Velocity语法添加判断语句，在扩展指标中配置动态查询的参数。这里的${tableindexorder} == 'order' 代表生意业务明细表，数据量较大。


在实际的即席查询场景中，如用户只选择了“纠纷介入率”这类指标和维度，和生意业务数据没有关系，那么最终执行的query将不会掷中${tableindexorder} == 'order' 这个分支下的SQL，借此实现对数据集SQL的裁剪，从而制止了每次查询都全量执行整体数据集，可以根据实际利用场景按照“不利用则不查询”的原则提拔查询效率。
实时离线联邦查询机动窗口对比

大促场景下实时离线联邦查询的诉求十分常见，尤其当前时间点位同环比历史同期时段点位这类对比需求，现在基于明细宽表的即席查询架构更加机动高效。起首离线部分无需再进行预盘算，尤其假如对比点位比较细的话，如5分钟、10分钟这类窗口点位的对比，那离线需要预盘算准备的数据较为复杂，数据量也十分大。另外对于运动当天退款量、退款金额的累计趋势这类很通例的诉求的实现，也不再需要通过Flink盘算每个点位的数值，再通过窗口函数进行聚合。直接对关键时间字段增加打点字段，一个简单的窗口函数即可完成累计趋势图的绘制。好比以下为一个10分钟窗口累计趋势的示例：
  1. select  date_id
  2.         ,create_time_10min ---10分钟向后打点
  3.         ,rfd_cnt --当前时间窗口退款量
  4.         ,rfd_amt --当前时间窗口退款金额
  5.         ,sum(rfd_cnt) over(partition by date_id order by create_time_10min asc) as total_rfd_cnt --累计退款量
  6.         ,sum(rfd_amt) over(partition by date_id order by create_time_10min asc) as total_rfd_amt---累计退款金额
  7. from    (
  8.             select  date_id
  9.                     ,create_time_10min
  10.                     ,count(*) rfd_cnt
  11.                     ,sum(refund_real_amt) as rfd_amt
  12.             from    ads_tb_di
  13.             where   date_id in ('20201111','20211111') --大促当天和历史同比
  14.             group by date_id
  15.                      ,create_time_10min
  16.         ) t
  17. ;
  18. --create_time_10min 这里是对退款发起时间的打点字段，等同于replace(substr(FROM_UNIXTIME(UNIX_TIMESTAMP(case_create_time) - (UNIX_TIMESTAMP(case_create_time)% (10 * 60)) + (10 * 60)),12,5),':','')
复制代码
Hologres动态分区回刷

由于采用了Hologres分区表的设计方式，当遇到需要同时回刷多个历史分区的环境时，由于Hologres分区是子母表结构且不支持向母表Insert数据，这里实现动态回刷多分区这类场景相对麻烦一些，Hologres当前不支持步伐块脚本，一般需要通过python/perl等脚原来进行对分区子表的循环操作。在这里我们采用DataWorks的控制节点配置用以相对简单的实现对Hologres分区表的动态回刷。
UV类去重盘算优化

在体验洞察的场景里，有着大量的去重盘算的诉求，好比咨询万笔订单求助量等这类指标，咨询场景中会话量的盘算大多是基于非主键列的盘算，在现在这种基于明细的查询下，固然制止了预盘算结果集上聚合数据值膨胀的环境，但大量的distinct操作极其影响性能。因而应对去重盘算，在不同场景下我们做了些不同的优化方案选择。

在首屏核心指标块这类紧张的呈现场景，好比万单求助量、小蜜发起量等紧张观测指标的大数概览统计，由于指标的精确性要求，我们会利用distinct去重盘算，这类指标数量不多，也由于不涉及下钻分析只是概览统计，对于离线场景可以在FBI等展示端设置较长的缓存周期，查询掷中缓存的概率较高，可以一定程度的减少distinct带来的性能影响。

对于行业、类目等这一类紧张而且高频被利用到的的维度场景，而且这些维度对盘算的精度也有着较高的诉求，为了保证去重计数查询的性能，我们利用Hologres的RoaringBitmap的数据压缩和去重特性在较大数据量下进行盘算。由于RoaringBitmap本质上还是做了一层预聚合盘算，假如维度太多粒度太细数据量也会膨胀的比较厉害，为了保证优化的效果，这里我们选取部分紧张维度，联合前文提到的FBI Velocity语法判断，当查询的维度掷中在RoaringBitmap底子聚合的维度范围时，通过RoaringBitmap快速返回结果。RoaringBitmap去重示比方下：
  1. CREATE EXTENSION IF NOT EXISTS roaringbitmap; --创建roaringbitmap extention
  3. -----创建映射表，用以映射去重字段serv_id到32位int类型
  4.     BEGIN;
  5. CREATE TABLE public.serv_id_mapping (
  6.      serv_id text NOT NULL,
  7.      serv_id_int32 serial,
  8.      PRIMARY KEY (serv_id)
  9. );
  10. CALL set_table_property('public.serv_id_mapping', 'clustering_key', 'serv_id');
  11. CALL set_table_property('public.serv_id_mapping', 'distribution_key', 'serv_id');
  12. CALL set_table_property('public.serv_id_mapping', 'orientation', 'column');
  13. COMMIT;
  15. -----创建基础聚合结果表
  16. BEGIN;
  17. CREATE TABLE ads_tb_roaringbitmap_agg (
  18.     date_id text NOT NULL,  --日期字段
  19.     bu_type text,
  20.     industry_name text,
  21.     cate_level1_name text,
  22.     cate_level2_name text,
  23.     cate_level3_name text,
  24.     uid32_bitmap roaringbitmap, -- 去重计算结果计算
  25.   primary key(bu_type, industry_name,cate_level1_name,cate_level2_name, cate_level3_name, date_id)--查询维度和时间作为主键，防止重复插入数据
  26. );
  27. CALL set_table_property('public.ads_tb_roaringbitmap_agg', 'orientation', 'column');
  28. CALL set_table_property('public.ads_tb_roaringbitmap_agg', 'clustering_key', 'date_id');
  29. CALL set_table_property('public.ads_tb_roaringbitmap_agg', 'event_time_column', 'date_id');
  30. CALL set_table_property('public.ads_tb_roaringbitmap_agg', 'distribution_key', 'bu_type,industry_name,cate_level1_name,cate_level2_name,cate_level3_name');
  31. end;
  33. --------将映射表里没有的serv_id写入进去
  34. WITH
  35.      serv_ids AS ( SELECT serv_id  FROM ads_xxx_crm_serv_total_chl_di WHERE date_id = '${bizdate}' GROUP BY serv_id )
  36.     ,new_serv_ids AS ( SELECT a.serv_id  FROM serv_ids a LEFT JOIN serv_id_mapping b ON (a.serv_id = b.serv_id) WHERE b.serv_id IS NULL )
  37. INSERT INTO serv_id_mapping SELECT  serv_id
  38. FROM    new_serv_ids
  39. ;
  41. ------按照聚合条件聚合后插入roaringbitmap聚合结果表
  42. WITH
  43.     aggregation_src AS( SELECT date_id,bu_type, industry_name,cate_level1_name,cate_level2_name, cate_level3_name, serv_id_int32 FROM ads_xxx_crm_serv_total_chl_di a INNER JOIN serv_id_mapping b ON a.serv_id = b.serv_id WHERE a.date_id = '${bizdate}' )
  44. INSERT INTO ads_tb_roaringbitmap_agg
  45. SELECT   date_id
  46.         ,bu_type
  47.         , industry_name
  48.         ,cate_level1_name
  49.         ,cate_level2_name
  50.         ,cate_level3_name
  51.         ,RB_BUILD_AGG(serv_id_int32)
  52. FROM    aggregation_src
  53. where cate_level3_name is not null
  54. and   bu_type is not null
  55. GROUP BY date_id
  56.         ,bu_type
  57.         , industry_name
  58.         ,cate_level1_name
  59.         ,cate_level2_name
  60.         ,cate_level3_name
  61. ;
  63. -------执行查询，RB_CARDINALITY 和 RB_OR_AGG 聚合计算
  64. SELECT  bu_type
  65.         , industry_name
  66.         ,cate_level1_name
  67.         ,cate_level2_name
  68.         ,cate_level3_name
  69.         ,RB_CARDINALITY(RB_OR_AGG(serv_id32_bitmap)) AS serv_cnt ---去重计算结果字段
  70. FROM    ads_tb_roaringbitmap_agg
  71. WHERE   date_id = '${bizdate}'
  72. GROUP BY bu_type
  73.         , industry_name
  74.         ,cate_level1_name
  75.         ,cate_level2_name
  76.         ,cate_level3_name;
复制代码

而对于大多数维度场景，对去重并不是要求100%精确，利用Hologres自身的APPROX_COUNT_DISTINCT近似盘算，去重精度误差可达1%以内，在可接受范围内且不会大幅影响查询性能。同时可如下通过调整精度参数来控制盘算的精确度，但也会相应的增加盘算开销，实测默认参数值17就可以达到较好的去重精度。
  1. set hg_experimental_approx_count_distinct_precision = 20;
复制代码
同时Hologres 1.3版本也支持了UNIQ函数，跟count distinct是一样的语义，但是盘算效率更高，更节省内存，后续我们将会利用。
快照采集及持久化离线存储

前文提到了CCO侧体验洞察分析存在大量的快照类特征诉求，好比用户咨询时刻的货物状态、物流节点等，这类快照对分析用户求助、退款时候的真实的境况和诉求及其紧张。而这类快照在各类体系中不太大概都有业务埋点，因此需要数据侧去加工得到对应的数据。这类快照数据假如通过批任务处置惩罚存在的重要题目是无法精准的获取快照状态，好比咨询时的物流节点，通过离线ETL处置惩罚需要比对咨询时间和物流各节点的时间卡先后顺序得出其时的节点状态，对节点的枚举是否全面要求极高，而且处置惩罚复杂程度也较高。

因此，通过实时的消息联合实时更新的持久化存储的维表或线上接口来天生快照类数据是较为合适的方案，以咨询时订单状态的实现为例，我们接入咨询创建的TT/MQ，发生咨询之后去查询对应订单维表或者TC接口，返回的数据写入当天的实时分区，在T+1日我们通过Hologres的外表导出的功能，将T日实时写入的这类快照状态字段从Hologres导出到MaxCompute做持久化离线存储，在批任务的链路里离线分区的快照类字段可JOIN这份数据产出，同时也可以用以后续的数据回刷、业务洞察分析。
  1. --回写至MaxCompute
  2. INSERT INTO ads_holo_imp_di_foreign --外表，映射ODPS表ads_xxx_holo_imp_di
  3.         (
  4.            date_id               
  5.           ,serv_id
  6.           ,xxx
  7.         )
  8. SELECT   date_id               
  9.          ,serv_id   
  10.          ,xxx
  11. FROM ads_total_chl_di
  12. WHERE date_id= '${bizdate}';
复制代码
业务效果

一体化体验洞察于本年初上线，现在重要支持在淘系退款、咨询万求等场景的实时多维交错分析、智能异常检测，月均50亿+数据量级下的聚合查询根本均能在秒级返回，支持到100+业务小二大促、一样平常的体验运营洞察分析，助力业务小二单次洞察分析提效10倍以上。
双11大促期间（11.1-11.20），一体化洞察提交执行的Query数为66w+，假设50%的Query为有效查询，同样按照每个Query小二过去平均需要投入10分钟进行编写、执行、检查等操作来盘算，共计节省了6875人日，当然假如没有对应的数据/产物能力，小二受限于SQL技能以及开辟本钱也不会产生这么多查询，但也侧面反映了一体化洞察对小二们工作效率的有力提拔。

将来方向和思考

流批一体化

由于现在上游依赖的中心层离线和实时模型还不能完全同一，整体的数据架构还是比较传统的Lambda架构，需要维护离线、实时两套任务，开辟、任务运维的本钱较高，而且实时、离线数据存在一定的差别。当然从一套代码实现原先流批两条链路的的角度来说，现在基于Hologres的架构下存储同一、盘算同一的条件都是具备的，后续我们重要推进DWD中心层的模型同一，完成一体化体验洞察整体数据架构流批一体。
数据集服务管理

为了整体快速上线，现在仍有大量的FBI数据集直连Hologres库而非托管在数据服务平台。因而数据集的监控、压测、慢查询的预警优化等没法依托数据服务平台的能力纳入同一管理，为了保障数据的稳固性、高可用性，后续需要将体验洞察的全部数据集依托服务平台会合管控。
作者：张乃刚（混名：隽驰)，CCO数据开辟
原文链接
本文为阿里云原创内容，未经答应不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4