摘要：随着互联网广告的发展，广告营销成为了互联网公司的重要变现手段，广告 数据分析处理的研究也因此引起了广泛关注。论文以短视频平台广告业务数据为 基础，设计并实现了可视化分析的数据报表系统。

短视频平台广告数据相比于传统网站具有数据量更大、数据处理时效性要求 更高等特点，如何对其存储并实时计算得到关键性指标是本文研究的重点。传统 数据库无法有效存储处理海量历史数据，而 Hive、Spark 等大数据平台无法做到 数据实时查询分析，基于此背景，系统采用高性能列式数据库 ClickHouse 作为报 表存储引擎，为系统提供底层数据存储查询能力。

系统主要功能是数据报表展示，根据业务具体需求，系统为用户提供了多维 取数、主题报表、自定义报表等数据功能以及权限控制、数据下载等基础功能， 使用户即可以享受专有的主题报表服务，又可以灵活自由配置报表展示条件和指 标以满足自助查询需求，此外，权限控制可以保障系统功能和数据的安全，数据 下载方便用户对数据进行汇总和归档存储。

系统为了提升报表性能做了以下优化：一是面对多字段匹配筛选查询场景， 使用 Elasticsearch 搜索引擎预存储筛选字段加速取数过程；二是设计基于时间片 的汇总指标缓存方案，结合最小代价缓存替换策略实现汇总指标的快速查询；三 是创建维度和时序物化视图存储报表预聚合数据，并设计实现基于“贪心”思想的 视图路由策略，降低报表整体响应时间。

在经过方案设计、功能实现和系统测试后，系统稳定运行，并为用户提供了 便捷、高效的数据实时查询、分析服务。

关键词：广告业务，数据报表，ClickHouse,物化视图

Abstract

With the development of Internet advertising, advertisement marketing has become an important method for Internet companies, and research on advertising data analysis and processing has also attracted widespread attention. Based on the advertising business data of the short video platform, this thesis designs and implements a data reporting system for visual analysis.

Compared to traditional websites, data of short video platform advertisement is greater, content is richer, and processing timeliness is higher. How to store it and calculate it in real time to obtain key indicators is the focus of this thesis. Traditional databases cannot effectively store and process large historical data, and big data platforms such as Hive and Spark cannot perform real-time data query and analysis. Based on this background, the system uses a high-performance columnar database ClickHouse as the report storage engine to provide the system with underlying data storage and query ability.

The main function of the system is the display of data reports. According to the specific business needs, the system provides users with data functions such as multi-dimensional data retrieval, theme reports, and custom reports, as well as basic functions such as permission control and data download, so that users can enjoy exclusive themes. The report service can flexibly and freely configure report display conditions and indicators to meet the needs of self-service query. In addition, permission control can ensure the security of system functions and data, and data download is convenient for users to summarize and archive data.

In order to improve report performance, the system has made the following optimizations: the first is to use Elasticsearch search engine to pre-store and filter fields to speed up the fetching process in the face of multi-field matching and filtering query scenarios; the second is to design a time-slice-based summary indicator caching scheme, combined with minimum cost caching The replacement strategy realizes quick query of aggregated indicators; the third is to create dimension and time series materialized views to store report pre-aggregated data, and to design and implement a view routing strategy based on the "greedy" idea to reduce the overall response time ofthe report.

After program design, function implementation and system testing, the system runs stably and provides users with convenient and efficient real-time data query and analysis services.

Keywords: Advertisement Marketing, Data Report, ClickHouse, Materialized view

目录

摘要......................................................................  i

Abstract.........................................................................................................................  I

目录.....................................................................  III

图目录...................................................................  V

表目录................................................................... VII

第 1 章 绪论..............................................................  1

1.1 研究背景及意义......................................................  1

1.2 国内外研究现状......................................................  2

1.3 研究问题与创新......................................................  4

1.4 本文主要工作........................................................  5

1.5 论文组织安排........................................................  6

第 2 章 关键技术介绍......................................................  7

2.1      OLAP...............................................................................................................  7

2.2      gRPC................................................................................................................. 8

2.3      KBean..............................................................................................................  8

2.4      ClickHouse......................................................................................................  9

2.5      Elasticsearch..................................................................................................  10

2.6      本章小结..........................................................  11

第 3 章 系统需求分析....................................................  12

3.1 系统业务背景......................................................  12

3.2 系统需求背景及目标................................................  13

3.3 系统功能性需求分析................................................  13

3.4 系统非功能性需求分析..............................................  15

3.5 本章小结...........................................................  16

第 4 章 系统总体设计....................................................  17

4.1 系统架构设计......................................................  17

4.2 系统功能模块设计..................................................  19

4.3 系统数据存储设计..................................................  20

4.4 本章小结...........................................................  26

第 5 章 系统功能设计与实现..............................................  27

5.1 多维取数模块......................................................  27

5.1.1      字段和指标设计................................................  27

5.1.2      取数流程实现..................................................  28

5.2 主题报表模块......................................................  30

5.2.1      报表主题划分..................................................  30

5.2.2      主题报表实现..................................................  31

5.3      自定义报表模块....................................................  34

5.3.1      自定义元数据采集..............................................  36

5.3.2      自定义报表配置................................................  37

533自定义SQL模板生成.............................................. 38

5.3.4 自定义报表模板调用............................................  41

5.4      权限管理模块......................................................  42

541基于RBAC的功能权限............................................ 42

5.4.2      数据权限设计实现..............................................  44

5.5      数据下载模块......................................................  45

5.6      本章小结..........................................................  47

第6 章 系统查询性能优化.................................................  48

6.1     基于缓存的优化....................................................  48

6.1.1     汇总指标缓存设计..............................................  48

6.1.2      最小代价缓存淘汰策略..........................................  50

6.1.3      查询缓存执行过程..............................................  50

6.1.4      缓存优化结果对比..............................................  51

6.2     基于物化视图的优化................................................  52

6.2.1     物化视图方案设计..............................................  53

6.2.2      物化视图划分与创建............................................  54

6.2.3      物化视图请求路由策略..........................................  56

6.2.4      物化视图优化结果..............................................  60

6.3     本章小结..........................................................  61

第7 章 系统测试.........................................................  62

7.1 系统运行环境......................................................  62

7.2     系统功能性测试....................................................  63

7.3     系统非功能性测试..................................................  71

7.4     本章小结..........................................................  73

第8 章 总结与展望.......................................................  74

8.1     工作总结..........................................................  74

8.2     未来展望..........................................................  75

参考文献.................................................................  76

作者简历.................................................................  79

致谢.....................................................................  80

图目录

图 2.1       OLAP 架构结构图...........................................  7

图 2.2       ClickHouse 架构核心模块图 .................................  9

图 3.1       报表系统功能用例图 .........................................  14

图 4.1       报表系统架构图 .............................................  17

图 4.2       报表系统功能结构图 .........................................  19

图 4.3       自定义报表功能 ER 图 .....................................................................  21

图 4.4       权限管理功能 ER 图 ........................................................................  22

图 5.1       多维取数执行流程图 .........................................  29

图 5.2       多维取数请求执行时序图 ....................................  29

图 5.3       主题报表功能报表主题划分图 ................................  30

图 5.4       主题报表实现流程图 .........................................  31

图 5.5       主题报表实现时序图 .........................................  33

图 5.6       自定义报表架构图 ...........................................  34

图 5.7       自定义报表流程图 ...........................................  35

图 5.8       元数据采集操作图 ...........................................  36

图 5.9       元数据管理页面 .............................................  36

图 5.10     自定义指标配置图 ...........................................  37

图 5.11     自定义报表筛选配置图 ......................................  37

图 5.12     where 语句节点森林图               38

图 5.13     筛选条件配置图 .............................................  41

图 5.14     系统功能权限模型图 .........................................  43

图 5.15     功能权限实现时序图 .........................................  43

图 5.16     系统数据权限模型图 .........................................  44

图 5.17     数据权限实现时序图 .........................................  45

图 5.18     数据下载功能架构图 .........................................  46

图 5.19     数据下载实现时序图 .........................................  47

图 6.1       数据报表汇总指标图 .........................................  48

图 6.2       汇总指标缓存方案设计图 ....................................  49

图 6.3       缓存优化查询结果对比柱状图 ................................  51

图 6.4       缓存优化查询耗时和命中率对比图 ............................  52

图 6.5       维度物化视图设计图 .........................................  53

图 6.6       时序物化视图设计图 .........................................  54

图 6.7       ClickHouse 物化视图架构图 .................................  56

图  6.8  时序物化视图路由选择图.....................................  58

图  6.9  时序物化视图查询结果对比图 ..................................  61

图  7.1  报表系统微服务架构图.........................................  62

图  7.2  多维取数配置页面............................................  64

图  7.3  多维取数数据展示页面........................................  65

图  7.4  大盘分析报表总花费展示图...................................  66

图  7.5  代理商报表代理商分类消耗趋势图.............................  66

图  7.6  品运效率报表效率指标柱状图 ..................................  66

图  7.7  自定义报表模板列表图........................................  67

图  7.8  自定义报表模板数据展示图...................................  68

图  7.9  用户功能权限配置图..........................................  69

图  7.10  用户数据权限配置图.........................................  69

图  7.11 下载任务管理界面.............................................  70

图  7.12  多维取数模块 QPS 监测图.................................... 72

图  7.13  主题报表模块各报表 QPS 监测图.............................. 72

表目录

表 1.1      中国短视频用户规模及使用率表 ...............................  1

表 4.1      下载任务 TaskInfo 表......................................................................  22

表 4.2      Elasticsearch 账户宽表 ......................................  23

表 4.3      ClickHouse 广告创意宽表 ...................................  24

表 5.1      取数筛选字段表 .............................................  27

表 5.2      取数数据指标表 .............................................  28

表 5.3      主题报表请求主要参数表 .....................................  32

表 6.1      广告创意宽表维度物化视图表 .................................  55

表 6.2      广告创意宽表时序物化视图表 .................................  55

表 6.3      维度物化视图查询结果对比表 .................................  60

表 7.1      报表系统运行环境表 .........................................  63

表 7.2      报表系统使用软件及版本表 ...................................  63

表 7.3      多维取数功能测试用例和测试结果表 ..........................  63

表 7.4      多维取数功能测试用例和测试结果表 ..........................  65

表 7.5      自定义报表功能测试用例和测试结果表 ........................  67

表 7.6      权限管理功能测试用例和测试结果表 ..........................  68

表 7.7      多维取数功能测试用例和测试结果表 ..........................  70

表 7.8      系统查询性能测试结果表 .....................................  71

表 7.9      多维取数并发测试结果表 .....................................  72

表 7.10 系统安全性测试用例和测试结果表 ..............................  73

第 1 章 绪论

