通用数据查询框架的设计与实现

作为职场新人,免不了需要干一些Boss-orianted的报表工作。甚至,让你设计一个报表系统,一个BI系统。这时候,初级的解法通常都交给实习生去做,来一个需求写一个SQL一堆跟接口有关的东西,然后吭哧吭哧写前端。但是,作为老油条,我们怎么可以这么去做呢。于是乎设计了一套框架体系,已SQL + 低代码的形式,实现报表的自动化生成。SQL解析方案采用了DuridApache Velocity并结合JdbcTemplate构建,聚合方案利用AviatorScript构建。

这里我们仅讨论跟CRUD有关的工作。至于通过Hive/Flink拉数据,生成宽表,这些工作不可避免。

旧方法与新思路

这里描述的旧方法指的是,来一个图表需求,从SQL到DAO到Service到Controller写一整套。这样做工作量及其庞大,重复性非常大,可抽象可定制性几乎为0,全靠Copy-Paste。前端也是如此。有实习生(可怜的实习生)还好说,自己写真的是毫无营养。受到Grafana、Davinci等系统的启发。能不能只写SQL,预生成并解析SQL,来生成接口,至于前端,交给专业的人便是。

本方法的逻辑思路可以理解为:解析模板SQL -> 生成模板SQL -> JDBC查询。通过这种方式,将写代码写接口的工作退化到写SQL的工作,有效减少报表系统新需求所需的工作量。

架构设计

本方案的架构设计可规划为以下实体:

  • 数据源:注册并使用不同数据源,查询不同的库。可以仅存JDBC以及相关配置即可
  • 基础视图:用SQL、Velocity来实现有一定限制的动态SQL生成。其领域实体包含:
    • 视图,对应模板SQL、数据源等
    • 变量,该视图所允许的变量
  • 复合视图:对基础视图通过特定方法进行聚合,达到类似于SQL中JOIN的效果
    • 视图:复合视图本体定义
    • 视图映射:一个视图映射到一到多个视图的映射关系,用于设计聚合视图的映射表
    • 变量:指定该视图可用的变量
    • 变量映射:从复合视图变量映射到被映射的视图的变量

实体关系

通过如此设计,其整体的执行流程大概为,当用户请求某个接口时,通过接口URI映射到特定视图上。通过解析变量,生成SQL,利用JdbcTemplate查得数据,并返回给前端。下文会详细描述。
执行流程

基础视图解析

基础试图旨在通过定义模板SQL,可带入变量套入到JdbcTemplate进行查询。其中定义原始变量与模板变量,原始变量通过Velocity解析到SQL中,用于动态生成、字段操作等;模板变量通过编写解析器,会将其解析为JdbcTemplate需要的“?”。如select * from Apple where day >= {{day}}可以被解析为select * from Apple where day >= ?,同时参数为传入的day。更详细地,e.g.:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
#if($groupBy.contains("b1"))
b1_id,
b1_name,
#end
#if($groupBy.contains("b2"))
b2_id,
b2_name
from biz_sum
where
day >= {{start}} and day <= {{end}}
group by
#if($groupBy.contains("b1"))
b1_id,
b1_name,
#end
#if($groupBy.contains("b2"))
b2_id,
b2_name,
#end
day

当传入的参数为groupBy=b1,start=2022-02-02,end=2022-02-20的情况下,其解析结果为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
b1_id,
b1_name,
-- 会有空白行,可以通过别的手段去掉,但是不影响执行。


where
day >= ? and day <= ?
group by
b1_id,
b1_name,
day

-- params: 2022-02-02, 2022-02-20

通过这种模式,动态生成SQL与返回字段,实现动态查询。这里的关键在于,用在Velocity上的变量要尽量为操作变量而非数值变量,如上面的groupBy,不直接参与SQL的生成。这样可以预防可能的SQL注入。这里跟Mybatis#{}${}的道理是一样的。

基础视图流程

创建

创建流程可以大致划分为:

  • 校验数据源是否存在
  • 校验视图,如视图名称、模板变量、数据变量是否定义,检查变量是否在规范的类别内

