MLSQL： 可编程的SQL
前言
SQL简单易用，但是现在大家用的多了，也慢慢发现它还是存在诸多问题的。我们认为良好的编程应该是创建小型、可理解、可重用的逻辑片段，并且这些逻辑片段还要被测试、被命名、被组织成包，而这些包之后可以用来构造更多有用的逻辑片段，这样的工作流程才是合理又便捷的。更进一步的，这些“高阶”能力应该是可选的，我们总是希望用户一开始能用最简单的方式来完成手头的工作。尽管如此，现在的SQL并不能很好的支持我们前面提到的这些。
MLSQL完善了SQL对这些功能的支持，通过插件我们还支持覆盖了AI领域。那MLSQL是如何做到这一点的呢？
- 保留了原生SQL的所有优点
- 强大的插件能力，语言自身高度可扩展，这也是其能支持AI的关键之一（譬如Python插件就可以让MLSQL支持Python）
- 支持include语法以及模板，实现了创建小型、可理解、可重用的逻辑片段，并且这些逻辑片段还要被测试、被命名、被组织成包的能力
- 支持if/else，实现了分支能力
MLSQL使得通过SQL完成一个大型的项目变得可能。时代在变，SQL也需要与时俱进，而MLSQL正好是这个与时俱进的结果。
Talk is cheap, Show me the code
下面一条SQL是从网上随便找的，大家可以看到，这条SQL从结构上具备了复杂化的潜质，子查询，Join等用的不亦乐乎。然而和真实的业务场景里的SQL复杂度比前来，这条SQL的复杂度可能是孙子级别的（几百行的SQL很正常）。
如果我们仔细思考下，我们至少发现两点：
- 这条SQL语句嵌套比较多，也就是里面有不少子查询以及join(union)查询。无论对于计算机还是人，嵌套分支其实都不是友好的，对人而言加大了理解难度，对机器而言会影响流水线并行。
- SQL里很多比较复杂的结构比如case when会重复的使用在同一条SQL语句的多个地方，你会惊讶的发现没有办法复用。
还有一点，数仓是有严格管理的，通常也有专业的团队维护，这意味着你并不能随心所欲在在数仓创建你经常用到的表。比如你经常会组合数仓中的A,B,C表得到一张表D，该表一般作为子查询使用。遗憾的是，你可能没办法很容易的去说服数仓团队帮你持久化D表。不得已，你可能需要有个小本本记住这条SQL，然后需要用的时候复制拷贝黏贴进你的业务SQL（大部分情况会作为子查询）。
因为当前大部分系统实现的SQL是以语句为单位的，多条SQL要联系起来，需要将表落地，而这个成本是比较高昂的，所以大家尽可能将一个功能需求在一条SQL中来完成，这又反向导致SQL变得很复杂。
现在，我们来用MLSQL来简化上面那条SQL。MLSQL 是面向大数据和AI的一门语言，对SQL做了一定的增强，使得SQL更适用于脚本。
打平SQL，线性结构最符合大脑
首先，我们先把SQL展开，顺序化。
在MLSQL中做法很简单，把子查询都摘录出来，然后给每个子查询通过在后面加`as [TableName]`的方式取个名字，方便后续引用，最后，在每个子查询语句结尾处用分号来表示该语句的结束。如下图，我们将一个复杂的单条SQL 语句拆解成了好几条语句。
在MLSQL Console里是这样的：
这个脚本是可以直接运行的，只需点击Run即可。 我们看到MLSQL允许你将一条条子查询独立出来，每条语句使用分号进行分割。其次，在后续语句中，你可以直接引用已经独立出来的子查询。进一步的，为了保持语法上的一致，MLSQL要求所有Select语句都需要以as TableName结尾。通过打平SQL语句，使得单条SQL复杂度有了很大的降低。
将多条SQL语句拆开成多个文件
前面我们提到，良好的编程应该是创建小型、可理解、可重用的逻辑片段，并且这些逻辑片段还要被测试、被命名、被组织成包，而这些包之后可以用来构造更多有用的逻辑片段。MLSQL通过include语法完成这个能力。
手动创建a.mlsql, b.mlsql, main.mlsql 三个脚本。
a.mlsql
b.mlsql
main.mlsql
可以看到，MLSQL通过include将某些脚本包含到另外一个脚本中。最后目录结构是这样的：
这意味着，如果以后你要用a表，b表，你可以在你的新脚本里进行inlcude就可以复用了。
消除重复语句
前面，我们通过include可以包含一段有价值的，理论上可以独立运行的脚本。然而，很多情况，我们只是部分语句是重复的，而这种重复，原生的SQL几乎只能拷贝复制黏贴：
MLSQL的模板功能可以很好的解决这个问题：
首先，我通过set语法设置一个模板。文本。里面{0} {1}之类的表示占位符，也就是需要被替换的变量。接着就可以在其他地方通过template.get 来进行模板的渲染了，典型用法如下：
${template.get("selectTemplate","a","b")}比如这句渲染后的结果为：
a.player AS player ,
a.lose AS totallose,
b.win AS totalwin,
(totallose+totalwin) AS totalset语句也可以独立成单个文件，这样其他项目要使用这个模板就只要引入合适的文件即可。下面是最后的目录结构：
分支语法
在原生SQL中，是没有分支语法的。MLSQL补全了这个短板，下面是一个例子：
set a = "wow,jack";
!if ''' split(:a,",")[0] == "jack" ''';
select 1 as a as b;
!else;
select 2 as a as b;
!fi;
select * from b as output;Python支持
MLSQL通过!ray可以支持执行python代码，我们使用功能Python脚本处理一个表的数据，处理的结果可以形成一张新表，然后继续让SQL进行处理。
下面是一个例子：
set mockData='''
{"title":"第一","body":"内容1"}
{"title":"第二","body":"内容2"}
{"title":"第三","body":"内容3"}
''';
load jsonStr.`mockData` as data;
!python env "PYTHON_ENV=:";
!python conf "runIn=driver";
!python conf "schema=st(field(title,string),field(body,string))";
!python conf "dataMode=data";
!ray on data '''
import ray
from pyjava.api.mlsql import RayContext
import numpy as np;
ray_context = RayContext.connect(globals(),"auto")
def echo(row):
row1 = {}
row1["title"]="jackm"
row1["body"]= row["body"]
return row1
ray_context.foreach(echo)
''' named newdata;
select * from newdata as output;使用Java/Scala扩展SQL函数
对于不存在的SQL函数，我们可以随时随地注册和使用。
set mockData='''
{"title":"第一","body":"内容1"}
{"title":"第二","body":"内容2"}
{"title":"第三","body":"内容3"}
''';
load jsonStr.`mockData` as data;
register ScriptUDF.`` as substring where
lang="scala"
and code='''def apply(str:String,start:Int,end:Int)={
str.substring(start,end)
}'''
and udfType="udf";
select substring(body,0,2) from data limit 1 as output;总结
通过上面语法特性的介绍，可以看到，MLSQL在保留了SQL的简单的同时，也支持了可“编程性”。让用户可以通过类似SQL这种声明式语言也能够完成大型项目。
附录：
AI能力实操视频：
使用MLSQL Notebook 进行数据探索和AI模型开发_哔哩哔哩 (゜-゜)つロ 干杯~-bilibili
流批能力实操视频：
利用MLSQL开发流程序_哔哩哔哩 (゜-゜)つロ 干杯~-bilibili
SQL 代码智能补全实操视频：