模板地址：
https://yida.alibaba-inc.com/newApp.html?#/template/TPL_LPS8OWKVUIEHEVM80XFN?_k=z3r1e4
背景：
之前在内网上有讨论SQL算不算是编程语言，一石激起千层浪，大家参与度非常高，有很多运营同学也表示会用SQL，我觉得这个是很好的现象。编程语言只是解决问题的工具，黑猫白猫，能抓到老鼠就是好猫。
这个问题也侧面说明了SQL确实是一门上手难度低的语言，因为SQL语法相对简单，而且集团又有DataWorks这种线上运行SQL的工具，不需要本地配置各种复杂的环境，再加上几乎所有业务线都有专门的数据研发同学会建立相应的数据仓库，尽可能地减少使用同学的多表JOIN操作，使得没有编程基础的同学也可以很快的使用SQL解决自己的数据需求。
但是在实际业务场景中，还是有很多同学不能很好地使用SQL，还是有很多的临时取数的需求。大家用不好SQL的原因总结为以下几个痛点：
- 不会SQL语法
- 不知道要用哪张表
- 不知道要用哪个字段
- 不知道字段里都有哪些值
- 不知道特殊的业务逻辑
一般的做法是维护各种教程、文档、使用说明、积累SQL模板等等，但是实际效果并不理想，因为业务经常变动维护文档确实也是一件麻烦事，而且一旦字段多了以后看文档都能晕掉。
如果你也在为上面的问题而苦恼，那下面说的解决方案或许会帮到你
界面截图：
功能说明：
- 根据选择的筛选条件动态生成SQL
- 下拉列表内容为接口返回，不需要人工手动维护
- 所有输入都有格式检查，防止输入错误数据
- 可以使用之前的配置JSON快速生成SQL
有同学会问了，实现这些功能需要额外的前端后端的资源，但是我们团队的资源很紧张，没有余力开发一个这样的工具怎么么办？
完全不用担心，你们看到的功能全都是基于现有的工具做的，不需要从0开始开发。
用到的工具有：
下面我会一步一步教大家搭建一个属于自己业务的自动化SQL生成工具。
--------------------------------------- 我是分割线 -----------------------------------
1. 搭建界面
本教程使用宜撘演示，乐高比宜撘更自由，上手难度更高。
根据自己的业务经验总结出来常用的筛选条件，设置筛选条件，原则上能通过下拉选择完成的就不要用输入框，要相信 永远不要相信用户的输入 实在需要用输入框，则一定要对输入进行校验。
宜撘可以很方便的设置一些简单的校验规则，如果需要更高级的操作，可以通过自定义函数解决
格式校验的代码网上搜有很多例子，下面是一个只允许输入英文(小写)及数字及下划线的校验函数：
function validateRule(value) {
var re = /^[0-9a-z_]+$/;
return re.test(value)
}
我们有很多的筛选条件，但是用户不一定会用到所有的条件，我们希望当用户需要某个筛选条件的时候显示对应的输入框，其他不需要的隐藏掉。宜撘可以很方便的设置这个功能：
大家看图应该就能理解，就不具体描述了。
搭建界面的部分是最简单的，几乎不用代码就可以完成
2. 设置数据源
很多时候我们的筛选条件是枚举值，但是这个值会很多而且还会变化，我们不想每次发生变化的时候去手动修改，那就可以用DataWorks的数据服务功能，这个功能可以将ODPS表封装为一个HTTP的接口供宜撘来调用。数据服务教程：https://www.atatech.org/articles/120600
建好数据服务接口以后就可以配置宜撘的数据源来调用这个接口：选择远程数据源，直接将HTTP的链接复制到URL里面，方法选择GET就可以了
但是我们会发现通过数据服务返回的数据格式不能直接用于下拉框的展示组件，需要我们进行格式处理，宜撘也提供这个处理接口，在数据源的 DID 输入框里面可以进行数据格式的处理。下拉框和多选的数据格式为：
{
"options":[
{
"text":"选项一",
"value":"1"
},
{
"text":"选项二",
"value":"2"
}
]
}
用我自己的处理方法做个例子：
function didFetch(content, ctx) {
// alert(JSON.stringify(content));
var dp = ctx.getDataPool();
// 拼接成下拉框需要的格式
var result = {};
var options = new Array();
for (var i = 0; i < types.length; i++) {
var type_content = new Object();
type_content.text = types[i].task_type;
type_content.value = types[i].task_type;
options.push(type_content);
}
result.options = options;
}
根据实际的DataWork数据服务的返回格式进行修改就可适配不同的组件。
但是有的时候我们需要根据选择的内容作为参数动态的去请求返回值，宜撘也提供了这个功能：文档https://go.alibaba-inc.com/help/quick-start (文档是乐高的，宜撘差别不大
例如我要根据我选择的任务类型去请求服务，新建一个变量类型的数据源selected_task_type
然后在需要传参的数据源里面配置参数，需要注意的是变量参数的顺序一定要在引用这个变量的数据源之前
3. 设置输入后动作
我们希望可以在每一个输入框的值发生变动之后，动态的对SQL进行生成，可以使用宜撘的动作设置功能。文档：https://yuque.antfin-inc.com/legao/legao/events-call
由于宜撘目前没有对于表单的表单ID(只有唯一标识而且不能修改，使用起来不方便)的设置，所以在这里我用传入的参数key来告诉处理方法目前操作的是什么对象。
/**
* 更新querry
*/
export function updateQueryString(ctx) {
var dp = ctx.getDataPool();
var task_tag;
var table_name;
// 1.获取当前更新的key
var query_key = ctx.params.key;
// 2.获取变更的值
var query_value = ctx.state.getValue('fieldData/value');
// 如果是导入的json则直接赋值
if(query_key == "full_query_json"){
dp.setValue("query_str", query_value);
var sql_string = createSQL(query_str_json);
dp.setValue("sql_string", sql_string);
}else{
// 3.如果是选择任务类型，则更改已选择任务类型
if(query_key == "task_type"){
// alert("原始任务类型： "+ctx.store.get('selected_task_type'));
dp.setValue("selected_task_type", query_value);
}
// console.log(JSON.stringify(query_value));
// 3.更新请求参数
var old_qyery_str = ctx.store.get('query_str');
// alert("原始参数: " + old_qyery_str);
var json_obj;
// alert('参数： '+ old_qyery_str);
try{
json_obj = JSON.parse(old_qyery_str);
}catch(err){
json_obj = new Object();
}
json_obj[query_key] = query_value;
if(query_key == "task_type"){
var type_map = ctx.store.get("task_table_map").getValue();
// alert(type_map);
var task_info = JSON.parse(type_map);
for ( var i = 0; i <task_info.length; i++){
if(task_info[i][0] == query_value){
json_obj["task_tag"] = task_info[i][1].task_tag;
json_obj["table_name"] = task_info[i][1].table_name;
break;
}
}
// console.log(JSON.stringify(task_info));
}
var query_str_json = JSON.stringify(json_obj);
// alert(query_str_json);
// 4. 将更新后的值放回变量
dp.setValue("query_str", query_str_json);
// 根据传入的json拼装SQL
var sql_string = createSQL(query_str_json);
dp.setValue("sql_string", sql_string);
}
}其中query_str就是用于存储生成的JSON的变量，同样也需要在数据源中定义，如果想在界面上显示的话，直接在输入框中输入 ${query_str} 即可
4. 生成SQL
根据上一步生成的js对象进行处理，拼接SQL显示在界面上即可，这个步骤与业务强相关，需要大家根据自己的业务进行高度定制，上面代码中的 createSQL()方法。在这里我只举一个简单的例子：
/**
* 根据JSON生成SQL
*/
function createSQL(cfg_json) {
var task_info = JSON.parse(cfg_json);
var task_type = task_info.task_type;
var table_name = task_info.table_name;
var querry_option = task_info.querry_option;
var is_save = "and t1.is_save = 'Y' \n";
if(task_info.is_save == "归档"){
is_save = "and t1.is_save = 'Y' \n";
}else{
is_save = "";
}
var total_cnt_limit = "";
var id_filiter = "";
var mark_criterion_filiter = "";
var date_filiter = "";
var time_filiter = "";
var speed_filiter = "";
var weather_filiter = "";
var channel_filiter = "";
try{
// 循环判断筛选条件
for(var i = 0 ; i < querry_option.length ; i ++){
var key = querry_option[i];
if(key == "限制总数"){
total_cnt_limit = "order by rand() desc \nlimit " + task_info.rec_num + " \n";
}
if(key == "根据日期"){
date_filiter = "";
if(task_info.date_filiter.startDate != null){
var date = new Date(task_info.date_filiter.startDate);
var Y = date.getFullYear();
var M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1);
var D = (date.getDate() < 10 ? '0'+(date.getDate()) : date.getDate());
date_filiter += "and to_char(t1.log_time,'yyyymmdd') >= '" + Y + M + D + "' \n"
}
if(task_info.date_filiter.endDate != null){
var date = new Date(task_info.date_filiter.endDate);
var Y = date.getFullYear();
var M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1);
var D = (date.getDate() < 10 ? '0'+(date.getDate()) : date.getDate());
date_filiter += "and to_char(t1.log_time,'yyyymmdd') <= '" + Y + M + D + "' \n"
}
}
if(key == "根据时间"){
time_filiter = "";
if(task_info.start_time != null){
time_filiter += "and substr(t1.log_time,12,2) >= '" + task_info.start_time + "' \n"
}
if(task_info.end_time != null){
time_filiter += "and substr(t1.log_time,12,2) <= '" + task_info.end_time + "' \n"
}
}
}catch(err){
console.error(err);
}
var sql ="drop table if exists aicar_" + task_info.task_tag + "_" + task_info.task_id +"; \n"
+"create table if not exists aicar_" + task_info.task_tag + "_" + task_info.task_id +" as \n"
+"select distinct\n"
+" t1.uid \n"
// +" , t1." + task_info.task_tag + "_id as file_id \n"
+" , coalesce(t1.image_id,t1.pcd_id) as file_id \n"
+" , t1.car_id \n"
+" , t1.position \n"
+" , t1.speed \n"
+" , t1.extra_info1 \n"
+" , t1.results_json \n"
+" , t1.fmt_results_json \n"
+"from " + table_name + " t1 \n"
+"where t1.ds = max_pt('" + table_name + "') \n"
+"and t1.task_type = '" + task_type + "' \n"
+is_save
+id_filiter
+mark_criterion_filiter
+date_filiter
+time_filiter
+speed_filiter
+weather_filiter
+channel_filiter
+"and t1.is_valid = 'Y' \n"
+"and t1.is_empty = 'N' \n"
+"and t1.is_trap = 'N' \n"
+total_cnt_limit
+";";
return sql;
}根据以上步骤就可以实现自动生成SQL的功能了~直接复制生成的SQL到D2执行就可以了~希望这个方法可以让广大数据研发同学从枯燥的临时取数工作中解脱出来~提升工作效率~
什么？连复制粘贴运行都懒得做？想一键自动执行得到结果？虽然麻烦一点~不过这个需求也是可以满足的：
5. 调用HSF执行SQL（可选）
用到的工具：
1. DataWorks手动业务流程
2. 方舟流程平台（预计4月下旬对外BU开放使用）
DataWorks不久之前新发布的手动业务流程功能可以通过调用执行，可以通过API接收参数并执行相应的处理。例如将上述界面中的配置JSON作为参数掺入，然后在DataWorks中使用PyODPS节点用Python解析JSON然后再拼接SQL。
DataWorks手动业务流程可以通过API的方式调用，文档：https://yuque.antfin-inc.com/docs/share/f41a863a-1494-4d62-b6fb-4dccc59deff9#ezchau
https://help.aliyun.com/document_detail/93253.html
在本案例中我使用的是人工智能实验室的方舟流程平台对接，方舟已经实现了这个接口的组件，方舟平台预计4月下旬开发外BU使用。由于没有对外使用，在这里先不做介绍，等对外开放的时候会有使用的介绍。目前用到的功能就是对手动业务流程的API做了一层封装，并且可以可视化的编辑流程，调用各种服务(包括HSF)，也可以使用HSF的方式进行调用。下图是目前的界面，大家可以期待一下。
宜撘除了提供调用HTTP的接口，还提供了调用HSF的功能（毕竟我们很多服务都是通过HSF提供的）。
设置完服务之后就可以在宜撘中进行调用，本案例以流程页面为例，选择流程编辑界面：
添加自动节点，并在动作设置中选择刚才添加的HSF服务，这个服务有两个参数，第一个是对应的方舟流程CODE，第二个就是传入手动节点的参数，在这里我们选择上面我们自己拼装的JSON参数
这样在界面上点击提交之后就会自动调用手动节点进行处理~~
你说啥？？？你还想不跑SQL能直接看到结果？？放心。。。这都不是事儿~~
6. 使用FBI生成统计报表
直接上文档：https://yuque.antfin-inc.com/quark/help/firstpcreport
将手动流程的结果表拖入FBI做展示就OK了~新版的FBI非常好用~谁用谁知道~
宜撘也可以直接嵌入FBI报表做展示，在页面上插入HTML组件，手动编辑HTML：
<div><iframe name="fbi_report" id="fbi_report" src=" FBI报表链接 " width="100%" height="700" frameborder="0" align="left"></div>将上述代码的 FBI报表链接 替换为你自己的报表链接
结语：
“大中台小前台”，作为紧贴业务的小前台，多多利用集团中台化的能力提高工作效率是非常好的选择~希望这篇文章可以抛砖引玉，让更多人享受到中台化的技术福利~
模板已经发布：https://yida.alibaba-inc.com/newApp.html?#/template/TPL_LPS8OWKVUIEHEVM80XFN?_k=z3r1e4