执行

执行流程:

  • 必填变量校验
  • Velocity变量代入生成
  • SQLTemplate变量对齐,生成templateSQL
  • 交由DatasourceHandlerManager跑SQL

DatasourceHandler

每个数据源所对应的执行器,通过封装JdbcTemplate,使用Druid连接池实现,并重写queryForListqueryForObject等,同时可以拓展出queryForPage等语法糖性质的方法。
DatasourceHandler 类实现

DatasourceHandlerManager

用于管理DatasourceHandler的模块,需要保证并发查询时的正确性:

  • 不存在的数据源对应的DatasourceHandler,或者存在旧版本的handler,上锁并创建之
  • 定期清理不使用的连接池,降低系统悬吊的连接数量

DatasourceHandlerManager

复合视图解析流程

复合视图是在基础视图的基础上,通过视图映射、变量映射实现聚合查询。复合视图的变量可以映射到一到多个基础视图的变量,通过该方法实现基础视图的变量填充。同时,复合视图可以通过递归定义来实现多级JOIN。复合视图在代码设计的过程中可以通过多线程执行来并行获取数据,加速数据的获得。

一个三级的映射关系:(1) 最底层为Dataview,用SQL执行;(2) 第二层的MapView通过变量映射到Dataview,实现变量关联;(3) 第三级的变量通过与第二级建立联立,实现第二层的变量关联。执行流程中,第(2)、(3)层都通过代码的形式关联。利用DFS获取底层数据并聚合。

其中,最为关键的有三个函数:Map函数,Reduce函数,Filter函数。在实现中,这三个函数都用AviatorScript代入执行。(1) Map函数的语义定义为(obj) -> keyString,将每个数据行映射到一个集合下;(2) Reduce函数语义为(args: arg[]) -> List<Map<String, Object>>,是合并多个从属视图的方法。(3) Filter函数即为固定的过滤方法。

e.g.:

1
2
3
4
5
6
7
# map.ts (伪代码) 
# interface A {
# day: string;
# b1_id: string;
# val: number;
# }
return a['day'] + a['b1_id']

其执行流程为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# execute (伪代码)

let tableA : ObjectA = xxx;
let tableB : ObjectB = yyy;

let a = new HashMap<String, ObjectA>();
let b = new HashMap<String, ObjectB>();
for i in tableA {
a.put(mapFunc(i))
}
for i in tableB {
b.put(mapFunc(i))
}

return a, b

Reduce为:

1
2
3
4
5
6
7
8
# reduce 伪代码
(a:Map<String, Object>, b:Map<String, Object>) => {
let result = a;
for (var i in a) {
i['sth'] = b['sth']
}
return result;
}

Filter的含义就相对简单。

复合视图的设计缘由

为什么要这么复杂的视图映射,而不是使用LEFT JOIN等原始SQL?

这里的实现为解决两个问题:

  1. 解决不同复杂度、不同指标合并的SQL查询需求
    如:要求计算每日用量的同时,在每日用量的结构上附带当日、当周、当月的同比、环比。这种情况下,明显同比、环比所需的数据量要远超当日数据,而且LEFT JOIN写出来会非常丑。
  2. 解决视图复用的需求
    通过低代码的形式合并数据,将子查询利用起来,减少重复的工作。

该方法 VS 编写DSL to SQL解析引擎

最开始的时候,笔者试图通过编写解析引擎,实现 DSL to SQL 这种高端操作,类似于 SQL to ES query。但后面发现,工作量并不是一个人能够搞定的。

显然,该方法的复杂程度并不低。但比起编写DSL解析器,将DSL生成AST再解析成PreparedStatement,这种设计的工作量是一个人可以完成且接受的。而语言翻译的工作量就不是一个人能解决的了。而这种设计的好处在于,框架工作量能接受,同时也能大幅度降低编写数据接口所需的工作量。

评论

Your browser is out-of-date!

Update your browser to view this website correctly.&npsb;Update my browser now

×