基于Python-sqlparse的SQL表血缘追踪解析实现_jsqlparser数据血缘分析(1)

2401_84181326

于 2024-05-04 01:56:40 发布

阅读量656

点赞数 21

分类专栏：程序员文章标签： python sql 性能优化

本文链接：https://blog.csdn.net/2401_84181326/article/details/138430276

版权

程序员专栏收录该内容

167 篇文章 0 订阅

订阅专栏

本文探讨了SQL血缘分析在数据治理中的重要性，强调了数据质量、数据模型一致性以及通过元数据管理确保数据源头的追踪。作者介绍了实现过程，包括解析SQL语句、构建血缘关系，以及SQLFlow在可视化血缘追踪中的应用。

摘要由CSDN通过智能技术生成

首先来对该项目的目标来分析一下，说到SQL血缘分析，这偏向于数据治理。

1.数据治理

数据治理（Data Governance）是组织中涉及数据使用的一整套管理行为。数据要产生价值，需要一个合理的“业务目标”，数据治理的所有活动应该围绕真实的业务目标而开展，建立数据标准、提升数据质量只是手段，而不是目标。

数据治理的本质是管理数据，因此需要加强元数据管理和主数据管理，从源头治理数据，补齐数据的相关属性和信息，比如：元数据、质量、安全、业务逻辑、血缘等，通过元数据驱动的方式管理数据生产、加工和使用。

数据的质量直接影响着数据的价值，并且直接影响着数据分析的结果以及我们以此做出的决策的质量。数据模型血缘与任务调度的一致性是建管一体化的关键，有助于解决数据管理与数据生产口径不一致的问题，避免出现双重管理不一致的低效管理模式。

2.血缘追踪

数据被业务场景使用时，发现数据错误，数据治理团队需要快速定位数据来源，修复数据错误。那么数据治理团队需要知道业务团队的数据来自于哪个核心库，核心库的数据又来自于哪个数据源头。我们的实践是在元数据和数据资源清单之间建立关联关系，且业务团队使用的数据项由元数据组合配置而来，这样，就建立了数据使用场景与数据源头之间的血缘关系。数据资源目录：数据资源目录一般应用于数据共享的场景，例如政府部门之间的数据共享，数据资源目录是基于业务场景和行业规范而创建，同时依托于元数据和基础库主题而实现自动化的数据申请和使用。

也就是为什么我们需要解析SQL，追踪建表索引或者引用解析。

3.SQL表血缘

那么其中最重要的就是关于各个数据库之间的数据关系了，关于建表以及插入更新操作都会使数据发生一定的改变，那么这些操作就一定是被允许的？就像原来在网上看到的某某公司程序员删库跑路，或者是一不小心删错数据导致耽误产研线等等。为了防止以上事故的出现必定要为此操作上一层保险，为每个成员设定数据操作权限。这样以来提交的SQL语句就多了一层判断。

二、实现过程

1.目标效果

首先明白一点我们要做出的东西需要呈现一个怎样的形式，其中位于行业前排的无疑是SQLFlow：

当第一次看到此图我就决定血缘追踪就应该是这个样子，能够清晰的解析出每个字段和表之间的血缘关系。以此我们设定输出的基准，我们要做的项目目标就是如此。

2.代码实现

1.功能函数识别

该功能也是必须要实现的功能，我们需要明白这个SQL主要是干什么事情的。如果是插入INSERT或者是CREATE就有血缘分析的必要，如果是SELECT的话那么做简单的SQL解析即可。有了研究sqlparse源码的成果我们调用相应的函数即可：

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标准格式

对于传入的SQL我们首先要让这条语句符合标准的SQL语句格式，这样对于传输格式保持一致，兼容很有作用。一般我们都是通过文本来读入。故需要读取文本做处理：

原始文本：

处理后：

if __name__ == '__main__':
    sql=get_sqlstr('read_sql.txt')
    print(sql)

3.解析AST树

得到的SQL无论是ANTRL还是SQLPARSE都是解析为一棵树的形式进行递归回溯。最终都要解析生产的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 "产品",
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 '服务商消化'  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    --------产品消耗订单
               and t2.complete_state = 1  -----订单已经完成
               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 '企业管家消耗' 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 '交易管理系统' label,date(t6.close_ymd) close_ymd,t4.product_name,t4.product_id,


![img](https://img-blog.csdnimg.cn/img_convert/9c5dd13df034c48da7a695039fbe4353.png)
![img](https://img-blog.csdnimg.cn/img_convert/c81eb1d7eb44f267b4522fffd73b11e9.png)
![img](https://img-blog.csdnimg.cn/img_convert/a9b8bbf1c636887264204d98e084a498.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**

础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**