《Apache Doris在平安壹账通的实践经验.pdf》由会员分享，可在线阅读，更多相关《Apache Doris在平安壹账通的实践经验.pdf（26页珍藏版）》请在三个皮匠报告上搜索。

1、Doris Summit 2023Apache Doris 在平安壹账通的实践经验兰后平安壹账通Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summ

2、it Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023目录02 基于Apache Doris的指标架构升级03 基于Apache Doris的指标应用性能调优实践01 指标平台介绍与应用Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Do

3、ris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asi

4、a 20231指标平台介绍与应用Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20

5、23Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标设计框架：构建贴合战略的指标体系，是实现数字化经营的基础指标体系建设目标指标体系设计管理体系管理制度管理流程组织职责142多维多层指标框架客户产品渠道机构一级：战略指标二级：经营管理指标（结果指标）三级：业务执行指标（过程指标）横向：指标业务维度指标体系应用3标准报表自定义报表可视化应用（指标平台）指标体系设计方法纵向指标管理维度自下而上归纳业务部门主导自上而下演绎1、

6、业务发展战略解读2、企业价值树分解3、价值树推导指标3、指标整合2、指标归类1、指标收集银行指标库技术部门主导Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Dor

7、is Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标平台：提供集指标构建、指标查询、指标治理一体的指标数据服务平台助力企业经营数字化转型指标径、标准不统各个业务条线生成的报表堆积如山，不同分析工具生产的指标口径相互打架。指标计算重复、分析效率低“需求提出-数据探源与开发-报表制作-上线验收”的过程费时费力。对指标进行归因关系分析 基于指标标准体

8、系，帮助指标各层级找到过程中的问题，有效科学决策。对指标生命周期进行管理 支持指标的录入、发布、派生、分享、可视化应用以及深度分析等，并结合管理制度，在使用的各个环节落实指标的管理规范。Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Sum

9、mit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标平台：多种指标类型+不同维度构建指标体系指标：通过数据量化业务，从而促进业务目标可描述、可衡量、可拆解维度：分析业务的视角，包括各层级员工常用视角、业务属性、行业特征、地域特征等指标体系+派生指标机构渠道维度产品指标衍

10、生指标原子指标 简单派生：基于原子指标增加过滤条件生成的指标；如：上海银行AUM余额 复杂派生：经过多个原子指标运算生成的指标；如：AUM余额/全量客户数=客均AUM余额 含义：基于原子指标的金额计算的指标；如：AUM余额净增、AUM余额同比 含义：经数据开发通过中间表接入系统的指标；如：AUM余额产品1产品2产品3上海分公司深圳分公司北京分公司小程序APP网页Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Su

11、mmit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标平台：为银行提供全面的指标分析与展示、智

12、能归因分析，辅助经营决策内容：支持更多指标类型，支持指标配置化接入，完善指标/维度价值管理。BI：支持更多分析场景，持续优化操作体验，移动分析场景优化。AI：融入更多智能算法，赋能更多数据洞察。Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris

13、 Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标平台功能全景图元数据管理中间表注册关系配置指标录入离线录入指标校验指标衍生指标复制KPI配置重跑数据指标发布模型创建数据加载数据校验指标授权主题配置变更发布预警管理预警配置预警重跑邮件补发视图开放数据集新建添加

14、指标添加维度账号管理角色创建权限配置用户管理用户组管理应用存储RedisMySQL大数据平台PrestoApache Kylin指标平台指标地图上海品茶配置主题配置指标查询指标卡片指标派生指标推荐告警配置指标应用指标开发指标详情指标血缘Hive统一数据仓库Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20

15、23Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20232基于Apache Doris的指标架构升级Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit

16、 Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Dori

17、s Summit Asia 2023Doris Summit Asia 2023指标架构升级：引入 Apache Doris电邮平台数据资产管理平台邮件预警HTTP大数据平台统一账号管理平台网关智能决策中心指标分析策略分析HTTP接口路由HTTP登录认证指标服务HTTPHTTP权限查询HTTP人员同步HTTP元数据管理指标录入指标发布预警管理权限角色管理指标地图视图开放MySQLRedis指标管理指标数据数据提取指标元数据查询元数据查询HTTPJDBC指标查询Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Do

18、ris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asi

19、a 2023Doris Summit Asia 2023指标架构1.0：Hadoop+Presto+Apache Kylin Apache Kylin 无法满足更灵活的分析需求；使用成本高 Presto 负责明细查询下压，但性能不，并发不够 维护多套组件，运维成本高痛点：复杂场景多表关联查询性能好 支持高并发 运维简单，方便扩展 易用性强，开发简单方便诉求：指标维度时间过滤查询下压Cube 1Cube 2DIM 1FACT 1FACT 2FACT 3DIM 2模型构建、数据同步1.查询命中Hive TablesApache KylinCubesPrestoApache Kylin2.查询未命中

20、指标查询Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summ

21、it Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023OLAP 选型OLAP优点缺点是否开源协议应用场景和案例ClickhouseMPP单表查询性能强保留明细物化视图与索引多表 Join 支持差并发度低实时更新支持一般开源非标准协议金融：Ebay、Deutsche BankApache DorisMPP高并发和高吞吐的 Ad-hoc 查询多表 Join支持明细和聚合物化视图与索引实时+离线数据导入Schema 在线变更支持一定程度更新社区相对年轻产品

