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

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

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的情况下，其解析结果为：

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连接池实现，并重写queryForList、queryForObject等，同时可以拓展出queryForPage等语法糖性质的方法。

DatasourceHandlerManager

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

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

复合视图解析流程

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

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

e.g.:

# map.ts (伪代码) 
# interface A {
#   day: string;
#   b1_id: string;
#   val: number; 
# }
return a['day'] + a['b1_id']

其执行流程为：

# 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为：

# 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，这种设计的工作量是一个人可以完成且接受的。而语言翻译的工作量就不是一个人能解决的了。而这种设计的好处在于，框架工作量能接受，同时也能大幅度降低编写数据接口所需的工作量。