系统来源于作者在某短视频公司实习过程中参与的项目，主要功能是提供广 告业务数据可视化查询分析能力，作者参与了系统从技术选型到方案设计最后 到代码开发的整个过程。系统以公司广告营销数据为基础，以业务需求为依据， 以图表为数据展现形式，进行研究、设计和实现。

1.1 研究背景及意义

根据中国互联网络信息中心［1］数据显示：截至 2021 年 6 月，我国网民规模 达 10.11 亿，较 2020 年 12 月增长 2175 万，互联网普及率达 71.60% ，其中短视 频用户规模达 8.88 亿人，首次超越了网购用户的 8.12 亿规模，8.88 亿用户的庞 大规模，相当于整体中国网民规模的 87.80%，短视频用户数量具体如表 1.1 所 示，可以看出，刷短视频正在成为全民最热的休闲娱乐方式。

表 1.1 中国短视频用户规模及使用率表

对短视频公司而言，商业变现的主要途径就是广告营销业务，因此广告业务 的快速发展对公司来说至关重要［2］。面对用户每天浏览短视频 APP 产生的广告 链路日志，埋点统计的广告位点击、曝光日志，广告投放端生成的竞价、消耗、 计费日志等海量数据，如何有效地存储和统计分析数据中关键指标一直以来是 短视频广告业务发展过程中的难点。

短视频平台投放的广告一般为视频形式的效果或品牌广告，用户在浏览短视 频时，广告投放系统根据用户标签、行为记录和内容上下文等信息为用户匹配 感兴趣的广告进行展示，以达到广告收益最大化的目的［3］。通过记录用户观看、 点击广告以及跳转至落地页进行的下载、付费等行为日志，同时和广告消耗、 计费等信息经过ETL处理之后一同写入数据仓库，以满足上层应用系统实时分 析计算广告消耗、用户体验和转化效果等指标的需求。

不同于传统网站中的展示广告或搜索广告，短视频广告存在于短视频信息流 中，展示灵活且内容丰富，为了不影响用户的使用体验，需要控制广告展示频 次并及时精准的投放用户感兴趣的广告，这就要求短视频平台可以在短时间内 统计分析广告相关日志数据，从中得到关键性指标，并将结果及时、准确、清 晰的展现给业务人员，以帮助商家快速调整广告投放策略，制定收益更高的广 告创意和投放计划，实现短视频平台、用户和商家的互利共赢。

基于上述背景，本文设计并实现了面向短视频平台广告业务的数据报表系 统。主要研究重点为：1）如何及时有效的存储数据，实时提取、统计、分析和 计算数据中的关键指标；2）如何为用户提供即席查询服务；3）如何实现自定 义配置报表；4）如何将数据指标查询时间控制在秒级别。除此之外，本文研究 还包括灵活的权限控制和持久化的数据下载功能的实现。系统旨在提供专属的、 实时的、持久的广告数据可视化服务，实现为分析人员提供辅助决策支持和提 升业务人员工作效率，促进平台广告业务可持续发展。

1.2 国内外研究现状

短视频的快速推广，给很多商家提供了广告投放的途径，为了更好的促进短 视频平台广告业务的发展，需要对海量广告数据进行实时分析处理，做到即席 查询，秒级响应，并以多样化、自动化的图表形式展示，来帮助业务人员更好 的开展工作，这也是很多国内外学者和工程师的研究重点。

文献［4］探索开源商业智能 BI 的可用性并比较常见的开源工具：提取转换负 载（ETL）工具、数据库管理系统（DBMS）、联机分析处理（OLAP）服务器/ 客户端等。文献［5 ］对多维数据库（MDD ）的原理做了解释，并对联机分析处理、 报告视图、预测情景三大能力进行了综述。文献［6］研究了市面上主流的 BI 工具, 通过特定结构的输入并分析其输出，从而推测其内部可能的 SQL 生成规则，实 现结合 BI 工具的 OLAP SQL 语句生成系统。文献［7］概述了新颖的 BI 方法和相 关的 BI 解决方案，在个人计算机上执行小规模分析场景，设计实时大数据分析 场景上的架构维度，并对架构维度进行了特征比较。

文献［8］对 ClickHouse 和 MySQL 进行了分析，二者作为面向列和面向行的数 据库，从执行时间、处理能力、磁盘 I/O 进行了全面的对比，结果表明 ClickHouse 在大数据量场景资源充足条件下，性能远超过MySQL。文献［9］通过综合评述现有的数据仓库，认为在OLAP数仓下，使用ClickHouse可以满足大数据场景下 数据分析的快速响应要求。文献［10］通过分析和优化同一主机上的微服务通信， 对三种流行的通信协议：REST、gRPC、Thrift在网络、内存、CPU利用率和响 应时间四个方面进行了测评，结果表明：REST和gRPC由于压缩性更高，因此 传输速度更快，性能更佳。文献［11］认为在面对高速增长的数据，高效的数据分 析工具极其重要，给出了 Elasticsearch 在搜索、存储分析下的优势。文献［12］发 现海量数据存储分析场景下，经常需要模糊查询且对检索实时性要求比较高， 通过对 Elasticsearch 数据库进行测试分析，认为其是一个优秀的海量数据存储引 擎，底层采用倒排索引设计，可以提高全文搜索的速度。

文献［13］提出了一种利用 Top-k 查询算法选择物化视图的方法，通过选择基 于查询频率，改善了沿袭跟踪查询的启发式算法更新视图速度缓慢的不足。文 献［14］采用增量学习朴素贝叶斯算法设计了一种 OLAP 客户端缓存机制，通过根 据用户最近的操作决定是否缓存当前查询结果，以降低平均查询时间和提高缓 存命中率。文献［15］提出了一种新的 OLAP 支持网格的查询处理方法，该方法核 心是查询片段聚合和重组（FAR）策略，该策略将OLAP查询划分为子查询， 从而可以通过检索附近的网格源，聚合多个缓存数据片段，实验验证该方法将 查询时间减少了 50%左右。文献［16］针对大数据决策中数据类型异构、计算量大 的问题，提出了一种优化的分布式大数据 OLAP 系统，通过元数据自动配置和 开启 OLAP 查询缓存，系统的效率明显优于传统的解决方案。文献［17］提出了内 存索引结构的选择性缓存，基于DRAM中树节点动态和静态缓存混合，以达到 索引结构接近 DRAM 的访问速度。文献［18］通过考虑时间和空间的复杂性，构 建一种 MV table 的视图，可以将为 avg 事务中的常用查询提取的最新数据存储 到专用空间中，试图减少查询的访问时间。文献［19］提出了一种新的物化视图维 护方案，该方案利用马尔可夫分析来保证性能优化，通过分析初始概率和稳态 概率，来预测查询的变化，针对性的创建视图。文献［20］提出了一种基于索引和 物化视图的最佳动态选择方法，并探索出一个最优的适应基本数据修改的解决 方案，作用于物化视图、索引、分段和缓冲区，经过优化查询负载，对相似结 构的多重选择进行动态调优。

1.3 研究问题与创新

本文研究的问题是如何对短视频平台产生的海量广告数据进行实时分析，并 以多样化图表形式展示，最终设计可视化分析的数据报表系统。

通过对传统报表系统和现如今流行的BI［21 ］系统进行调研发现：传统报表系 统一般采用 MySQL 等数据库存储底层数据，数据规模较小且无法查询历史数 据；而 BI 系统，一般采用 Hive 数仓存储数据和 Spark 作为数据分析处理平台［22］， 支持海量规模数据存储，但是不能很好满足对数据进行实时查询分析。

报表系统存储的广告业务数据来自于各业务系统存储的业务维度数据，和投 放端产生的广告日志经过ETL［23］处理之后的广告指标数据，其中后者每日新增 数据规模在两亿左右。经过调研发现市面上有多款 OLAP 引擎满足此规模数据 的存储分析需求，如 Vertica［24］、 Greenplum［25］、 ClickHouse 等，其中 ClickHouse 性能优势明显［26］，因此本文选择 ClickHouse 作为报表引擎。

ClickHouse 查询性能出色，但是在面对某些查询时，依然无法做到秒级响应。 如查询客户近一年的消耗数据，底层会扫描近一年的所有分区数据，涉及数据 量过于庞大，无法短时间得到结果，且会耗费大量的计算资源和网络带宽，此 外，由于 ClickHouse 采用列式存储，所以不适合做明细数据匹配查询。

针对上述问题，本系统设计并实现了相应的优化方案： 1）针对汇总指标， 系统设计了基于时间片的数据指标缓存方案，结合最小代价缓存替换策略实现 汇总指标快速查询；2）针对多维度的明细数据和指标，系统设计了物化视图以 及基于“贪心”思想的视图路由策略进行优化，效果显著；3）针对 ClickHouse 多字段匹配查询性能较差，系统引入 Elasticsearch 来存储明细数据，用 ClickHouse 存储指标数据，采用二段式查询提升查询性能。

除此之外，系统还对功能模块做了创新应用： 1）针对自定义配置，系统基 于API接口 +SQL模板形式实现自定义配置报表，提供HTTP和RPC双接口， 使前端和服务端皆可调用；2）使用RBAC+组织架构模型实现功能权限，将组 织架构、人户关系和维度字段三者结合实现数据权限控制，为用户提供更加精 细化的权限配置与管理； 3）以下载任务形式实现报表数据导出，实现定时下载、 任务调度和大数据量分片下载。

1.4 本文主要工作

论文主要工作内容包括以下 5 个方面：

（ 1）调研分析短视频平台广告业务数据报表背景与意义。本文分析了短视 频市场规模及潜力，认为广告业务对短视频公司的发展至关重要，所以广告业 务数据可视化分析很有必要，本文基于此提出了广告业务数据报表系统。

（ 2）技术方案制定。系统展示数据来源于数据仓库，包括业务系统（ DSP、 CRM）产生的基本数据（客户、代理商和广告主信息等）和投放端（ADX、投 放平台）产生的广告相关日志数据（计费日志、广告链路日志），在此背景下， 系统采用 Elasticsearch 存储代理商、客户和广告主明细数据，为多维取数模块即 席查询服务提供多字段匹配查询能力。采用 OLAP 引擎 Clickhouse 存储数据仓 库中广告相关日志聚合处理后的离线和实时数据，并以宽表形式存储，为报表 系统提供数据的快速实时计算和分析处理。系统采用 gRPC 作为系统的微服务框 架， Spring Boot 作为系统开发框架，使用 Java 作为开发语言以及公司自研的 KBean作为系统操作数据库的持久层框架等。

（ 3）系统主要功能设计与实现。经过系统需求分析之后，依据用户的具体 需求确定系统的功能模块，主要划分为多维取数模块、主题报表模块、自定义 报表模块、权限管理模块和数据下载模块。本文为上述功能模块设计了详细的 技术方案，通过对比行业内的实现方式，分析其优缺点，并结合自身的能力和 资源条件，设计切合本公司广告业务的技术方案，最终实现落地。

