1。简介通过BI平台获取、读取和分析数据，已经成为辅助决策和精细化运营的非常重要的手段。简单易用的拖拽式报表、便捷访问的自由式分析、秒级响应的查询速度、准确可靠的观察指标数据等需求。面对用户的个性化需求和海量数据，平台体系建设和技术实现都存在一定的挑战。本文将介绍去哪儿网BI平台的建设过程和实践，为业务增长打造全场景BI平台。授权。2、建设过程2015年至今，BI平台的建设经历了多年的迭代发展，始终结合业务需求遵循以下原则：尽可能让用户自己完成，让开发同学可以尽量少干预，即提高访问数据分析的效率；在平台功能方面，操作简便的门槛要低，接入方式要丰富，即提高平台的易用性；在系统性能方面，查询速度要快，即提高查询性能；数据可信度。BI平台的建设时间线如上图所示。根据实际业务需求，推出相应的模块。大致可以分为三个阶段：原始阶段，2016年之前，报表系统V1一路走到尽头；开发阶段，2016-2018，配置型报表系统V2，自助分析，OLAP；系统建设阶段，2019-至今，即兴查询，自助邮件报表，数据报表系统V3，OLAP（CRM）；子系统互联互通，综合对接指标体系建设标准化指标。后面会详细介绍每个阶段的痛点和解决方案。1.原始阶段关键词：一路到底，数据量小，快速完成需求开发原始阶段，业务处于高速发展阶段，公司大部分精力都在业务上，用户的数据的接入、数据的分析、数据的诉求，基本都是依托于数据部门开发的报表，为了满足如此庞大的数据需求，所有的数据开发资源都投入到业务需求上，报表开发是一路进行，从收集分析日志，ETL，衍生品，写报告到平台前后台等等。如下图。通过Hive解析日志，进行ETL，根据业务逻辑将计算结果导出到关系型数据库MySQL；在报表系统项目中编写后台逻辑，从MySQL数据库查询；编写前端页面，实现各种个性化图表展示数据。这种方式属于起步阶段，还不是平台建设。虽然可以快速满足业务需求，但同时也暴露出很多问题：这种一路走到底的开发模式并没有像想象中那样快速消化需求，反而效率低下，代码风格不一样而且质量不同；每个需求都有自己的个性，也有共性。结果就是无论是数据还是图表，都存在大量的个性化和相互冗余。总结之后，我们发现业务的快速发展不是借口。这种方法不快但很痛苦。迫切需要明确分工，通过平台建设，将大量积累的数据需求转化为用户的部分自助。2.发展阶段关键词：分工明确，部分自救。随着业务和数据团队的发展，数据仓库和数据平台的建设同样重要。从此，分为两个方向。一种是偏向于业务数据的团队，更多的是关注业务和数据本身以及数据仓库模型的构建；另一个是偏向于数据平台的团队，负责重构报表、权限等系统，有利于让数据平台更易用，提高用户拿数据查看数据、分析数据的效率。因此，这一阶段除了重构报表系统，将报表的配置交给用户，还搭建了自助数据分析系统和OLAP系统，进一步提高数据检索和分析的自助率。1）报表系统V2数据开发同学根据需要将数据仓库最终产生的ADS层数据导入到PostgreSQL中。这里使用PostgreSQL是因为它有丰富的分析功能，统计效率也不错；产品等用户首先配置维度、指标、筛选项作为数据单元，可以在不同的报表中重复使用，然后在报表中引用，然后作为最终报表发布展示数据。2）自助数据分析①场景痛点报表系统引入了数据仓库的ADS层数据表，是固化口径计算的结果，但不能很好的支持各种分析场景；从DWD到ADS的过程需要数据开发同学完成进度。对于不会写SQL的产品运营者来说，需要请求临时、灵活、简单的聚合分析来获取数据，周期太长。如果能够基于DWD明细数据，用户可以在很大程度上直接自由地进行分析，提高数据分析效率，数据开发同学也可以从琐碎的数据需求中解放出来；标准化埋点的实时数据（包括行为和订单数据），数据开发同学也需要调度实时ETL。整个过程可以自动化吗？平台可实时分析埋点数据。如下图所示的接口函数是从SQL语法中抽象出来的。用户只需点击即可自行完成常规聚合查询分析。②整体架构结合实际痛点分析有如下必要需求：需要支持实时数据插入、更新和读取，需要支持直读离线数仓表和实时数据表，以及查看到当前时间段的数据，并且需要支持表关联进行分析自助分析需要查询多个维度的指标，需要快速响应对于离线和实时数据同时查询的需求，首先是all，必须要有一个统一的查询入口，然后对于亿级以下的数据做数据分析的时候要保证效率，所以我就想到了Impala+Kudu和Impala+HDFS(Parquet)的组合（Kuduonly存储当天的实时数据，离线数据从原来的HDFS读取）。与将两种类型的数据导入其他引擎相比，该方案在存储和实施成本方面相对较小。ApacheKudu介于Hadoop和Hbase之间，既能满足高吞吐量的数据分析需求，又能满足数据快速随机读写的需求。基于Impala和Kudu的系统架构图如下：数据写入过程中，离线数据不需要写入，数据存储在HDFS上。Impala可以直接用Hive读表查询，减少离线数据处理，节省成本。这也是选择Impala的原因之一；实时数据源包括实时数仓、标准化埋点实时数据等，由Flink通过Kafka实时写入Kudu表作为热点数据，同时写一份到HDFS作为冷数据和备份；基于Hive表和Kudu表创建Impala视图，结合离线和实时数据表进行查询。用户在数据读取过程中，在分析页面选择维度分组、过滤条件、统计周期、指标计算等，点击查询；查询模块先从缓存中取出，如果是重复请求则直接返回，如果不是则解析请求参数，拼接查询ImpalaSQL；对于多指标分析，为了提高整体查询效率，拆分成多条SQL并行执行；对于跨越多天的非重复查询，查询结果按天存储为分片缓存，以减少后续重复查询，提高查询性能效率；Impala中的数据会被查询，并且从分片缓存中获取的数据将被合并并返回到页面显示。3）实时OLAP业务中有两种典型的OLAP场景。一是营收团队需要对历史全订单、亿级数据量进行全维度分析，制定策略。策略启动后，实时监控收益。效果，通过多维度分析，定位哪些渠道、城市、星级等维度效果不佳，指导及时调整策略。另一个是酒店业务团队。我们知道，酒店预订的流程包括搜索、列表页、详情页、预订页、下单。需要监控每个阶段业务系统的实时流畅性。实时收集每个阶段每个请求的流畅度。这个数据量是亿级别的，然后进行多维度的统计分析和实时监控。如有问题可及时报警甚至需要屏蔽发布并自动报错，协助业务团队定位问题并解决问题，提升用户预订酒店过程中的体验.从这些场景中可以提炼出一些关键点：数据实时性要求高，数据量上亿级，维度指标上百个，适合大宽表的数据存储，秒级查询响应。①计算引擎是满足上述要求的关键。OLAP引擎的选择是关键。我们当时比较常用的引擎是这样的：Kylin在十几个维度上和Druid类似，但是我们遇到的场景是几十个维度。Kylin的Cube扩容率高，查询性能没有达到预期；另一个业务需求变化导致重建立方体不够灵活，Kylin更适合固定20个维度的场景，业务逻辑计算复杂，需要预计算；Presto不支持实时Data，秒级交互查询不够用；ES在亿级数据量下的多维分析和查询性能较差，写入效率不高；Kudu+Impala的方案貌似更合适，但是目前需求不关心数据更新，更关注亿级数据量下的查询响应时间。通过多维度的对比，我们最终选择了ApacheDruid，它可以支持亿级数据量，支持实时数据，并且在近百个维度指标的情况下仍然有很高的查询性能，来支持这种真实的-timeOLAP场景。②数据可视化数据可视化选择开源的Superset，主要是因为它深度支持ApacheDruid，并且支持很多其他的数据源，对历史数据的兼容很好。Superset具有强大的数据分析能力，并拥有丰富的可视化图表类型。此外，还支持配置图表为数据大盘，以报表的形式呈现固化的分析口径。至此，OLAP方案如下：离线数据通过Hive同步到PostgreSQL。此链接为报表系统V2统计数据来源。可以直接访问超集。对可视化图表类型要求较高的用户是不错的选择；实时数据通过KafkaIndexServer馈入Druid集群，Superset接入Druid，在看板设置刷新频率或手动刷新查看实时数据；我们对Superset进行了二次开发，包括接入公司SSO登录，统一权限体系，集成ECharts让可视化图表更加丰富，比如漏斗图，国家地图等。4）阶段总结在这个阶段，通过清晰的划分人力，专项资源集中在数据平台建设上，解决了用户访问数据、查看数据、分析数据场景的大部分诉求，包括报表配置、自助数据分析、实时OLAP等，用户通过工具自助获取，不再完全依赖数据开发同学，效率相对大幅提升；数据开发同学也大大减少了对临时和琐碎数据检索的需求，把更多的精力放在业务本身和数据仓库的建设上。但是，面对用户的各种需求，我们不断投入专门的资源去满足，不断的推翻迭代造成资源的浪费，从而引发了BI平台下一步的系统化建设。3.系统建设阶段的关键词：多样化、个性化、标准化、系统化。用户的需求是多种多样的，却不可能开发出相应的系统来满足他们。针对以下痛点，我们目前需要统筹考虑，统筹设计，一劳永逸，系统化建设。报表系统，可视化图表类型不够丰富，新增图表类型需要自定义开发，涉及大量前端开发工作；Superset看板可以部分替代数据报表，分析功能强大，数据分析同学很喜欢，但是产品运营同学觉得使用门槛太高，不想用；OLAP场景需要更多的个性化，大宽表模式已经不能满足需求；极其个性化的读取、查看数据需求，不得不写SQL通过AdHoc获取；业务指标千差万别，同一个指标数据各方看到的结果并不一致，开发??和产品需要反复寻找同一个口径。经过前两个阶段，BI平台的雏形已经出现。下图展示了现阶段BI平台的总体架构设计。本文将重点介绍该阶段的搭建与实践，然后从场景模块的角度进行介绍。1）Adhocquery和自助邮件报表Adhocquery之前都是通过登录客户端或者开源的HUE写SQL数据访问。这种方式会面临很多问题，比如权限控制不能很好的保证数据安全隐患，SQL脚本无法管理人员流失等个性化需求，写SQL时使用的日期变量不灵活支持。为此，结合业务需求，构建了一个即席查询和自助邮件报表系统。即席查询和自助邮件报表用户自己编写SQL，即席查询用户手动点击运行，自助邮件报表通过配置的定时或调度依赖自动触发查询请求；服务模块首先进行语法检测，然后解析SQL获取需要查询的表和分区数量，调用权限系统验证用户是否有访问该表的权限，然后根据限制允许加载的分区数量到用户层，验证允许同时执行的任务数，任务MR并行度等，最后提交查询给执行模块；execute模块通过JDBC连接执行SQL，然后将数据显示在前端页面进行预览或者下载到本地或者以邮件的形式发送报表。2）数据上报模块数据上报模块是第三次迭代。除了满足业务需求，我们还需要考虑重构前第三版能用多久。带着这个问题，我们提炼出以下原则：尽可能培养学生作为数据平台开发学生少干预，具有平台搭建思维，一次性搭建，图表组件化。作为一名数据开发同学，开发和维护一个良好的数据模型，在此基础上可以支持配置各种口径的各类图表。作为产品运营商尽可能使用最低门槛，不需要写SQL，拖拽就可以得到所见即所得。业务内部管理权限、数据看板等复杂事务完全自助化。①架构设计数据源层离线数据支持从MySQL、离线数仓、指标体系同步，也支持业务系统监控数据同步；实时数据从实时数仓提供的Kafka访问，基本是DWD层数据；数据接入层内置专用衍生平台，支持离线和实时数据导入各类存储；离线数据衍生任务完成后会进行数据校验，失败则告警给衍生任务的开发同学和引用该数据源的图表负责人；实时数据从Kafka导入到ClickHouse或ApacheDruid。存储/引擎层根据不同的场景选择不同的存储。离线结果数据推荐PostgreSQL，大数据量推荐GP；实时统计数据存储在Druid，多维数据分析场景存储在ClickHouse；为提高查询效率，离线派生任务成功后，触发将引用当前数据源的图表数据刷入缓存，用户自己查询的结果也存储在临时缓存中；数据模型层基于导入的底层数据表，设置维度和指标定义数据模型，根据业务需求抽象出合理的模型，是数据报表模块的核心，也是数据开发同学工作的重点;数据展示层可视化图表提供近十种常用图表模板，维度指标可基于数据模型自由拖放；最上层会有更多的Chart组成数据仪表盘，用于报表展示，支持常用的滚动和下钻；对于实时数据大屏场景，也可以通过拖拽的方式高效完成。系统管理数据看板作为资源接入的统一权限体系，管理线下衍生任务。通过调度系统定期调用任务。各层级系统性能监控，时刻关注平台性能状态。通过埋点记录用户使用平台的行为，掌握用户使用情况，特别适合无人访问的看板离线参考支持图表、看板嵌入第三方系统，已嵌入机器酒多个平台业务②自助拖拽配置对于可视化图表，拖拽是由前端实现的，因此前端所有用户的拖拽和配置信息都以Json的形式内置到一个Config中，传输到后端进行存储；可视化图表打开时，前端获取Json形式的Config，解析后渲染。自由拖动实际上降低了图表配置的门槛，提高了配置效率。原报表系统V2配置步骤复杂，大部分由数据开发同学配置，开发周期长。为了提高整体效率，先把这个模块抽象成四个部分，存储/引擎，数据模型，可视化图表，看板/大屏，在上一节中已经详细介绍过了。其次，明确分工。数据开发同学主要是根据需求场景将数据引入合适的存储/引擎，根据需求内容抽象出合理的数据模型。自拖完成。③业务自主管理各业务整合后，面向整个公司的用户涉及整个业务。每个业务在报表的组织和权限管理上都有很大差异。我们希望能够独立管理。因此，我们加入了BU的概念，逻辑上按照BU完全隔离，包括导入的存储和引擎、数据模型、可视化图表、数据仪表盘，以及权限体系中的所有相关资源。④亮点功能作为数据报表系统，除了看板/大屏、上下滚动钻取、环比等常规功能外，还重点支持以下重要功能。多指标计算对于现有指标PV和UV，需要对PV/UV进行两次计算，得到新的人均浏览量指标。针对特定（或批量）维度进行监控报警，设置任意数量的指标组合规则报警，支持向去哪儿内部即时通讯QTalk和企业微信发送报警信息。血统信息用户可以在看板中查看某个指标或某个图表的上游血统信息，包括底表生产信息、底表质检信息、底表准入信息，使血统信息一目了然一目了然，数据还可以提升。可靠性。数据问题也易于定位，提高解决问题的效率。3）OLAP模块①需求场景在原有实时OLAP模块的基础上进行升级，以酒店CRM系统的数据部分为例。节庆期间，运营、销售、管理等角色的用户需要在活动期间实时分析各维度的主管酒店各项数据指标，从而做出战略调整和决策。具体形式如上图（部分截取）。针对酒店用户，可任意选择20多个维度近100个指标，要求3秒内显示结果展示图表。通过对需求的详细分析和归纳，得到以下技术挑战：任意多个维度的UV指标的聚合计算必须保证准确的去重，所以无法进行预聚合，数据只能存储为详情即时查询；酒店维度表通常思路可以做成一个大而宽的表来提前关联，比如酒店所在的城市，但是对于动态信息字段比如HOS分行会经常变化，要求是得到查询时间段最后一天酒店的HOS评分，只能在查询时获取。维表实时关联；需要按照维度指标的任意排列组合进行查询，要求3秒内出结果，但整体数据量上百亿。②引擎选型引擎选型调查ES、Presto、Kylin之前对比过ES、Presto、Kylin，结论是不适用当前场景。本次选型主要对比了目前使用的ApacheDruid、Impala和新加入的ApacheDoris、ClickHouse。ApacheDruid对实时数据有很好的支持，基于数据预聚合模型的查询性能强，但是当前场景下用户的查询灵活性非常高，而且数据要存储为明细，并且需要计算UV等精准去重指标，必须关联Dimension表查询，所以Druid不是最好的选择，以后也没有必要参与实际的性能测试；Impala对我们来说最大的优势就是可以和离线数仓中已有的数据无缝集成，不需要导入操作，所以查询性能受制于底层存储系统HDFS，尤其是对于复杂的SQL场景，性能下降严重，性能不达标；ApacheDoris比较适合Druid和Impala，支持MySQL访问协议，也支持高并发高吞吐查询，并且响应及时，但是在当前场景需要百亿级的情况下性能略逊于ClickHouse数据，其社区与ClickHouse相比并不活跃和成熟。对Impala、Doris、ClickHouse三个引擎进行Benchmark，保证相同的数据表（真实的数据和需求相关的量级）和相同的SQL（根据需求的实际需要编写），对每个引擎做简单的测试（Impala使用Parquet，ClickHouse使用MergeTree表引擎），多次查询平均结果如下：通过直观的性能对比结果，ClickHouse的查询性能非常好。另外，实际测试发现，随着查询指标数量的增加，ClickHouse的性能也会受到影响。它不是很大。结合我们的实际场景需求（主打宽表查询，小表join），硬件条件，开发成本，行业经验综合对比，ClickHouse成为了一个不错的选择。③将架构设计数据写入离线数据，通过衍生平台Waterdrop从Hive导入ClickHouse；通过衍生平台ClickHouse的KafkaEngine从Kafka实时消费实时数据，然后物化视图将数据实时写入到MergeTree的单机表中，然后基于此构建一个分布式表。数据读取用户可以在页面上查看自己想要看到的维度和指标，将查询提交给数据查询服务，然后解析组装查询SQL，提交给ClickHouse执行SQL，最终得到结果数据和返回到前端页面以图表形式显示。④场景应用及优化整个集群部署如上图所示。访问入口由Nginx负载均衡。CHProxy代理用于管理集群用户、限制并发和设置请求超时。集群的大部分分布式功能都需要通过ZooKeeper来实现。结束。结合CRM项目本身的需求和性能要求，设计了两张表。总体原则是充分利用ClickHouse强大的单机计算性能优势。第一种分布式表通常用于存储指标数据和关联的维度字段（如订单流数据中添加的日期、酒店维度字段）。这种表通常数据量很大（TB甚至PB级别），需要跨多台机器分布式存储。分布表需要通过设置shardingkey来确定。由于涉及查询优化，shardingkey最好对应场景中出现频率最高的查询维度（如date），以保证同组维度数据在groupby时一定在同一张表中物理机，通过修改配置distributed_group_by_no_merge=1，将所有聚合转化为本地操作，避免了额外的网络开销，提高了查询性能。第二种单机表通常用于存储非静态维度表。这种类型的维度表包含随时间更新的维度（例如酒店星级、HOS评级等）。加盟运营。通过设置一表多备，我们让每台机器都持有全量的一致维表数据，这样Join时ShuffleJoin可以优化为LocalJoin（因为每个JoinKey对应的右表全量数据必须是本地的）以提高查询的整体性能。4)标准化指标基于OneData方法。数据仓库建模最终通过指标体系产生标准化的指标，有定义，有统一的口径。数据仓库同学负责标准化的指标数据。QBI各模块从指标体系中获取标准化的数据，或者进行分析、展示，保证大家看到同一个指标时数据是一致的，从根源上进一步提高了数据的可信度。具体关系详见下图。①数据分析场景数据分析模块引入指标体系管理的DIM表和DWD展示精细数据，获取指标体系构建的原子指标、复合指标、派生指标信息。用户在分析事件时可以在指标体系中自由选择标准化指标，实际查询对应的底层调度进行分析，效果如下图所示。②将数据报表场景指标体系生产的标准化ADS表通过衍生平台导入数据报表模块，根据指标体系定义的维度指标自动生成数据模型。在图表中可以查看底层表格和指标的来源信息，使用效果如下图所示。5）互联互通QBI形成了多模块的全场景数据消费形态，但各模块之间不是分离而是互联，关系非常紧密。标准化指标体系的核心如下图所示。数据分析模块根据指标体系产生的明细表和衍生指标、复合指标、原子指标进行深度探索和分析。分析的固化结果可以保存到数据看板模块的数据报表模块中，用于展示指标系统输出的业务指标数据。从数据分析模块保存的看板可以返回到数据分析模块继续探索和分析即席查询模块，可以直接查询索引系统建模产生的数据仓库表，SQL结果可以直接发送至邮件报表，也可固化保存至数据看板模块QBI各模块可从指标体系获取标准化数据，反之亦可返回指标体系查看指标元信息。3、QBI总结QBI目前服务于去哪儿的十几个业务线，整体MAU为3000。已经形成了比较完整的产品矩阵，包括以下几个场景：即席查询模块，适合自由编写SQL流水号；每日执行次数为5000次，平均执行时间在一分钟以内。邮件报表模块适用于免费编写SQL自助式邮件报表。简单的数据需求，不用说完全可以自己完成；OLAP模块适用于对某一业务数据维度指标进行多维分析和即时响应，目前支持百亿条明细数据和上百个维度指标，2秒内查询P99。数据分析模块适用于数据自由探索，如上下滚动钻取深度分析，8秒查询P95；数据报表模块适用于丰富的数据图表展示和日常业务指标。2000MAU，10000+可视化图表，1秒展示数据P90。四、未来规划1、移动BI基于公司自主研发的IM工具，支持订阅数据看板、交互式数据分析、业务指标监控告警等，随时随地关注业务数据动态。2.QBI各层与抽象QBI的集成。QBI的每个模块都是从历史上的实际业务需求出发开发的。虽然它已经形成了一个体系，但是从抽象的角度来看，数据访问层、引擎层、查询层可以合并相似的项，抽象出公共的组件化服务，降低维护成本。3.丰富的数据分析场景目前数据分析模块对事件分析和漏斗分析的支持比较好，未来可以扩展更多的用户分析场景，比如留存分析、归因分析、分布分析、用户路径分析等。支持全业务各种细分场景需求，助力业务决策。
