{"id":2626,"date":"2023-01-12T08:29:42","date_gmt":"2023-01-12T13:29:42","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2626"},"modified":"2023-02-23T10:09:17","modified_gmt":"2023-02-23T15:09:17","slug":"instantly-get-the-data-lineage-from-complex-sql-statements","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/instantly-get-the-data-lineage-from-complex-sql-statements\/","title":{"rendered":"Instantly get the data lineage from complex SQL statements"},"content":{"rendered":"\n

Data lineage plays an important role in Data Governance, you can refer to this blog<\/a> for why data lineage is so important for an enterprise and how can we correctly use the data lineage. In this article, we will introduce how to use Gudu SQLFlow UI, SQLFlow Rest API and SQLFlow Java library to retrieve data lineage from complex SQL statements with which you can potentially integrate in your own data governance platform.<\/p>\n\n\n\n

\"\"
Automation with SQLFlow in building data lineage<\/figcaption><\/figure>\n\n\n\n

A complex SQL statement example<\/h2>\n\n\n\n
select data.*, location.remote\nfrom (\n\tselect e.last_name, e.department_id, d.department_name\n\tfrom employees e\n\tleft outer join department d\n\t\ton (e.department_id = d.department_id)\n) data inner join\n(\n\tselect s.remote,s.department_id \n\tfrom source s\n\tinner join location l \n\ton s.location_id = l.id\n) location on data.department_id = location.department_id;<\/code><\/pre>\n\n\n\n
Above SQL has some complexities and let’s take this SQL statement as an example source of retrieving data lineage with SQLFlow. Note that in the real situation, our SQL statements might be far more complicated than this example especially when we have views or stored procedures to deal with.<\/p>\n\n\n\n
To generate the correspond data lineage, we need to know which fields or columns are included in the top-level select list, and what is the data source for these fields or columns comes from. After some analysis, an ideal result should be as shown in the figure below:<\/p>\n\n\n\n
\"\"
                Data Lineage for Top Level Select List<\/figcaption><\/figure>\n\n\n\n
To archive the above results, we have to:<\/p>\n\n\n\n
1.Expand the asterisk (*<\/code> in the SQL statement) in data.*<\/code> to find the corresponding fields<\/p>\n\n\n\n
The asterisk in the Select <\/code>list stands for all fields of the relation, we will need to retrieve the exact fields to generate the data lineage. In this example, Gudu SQLFlow is able to get the fields info based on the contextual information provided in the SQL statement. Nevertheless, most of SQL statements are not able to provide sufficient information for us to detect what are the fields in the relation of the asterisk. In that case, giving metadata would be necessary to generate the result. You can refer to this article<\/a> for how to get metadata.<\/p>\n\n\n\n
2.Perform data tracing for each field in the select <\/code>list to find the original tables and fields. This process may require multi-level tracing until we find the final data source as the result.<\/p>\n\n\n\n
Gudu SQLFlow UI<\/h2>\n\n\n\n
You can check our online Gudu SQLFlow Cloud<\/a>. This is probably the most easiest way to instantly archive data lineage cause no toolkit or software is required on your side. The usage is quite simple, directly paste the SQL statement to be processed to the input area, and then click Visualize button.<\/p>\n\n\n\n
\"\"
                                                          Gudu SQLFlow Data Lineage<\/figcaption><\/figure>\n\n\n\n
If you prefer to not display the intermediate processing steps in the data lineage, you can show the source data with target data by config the setting as:<\/p>\n\n\n\n
\"\"
                Gudu SQLFlow Setting<\/figcaption><\/figure>\n\n\n\n
Download the data lineage in Json for further processing:<\/p>\n\n\n\n
\"\"
                                                Gudu SQLFlow Data Lineage in JSON<\/figcaption><\/figure>\n\n\n\n
Gudu SQLFlow Rest API<\/h2>\n\n\n\n
Calling SQLFlow Rest API in your code to instantly get the data lineage from SQL statements. You can embed the request in your code and processing the result after getting the Rest Api response.<\/p>\n\n\n\n
A Gudu SQLFlow Cloud Premium Account<\/a> is required to use the Rest Api feature. Check our docs here<\/a> to get the way of calling our Rest Api. <\/p>\n\n\n\n
You will also need to install SQLFlow On-Premise<\/a> on your server if you would like to use SQLFlow internally. <\/p>\n\n\n\n
You can also check our sample code, the sample code is in Python:<\/p>\n\n\n\n