（ 4）系统查询性能优化。经过对系统查询性能问题调查分析，并调研业界 的解决方案，系统设计了指标缓存方案和物化视图+贪心路由策略对系统查询性 能进行优化。在报表查询条件不确定情况下提出了基于时间片的缓存优化方案， 并结合最小代价缓存淘汰策略，实现汇总指标的毫秒级查询；根据具体场景设 计并划分物化视图来存储预聚合数据，并基于贪心思想设计物化视图的路由算 法，最终实现报表查询性能的整体提升。

（ 5）系统测试。对系统进行功能性和非功能性测试，保证系统功能正常可 用，安全可靠，通过设计测试用例检验各功能点是否完善，以及系统整体性能 和安全性是否到达要求，经测试发现系统功能正常，整体运行状况良好。

1.5 论文组织安排

第一章是绪论，首先介绍了报表系统研究背景及意义和国内外研究现状；其 次介绍本文研究的难点问题，以及如何创新实现；接着介绍论文撰写过程中的 工作内容；最后介绍论文结构和组织安排。

第二章是关键技术介绍，首先介绍了 OLAP 概念，因为报表系统是 OLAP 技术的典型应用；其次介绍本文使用技术及其特点，包括微服务框架gRPC，数 据库持久层框架KBean, OLAP引擎ClickHouse,实时搜索引擎Elasticsearch。

第三章是系统需求分析，对报表系统业务背景、需求背景和建设目标进行说 明。系统需求从使用角度可划分为功能性需求和非功能性需求，功能性需求包 括：多维取数、主题报表、自定义报表、数据下载和权限管理，非功能性需求 分析从系统性能和安全性等方面进行说明。

第四章是系统总体设计，介绍了系统架构设计、系统功能模块设计、数据存 储设计。系统架构采用分层模型实现，系统功能模块依据用户需求设计划分为 5 个主要模块，系统存储使用 MySQL 作为系统数据库，使用 Elasticsearch 作为取 数模块搜索引擎，使用 ClickHouse 作为数据实时计算分析引擎。

第五章是系统功能设计与实现，首先介绍多维取数功能和主题报表功能的设 计与实现，接着介绍自定义报表模块的组成部分和自定义报表实现流程，包括 元数据采集、自定义配置、 SQL 模板和模板调用等步骤，最后介绍系统功能和 数据权限管理以及报表数据下载等基础功能设计实现。

第六章是系统查询性能优化，首先介绍了针对汇总指标的基于时间片的缓存 方案和基于最小代价的缓存淘汰策略，然后介绍了针对一般维度数据和指标的 物化视图优化方案以及基于贪心思想的路由策略。经过实践证明，上述两种方 案相结合可以大幅提升系统报表的整体查询性能。

第七章是系统测试，首先介绍系统的运行环境和软件版本，其次对系统功能 模块编写测试用例进行测试，并展示系统功能对应的页面应用效果，最后是测 试系统性能和安全性是否满足要求。

第八章是总结与展望，总结本次工作的收获，指出论文研究的优点和不足， 介绍报表系统除论文中提到的其他实现方案，最后对报表系统和相关技术的应 用和发展作出展望。

第 2 章 关键技术介绍

2.1      OLAP

OLAP全称On-Line Analytical Processing,中文名“联机分析处理"，由 E.F.Codd于1993年首次提出［27］。随着人们对数据分析的需求日益增加，Codd认 为传统的联机事务处理（Online Transaction Processing, OLTP ）通过SQL语句 对数据库进行查询分析操作，已经不能满足用户对历史数据和批量数据的分析 需求，从而正式提出了多维分析的概念，即 OLAP。

简单来讲， OLAP 是数据分析方法中的一种，是指通过从数据库或数据仓库 中抽取数据子集，经过必要的转储、计算、聚合，最终以图或表的形式进行结 果展示。OLAP处理的结果一般是整合的历史数据，这些结果往往可以反映出企 业某个阶段的业务指标，故可被用于为领导者提供针对性的决策支持， OLAP 系 统一般以数据仓库作为底层数据存储方式［28］。

典型的OLAP系统通常包含数据源、数据存储、OLAP分析服务以及数据应 用四个部分，如图 2.1 所示：

图 2.1 OLAP 架构结构图 

（1） 数据源：是 OLAP 系统分析处理数据的来源，一般来自于企业内部的 业务系统，包括以数据库存储的结构化数据和文件存储的非结构化日志数据。

（2） 数据存储：主要负责对源数据进行抽取、清洗、转换、加载，并按照 主题域重新组织和集成，存储到数据仓库［29］。

（3）  OLAP 服务器：直接操作数据仓库查询分析数据，已经不能满足用户 灵活多变的需求，用户期望的是即席查询，配置查询条件立刻得到结果，因此 需要引 入高性能 OLAP 引 擎提供实时 分析服务， 主流的 OLAP 引 擎包 括 SparkSQL、Presto、Kylin、Impala、Druid、ClickHouse 等［30］。

（4）数据应用：数据应用即前端页面以图表形式展示分析数据，目前常见 的前端BI工具有BusinessObjects报表界面、QuickView等，主要提供数据查询、 报表展示、数据分析和数据挖掘等功能。

2.2     gRPC

gRPC［31 ］是由 Google 开发的一个高性能开源 RPC（ Remote Procedure Call） 框架， RPC 即“远程过程调用”，是微服务架构服务之间的交互方式。 gRPC 采 用 HTTP/2 协议标准开发，采用 protobuf 作为接口序列化工具和定义语言［32］。 gRPC提供了一整套数据定义和RPC传输的方式，不同于Spring Cloud和Dubbo, gRPC 拥有更好的扩展性和多语言支持等特点。

gPRC 的主要特点： 1）采用 HTTP 2.0 网络通讯协议进行传输，支持双向流、 消息头压缩、共享连接、服务端推送等特性，可以降低带宽、减少能耗［33］； 2） 采用 protobuf 定义接口服务， protobuf 是 Google 开发的高性能数据序列化协议， 采用二进制编码，具有压缩和传输效率高，语法简单，表达力强的特点； 3）支 持跨平台，跨语言，多种语言可以根据脚本自动生成服务端客户端代码， gPRC 支持 C、 C++、 C#、 JAVA、 GO、 Ruby 等多种语言［34］。

2.3     KBean

KBean 是实习所在公司内部研发的数据持久 层框架，不同于 MyBatis 和 Hibernate，其实现是基于 Spring Data 提供的 JPA（Java Persistence API，即 Java 持久化API）开发框架，在其之上封装并简化了持久化操作步骤，提供了配置化、 统一操作多数据源的能力［35］。开发者只需要声明数据源表结构对应的实体 Model，并配置数据源和表的名称即可操作底层数据，另外，其内部集成了操作 MySQL、Druid、ClickHouse 和 Elasticsearch 的 client，并提供了 统一访问接口 API，通过将用户传入的参数转化成各数据源支持的SQL/JSON语句，并调用对 应 client 执行，最终将结果转化成统一的 Bean 形式返回给调用者。

2.4     ClickHouse

ClickHouse［36］是近年来备受关注的开源列式数据库，是搜索引擎公司Yandex 在 2006年源的一款应用于主要用于 OLAP 数据分析领域的数据库。 ClickHouse 使用 SQL 作为查询语言，底层数据存储采用的是基于关系模型的 OLAP 方案， 通过充分利用 CPU 性能实现快速的数据查询分析［37］。

图 2.2 ClickHouse 架构核心模块图 

ClickHouse 架构核心模块如图2.2所示， Column 和 Field 是 ClickHouse 数据 最基础的映射单元,ClickHouse按列存储数据，内存中的一列数据由一个Column 对象表示， DataType 负责数据的序列化和反序列化工作， IDataType 接口定义了 许多正反序列化的方法，它们成对出现，涵盖了常用的二进制、文本、 JSON、 XML、CSV和protobuf等多种格式类型。ClickHouse提供了普通函数和聚合函 数两种函数，普通函数由 IFunction 接口定义，提供如四则运算等常见操作，聚 合函数由 IAggregateFunction 接口定义，提供 sum、 avg 等聚合操作。

Parser 和 Interpreter 是非常重要的两组接口， Parser 分析器负责创建 AST 对 象，而Interpreter解释器则负责解释AST，并进一步创建查询的执行管道，它们 与Storage 一起，串联起了整个数据查询的过程。Parser分析器可以将一条SQL 语句以递归下降的方法解析成 AST 语法树的形式。

ClickHouse内部的数据操作采用Block流形式，Block对象可以看作数据表 的子集，其本质是由数据对象、数据类型和列名称组成的三元组，即 Column、 DataType及列名称字符串。流操作有两类顶层接口： IBlocklnputStream负责数 据的读取和关系运算， IBlockOutputStream 负责将数据输出到下一环节。

ClickHouse 是一款 MPP 架构的列式存储数据库，它具有以下核心特性［38］： （ 1）数据压缩 列式存储可以大幅减少查询时数据扫描范围，提高查询速度，另外，数据 按列进行组织，可大幅度提升数据压缩效果。 ClickHouse 采用默认的 LZ4 算法 压缩，压缩比可达到 8:1，极大降低了网络/磁盘 IO 和存储压力。

（ 2 ）向量化执行 向量化执行可以看做是消除程序中循环的优化，实现多行数据并行计算，为 了实现向量化执行，需要利用 CPU 的 SIMD（ Single Instruction Multiple Data） 指令，即用单条指令操作多条数据，底层是通过 CPU 寄存器层面实现数据并行 操作以提高性能。

（ 3 ）分布式计算 采用分布式架构，预先将数据分布到集群中多台服务器之上，自动将查询任 务分解成多个子任务随机下发到集群中进行多计算机并行处理，最终将处理后 的数据收集并合并成最终结果返回给用户。

（ 4）多主架构

不同于 HDFS、 Spark 和 Elasticsearch 这类采用 Master-Salve 主从架构的分布 式系统， ClickHouse 采用 Multi-Slave 多主架构，集群中每个节点功能相同，访 问任意节点都可以得到相同效果，从而规避了单点故障问题。

ClickHouse 性能超过了市面上大部分列式存储数据库，在 100 万和 10 亿 的数据集上， ClickHouse 比 Hive 快几百倍，比 Vertica 约快五倍，而且 ClickHouse 支持灵活多变的多维数据统计分析场景。

2.5     Elasticsearch

Elasticsearch （简称ES）是一个基于ApacheLucene的开源分布式搜索引擎 ［39］。 Lucene 是开源的搜索引擎包，允许你通过自己的 Java 应用程序实现搜索功 能， ES 对 Lucene 进行了扩展，使存储、索引和搜索都变得更快、更容易，最重 要的是提供了分布式的能力，可以实现分布式存储数据和并行计算，此外 ES 提 供了 RESTful 风格的 API 和 JSON 格式的查询语法，可方便用户快速上手使用。 ES 也可以作为 OLAP 引擎，为用户提供多维数据分析能力［40］。

