Python sqlparse解析SQL表血緣追蹤實現(xiàn)
引言
SQLparse的開源庫解析中就說過自己在尋找在python編程內(nèi)可行的SQL血緣解析，JAVA去解析Hive的源碼實踐的話我還是打算放到后期來做，先把Python能夠?qū)崿F(xiàn)的先實現(xiàn)完。主要是HiveSQL的底層就是JAVA代碼，怎么改寫還是繞不開JAVA的。不過上篇系列我有提到過sqlparse，其實這個庫用來解析血緣的話也不是不可以，但是能夠?qū)崿F(xiàn)的功能是有限的，目前我實驗還行，一些較為復(fù)雜的SQL也能解析得出，算是成功達到可部署服務(wù)的水準(zhǔn)了，但是根據(jù)SQL格式來匹配的話肯定是有些SQL格式不能完全匹配成功的，如果大家有需要血緣分析的SQL可以再次驗證一下。
算是填完了之前的部分坑，目前的開發(fā)進度已經(jīng)可以將SQL的表血緣分析追蹤實現(xiàn)了，實現(xiàn)字段血緣的功能開發(fā)等后續(xù)將陸續(xù)上線。
一、主線任務(wù)
首先來對該項目的目標(biāo)來分析一下，說到SQL血緣分析，這偏向于數(shù)據(jù)治理。
1.數(shù)據(jù)治理
數(shù)據(jù)治理（Data Governance）是組織中涉及數(shù)據(jù)使用的一整套管理行為。數(shù)據(jù)要產(chǎn)生價值，需要一個合理的“業(yè)務(wù)目標(biāo)”，數(shù)據(jù)治理的所有活動應(yīng)該圍繞真實的業(yè)務(wù)目標(biāo)而開展，建立數(shù)據(jù)標(biāo)準(zhǔn)、提升數(shù)據(jù)質(zhì)量只是手段，而不是目標(biāo)。
數(shù)據(jù)治理的本質(zhì)是管理數(shù)據(jù)，因此需要加強元數(shù)據(jù)管理和主數(shù)據(jù)管理，從源頭治理數(shù)據(jù)，補齊數(shù)據(jù)的相關(guān)屬性和信息，比如：元數(shù)據(jù)、質(zhì)量、安全、業(yè)務(wù)邏輯、血緣等，通過元數(shù)據(jù)驅(qū)動的方式管理數(shù)據(jù)生產(chǎn)、加工和使用。
數(shù)據(jù)的質(zhì)量直接影響著數(shù)據(jù)的價值，并且直接影響著數(shù)據(jù)分析的結(jié)果以及我們以此做出的決策的質(zhì)量。數(shù)據(jù)模型血緣與任務(wù)調(diào)度的一致性是建管一體化的關(guān)鍵，有助于解決數(shù)據(jù)管理與數(shù)據(jù)生產(chǎn)口徑不一致的問題，避免出現(xiàn)雙重管理不一致的低效管理模式。
2.血緣追蹤
數(shù)據(jù)被業(yè)務(wù)場景使用時，發(fā)現(xiàn)數(shù)據(jù)錯誤，數(shù)據(jù)治理團隊需要快速定位數(shù)據(jù)來源，修復(fù)數(shù)據(jù)錯誤。那么數(shù)據(jù)治理團隊需要知道業(yè)務(wù)團隊的數(shù)據(jù)來自于哪個核心庫，核心庫的數(shù)據(jù)又來自于哪個數(shù)據(jù)源頭。我們的實踐是在元數(shù)據(jù)和數(shù)據(jù)資源清單之間建立關(guān)聯(lián)關(guān)系，且業(yè)務(wù)團隊使用的數(shù)據(jù)項由元數(shù)據(jù)組合配置而來，這樣，就建立了數(shù)據(jù)使用場景與數(shù)據(jù)源頭之間的血緣關(guān)系。 數(shù)據(jù)資源目錄：數(shù)據(jù)資源目錄一般應(yīng)用于數(shù)據(jù)共享的場景，例如部門之間的數(shù)據(jù)共享，數(shù)據(jù)資源目錄是基于業(yè)務(wù)場景和行業(yè)規(guī)范而創(chuàng)建，同時依托于元數(shù)據(jù)和基礎(chǔ)庫主題而實現(xiàn)自動化的數(shù)據(jù)申請和使用。
也就是為什么我們需要解析SQL，追蹤建表索引或者引用解析。
3.SQL表血緣
那么其中最重要的就是關(guān)于各個數(shù)據(jù)庫之間的數(shù)據(jù)關(guān)系了，關(guān)于建表以及插入更新操作都會使數(shù)據(jù)發(fā)生一定的改變，那么這些操作就一定是被允許的？就像原來在網(wǎng)上看到的某某公司程序員刪庫跑路，或者是一不小心刪錯數(shù)據(jù)導(dǎo)致耽誤產(chǎn)研線等等。為了防止以上事故的出現(xiàn)必定要為此操作上一層保險，為每個成員設(shè)定數(shù)據(jù)操作權(quán)限。這樣以來提交的SQL語句就多了一層判斷。
二、實現(xiàn)過程
1.目標(biāo)效果
首先明白一點我們要做出的東西需要呈現(xiàn)一個怎樣的形式，其中位于行業(yè)前排的無疑是SQLFlow：
當(dāng)?shù)谝淮慰吹酱藞D我就決定血緣追蹤就應(yīng)該是這個樣子，能夠清晰的解析出每個字段和表之間的血緣關(guān)系。以此我們設(shè)定輸出的基準(zhǔn)，我們要做的項目目標(biāo)就是如此。
2.代碼實現(xiàn)
1.功能函數(shù)識別
該功能也是必須要實現(xiàn)的功能，我們需要明白這個SQL主要是干什么事情的。如果是插入INSERT或者是CREATE就有血緣分析的必要，如果是SELECT的話那么做簡單的SQL解析即可。有了研究sqlparse源碼的成果我們調(diào)用相應(yīng)的函數(shù)即可：
sql="select * from table1;insert into table select a,b,c from table2"
if __name__ == '__main__':
table_names=[]
#sql=get_sqlstr('read_sql.txt')
stmt_tuple=analysis_statements(sql)
for each_stmt in stmt_tuple:
type_name=get_main_functionsql(each_stmt)
print(type_name)
輸出：
那么對于SELECT我們就SQL涉及到的表追溯即可：
對于CREATE和INSERT的做血緣即可：
2.SQL標(biāo)準(zhǔn)格式
對于傳入的SQL我們首先要讓這條語句符合標(biāo)準(zhǔn)的SQL語句格式，這樣對于傳輸格式保持一致，兼容很有作用。一般我們都是通過文本來讀入。故需要讀取文本做處理： 原始文本：
處理后：
if __name__ == '__main__':
sql=get_sqlstr('read_sql.txt')
print(sql)
3.解析AST樹
得到的SQL無論是ANTRL還是SQLPARSE都是解析為一棵樹的形式進行遞歸回溯。最終都要解析生產(chǎn)的SQL樹：
sql="select * from table1;insert into table3 select a,b,c from table2"
if __name__ == '__main__':
#sql=get_sqlstr('read_sql.txt')
stmt_tuple=analysis_statements(sql)
for each_stmt in stmt_tuple:
table_names=[]
type_name=get_main_functionsql(each_stmt)
get_ASTTree(each_stmt)
4.最終效果：
SQL：
select
b.product_name "產(chǎn)品",
count(a.order_id) "訂單量",
b.selling_price_max "銷售價",
b.gross_profit_rate_max/100 "毛利率",
case when b.business_type =1 then '自營消化' when b.business_type =2 then '服務(wù)商消化' end "消化模式"
from(select 'CRM簽單' label,date(d.update_ymd) close_ymd,c.product_name,c.product_id,
a.order_id,cast(a.recipient_amount as double) amt,d.cost
from mysql4.dataview_fenxiao.fx_order a
left join mysql4.dataview_fenxiao.fx_order_task b on a.order_id = b.order_id
left join mysql7.dataview_trade.ddc_product_info c on cast(c.product_id as varchar) = a.product_ids and c.snapshot_version = 'SELLING'
inner join (select t1.par_order_id,max(t1.update_ymd) update_ymd,
sum(case when t4.product2_type = 1 and t5.shop_id is not null then t5.price else t1.order_hosted_price end) cost
from hive.bdc_dwd.dw_mk_order t1
left join hive.bdc_dwd.dw_mk_order_status t2 on t1.order_id = t2.order_id and t2.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql7.dataview_trade.mk_order_merchant t3 on t1.order_id = t3.order_id
left join mysql7.dataview_trade.ddc_product_info t4 on t4.product_id = t3.MERCHANT_ID and t4.snapshot_version = 'SELLING'
left join mysql4.dataview_scrm.sc_tprc_product_info t5 on t5.product_id = t4.product_id and t5.shop_id = t1.seller_id
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
and t2.valid_state in (100,200) ------有效訂單
and t1.order_mode = 10 --------產(chǎn)品消耗訂單
and t2.complete_state = 1 -----訂單已經(jīng)完成
group by t1.par_order_id
) d on d.par_order_id = b.task_order_id
where c.product_type = 0 and date(from_unixtime(a.last_recipient_time)) > date('2016-01-01') and a.payee_type <> 1 -----------已收款
UNION ALL
select '企業(yè)管家消耗' label,date(c.update_ymd) close_ymd,b.product_name,b.product_id,
a.task_id,(case when a.yb_price = 0 and b.product2_type = 1 then b.selling_price_min else a.yb_price end) amt,
(case when a.yb_price = 0 and b.product2_type = 2 then 0 when b.product2_type = 1 and e.shop_id is not null then e.price else c.order_hosted_price end) cost
from mysql8.dataview_tprc.tprc_task a
left join mysql7.dataview_trade.ddc_product_info b on a.product_id = b.product_id and b.snapshot_version = 'SELLING'
inner join hive.bdc_dwd.dw_mk_order c on a.order_id = c.order_id and c.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join hive.bdc_dwd.dw_mk_order_status d on d.order_id = c.order_id and d.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql4.dataview_scrm.sc_tprc_product_info e on e.product_id = b.product_id and e.shop_id = c.seller_id
where d.valid_state in (100,200) and d.complete_state = 1 and c.order_mode = 10
union ALL
select '交易管理系統(tǒng)' label,date(t6.close_ymd) close_ymd,t4.product_name,t4.product_id,
t1.order_id,(t1.order_hosted_price-t1.order_refund_price) amt,
(case when t1.order_mode <> 11 then t7.user_amount when t1.order_mode = 11 and t4.product2_type = 1 and t5.shop_id is not null then t5.price else t8.cost end) cost
from hive.bdc_dwd.dw_mk_order t1
left join hive.bdc_dwd.dw_mk_order_business t2 on t1.order_id = t2.order_id and t2.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
left join mysql7.dataview_trade.mk_order_merchant t3 on t1.order_id = t3.order_id
left join mysql7.dataview_trade.ddc_product_info t4 on t4.product_id = t3.MERCHANT_ID and t4.snapshot_version = 'SELLING'
left join mysql4.dataview_scrm.sc_tprc_product_info t5 on t5.product_id = t4.product_id and t5.shop_id = t1.seller_id
left join hive.bdc_dwd.dw_fact_task_ss_daily t6 on t6.task_id = t2.task_id and t6.acct_time=date_format(date_add('day',-1,current_date),'%Y-%m-%d')
left join (select a.task_id,sum(a.user_amount) user_amount
from hive.bdc_dwd.dw_fn_deal_asyn_order a
where a.is_new=1 and a.service='Trade_Payment' and a.state=1 and a.acct_day=substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
group by a.task_id)t7 on t7.task_id = t2.task_id
left join (select t1.par_order_id,sum(t1.order_hosted_price - t1.order_refund_price) cost
from hive.bdc_dwd.dw_mk_order t1
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2) and t1.order_type = 1 and t1.order_stype = 4 and t1.order_mode = 12
group by t1.par_order_id) t8 on t1.order_id = t8.par_order_id
where t1.acct_day = substring(cast(DATE_ADD('day',-1,CURRENT_DATE) as varchar),9,2)
and t1.order_type = 1 and t1.order_stype in (4,5) and t1.order_mode <> 12 and t4.product_id is not null and t1.order_hosted_price > 0 and t6.is_deal = 1 and t6.close_ymd >= '2018-12-31'
)a
left join mysql7.dataview_trade.ddc_product_info b on a.product_id = b.product_id and b.snapshot_version = 'SELLING'
where b.product2_type = 1 -------標(biāo)品
and close_ymd between DATE_ADD('day',-7,CURRENT_DATE) and DATE_ADD('day',-1,CURRENT_DATE)
GROUP BY b.product_name,
b.selling_price_max,
b.gross_profit_rate_max/100,
b.actrul_supply_num,
case when b.business_type =1 then '自營消化' when b.business_type =2 then '服務(wù)商消化' end
order by count(a.order_id) desc
limit 10
if __name__ == '__main__':
table_names=[]
sql=get_sqlstr('read_sql.txt')
stmt_tuple=analysis_statements(sql)
for each_stmt in stmt_tuple:
type_name=get_main_functionsql(each_stmt)
blood_table(each_stmt)
Tree_visus(table_names,type_name)
以上就是Python sqlparse解析SQL表血緣追蹤實現(xiàn)的詳細內(nèi)容，更多關(guān)于Python sqlparse血緣追蹤的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
基于python的圖片修復(fù)程序（實現(xiàn)水印去除）
這篇文章主要給大家介紹了關(guān)于python圖片修復(fù)程序的相關(guān)資料，可以用于實現(xiàn)圖片中水印去除，主要利用的是OpenCV這個框架實現(xiàn)的，文中通過示例代碼介紹的非常詳細，需要的朋友可以參考借鑒，下面來一起看看吧2018-06-06
這篇文章主要為大家詳細介紹了wxPython實現(xiàn)繪圖小例子，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-11-11
這篇文章主要介紹了使用Python控制攝像頭拍照并發(fā)郵件的相關(guān)知識，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2019-04-04
python 實現(xiàn)多進程日志輪轉(zhuǎn)ConcurrentLogHandler
這篇文章主要介紹了python 實現(xiàn)多進程日志輪轉(zhuǎn)ConcurrentLogHandler，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-03-03
今天小編就為大家分享一篇python 畫函數(shù)曲線示例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-12-12
這篇文章主要介紹了利用python 下載bilibili視頻，幫助大家更好的理解和使用python，感興趣的朋友可以了解下2020-11-11
在本篇內(nèi)容中小編給大家分享了關(guān)于python打印1~20的整數(shù)的具體步驟以及實例方法，需要的朋友們參考下。2019-07-07
這篇文章主要為大家詳細介紹了Python如何抓取天貓商品詳細信息及交易記錄，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-02-02
pycharm進行Git關(guān)聯(lián)和取消方式
這篇文章主要介紹了pycharm進行Git關(guān)聯(lián)和取消方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
使用PyTorch處理多維特征輸入數(shù)據(jù)的完美實現(xiàn)
在機器學(xué)習(xí)和深度學(xué)習(xí)領(lǐng)域,我們經(jīng)常會面對具有多維特征輸入的問題,這種情況出現(xiàn)在各種應(yīng)用中,包括圖像識別、自然語言處理、時間序列分析等,PyTorch是一個強大的深度學(xué)習(xí)框架,在本篇博客中,我們將探討如何使用PyTorch來處理多維特征輸入數(shù)據(jù)2023-10-10