22、迭代较快开源兼容 MySQL 协议金融：招商、邮储、平安、广发、兴业等TiDBHTAP架构简单OLAP 能力不足开源兼容 MySQL 协议金融：中行、平安、光大、北京银行、微众、浙商银行、陆金所GreenplumHTAP支持多级分区多表 Join单机性能瓶颈开源兼容 MySQL 协议金融：建行Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 202

23、3Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标架构2.0：Apache Doris 使用 Duplicate 模型+物化视图 使用 RANGE 进行时间分区

24、 指定 维度键作为 KEY数据：重写MySQL兼容的查询引擎 调整简化指标发布流程应用：指标维度时间过滤FE指标查询BEBEBEDoris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asi

25、a 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023指标架构2.0：Apache Doris 支持更灵活的指标分析 高效查询分析性能 运维简单收益00500600700800Q1Q2Q3Q4Q5Q6Q7Q8Q9Q10Apache Kylin vs Apache Doris(多表 JOIN)Apache Kyl

26、inApache DorismsDoris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20

27、23Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20233基于Apache Doris的指标应用性能调优实践Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Su

28、mmit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20231.数据准备表中文名行数汽融申请表900,000,000客户维度表900,000,000省市区维度表7,4210银行机构维度表448

29、0业务员维度表44801建表：Duplicate+时间分区考虑到指标平台以多表关联场景为主，我们选择明细模型作为数据模型.KEY值选择：指定维度键作为KEY指定DT（INT）字段进行RANGE方式进行2运行 Python 脚本造数生成多个12G 的 CSV 文件3运行 Bash 脚本发送导入请求基于StreamLoad方式，完成批量导入。最终压缩比3:1，速度40MB/s。Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Do

30、ris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20232.部署集群监控：Grafana

31、+PrometheusDoris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Dor

32、is Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023单页面加载性能瓶颈问题发现：多指标查询时，BE 节点因 OOM 宕机 单接口查询性能无法达到预期解决方案：Colocation Join 调整数据模型Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Su

33、mmit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023解决方案1.Colocation Join2

34、.调整数据模型Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris S

35、ummit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20231.Colocation Join获取指标趋势图耗时：5 s 144 msAnalysis Time：245.603usPlan Time：1.269 msSchedule Time：15.422 msWait&Fetch Result Time：5 s 144 msAnalysis Time：267.687usPlan Time：1.377 msSchedule Time：12.203 m

36、sWait&Fetch Result Time：1 s 48 ms获取指标趋势图耗时：1 s 48 msBroadCast JoinColocation Joinselectsum(f.METRIC)asm,f.DT asDTfromfleftjoindim0onf.FKEY=dim0.DKEYandf.DT=dim0.DTleftjoindim1ondim0.DKEY=dim1.DKEYanddim0.DT=dim1.DTwheref.DT in(20220103,.,20220201)groupbyf.DTselectsum(f.METRIC)asm,f.DT asDTfromfleftj

37、oindim0onf.FKEY=dim0.DKEYandf.DT=dim0.DTleftjoindim1ondim0.DKEY=dim1.DKEYanddim0.DT=dim1.DTwheref.DT in(20220103,.,20220201)groupbyf.DTDoris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summi

38、t Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20232.调整数据模型：SQL执行计划获取指标趋势图耗时：5 s 144 ms获取指标趋势图耗时：63.396 ms雪花模型大宽表selects

39、um(f.METRIC)asm,f.DT asDTfromfleftjoindim0onf.FKEY=dim0.DKEYandf.DT=dim0.DTleftjoindim1ondim0.DKEY=dim1.DKEYanddim0.DT=dim1.DTwheref.DT in(20220103,.,20220201)groupbyf.DTselectsum(f_flat.METRIC)asm,f_flat.DT asDTfromf_flatleftjoindim1onf_flat.DKEY=dim1.DKEYandf_flat.DT=dim1.DTwheref_flat.DT in(20220

40、103,.,20220201)groupbyf_flat.DTAnalysis Time：264.231u sPlan Time：793.918 usSchedule Time：4.835 msWait&Fetch Result Time：63.396 msAnalysis Time：245.603usPlan Time：1.269 msSchedule Time：15.422 msWait&Fetch Result Time：5 s 144 msDoris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Su

41、mmit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023

42、Doris Summit Asia 20232.调整数据模型：查询性能Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023D

43、oris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 20232.调整数据模型：服务器压力Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023D

44、oris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023总结与思考1在启用缓存前提下，单用户访问，页面满足4s内加载完成，接口满足600ms内返回。并发场景下，最优TPS达到

45、300，CPU、内存、磁盘和IO满足80%以下。2经评估，3台8C16G的BE节点至少可以缓存上万指标（4条SQL/指标）。3对于数据量大的多表关联，借助AuditLoader收集高频SQL，构建宽表替代星型模型。Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia

46、 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023未来规划整合 Flink CDC+IceBerg+Apache Doris 构建实时分析123将中台其他产品迁入 Apache Doris 探索多表关联下的查询优化，比如构建多表物化视图Dori

47、s Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia

48、2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023获取更多社区动态与最佳实践Doris Summit 峰会官网：doris- Doris Summit 峰会回放：https:/ Doris 官网：doris.apache.orgApache Doris GitHub： Doris 官方平台：Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 202

49、3Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023Doris Summit Asia 2023