ES 使用 Java 语言编写，具有以下几个优点： 1）易扩展，集群扩展非常容易， 增加服务器节点，简单配置即可并入集群；2）高可用，提供主从复制能力，主 节点数据分片可以设置多个从片，当主节点服务器发生故障时，集群依然可以 正常提供服务；3）分布式，索引可以分成多个分片，存储在不同机器上，通过 分而治之的方式提升处理效率；4）速度快，采用倒排索引，将索引存入内存， 海量数据查询可以做到秒级响应［41］。

2.6 本章小结

本章介绍了系统涉及的概念以及使用的技术。第1节介绍OLAP技术概念、 组成部分和应用场景。第 2 节至第 5 节介绍了系统使用技术及其特点，包括微 服务框架gRPC，数据持久层框架KBean，高性能数据查询分析引擎ClickHouse 和大数据搜索引擎 Elasticsearch。

第 3 章 系统需求分析

3.1 系统业务背景

（ 1）系统广告业务数据 报表系统广告业务数据主要包括广告主体数据和广告投放数据。 广告主体数据，在本系统中指广告账户、客户和代理商。账户是客户在媒体 端投放广告时创建的账号，是投放过程的主体，通过充值购买竞价广告实现投放； 客户是指在短视频平台进行广告投放的商家；代理商即代理有投放需求的客户选 择合作的媒体并制作广告进行投放的公司。广告主体数据即上述三种主体的明细 数据，如代理商名称、客户营业执照、广告账户信息等，还有各主体的负责人信 息，包括：代理商销售/运营负责人、客户销售/运营负责人等。

广告投放数据，是指广告制作完后在媒体端进行投放时产生的数据记录，主 要包括广告消耗数据（现金消耗、前返消耗、后返消耗等）、效果数据（封面曝 光数/点击数、素材曝光数/点击数、 CPM、 CPC 等数据）、用户体验数据（播放 数、点赞数、评论数、举报数等）、落地数据（应用下载数、注册数、激活数、 付费金额、订单支付数等）。除了上述的数据指标，还有运营数据，包括运营负 责的项目数、任务数、排期数、点位数、开票数、工单提交数等。

（ 2）系统主要用户 报表系统的主要用户包括销售、运营、财务和管理层。 销售使用报表系统查看自己负责的代理商、客户和广告主的投放消耗和效果 情况，以及查看自己的业绩数据等。

运营使用报表系统查看自己负责的代理商、客户和广告主的投放消耗和效果 数据，当账户余额不足、客户消耗指标浮动较大或者效果转化情况不及预期时， 随时将信息反馈给客户，并协商制定继续投放策略。

财务通过报表系统查询代理商、客户、广告主的花费情况进行核算，确认订 单和发票信息，方便财务在第一时刻发现问题，减少经济损失。

管理层通过查看大盘数据、消耗总览、行业消耗、消耗趋势等指标，分析广 告业务的发展情况，针对性的制定营销策略，促进广告业务发展。

3.2     系统需求背景及目标

随着在短视频平台投放广告的客户日益增多，广告部门内部积累了海量的客 户信息和广告投放相关日志数据。如何对上述数据进行快速统计分析并提供可视 化途径，从而提升部门销售、运营、财务人员的工作效率，同时也为管理者提供 广告业务相关决策的辅助支持，已然成为了公司业务持续发展的重要问题，在此 背景下，基于广告业务海量数据的报表系统应运而生。

系统目标是创建广告数据可视化图表来提升销售、运营、财务等人员的工作 效率和为管理者提供决策分析，目的是为商家客户提供快速、精准、高效的广告 投放服务，并帮助平台获得最大化的广告营销收益。为此需要设计并实现一个具 备高性能、高可用、功能丰富、易操作等特点的报表系统。

短视频平台广告数据具有数据量大、时效性要求高等特点，数据海量则要求 系统需要采取高效的存储处理技术，时序性高则要求系统能够实时计算分析广告 数据关键指标，因此，系统在使用高效的存储技术下还需要设计查询分析优化方 案，使系统在面对 TB 级别的数据可以做到90%以上指标计算秒级响应，给用户 提供极速流畅的使用体验。

3.3     系统功能性需求分析

如系统数据和 3.1 节用户分析中描述，报表系统的用户是公司内部广告业务 的销售、运营、财务、领导和管理员，他们的需求如下： 1）可以灵活查看广告 数据（广告主体信息及投放花费、效果等指标）；2）可以通过主题域方式查看 专属分类数据；3）可以自定义取数规则和展示指标，以满足一些特殊需求；4） 可以方便可靠的对数据进行下载管理。通过对用户的需求进行分析，以及调研行 业内的相关实现案例，系统为用户提供功能如图3.1 所示：

（1） 多维取数是根据用户配置的筛选条件进行实时查询计算。业界常见做法 是用户自己编写或配置 SQL 来实现，但是存在使用门槛高、多表关联查询实现困 难、性能较差等问题。针对以上问题，系统采用折中的方案：对筛选字段、聚合 字段以及展示指标进行定制开发，用户可自由勾选配置，底层也可以支持复杂的 计算逻辑。此外，为了加速筛选过程，系统引入 Elasticsearch 搜索引擎，配合 ClickHouse 使用，借助 Elasticsearch 优秀的全文搜索能力和 ClickHouse 强大的指 标聚合计算能力，采用“二段式”查询，大幅提升取数性能。

（2） 主题报表是依据业务需求设计开发的报表。本系统中的主题报表通过集 成最底层的基础数据信息，采用特定的布局设计，将数据以表格、折线图、柱状 图和饼状图等形式清晰直观的展示。系统随着主题报表数量增加，存在开发管理 困难的问题，为此，系统设计了主题报表统一服务层收敛报表调用过程，使用工 厂类根据报表类型生成具体实现类并进行调用管理，并提供统一的 RPC 服务接 口，方便查询调用，也便于对主题报表做埋点分析和性能监控。

（3） 自定义报表是指用户自助配置管理的数据报表。在实际应用中，主题报 表不能满足用户所有需求，因此需要为用户提供自定义支持。经过调研发现传统 报表不支持或以简单形式支持用户自定义配置，现代 BI 系统支持灵活的自定义 报表配置，但是需要配合智能前端UI工具使用，开发实现不灵活且复杂，所以 本系统设计了 一种新的基于API接口 +SQL模板的实现方式，配置页面借鉴BI 报表系统，后端使用HTTP和RPC双接口实现，为了避免表和字段的变更导致查 询异常，自定义报表采用实时（在发生调用时）生成 SQL 模板策略，用户可随时 查看配置的 SQL 模板，实现一次配置多次、多处调用展示。

（4） 报表系统简单来说是数据可视化应用系统，除了对数据进行展示外，还 应该具备数据下载功能。行业内报表系统习惯使用前端组件方式导出数据文件， 虽然使用和实现起来简单，但是无法做到大数据量可靠下载、历史行为追踪等。 系统采用生成定时任务形式实现数据下载，此方式可以实现定时（指定未来下载 时间）下载、大文件分片下载、任务调度（重试、终止、设置优先级等）和查看 历史下载等功能，使数据下载更可靠，也防止了非法下载情况发生。

（5） 报表系统展示的是公司广告业务数据，包括客户信息、广告投放消耗和 广告营收等敏感信息，为了避免数据泄露，需要为系统制定安全可靠的权限控制 机制。通过调研发现市面上常见的权限暗访发现不太适合本系统的需求，比如： 权限粒度不够细、配置不够灵活等，基于此本系统将权限分为功能权限和数据权 限，功能权限使用 RBAC 模型+组织架构方式实现，数据权限使用组织架构+维度 字段+人户关系三者结合的方式实现，两种权限配合使用可以满足多业务、细粒 度的权限配置需求，且使用简单易上手，也避免了重复性配置问题。

3.4     系统非功能性需求分析

非功能性需求描述了系统在建设时，必须遵守的约束和必须具备的质量属 性，会影响用户的使用体验以及工作效率。同时，非功能性需求分析也是在研发 人员在设计系统时需要考虑到的，主要是从技术角度分析、设计并实现技术方案。 本系统主要从性能和安全性两个方面考虑。

（1）系统性能需求

性能指标一般包括响应时间、吞吐量、并发量等［45］。并发量即系统在同一时 刻收到的用户请求数量，本文中报表系统的主要用户是公司的销售、运营和管理者等内部员工，员工数量在几百左右，并发量不大。吞吐量是指在单位时间内 CPU 从存储设备读取、处理并存储的数据量，与 CPU 性能、存储介质和网络 IO 相关。 响应时间主要是服务器分析处理数据并返回的时间，与数据量大小、计算逻辑和 网络 IO 密切相关。对于数据量比较大的情况，需要考虑数据存储设计，如添加 索引和增加缓存，若计算逻辑比较复杂，则需要考虑分布式并行计算等。

本系统的核心功能即广告业务指标的可视化展示，性能要求如下：

1）前端页面跳转和组件展示时间在毫秒级别；

2）多维数据多字段匹配查询响应时间在 3 秒内；

3）单指标计算时间控制在 1 秒内，报表整体响应时间不超过 5 秒。

（2）系统安全性需求 系统安全性需求是系统设计开发过程中的重要关注点，尤其是当系统涉及敏 感功能和数据时，需要确保用户有权限访问该功能和数据资源，这就要求用户必 须要经过统一的登录校验方式进行登录，从而确保用户身份的正确性，并为其赋 予专属的功能和数据权限，避免敏感数据发生泄漏，要求如下：

1）严格控制访问权限，不同的用户具有不同的身份和权限；

2） 抵御常见的网络攻击，如SQL注入、XSS和CSRF攻击等；

3） 记录系统运行和用户操作日志，提供监控和报警通知功能。

3.5 本章小结

本章介绍的是系统需求分析。第 1 节介绍了系统业务背景和用户分类。第 2 节介绍系统需求背景以及系统建设目标。第 3 节介绍了系统功能性需求分析，即 用户希望系统应该具备的功能，功能依据业务具体需求进行设计。第 4 节是系统 非功能性需求分析，是对系统性能和安全性方面提出的要求。

第 4 章 系统总体设计

4.1 系统架构设计

报表系统架使用分层结构，根据角色定位和提供能力的不同可分为以下 5 层：访问控制层、数据应用层、OLAP服务层、数据仓库层和数据源层，每层之 间通过统一的适配能力支持，可以实现灵活交互并降低耦合度，系统架构设计如 图 4.1 所示：

（1）访问控制层

用户访问系统时，所有的请求都将会被访问控制层所拦截。首先网关依据安 全策略对请求进行校验，防止外部网络访问系统；接着开启用户登录校验，通过 检查 Cookie 中是否含有登录 Token 方式来验证用户登录状态，否则跳转到 CAS

