模闆位址： 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報表連結 替換為你自己的報表連結
結語：
“大中台小前台”，作為緊貼業務的小前台，多多利用集團中台化的能力提高工作效率是非常好的選擇~希望這篇文章可以抛磚引玉，讓更多人享受到中台化的技術福利~
模闆已經釋出：