DEV Community

zgl-20053779
zgl-20053779

Posted on

Open-source tool: Cross-database universal "field-level" data lineage analysis and visualization (Part 1 of 3)

#datalineage #sql #bigdata #database

Background

  1. Data lineage can significantly enhance the capabilities of data governance and problem localization, and its importance is self-evident

  2. Currently, most data lineage tools can only achieve table-level lineage. Field-level lineage is either nonexistent, inaccurate, or requires significant manual maintenance

Question

  1. Can we use a single command to automatically parse out accurate field-level data lineage information for various SQL scripts across different relational databases?

  2. Can we visualize field-level data lineage and implement a traceability function?

Solution

The overall content can be divided into three parts:

  1. Use ZGLanguage to parse and convert SQL script files, identify, extract, and annotate lineage information within the scripts, thereby obtaining preliminary lineage annotation information

  2. Use Python to process the above annotation information: (a) disassemble at the code level; (b) identify and verify the mapping between fields and tables; (c) obtain the final field-level lineage data and save it to the MySQL database

  3. Use Django to create a web application for reading, graphically displaying, and tracing upstream and downstream field-level lineage data in the database

Due to space limitations, the three steps outlined in the plan will be presented in three separate articles. This article serves as the first part:

Create a cross-database universal SQL syntax parsing and transformation configuration file: MARK_SQL_4_COLS_DATA_LINEAGE.syn. Due to its large size, you can view it at the following link:
https://github.com/zgl-20053779/ZGLanguage/tree/main/etc/SQL_DATA_LINEAGE

Use the configuration file MARK_SQL_4_COLS_DATA_LINEAGE.syn to process SQL scripts (such as xxxxx.hql), with the following command:

ZGLanguage -e MARK_SQL_4_COLS_DATA_LINEAGE.syn -t xxxxx.hql -o mark_sql_cols_data_lineage.zgl > log.log
Enter fullscreen mode Exit fullscreen mode

Example demonstration

Instance source code:

DROP TABLE IF EXISTS bi_dw.dw_omc_sales_detail_f_tmp
;
CREATE TABLE bi_dw.dw_omc_sales_detail_f_tmp
(
company_wid int4,
org_id      int4 COMMENT'机构ID',
org_code    varchar(255) COMMENT'机构code',
org_name    varchar(255),
customer_wid    int4,
cust_account_id int4
)
distributed randomly
;

--ALTER TABLE bi_dw.dw_omc_sales_detail_f_tmp ADD PRIMARY KEY(invoice_number);
GRANT ALL PRIVILEGES ON bi_dw.dw_omc_sales_detail_f_tmp TO gkht_yibai;

set optimizer = off;
insert into bi_dw.dw_omc_sales_detail_f_tmp
(
company_wid,
org_id,
org_code,
org_name,
customer_wid,
cust_account_id
)
select 
123 as company_wid,
ifnull(T.org_id, '9999') org_id,
T.org_code as org_code,
T.org_name,
T.customer_wid,
T.cust_account_id
FROM bi_dw.dw_om_sales_detail_f_data_tmp T
where 1=1
;

reset optimizer;
Enter fullscreen mode Exit fullscreen mode

Instance conversion result: 

__CREATE_TABLE_STRUCT__{:::}dw_omc_sales_detail_f_tmp{:::}
company_wid||int4||{###}
org_id||int4||'机构ID'{###}
org_code||  varchar(255)||'机构code'{###}
org_name||  varchar(255)||{###}
customer_wid||int4||{###}
cust_account_id||int4||
{111} 


__DECLARE_VAR__{:::}optimizer
{111}


__INSERT_TABLE_SELECT__{:::}dw_omc_sales_detail_f_tmp{:::}
{###}<tartab_col_list>{###}company_wid,org_id,org_code,org_name,customer_wid,cust_account_id{###}</tartab_col_list>{###} 
select 
{###}<column_logic>{###}
    123 as {###}<column_oth_name>{###}company_wid{###}</column_oth_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    ifnull( {###}<column_name>{###}T.org_id{###}</column_name>{###}  , '9999'  ) {###}<column_oth_name>{###}org_id{###}</column_oth_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    {###}<column_name>{###}T.org_code{###}</column_name>{###} as {###}<column_oth_name>{###}org_code{###}</column_oth_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###} 
    {###}<column_name>{###}T.org_name{###}</column_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    {###}<column_name>{###}T.customer_wid{###}</column_name>{###}
{###}</column_logic>{###}

{###}<column_logic>{###}
    {###}<column_name>{###}T.cust_account_id{###}</column_name>{###} 
{###}</column_logic>{###}

{###}<source_table_info>{###}
    FROM||dw_om_sales_detail_f_data_tmp||T
{###}</source_table_info>{###}

{###}<source_table_condition>{###}
    where 1 = 1 
{###}</source_table_condition>{###}
{111}
Enter fullscreen mode Exit fullscreen mode

The first part is over.

Top comments (0)