（Central Authentiction Service,中心认证服务）平台进行登录（若未注册则先注 册）；最后再跳转回原系统，这一过程也叫做SSO（Single Sign On，单点登录）。 访问控制还包括请求解析转发，即当用户通过系统访问外部页面时进行的“跨域” 处理，最重要的是当用户访问系统时，需要验证用户有没有查看系统菜单、页面、 功能的权限，若没有则跳转到权限申请页面。

（ 2）数据应用层 数据应用层是指报表系统为用户提供的数据查询、分析和展示服务，用户的 功能由权限角色控制，注册之后通过申请功能查看权限来解锁系统的业务功能。 系统业务代码代用 Java 语言开发，各业务模块通过 gRPC 框架开发微服务，使用 protobuf 序列化方式实现服务间通信，服务之间保持隔离，并采用分布式方式部 署。报表系统基于业务需求为用户提供了多维取数、主题报表和自定义报表等数 据服务，以及权限管理、数据下载等基础功能。

（ 3） OLAP 服务层

传统报表系统在进行展示时依靠数据仓库的 OLAP 分析功能，其数据仓库一 般是通过 HDFS 或 HBase 作为存储介质，使用 Hive 或 Spark 作为计算引擎，但 是依靠这种架构实现的报表系统无法做到即席查询，比较适合用于查询规则固定 的离线数据报表展示，不适合具有灵活查询规则的广告业务报表系统。因此，本 系统在数据仓库之上又引入 OLAP 服务层（此层也可以归到数据仓库中），使用 搜索引擎 Elasticsearch 为取数功能提供多字段匹配和全文检索查询，使用计算引 擎 ClickHouse 为报表系统提供 OLAP 实时分析功能。

（ 4 ）数据仓库层 数据仓库用来存储业务底层数据，为系统提供离线数据或实时数据同步支持， 依据数据时效性需求的不同，可以将数据仓库分为离线数仓和实时数仓。使用 HDFS/Hive实现离线数仓，负责存储T+1 （今日查看昨日及之前的数据）时间的 数据，每日 24:00 点开始抽取业务数据库数据，经过处理之后以 Hive 表形式写入 数据仓库中。实时数仓用来存储广告相关日志的实时流数据，具体实现是将日志实时写入 Kafka 中，然后使用 Flink 从 Kafka 中读取数据，并进行过滤、去重、 聚合操作。数据仓库中数据最终需要写入到 OLAP 引擎中做实时 OLAP 分析。

（5）数据源层

报表系统核心数据包括广告主体相关的代理商、客户和广账户基本信息，和 由广告投放之后的用户交互日志数据经过 ETL 处理之后的广告消耗、变现效率、 用户体验等数据，此外还有销售运营相关的业绩、任务、合同和开票等数据，这 些数据来源于广告业务系统，如CRM系统、DSP系统、广告投放系统和ADX 系统，其中效果转化数据来源于媒体端埋点产生的日志记录。

4.2      系统功能模块设计

根据第三章的需求分析可知，报表系统主要分为以下 5 个功能模块：多维取 数功能、主题报表功能、自定义报表功能、权限管理功能和数据下载功能，系统 具体功能模块如图 4.2 所示：

图 4.2 报表系统功能结构图 

多维取数功能指多字段筛选查询多维指标数据，可以查看所有的代理商、客 户、广告账户的基本信息，以及在各种筛选条件和聚合维度下的的广告投放相关 数据指标，底层数据是 T+1 形式的离线数据，来源于业务数据库和广告日志。

主题报表功能是用户使用频率最高的功能，是产品和业务方确定具体数据需求后，研发人员设计开发的报表。从研发角度来看，主题报表模块存在重复开发 的工作，但是从用户角度来讲，主题报表使用简单，展示效果稳定，且随时可以 查看自己专属的主题数据，大幅降低了用户成本，所以主题报表对广告业务的快 速开展和推进有重大意义。报表的主题按照需求可划分为以分析为主的大盘分 析、行业分析报表和以用户为出发点的销售、运营报表等。

自定义报表功能是主题报表功能的扩展，在实际应用中存在主题报表没有覆 盖的数据指标，这些指标不是每个用户都需要查看，所以系统为用户提供了自定 义报表的选择。自定义实现流程：首先用户确定数据源，采集数据库、表和字段 等元数据，接着配置筛选、计算和排序等规则生成报表模板，最后前端调用模板 向后端发起计算请求，并将返回结果以图表形式展示。

权限管理是系统必备的基础功能，报表系统是业务数据和日志数据经过 ETL 处理后形成的数据仓库之上的可视化应用系统，底层数据来自多个业务系统，其 中不乏敏感信息，包括客户基本信息、销售业绩和客户投放广告花费金额等敏感 信息，系统需要为用户制定严格的使用权限，以避免机密信息泄露风险。

数据下载是系统常见的基础功能，用户在查询或查看数据指标后，一般需要 将数据导出为报表形式，并将报表同步给客户或者上级领导，此外还需要对文件 进行归类存档，方便日后查阅分析。所以，数据下载功能提供了用户下载任务列 表，记录用户下载记录，云文件系统存储实现历史文件随时下载。

4.3     系统数据存储设计

系统分为功能相关数据和报表展示数据，功能相关数据存储在 MySQL 数据 库中，为系统功能实现提供支持，展示数据来源于广告部门数据仓库，通过将数 据导入到 Elasticsearch 和 ClickHouse 中存储，来提高报表数据查询分析性能。

( 1) MySQL 表设计

报表系统展示数据来自数据仓库，但是自定义报表、权限管理和数据下载功 能的实现离不开数据库的支持，需要为报表系统创建 MySQL 数据库。

自定义报表功能分为元数据管理和报表模板管理两部分，因此需要设计元数 据表和字段表用来存储数据源、数据库、表和字段等信息。用户通过配置页面导 入元数据，以及配置报表展示维度和指标的计算规则，使用模板表和参数表用来存储模板信息和用户配置的请求参数信息，表关系设计如图 4.3 所示：

系统权限可划分为功能权限和数据权限：功能权限控制基于 RABC 模型实现， 所以需要设计用户信息表、角色信息表、资源信息表以及它们之间的映射关系表， 报表系统引入了组织架构概念，所以需要设计组织架构信息表以及和用户的关系 映射表；数据权限除了上述的组织架构之外，还引入了人户关系和数据范围，其 中人户关系维护在 CRM 系统中，系统通过 RPC 接口调用得到对应数据，数据范 围维护在报表系统数据库中。权限管理功能 ER 图如图 4.4 所示：

图 4.4 权限管理功能 ER 图

数据下载以任务形式实现，下载任务 TaskInfo 表字段如表 4.1 所示:

表 4.1 下载任务 TaskInfo 表

22

23

（ 3）ClickHouse 表设计

广告业务系统会将产生的数据以特定间隔（天/小时/分钟）写入数据仓库， 方便下游业务进行查询分析。系统将数据仓库中数据表划分主题域，以宽表形式 导入 ClickHouse 中，为报表提供实时查询分析能力，使用宽表存储可以方便计算， 减少多表 join 操作，下面以广告创意宽表为例介绍表设计。

广告创意宽表是广告主投放创意广告后每天统计的广告消耗、计费、素材播 放、点击等信息，以广告创意 id 作为最小统计粒度，包含广告账户、客户和代理 商等主体维度信息，为多维取数、主题报表、自定义报表功能提供效果广告消耗、 变现效率、用户体验、广告落地等数据分析支持，以 T+1 形式存储，每日新增两 亿行左右记录，总数据量3〜5TB。广告创意宽表ad_dw_creative_report_di其主要 字段如表 4.3 所示：

表 4.3 ClickHouse 广告创意宽表

24

除此之外，还有和业绩相关的业绩宽表、DSP平台产生的计费宽表和实时数据宽表，实时数据宽表中数据来源于投放端产生的实时日志流，通过 Kafka+Flink 实 现实时消费和实时 ETL 处理，使用 Flink 对实时流数据进行过滤、去重、转化和 聚合操作后，实时写入 ClickHouse 宽表中，时间粒度可以到 3〜5 分钟级别，每日 新增数据量达到两亿级别，所以不建议对实时数据宽表进行大时间范围查询。

4.4 本章小结

本章介绍的是系统总体设计。第 1 节是论文重点，介绍了系统整体架构的设 计，以分层的方式介绍了数据访问层、数据应用层、OLAP服务层、数据仓库层 和数据源层。第 2 节介绍了系统功能模块设计，包括多维取数、主题报表、自定 义报、权限管理和表数据下载。第 3 节介绍系统数据存储，包括系统功能数据库 MySQL、搜索引擎Elasticsearch和报表引擎ClickHouse表结构和字段设计。

第 5 章 系统功能设计与实现

5.1       多维取数模块

多维取数是即席查询的典型应用场景，用户根据自己需求灵活配置取数条件 进行查询分析，“多维”是指支持多种汇总方式、筛选字段、聚合维度和展示指 标，支持它们相互交叉配置查询。因为 ClickHouse 采用列式存储，在面对多字段 匹 配查询和 明 细数据查询时性能较差， 所以 本文将客户 明 细数据存储在 Elasticsearch 中，将广告指标数据存在 ClickHouse 中，采用两段式查询，其底层 数据来自数据仓库，以定时任务形式加载到 Elasticsearch 和 ClickHouse 中。

5.1.1      字段和指标设计

取数功能在设计时，需要根据用户需求设计具体的汇总粒度、筛选条件、聚 合维度和展示指标。取数首先需要确定数据源和展示数据指标及计算逻辑，其次 确定时间汇总方式、筛选字段和聚合字段，最后确定数据展示形式。

汇总粒度是指数据汇总处理时可以选择的单位时间。取数主要是针对广告投 放消耗、变现、播放、落地相关指标的查询，这些指标来源于业务系统数据和投 放端日志数据，具有时间属性，可以按照天、周、月、季度甚至年粒度来进行汇 总，还支持按照所选时间范围进行汇总。

筛选字段来源于客户主体的属性信息，分为基本信息、客户信息、代理商信 息、广告账户信息5 个部分，具体字段如表 5.1所示：

表 5.1 取数筛选字段表

■^度          字段名称

基本信息      客户所在行业群、行业、广告业务线、投放媒体等

客户信息      客户id、公司营业执照、统一信用码、注册地址、创建时间、进私海时间、

首次消耗时间、销售责任人、销售协作人等

代理商信息 代理商id、代理商名称、代理商商类型、销售责任人、销售协作人、运营 责任人、运营协作人、媒介责任人、媒介协作人等

广告账户信息 广告主id、广告主名称、账户类型、广告主类型、销售运营责任人等

聚合维度代表数据展示的粒度，对应 SQL 中的 group by 操作字段，字段的聚 合粒度不同，按照聚合粒度从大到小计算，展示时可以进行上卷、下钻操作。聚 合维度和筛选字段设计中的基本信息对应，主要支持以下聚合维度：营业执照、 分公司、代理商、广告账户、素材、产品名、行业群、一级行业、二级行业、业 务线、媒体分类、 APP 类型、应用平台、广告来源、出价类型。

