静态类型检查的重要
如果要把SQL按照编程语言的类型来分类的话,SQL应该属于一种描述型的动态语言。
用动态语言编写的程序,当达到一定的复杂度后,相比强类型静态编译语言来说,更容易出问题。
从2个例子可以看出静态类型检查的重要:
- 为JavaScript添加了静态类型检查的TypeScript语言的大流行
- Scala的极其复杂和完善的类型系统,使得非常多的Bug都可被编译器发现。(对于scala新手来说,大部分的时间可能都是在看为啥写的程序总是无法编译通过)
那么,是否可以对SQL增加更多的静态检查呢?
使用静态语言来实现SQL功能
最直接的想法可能是:用高级语言来实现SQL的功能,比如:
Spark中可以使用其Dataset API 来实现类似SQL一样的数据处理逻辑,比如,对于前文中的“customers”表:
我想来算出每个客户ID的总消费金额的话, 用 Spark 的Scala DSL来写:
val df = load_table("customers")
df.groupBy( functions.col("customer_id"))
.agg( functions.expr("SUM(unit * unit_price * (1 - discount))") )
.toSQL()
来实现如下SQL的逻辑:
SELECT customer_id, SUM(unit * unit_price * (1 - discount))
FROM customers
GROUP BY customer_id
虽然我们确实用Scala语言实现了SQL的逻辑但是: Scala的版本看着比SQL复杂多了,而且也没有怎么利用上类型信息
为什么我们没利用上类型信息
虽然我们用了Scala, 但是我们没有利用上类型信息,为什么?
因为虽然我们调用了:
val df = load_table("customers")
但是这个是数据库中的表,我们的程序在执行前是无法获取到其列信息的。那我们怎么解决这个问题呢?
我们可以分步来执行这个程序。比如:第一步从数据库获取到customers表的列信息,然后我们再基于列信息来增强 类Spark的DSL 的类型信息功能。
如何做到程序分步骤执行? 这时自然想到了数据科学领域非常流行的 notebook 功能。
Notebook 助力类型信息
说到Notebook,最流行的应该就是 Jupyter Notebook 了,不过我这里主要是用另一个源自于 Netflix 公司的 polynote, (https://github.com/polynote/polynote ),主要是:
- polynote 不光支持python,还原生的支持scala和spark
- polynote 的编辑器功能更强大,非常像是集成开发环境IDE,包括了自动补全,参数提示,直接在错误行上显示提示等。其编辑器使用了微软开源的monaco-editor (也是支持VS Code的编辑器)
- 支持在同一个notebook页面中,能混合 Scala, Python,SQL 和 Vega.js 等
- 代码相对还简单,所以,我也基于polynote做了些修改,并提交到了 polynote 官方仓库,成为了polynote的contributor之一。(有个能自己修改,并自己日常使用的开源工具真的不错!)
那么我们的流程将变为如下:
1). 在polynote笔记本中的第一个cell, 输入并执行:
val customers = load_table("customers")
那么,我们的程序将会读取服务器上的 customers 表的列信息,
并产生一个类 customers 来表示 customers表的列信息。
class customers extends BaseTable(
val id: LongColumn,
val customer_id: LongColumn,
val product_id: LongColumn,
val unit: IntColumn,
val unit_price: DoubleColumn,
val discount: DoubleColumn
)
2). 在笔记本第二个cell中,我们就可以输入:
val aggTable1 = customers.groupBy( customers.customer_id )
.agg( functions.sum( customers.unit * customers.unit_price * (1 - customers.discount)))
这里我们不再直接把这个聚合表转为SQL,而是同样的,注册一个新的表为: aggTable1,然后其列信息我们同样也可以获得,并生成一个新的类 class aggTable1
这一步比之前的改进:
- 当我们输入:
customers.
之后,编辑器能自动弹出相关的列来供我们选择。 - 我们的代码有了静态类型检查, 比如如果我有一个文本列 (StringColumn ) 类,那当我要计算其平均值的时候,编译器就可以检查出:StringColumn上不支持平均值的计算。
- 每一步的执行,只记录schema信息,而不是直接执行SQL获取所有数据,只有当最后需要所有的数据的时候,我们才会调用collect()等来实际获取结果。这也是和 Spark 的 Lazy Evaluation 是一样的
当我们能通过把一个虚拟表由另一个表创建出来后,我们理论上也可以无限的组合创建更多的表。
这样,我们的列类型静态检查终于借助 Scala 编译器 和 Polynote 笔记本的结合解决了!
注:polynote截图如下:
你说的很好,但是我还是继续用SQL
虽然有很多人说:复杂SQL不利于工程化,而用高级语言等操作数据,更能利用软件工程的诸多思想来维护,但是,SQL还是有很多很多优点的,所以,我想继续用SQL
没问题,其实SQL和 Scala DSL都只是工具,只要思想清晰了,无论你用啥工具,都是没问题的!
前文中,我还提到了用一个“转化程序”转化“由多个小的逻辑SQL组成的YAML文件”到“面向机器的复杂SQL”,这个其实也是一种工具,思路是化繁为简。所谓万变不离其宗,下面我们来用本文中的 polynote + SQL + scala 的形式来描述一下为啥这个和 “YAML转化程序”本质也是一样的。
YAML文件如下:(为了避免篇幅太多,只写了前面2个SQL)
Steps:
- name: step_filter_customer1
comment: 过滤掉非法客户
sql: |-
SELECT *
FROM customers
WHERE customers.is_delete=False
- name: step_calculate_total_sales
comment: 计算客户的总消费额
sql: |-
SELECT orders.customer_id,
MAX(customer_name) AS customer_name,
MAX(city) as city,
SUM(unit * unit_price * (1 - discount)) AS total_sales
FROM orders JOIN step_filter_customer1
ON orders.customer_id = step_filter_customer1.customer_id
GROUP BY orders.customer_id
ORDER BY total_sales DESC
cell1,step_filter_customer1
SELECT *
FROM customers
WHERE customers.is_delete=False
这一步的执行结果,我们并不是真正去数据库执行这个语句并获取数据, 而是,我们会去数据库查询: 如果执行这条SQL,那么它的结果的schema该如何 (有哪些列,都是什么类型),然后我们会和Scala一样, 同样记录其列信息,和生成一个Scala类。
并产生一个类 customers 来表示 customers表的列信息。
class customers extends BaseTable(
val id: LongColumn,
val customer_id: LongColumn,
val product_id: LongColumn,
val unit: IntColumn,
val unit_price: DoubleColumn,
val discount: DoubleColumn
)
这样SQL的结果,也能直接在Scala DSL中使用。
如何获取到结果的列信息呢,方法很多,这里只举出一种简单的实现,因为 customers 表本身是在数据库中存在的,那么我们可以向数据库执行如下SQL:
SELECT *
FROM customers
WHERE customers.is_delete=False
AND 1 = 0
而我们知道 1 不可能等于 0, 所以,数据库不会真正去执行这个查询,而只会返回一个有列信息的空的结果。
cell2,step_calculate_total_sales
这一步有些少许复杂。
SELECT orders.customer_id,
MAX(customer_name) AS customer_name,
MAX(city) as city,
SUM(unit * unit_price * (1 - discount)) AS total_sales
FROM orders JOIN step_filter_customer1
ON orders.customer_id = step_filter_customer1.customer_id
GROUP BY orders.customer_id
ORDER BY total_sales DESC
因为这一步骤中,引入了我们第一步产生的临时结果 step_filter_customer1,而它是不存在的,我们只支持它的列信息和一个生成的 scala class。
一种方式是: 利用 WITH 语句, 把步骤1的SQL 和 步骤2 的SQL合并成一个SQL,但是这个是有问题的:
- 当合并的SQL比较多时, 上面的 1=0 的把戏将不能生效,因为即使在最外层的SQL中加上1=0,但是其内部的复杂多层SQL仍然有可能是复杂计算,导致这个查询占用过多资源和非常慢。
- 如果我们的临时结果,并不是SQL产生的,而是 Scala DSL 产生的,那我们的合并逻辑就将更加复杂
所以,我们需要直接用之前记录的 step_filter_customer1 的列信息,来获取这个新的步骤中产生的表的列信息。即用这个:
产生SQL
WITH step_filter_customer1 AS (
SELECT NULL::bigint AS id,
NULL::bigint AS customer_id,
NULL::bigint AS product_id,
NULL::int AS unit,
NULL::float8 AS unit_price,
NULL::float8 AS discount
LIMIT 0
)
SELECT orders.customer_id,
MAX(customer_name) AS customer_name,
MAX(city) as city,
SUM(unit * unit_price * (1 - discount)) AS total_sales
FROM orders JOIN step_filter_customer1
ON orders.customer_id = step_filter_customer1.customer_id
AND 1 = 0
GROUP BY orders.customer_id
ORDER BY total_sales DESC
通过构造一个同名,同样类信息的空的表,来实现快速获得新的结果表的列信息。这样的话,Scala DSL生成的中间表,SQL中也可以直接使用。
而当真正执行最终的结果查询时,我们可以把其层层依赖的所有步骤都串起来,生成最终的SQL。
通过上面的步骤,我们可以实现,同一个笔记本中,既有scala生成的虚拟表,也有SQL生成的虚拟表,它们之间可以互相引用,充分发挥各自的特长! Notebook的组织形式应该比之前直接手写 YAML 的方式更易用了不少!
注:polynote截图如下: