This article is Part 2.
In the previous step, we obtained "primary lineage annotation information".
In this step, we will obtain complete field-level lineage data and save it into three tables in the MYSQL database.
The names and functions of these three tables are as follows:
sql_table_struct_info : Save table structure information for tables, views, and functions (possibly)
sql_table2table_info : Save the relationships between tables in the script, such as target table, source table, table alias, table association method, association filtering conditions, and other related information
sql_table2column_info : Save the logic code for each field in the script, as well as the source field information referenced by the logic code, that is, field-level lineage information
The above three tables need to be created in advance. The table creation statements are as follows:
drop table sql_table_struct_info
;
create table sql_table_struct_info
(
table_name_hash bigint COMMENT 'hash value of table name'
, table_name varchar(200) COMMENT 'table name'
, column_seq int COMMENT 'column order'
, column_name varchar(200) COMMENT 'column name'
, column_data_type varchar(200) COMMENT 'column data type'
, column_comment varchar(200) COMMENT 'column comment'
)
DEFAULT CHARSET=utf8
partition by hash(table_name_hash)
partitions 197
;
drop table sql_table2table_info
;
create table sql_table2table_info
(
file_name_hash bigint COMMENT 'hash value of file name'
, file_name varchar(200) COMMENT 'SQL file name'
, sql_seq int COMMENT 'SQL sentence order'
, sql_type varchar(50) COMMENT 'SQL type'
, target_table varchar(200) COMMENT 'SQL target table'
, source_table varchar(200) COMMENT 'SQL source table'
, source_table_seq int COMMENT 'SQL source table Sequence'
, source_table_join_type varchar(50) COMMENT 'join type of source table'
, source_table_other_name varchar(200) COMMENT 'source table alias'
, source_table_condition varchar(10000) COMMENT 'source table condition'
, src_tab_seqs_of_cdt_join varchar(100) COMMENT ''
)
DEFAULT CHARSET=utf8
partition by hash(file_name_hash)
partitions 197
;
drop table sql_table2column_info
;
create table sql_table2column_info
(
file_name_hash bigint COMMENT 'hash value of file name'
, file_name varchar(200) COMMENT 'SQL file name'
, sql_seq int COMMENT 'SQL sentence order'
, target_table varchar(200) COMMENT 'SQL target table'
, column_name varchar(200) COMMENT 'column name of target table'
, column_logic varchar(10000) COMMENT 'column logic code'
, source_table_column varchar(4000) COMMENT 'The source tables and fields involved in field logic'
)
DEFAULT CHARSET=utf8
partition by hash(file_name_hash)
partitions 197
;
After the database is prepared, Python is needed to further process the "primary lineage annotation information". The steps can be roughly divided into:
De-hierarchize the code, that is, extract code segments such as subqueries and UNION queries, treat them as independent code segments, and assign them new IDs(Such as：SUB_SELECT_123)
Traverse each independent code segment and extract table-level information such as type, target table, source table, source table association method, source table alias, and association filtering conditions
Traverse each independent code segment again and process the source fields in each field logic, mainly including: (a) determining the source table for specific fields; (b) expanding ambiguous fields (i.e., asterisks /T.)
We can incorporate all the processing into Python code and execute it together, resulting in: col_lvl_data_lineage_reader.py. Due to the large size of the code file, you can refer to the GitHub link:
https://github.com/zgl-20053779/ZGLanguage/tree/main/project/Field-level%20data%20lineage%20analysis%20and%20visualization
The usage method is as follows:
python col_lvl_data_lineage_reader.py your_etl_file.sql
Example Display:
Assuming the script file is named 2-codefile.sql, its content is as follows:
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;
Use commands to parse:
python col_lvl_data_lineage_reader.py 2-codefile.sql > log.log
After parsing, query the database for results:
1
2
3
Analysis steps and suggestions for large projects:
Divide the parsed content into several types:
(a)CREATE TABLE statement
(b)CREATE VIEW statement
(C)ETL scripts, procedure scriptsDue to the dependencies in the parsing process, please proceed with the parsing in the order of (a) -> (b) -> (c)
The table creation statements do not have a lineage relationship and can be parsed together in the same file. However, considering that the total number of table creation statements may amount to hundreds of thousands of lines, it is recommended to parse them in multiple batches, with each batch containing around 50,000 lines
There may be thousands or even millions of view creation scripts, stored procedure scripts, and ETL scripts. Shell scripts can be used to execute parsing commands in batch, with each parsing command taking no more than 3 seconds. Considering the load capacity of MySQL, a short pause of a few tenths of a second should be introduced between the execution of each parsing command to prevent the loss of inserted lineage data.
For each script parsed, the printed information should be redirected to generate a log file, facilitating unified troubleshooting in the future
Based on experience, a project that requires parsing tens of thousands of scripts can be completed in approximately 5 to 10 person-days
When subsequent changes occur to certain script codes, it is only necessary to re-parse these changed scripts to achieve automatic updating and overwriting of lineage data, without requiring significant manpower for maintenance