展示指标是广告投放之后进行统计、计算的反映投放消耗和转化效果的指标， 展示指标设计如表 5.2 所示：

表 5.2 取数数据指标表

■^度      字段名称

广告消耗 总消耗、现金消耗、返点消耗、前返消耗、后返消耗、框返消耗、信用消耗、 激励消耗、环比消耗、周/月/季度同比消耗等

变现效率 封面曝光数、封面点击数、封面点击率、素材曝光数、行为数、素材行为率、 封面行为率、广告曝光数、行为率、封面CPM、素材CPM等

用户体验 3s 播放数、 5s 播放数、播放完成数、 3s 播放率、 5 秒播放率、素材播放完成率、 封面曝光正反馈率、封面曝光负反馈率、评论数、点赞数、举报数、减少此类 作品数、加入黑名单数等

转化效果 下载数、下载完成数、激活数、激活单价、新增付费人数、订单支付数、订单 成交金额、提交订单数、广告观看数、表单提交数据商品访问数、直播观看数、 落地页下载数、按钮点击数、电话拨打数、预约数等

账户分析 总广告主数、活跃广告主数、活跃广告主占比、新增广告主数等

取数功能的筛选字段和展示指标由业务方制定，用户无法做出修改，但是， 取数功能已经涵盖了大部分的筛选条件和广告数据指标，用户只需要自由组合取 数规则，就可以达到灵活取数的目的，降低了用户的使用成本。

5.1.2 取数流程实现

取数执行流程如图 5.1 所示，流程如下：

1）  用户在页面配置汇总时间粒度、筛选条件、聚合维度和展示指标；

2）  系统计算当前用户的数据权限，若用户没有有权限的数据，则直接返回；

3）  系统解析用户的输入参数，查看筛选字段值；

4）  如果筛选字段不为空，则根据筛选条件查询 Elasticsearch；

5）  查询 ClickHouse 得到数据指标；

6）拼接 Elasticsearch 明细结果和 ClickHouse 中的数据指标，返回给用户。

图 5.1 多维取数执行流程图 

取数功能实现主要由接收前端请求的接口 ReportAccountController，取数逻辑 实现服务类 ReportAccountService，用户权限计算 RPC 服务 UserAuthRpcService， KBean 提供的数据持久化实现类：用于访问 Elasticsearch 的 EsClientDao 和访问 ClickHouse的CHClientDao，它们的调用逻辑如图5.2所示：

图 5.2 多维取数请求执行时序图

用户 在 页 面发 起取数请求 后，前端 将请 求参数映射 成 QueryParam 并 调 用 ReportAccountController 接 口 向 后 端 发 起 HTTP 请 求 , 接 着 后 端 接 口 调 用 ReportAccountService 的查询方法执行取数逻辑，方法传参为 ReportAccountParam 对象。查询首先调用权限计算RPC服务的calAccountIdByUser()方法计算用户的 数据权限，得到用户有权限的 account_id 集合；接着根据传参中的 filter 条件来判 断是否查询Elasticsearch，若是则生成JSON格式查询语句，调用EsClienDao查 询得到 account_id 和明细数据集合；之后将得到的 account_id 集合最为新的参数 生成查询SQL，调用CHClientDao计算得到展示指标；最终在查询方法末尾拼接 明细数据和展示指标得到 ReportAccountView 结果对象返回给前端展示。

5.2 主题报表模块

主题报表是依据用户需求定制开发的数据报表，依据不同维度可以划分成多 种不同报表主题，如大盘分析报表、销售报表、代理商报等，针对各主题报表的 数据查询提供了多样的筛选聚合方式和图表展示方式。

5.2.1 报表主题划分

广告业务报表可以依据数据分析、用户使用等角度划分主题，如图 5.3 所示：

图 5.3 主题报表功能报表主题划分图

大盘分析报表旨在为管理侧提供全局数据可视化服务，主要展示消耗计费、 变现效率、用户体验等指标的汇总结果。

行业分析报表将数据按照行业维度聚合，展示各行业不同时间范围的花费、 点击、曝光、计费等指标信息，为管理侧提供数据分析支撑。

销售报表以销售为出发点主要是展示销售所负责或协作客户的消耗、计费指 标，包括数据总览、消耗趋势和 Top10 消耗明细数据等。

品运效率报表以品牌广告运营使用角度设计报表主题和指标，主要展示运营 负责项目、任务、订单、排期等效率指标数据。

代理商报表以代理商为主题设计报表模块和展示指标，展示各代理商的消耗 计费情况，代理商类型有很多种，包括：效果代理商、品牌代理商、直签代理商、 本地渠道代理商和招商加盟代理商等。

实时数据报表展示的是来源于数据仓库的准实时数据，有几分钟的延迟，而 上述其他报表数据为 T+1 形式。依据不同主体可划分为代理商实时数据、客户实 时数据、本地直营实时数据等，主要展示消耗、计费和效果转化指标。

除了上述主题报表之外，还有与销售业绩相关的业绩报表和直客业绩报表， 电商广告相关的电商报表和电商运营报表等。

5.2.2 主题报表实现

报表筛选条件、展示指标和计算逻辑由业务侧制定，所以，当数据源和指标 确定后，后续开发流程相似，为避免重复工作，系统设计一套主题报表服务体系， 便于日后开发维护。

图 5.4 主题报表实现流程图 

主题报表实现如图 5.4 所示，系统设计 RPC 顶层接口为主题报表功能提供统 一服务，使用 reportType 枚举类型唯一标识主题报表，主题报表实现抽象接口后， 便可根据 reportType 通过 Bean 工厂生成具体实现类，由实现类调用 KBean 方法 访问数据源查询结果。

主题报表底层代码实现主要由以下类组成：

1 )ReportController 接 口用 来 接收用 户 的报表 展示 请 求， 前端传 参 映射为 QueryParam 对象，参数类型如下表 5.3 所示：

表 5.3 主题报表请求主要参数表

除表5.3中参数外，还有用户名userName、起始时间startTime、结束时间endTime 等基本参数。服务端为了区分字段含义，需要创建字段枚举类 ReportFieldEnum， 通过如“todayCharge:今日消耗"声明方式实现字段映射。

2)          ReportRpcService 服务是主题报表功能的统一访问层，其核心代码如下：

1 public class ReportRpcService extends ReportRpcServicelmplBase {

©Resource

3             private ReportFactory reportFactory;

4             (aOverride

5             public void queryStat(ReportRequest request,

6                                  StreamObserver<ReportResponse> responseobserver) {

try {

//解析传入J SON格式参数

ReportVo ReportVo = ObjectMapperUtils・fromJS0N(request・getContent(), ReportVo・class);

10                                  //通过bean工厂生成报表实现类

11                                  Reportservice reportservice = reportFactory・getService(reportVo・getReportType()))

12                                  //执行报表查询逻辑，转化为统一结果返回

13                                  return (AdCrmReportResponse) reportservice・query(reportVo);

14                         } catch (ServiceException e) {

15                                  return AdCrmReportResponse.newBuilder()・setResultCode(

16                                                             ResultCode.SYSTEM_ERROR).setErrorMessage(e.getMessage()).build();

17                         }

18             }}

该服务提供了访问所有主题报表的接口 queryStat()，使用传参中的reportType值 和工厂类 ReportFactory 来创建具体的报表实现类，并以 JSON 格式返回结果。

3)        ReportService 是报表的顶层抽象接口，所有主题报表必须实现该接口并重 写query()和getReportType()方法。query()方法是报表数据的查询实现，所有报表 服务重写该方法定义数据查询计算逻辑，getReportType()方法返回当前报表的类 型， ReportTypeEnum 枚举类中包含了所有的主题报表对应的枚举值。

4)       ReportFactory 是用来生成报表的工厂类，通过实现 ApplicationContextAware 接口的setApplicationContext()方式，实现加载所有的主题报表实现类，代码如下：

1 public class ReportFactory implements ApplicationContextAware {

private static Map<Integerf ReportService> serviceMap = new HashMap<>(): public <T extends CrmReportBaseVo>0bjec1: getService(CrmReportTypeEnum reportType) { Reportservice reportService = serviceMap.get(reportType.getReportType()));

5                          if (reportService == null) {

6                                    throw new RuntimeException("对应报表服务不存在")；}

return reportservice.querytcrmReportVo);

8            }

9            ^Override

public void setApplicationContext(Applicationcontext applicationcontext) { //通过反射机制得到Reportservice的所有实现类

12                            Map<Stringf ReportService> reportServices =

13                                      applicationContext.getBeansOfType(ReportService.class);

14                            reportServices.valuest).forEacht

15                                      item -> serviceMap.put(item.getReportType().getld(item));

16                  }}

服务在启动时完成对所有实现类的加载，需要时直接通过reportType获取即可。

5)ReportXxxServiceImpl 是主题报表具体实现类，其需要实现 ReportService 接口，重写query()和getReportType()方法，在query()方法中编写报表具体查询逻 辑，并在此完成数据权限的计算，拼接结果等操作。

除此之外还有计算用户权限的 UserAuthRpcService 服务和由 KBean 提供的操 作ClickHouse数据的实现类CHClientDao，前者提供了计算用户有权限的广告主 id、客户id和代理商id等方法，后者提供查询ClickHouse表数据功能。

图 5.5 主题报表实现时序图

主题报表各模块调用时序如图 5.5 所示，用户在页面发起报表展示请求后， 前端将用户选择和输入的条件参数封装成 QueryParam 对象并调用后端定义的 ReportController 接口发起 HTTP 查询请求， ReportController 调用 RPC 统一访问 层的query Stat()方法计算结果。query Stat()方法首先通过ReportFactory工厂类生 成报表具体实现类ReprtXxxServiceImpl并调用其query()方法(工厂类根据 reportType 枚举值使用 Java 反射机制来创建实现类对象)；其次，报表实现类的 query()调用KBean提供的数据访问方法CHClientDao生成查询SQL并执行，为 了加快查询速度使用线程池并行访问ClickHouse；最后，将ClickHouse返回结果 在内存中拼接汇总返回，

5.3 自定义报表模块

主题报表操作简单，但是开发维护成本较大，只适用于指定的业务背景，数 据需求方难以定制化获取数据，因此，系统设计并实现了基于接口 API+SQL 模 板形式的自定义报表功能，提供 HTTP 接口和 RPC 接口供前后端调用。

自定义报表功能架构设计如图 5.6 所示，自上而下包括顶层的自定义配置、 元数据管理，访问接口、 SQL 模板，中间的元数据池和最底层的数据源。

( 1)元数据管理

元数据是指标描述指标的数据，由数据源、库、表和字段信息组成，来源于部门内部各业务系统生产的数据。用户添加数据源中有权限的数据库、表和字段 作为数据集，然后在数据集页面对元数据进行管理。

（ 2）API 接口

API 接口是用户生成自定义报表之后的服务器端访问 HTTP 或 RPC 接口，通 过生成唯一接口方式，实现一次配置，处处调用。内部其他服务也可通过调用 RPC 接口实现复用数据访问功能，降低数据开发成本。

（ 3）SQL 模板

SQL 模板在用户完成报表自助配置后生成，用户在前端页面配置报表数据筛 选条件、展示指标和计算逻辑等参数，系统解析参数生成查询SQL，为了避免数 据库和表字段的变化导致 SQL 失效，当发生调用时模板才会生成。用户使用模板 时设置或输入参数和对应值，前端调用 API 接口传递参数值，后端将生成的 SQL 模板和参数值映射成可执行 SQL 语句。

（ 4）元数据池

元数据池即公司内部的数据资源，根据部门和业务线进行划分管理，可以通 过公司内部的数据管理平台查看并操作有权限的数据。报表系统通过接入数据管 理平台提供的 API 接口，可以获取某数据源下用户有权限的数据表和字段，用户 手动添加至报表数据集中，实现自定义报表元数据管理。

（ 5）数据源

自定义报表数据集构建目前已经接入 MySQL 和 ClickHouse 作为数据源，目 前已经覆盖绝大部分广告业务数据，未来可逐步接入更多的数据源，提供更多、 更全面的数据指标配置展示。

自定义报表实现流程如图 5.7 所示，默认情况下，用户在报表系统中没有任 何的元数据，需要用户指定数据源库和指标创建属于自己的数据集，才可以进行 自定义报表配置，配置完毕后，可点击获取 SQL 模板进行检验，之后点击生成报 表模板，服务端所有参数存入数据库中，返回报表对应的apiKey，前端调用报表 模型执行接口，传入 apiKey 和对应参数值即可完成查询展示。

图 5.7 自定义报表流程图

5.3.1 自定义元数据采集

元数据采集需要公司内部的数据资源管理中心提供支持，通过调用数据中心 提供的 UserDataSourceAPI 接口，可以拿到用户有权限的数据库、表和字段，其 中数据库和表的权限需要在数据管理平台上申请。

元数据采集即选择数指定据源和字段同步至用户空间，操作如图 5.8 所示：

图 5.8 元数据采集操作图

系统调用 UserDataSourceAPI 接口的 getUserAuthDBFieldInfos()方法，传入参数 userName="xxx"和 resourceType="ClickHouse"，可拉取用户 xxx 有权限的数据表 和字段，以及字段的描述信息，接着用户根据业务需求选择对应数据库、表和字 段作为自定义报表的元数据集，最后设置分区字段，填写数据源集名称，选择导 入的目录和权限责任人后，系统开始执行导数操作，并将数据源、库、表和字段 存入系统自定义报表功能对应的数据库中。系统采用实时采集可以避免数据源表 和字段发生变更后同步不及时导致的查询异常。

采集后的元数据形式如图 5.9 所示，用户在此页面可以查看自己创立的数据集，并可以对其执行编辑、修改和删除操作，还可以查看数据集基本信息、字段和变更记录等信息。元数据准备妥当之后，用户在报表配置页面可以通过下拉查 看，之后可以通过配置页面来配置取数逻辑，从而生成报表模板。

5.3.2 自定义报表配置

自定义报表配置分为基本信息、展示指标、聚合维度、条件筛选、结果筛选 和排序分页六种配置选项，通过对各选项进行自定义配置实现数据灵活展示。

基本信息即模板名、数据类型、时间粒度、数据源信息以及模板描述等；展 示指标可以是表中的字段也可是计算函数得到数据指标，计算函数支持算数运算 符、字符串操作以及SQL聚合函数sum()、count()、round()等，如图5.10所示：

图 5.10 自定义指标配置图

指标配置参数包括指标的定义、参数名称、参数类型、备注等；聚合维度也称为 分组字段，通过执行 group by 进行聚合分组，配置实现和展示指标相同，通过参 数前缀dim_和展示指标区分；筛选条件分为字段筛选和结果筛选(指标筛选)， 前者是数据表中存在的字段，后者是用户自定函数生成的数据指标，筛选条件支 持算术运算以及 in、 not in 等操作，操作符后的参数用来映射调用时的传参值， 具体筛选配置如图 5.11 所示：

筛选条件(选填)

•同级之间关系为【且】，子级关系为【或】

first_industry_id                         在列表中        industrylds                                  number                          可选   J [ — fTlfc                                       J ・

策 second_industry_id                      在列表中      industrylds                             number                          可选           二级行业                 血     + 且      + 或

::businessjine                                   在列表中        businessLines                            string                             可选           大产品线             血 +且

訐 servicejine                                    在列表中      serviceLines                          string                             可选           产品线                   血     +且

s second_service_li...                在列表中          secondServiceLines string                                                可选         细产品线                  血 + 且 + 或

图 5.11 自定义报表筛选配置图

排序分页配置包括按升序降序排序、指定分页的偏移量、 Top 列展示，报表配置 完毕后，可以点击查看生成的 SQL 模板进行校验，格式如下所示：

SELECT

round(SUM(total_cost)/100, 0)) AS totalCost,

round(SUM(total_cost)/100, 0)) AS preCost,

round(SUM(total_cost)/100, 0)) AS lastCost, FROM

ad_account_performance_charge_di

WHERE

(first_industry_id IN (:Industrylds)

OR second_industry_id IN (:Industrylds))

AND (business_line IN (:businessLines))

OR service_line IN (serviceLines))

OR second_service_line IN (secondServiceLindes))

GROUP BY

account_id

其中where语句中的“:参数”代表着再次调用模板进行展示需要传递的值，如: 传递 IndustryIds=[1,2,3..]，传参和 SQL 模板需要对应，否则查询失败。

5.3.3 自定义 SQL 模板生成

自定义报表的核心就是根据配置生成 SQL 模板，然后调用 SQL 模板查询取 数。为了避免数据库表和字段变化导致的 SQL 模板失效，本系统采用实时生成策 略，即发生查询调用时生成模板，数据库中只存储配置参数。

接口配置中包含数据表、选择字段、筛选条件、分组（维度）字段、排序字 段、限制数量等字段配置，对应着SQL的子语句模块，查询SQL格式为：“select ［字段列表/别名］ from 数据库.表 where 条件语句 group by 分组语句 order by 排 序语句 limit 数量”，其中 where 语句因为 "and 条件（且）" 与 "or 条件（或）" 的 组合而较为复杂，在报表配置中的数据结构也较为繁琐。

图 5.12 where 语句节点森林图

如图 5.12 所示，本文设计思想是各条件之间的嵌套组合关系采用树/森林表示，同一父节点且同一层次的节点的关系表示为“and”，同一条路径上的条件节点的关系为“or”，每个节点表示一个筛选条件，则可以转为SQL形式:

或者

where (A or (B

and C or (D and E)))

and (a or ((B or e)

and (y or (< and (r| or k))

and (6 or 0)))

因此可以对以上森林结构节点进行遍历，上述左侧 SQL 采用后序遍历方式生成嵌套的条件关系，而右侧 SQL 采用深度优先遍历可以较为简单地获取每一条子条件的路径，并通过 and 连接起来，对于开发实现来说，右侧 SQL 更为简单。

where 语句生成需要前端将字段以层级形式传递到服务端，即将 WhereField

Param 以 list 传递至后端，其中 WhereFieldParam 类中定义了包含自身的 children

属性， children 属性类型为 List<WhereFieldParam>， where 语句以此树状结构进行存储，其中当前节点和children为“and"关系，和children列表中的字段对象为

"or"关系，并后序遍历树实现where语句生成，通过递归方式实现树的后序遍

历：若 fields 不为空，则递归遍历节点构建子条件语句，根据前一个节点的返回

判断是否还有右节点，有则拼接为“and”。核心代码如下所示：

1        boolean and = false;

2        for (int i = 0; i < f ields. size(); i++) { //递归遍历

3                 ConditionField field = fields・get(i);

4                 //构建查询条件子语句

5                 String sql - buildConditionFieldSql(field, template);

6                 if (CollectionUtits.isNotEmpty(field.getChildren())) {

7                        //递归计算子节点对应语句

String childrenSql = buildConditionSql(field・9已1:(；11：11(1厂611(), template)

9                  if (StringUtils.isNotEmpty(sql)) {

在生成 SQL 模板之前需要进行校验，主要包括检验 selectFields、tableInfo 和 Group

39
ByFields 参数是否为空，检验表是否存在，字段是否存在，即字段和表是否匹配， 之后生成SelectHandler用于生成select SQL语句。

综上所述，生成 SQL 模板的主要步骤为：

1)      拼接展示字段，字段类型分为符号、字段、常量和函数四种，符号即+-*/()， 字段对应的是表字段，从字段记录表中获取，常量即系统定义的一些常用的数字， 如1、10、100、1000等，函数即SQL支持的sum()、count()等聚合函数，遍历输 入的展示字段参数，对上述的四种类型匹配拼接；

2)          拼接 where 语句，上文已经介绍；

3)          拼接 group by 语句，直接遍历字符串拼接即可；

4)          拼接 having 语句， having 语句生成规则和 where 类似，都是通过后序递归 遍历森林方式生成；

5 )拼接order和limit语句，直接拼接字符串。

生成 SQL 模板核心代码实现下所示：

public String genTemplateSqK) {

StringBuilder sb = new StringBuilder("SELECT ");

〃拼接select字段

dimFields・ str~eam()・ filter(dimField ->

dimField・getSelectType() == SelectTypeEnum・YES・getCode())

・forEach(dimField -> sb・append(buildDimFieldSql(dimField))); selectFields・fo「Each(selectField ->

sb・append(buildSelectFieldSqI(selectField, timeUnit, groupFields))); 〃拼接WHERE语句

sb.append(buildColumns()).appendf" FROM ").append(getTable());

sb・appendC WHERE ")・append(buildConditionSqI(whereConditionFields, true)); //拼接GROUP BY语句

groupFields・addAll(dimFields・ st ream()・map(DimField::getFieldName)

・distinct()・ collect(Collectors・toList()));

groupFields = groupFields・ st ream()・distinct()・ collect(Collectors・toList());

sb.appendf GROUP BY ").append(Joiner.on.join(groupFields));

〃拼接H AVING语句

sb・appendC HAVING ”).append(buildConditionSql(havingConditionFields, true)); //拼接ORDER和LIMIT语句

sb・ append (buildOrderFields());

if (pageSize > 0) {

sb.appendf LIMIT ") .append(offset) .appendf,"). append (pageSize);

return SqlFormatter・fo「mat(sb・toString());

示，即字段以“:参数”字符形式存在，等待调用时和传递的参数值进行映射

5.3.4 自定义报表模板调用

报表模板以 API 接口形式存在，系统提供了两种调用方式，一种是前端常用 的 HTTP 接口调用，一种是服务端使用的 RPC 接口调用。

两种方式调用传入参数类似，传入参数 ApiRequestParam 属性值主要包括：

1）接口唯一标识apiKey，在配置页面创建模板生成；

2 ）接口调用方invoker，用户在公司内的userName或邮箱；

3）筛选条件参数和值的映射关系 Map<String,Object> filterValueMap；

4     ）自定义参数对象customizeParam，用户配置的各种参数，其中主要包括自 定义列、自定义指标、自定义维度、自定义排序等参数；

5     ）分页参数对象pageInfo，由offset、limit和totalCount参数组成；

6）排序后取前 N 位的数据参数 topNum。

HTTP调用通过Controller接口 callDataApi（）实现，接口提供后端访问URL（域 名/call）和请求参数ApiRequestParam以及返回参数ApiResultView。RPC接口目 前支持三种数据类型的接口：单条数据querySingleDataByConfigApi，列表数据 queryListDataByConfigApi，多个列表数据 queryMultiListsDataByConfigApi。请求 参数的filterValueMap和上述的有所不同，由于protobuf序列化方式对Map类型 数据表示不够友好，所以将Map拆分成字符串条件stringListCondition、数字条件 numberListCondition 等列表形式。

以提供给代理商任务的接口为例，其 apiKey 为 “external_agentTaskCostData”， 调用方为 “代理商平台”。接口配置为一个单数据接口，因此在调用的时候使用 querySingleDataByConfigApi，接口筛选条件配置如图5.13所示：

筛选条件（选填）

O同级之间关系为【且】，子级关系为【或】                                                                                                                                                                            x

•: year                                                     等于             year                                              number                            必选             年                        會 +且

quarter                                     等于             quarter                                         number                            必选             季度                      曲 +且

agentjd                                            等于              agentld                                        number                            必选      代理商ID                                  血 +且

::loopjype                                        等于              loopType                                     number                            必选            循环类型:all,inner,outsi（血 + 且 + 或

图 5.13 筛选条件配置图

其中除 loopType 为字符串类型的筛选条件外，其余三个条件均为数字类型条件，

因此接口的调用代码如下：

DataApiRequest dataApiRequest = DataApiRequest.newBuilder()

setApiKey("external_agentTaskCostData")

・ setlnvokerCAGENT.PLATFORM")

・setDataApiParam(D6taApiPanewBuilder()

.putStringCondition("loopType", "all")

・putNumCondition("year", 2021)

・putNumCondition("quarter", 3)

・putNumCondition("agentId", 60358)

・build()

).build();

//根据配置参数调用模板计算数据指标

ListenableFuture<DataApiResponse> agentDatmApiResponseFuture

dataApiRpcServiceClient・ queryDataByConfigApi(dataApiRequest);

DataApiResponse agentDataApiResponse = agentDataApiResponseFuture・get();

当然，除了上述代码中的场景，用户还可以根据实际情况配置复杂的查询，模板 配置由用户自由选择，得到的数据结果如何进行展示也由调用方来决定。

5.4 权限管理模块

报表系统底层数据来自多个数据源，其中包含很多敏感信息，例如：客户基 本信息、销售业绩和广告收入等数据信息，为了保证数据的安全性和隔离性，需 要为用户配置专属的系统权限。为了灵活配置用户权限，实现对数据的细粒度访 问控制，系统设计了基于 RBAC 模型+组织架构的功能权限和基于组织架构+人户 关系+业务字段的数据权限。

5.4.1 基于 RBAC 的功能权限

功能权限基于RBAC[42] （ Role-Based Access Control，基于角色的访问控制） 模型实现， RBAC 模型在用户和权限之间引入了角色的概念，模型主要由用户、 角色和权限三个部分组成，用户即本系统的使用者，主要包括：产品、销售和运 营等；角色是指具有某些权限的主体，如销售主管、系统管理员等；权限即能够 访问和操作系统某些资源的资格，如创建用户、查看销售业绩等权限。

随着用户的增多，用户职能和职级的不断变化， RABC 权限模型不能很好地 满足现有的功能权限需求，比如销售部门leader理论上是具有所有下属的功能权 限，即权限向下继承，所以报表系统在 RBAC 模型的基础上引入组织架构的概念， 通过RBAC+组织架构两种方式实现对功能权限的控制，具体如图5.14所示：

图 5.14 系统功能权限模型图

系统功能权限在 RBAC 的基础上增加组织架构层级，可以认为是除了“角色” 之外又引入了“部门”或者“用户组”的的概念，即公司内部某个部门或组下面 的所有用户的权限并集，在为用户配置权限时可以为其绑定组织架构权限，此权 限一般赋予部门 leader 或者管理员。

用户功能权限的鉴定过程发生在前端页面展示阶段，功能权限是对系统的菜 单、页面和页面功能等前端资源的访问控制。功能权限的控制展示过程发生在登 录之后，或者当用户点击菜单展开页面及功能按钮时触发功能权限校验。

图 5.15 功能权限实现时序图

功能权限实现流程如图5.15所示，当用户登录成功后，系统通过Java拦截器 UserPermissionlntercepter拦截用户的页面功能展示请求，之后调用用户权限RPC 服务 UserPermissionRpcService 实现对用户权限的鉴定。 RPC 服务内部调用用户 权限服务类 UserPermissionService，服务类调用 UserDepartmentMapService 查询 用户在 MySQL 中的组织架构信息得到下属用户集合（若没有下属则返回当前用 户信息），接着去 MySQL 表中查询用户集合对应的角色，然后通过角色找到功 能资源集合，最终将功能资源集合返回前端，前端根据事先定义的资源唯一标识 来判断为用户展示哪些功能。

5.4.2 数据权限设计实现

数据权限模型设计如图 5.16 所示，和功能权限一样引入了组织架构，但不同的是没有角色的概念，而是通过人户关系和维度字段来控制用户的数据权限。

图 5.16 系统数据权限模型图 

人户关系即销售、运营等人员和广告客户、代理商的映射关系，人和广告主 的关系划分为：广告销售责任人、广告运营责任人、广告媒介责任人以及对应协 作人等。人和代理商的关系：代理商销售责任人、代理商运营责任人、代理商媒 介责任人以及对应协作人等，其中代理商和客户是一对多的关系。

维度字段即将广告数据按照行业群、行业、业务线、任务类型、项目类型等 维度进行划分，每个维度对应数据库中一个或者多个数据字段，通过对上述维度 进行枚举细分，将得到多维度下的多种类型数据。依据不同需求为用户配置不同 维度和其细分类型对应的数据权限，从而实现对数据权限精细化的控制。

数据权限是对用户筛选查询展示的代理商、广告主和代理商主体对应的明细数据和指标数据等进行权限控制，只返回用户有权限的数据，例如：销售和运营

只能看到自己负责的客户数据，从而防止重要信息和数据泄漏。

图 5.17 数据权限实现时序图

数据权限底层实现如图 5.17 所示，数据权限计算发生在报表展示请求中间阶 段，由报表服务调用执行，通过调用用户权限 RPC 服务获得用户有权限的 id 集 合，其中id为广告主id或代理商id。UserPermissionService是真正的用户权限实 现类，其先调用 UserDepartmentMapService 获得用户的组织架构信息，之后调用 UserClientRelationService 查询用户和广告客户的关系，返回广告账户 id 集合 list1 同时 UserDataRangeMaPService 查询用户有权限的数据字段和枚举值，计算用户 有权限的id集合list2，最后取id集合list1和list2的并集返回给报表服务。

5.5 数据下载模块

无论是取数分析还是数据看板和数据报表等功能模块，都有对展示数据进行 下载的需求，方便用户日后对数据进行分类存档和汇总统计，所以数据下载可以 专门作为一个模块，抽象顶层逻辑，提供统一的下载任务管理调度能力，为其他 功能模块提供数据下载服务。

数据下载架构设计如图 5.18 所示，报表系统中的数据一般数据量比较大，且 存在数据分页展示的情况，不适合使用前端即时下载的方式，另外部分数据存在定时下载的需求，所以报表系统均采用后端下载服务定时执行+云文件系统存储 的方式实现数据下载，借助云文件系统存储可以实现通过链接随时下载历史文 件，保证历史文件不会丢失。

图 5.18 数据下载功能架构图 

数据下载实现主要由以下 5 个部分组成：

1   )抽象下载接口 DonwnloadService，提供下载数据计算抽象方法process()方 法 ， 使用 泛型 参 数 来兼容所 有 的 传参 ； 提 供 获得下 载 服务类 型 的 方 法 getServiceType()方法，返回值为定义好的serviceType枚举值。

2    )下载服务具体实现类 XxxDownloadServiceImpl，继承 DonwnloadService 接口，重写process()方法和getServiceType()方法，process()方法中根据传数计算 需要被下载的数据，getServiceType()方法返回已声明的对应serviceType。

3)下载定时任务 DownloadCronTask，实现 Spring 提供的 ScheduleTask 接口， 并重写run()方法，依据serviceType枚举值，通过Java反射机制和工厂模式得到 下载任务的具体实现类，并在run方法中执行实现类的process()方法，最终得到 需要下载的数据并将其转成 XLS 或者 CSV 文件。

4 )下载任务调度服务DownloadRpcService，下载任务生成时负责将用户信息、 传参信息、 serviceType 等数据写入到 MySQL 数据库中，并提供下载任务插入、 查询、终止、失败重试和设置优先级的功能。

5)云文件系统，持久化定时任务生成的数据报表文件到云服务器，返回文件 对应的网络URL，将URL在浏览器回车即可得到文件。

数据下载通过生成下载任务方式，实现对报表数据的下载和管理，基于上述 数据下载组成部分可以得到下载实现过程，如图 5.19 所示：

图 5.19 数据下载实现时序图 用户在页面发起数据下载请求后，系统调用 RPC 服务 DownloadRpcService 将请 求参数封装成下载任务写入TaskInfo表中。DownloadCronTask是数据定时下载 服务，以分布式多实例方式部署，其首先每隔一分钟去 TaskInfo 表中查询状态为 待执行的下载任务；然后提取下载任务参数使用 Java 反射机制生成下载具体实现 类，实现类中指定了用户数据权限的计算方式；之后定时任务运行该实现类得到 数据结果列表，并将数据结果以文件形式上传至云文件系统中得到文件对应的唯 一 URL；最后将URL写入数据库中，同时更改任务状态为已完成。上述过程完 成后，用户可以在下载任务管理页面中查看，并点击链接下载文件。

5.6 本章小结

本章是论文的重点章节，主要描述系统功能模块设计和具体实现。第 1 节介 绍多维取数模块的设计与实现，第 2 节介绍主题报表模块，通过分析业务需求确 定报表主题；第 3 节是自定义报表模块设计实现，包括元数据采集、自定义配置 SQL 模板生成和报表模板调用 4 部分；第 4 节和第 5 节是权限管理和数据下载模 块，设计基于 RBAC 的功能权限模型，使用下载任务实现数据下载。