Skip to content

Commit fe6c96b

Browse files
sqlparsersqlparser
committed
update readme file
1 parent b413ed1 commit fe6c96b

2 files changed

Lines changed: 214 additions & 2 deletions

File tree

‎README.md‎

Lines changed: 213 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,213 @@
1-
# python_data_lineage
2-
Data lineage tools in python
1+
## Gudu SQLFlow Lite version for python
2+
3+
[Gudu SQLFlow](https://sqlflow.gudusoft.com) is a tool used to analyze SQL statements and stored procedures
4+
of various databases to obtain complex data lineage relationships and visualize them.
5+
6+
Gudu SQLFlow Lite version for python allows Python developers to quickly integrate data lineage analysis and
7+
visualization capabilities into their own Python applications.
8+
9+
Gudu SQLFlow Lite version for python is free for non-commercial use and can handle any complex SQL statements
10+
with a length of up to 10k, including support for stored procedures.
11+
12+
Gudu SQLFlow Lite version for python includes a Java library for analyzing complex SQL statements and
13+
stored procedures to retrieve data lineage relationships, a Python file that utilizes jpype to call the APIs
14+
in the Java library, and a JavaScript library for visualizing data lineage relationships.
15+
16+
Gudu SQLFlow Lite version for python can also automatically extract table and column constraints,
17+
as well as relationships between tables and fields, from [DDL scripts exported from the database](https://docs.gudusoft.com/6.-sqlflow-ingester/introduction)
18+
and generate an ER Diagram
19+
20+
### Automatically visualize data lineage
21+
22+
By executing this command:
23+
```
24+
python dlineage.py /t oracle /f test.sql /graph
25+
```
26+
27+
We can automatically obtain the data lineage relationships contained in the following Oracle SQL statement.
28+
```sql
29+
CREATE VIEW vsal
30+
AS
31+
SELECT a.deptno "Department",
32+
a.num_emp / b.total_count "Employees",
33+
a.sal_sum / b.total_sal "Salary"
34+
FROM (SELECT deptno,
35+
Count() num_emp,
36+
SUM(sal) sal_sum
37+
FROM scott.emp
38+
WHERE city = 'NYC'
39+
GROUP BY deptno) a,
40+
(SELECT Count() total_count,
41+
SUM(sal) total_sal
42+
FROM scott.emp
43+
WHERE city = 'NYC') b
44+
;
45+
46+
INSERT ALL
47+
WHEN ottl < 100000 THEN
48+
INTO small_orders
49+
VALUES(oid, ottl, sid, cid)
50+
WHEN ottl > 100000 and ottl < 200000 THEN
51+
INTO medium_orders
52+
VALUES(oid, ottl, sid, cid)
53+
WHEN ottl > 200000 THEN
54+
into large_orders
55+
VALUES(oid, ottl, sid, cid)
56+
WHEN ottl > 290000 THEN
57+
INTO special_orders
58+
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
59+
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
60+
FROM orders o, customers c
61+
WHERE o.customer_id = c.customer_id;
62+
```
63+
64+
And visualize it as:
65+
![Oracle data lineage sample](samples/images/oracle_data_lineage.png)
66+
67+
### Oracle PL/SQL Data Lineage
68+
```
69+
python dlineage.py /t oracle /f samlples/oracle_plsql.sql /graph
70+
```
71+
72+
![Oracle PL/SQL data lineage sample](samples/images/oracle_plsql_data_lineage.png)
73+
74+
The [source code of this sample Oracle PL/SQL](samples/oracle_plsql.sql).
75+
76+
### Able to analyze dynamic SQL to get data lineage (Postgres stored procedure)
77+
```sql
78+
CREATE OR REPLACE FUNCTION t.mergemodel(_modelid integer)
79+
RETURNS void
80+
LANGUAGE plpgsql
81+
AS $function$
82+
BEGIN
83+
EXECUTE format ('INSERT INTO InSelections
84+
SELECT * FROM AddInSelections_%s', modelid);
85+
86+
END;
87+
$function$
88+
```
89+
90+
![Postgres stored procedure data lineage sample](samples/images/postgresql_plsql_data_lineage.png)
91+
92+
### Nested CTE with star columns (Snowflake SQL sample)
93+
```
94+
python dlineage.py /t snowflake /f samlples/snowflake_nested_cte.sql /graph
95+
```
96+
97+
![Snowflake nested CTE data lineage sample](samples/images/snowflake_nested_cte_data_lineage.png)
98+
99+
The [snowflake SQL source code of this sample](samples/snowflake_nested_cte.sql).
100+
101+
### Analyze DDL and automatically draw an ER Diagram.
102+
103+
By executing this command:
104+
```
105+
python dlineage.py /t sqlserver /f samples/sqlserver_er.sql /graph /er
106+
```
107+
108+
We can automatically obtain the ER Diagram of the following SQL Server database:
109+
110+
![SQL Sever ER Diagram sample](samples/images/sqlserver_er_diagram.png)
111+
112+
The [DDL script of the above ER diagram is here](samples/sqlserver_er.sql).
113+
114+
## Try your own SQL scripts
115+
116+
You may try more SQL scripts in your own computer without any internet connection by cloning [this python data lineage repo](https://github.com/sqlparser/python_data_lineage)
117+
```shell
118+
git clone https://github.com/sqlparser/python_data_lineage.git
119+
```
120+
121+
- No database connection is needed.
122+
- No internet connection is needed.
123+
124+
You only need a JDK and a python interpreter to run the Gudu SQLFlow lite version for python.
125+
126+
### step 1 Prerequisites
127+
* Install python3
128+
* Install Java jdk1.8
129+
130+
### step 2 Open the web server
131+
Switch to the widget directory of this project and execute the following command to start the web service:
132+
133+
`python -m http.server 8000`
134+
135+
Open the following URL in a web browser to verify if the startup was successful：http://localhost:8000/
136+
137+
Note: If you want to modify the port 8000, you need to modify the widget_server_url in dlineage.py accordingly.
138+
139+
### step 3 Execute the python script
140+
Open a new command window, switch to the root directory of this project, where the dlineage.py file is located, and execute the following command:
141+
142+
`python dlineage.py /t oracle /f test.sql /graph`
143+
144+
This command will perform data lineage analysis on test.sql and open a web browser page to display the results of the analysis in a graphical result.
145+
146+
Explanations of the command-line parameters supported by dlineage.py:
147+
148+
/f: 可选, sql文件.
149+
150+
/d: 可选, 包含sql文件的文件夹路径.
151+
152+
/j: 可选, 返回包含join关系的结果.
153+
154+
/s: 可选, 简单输出，忽略中间结果.
155+
156+
/topselectlist: 可选, 简单输出，包含最顶端的输出结果.
157+
158+
/withTemporaryTable: 可选, 简单输出，包含临时表.
159+
160+
/i: 可选, 与/s选项相同，但将保留SQL函数生成的结果集，此参数将与/s/topselectlist+keep SQL函数生成结果集具有相同的效果。
161+
162+
/showResultSetTypes: 可选, 带有指定结果集类型的简单输出，用逗号分隔, 结果集类型有： array, struct, result_of, cte, insert_select, update_select, merge_update, merge_insert, output, update_set pivot_table, unpivot_table, alias, rs, function, case_when
163+
164+
/if: 可选, 保留所有中间结果集，但删除 SQL 函数生成的结果集。
165+
166+
/ic: 可选, 忽略输出中的坐标.
167+
168+
/lof: 必选, 将孤立列链接到第一个表.
169+
170+
/traceView: 可选,只输出源表和视图的名称，忽略所有中间数据.
171+
172+
/text: 可选, 如果只使用/s 选项，则在文本模式下输出列依赖项.
173+
174+
/json: 可选, 打印json格式输出.
175+
176+
/tableLineage [/csv /delimiter]: 可选, 输出表级血缘关系.
177+
178+
/csv: 可选, 输出csv格式的列一级的血缘关系.
179+
180+
/delimiter: 可选, 输出csv格式的分隔符.
181+
182+
/t: 必选, 指定数据库类型.
183+
支持 access,bigquery,couchbase,dax,db2,greenplum, gaussdb, hana,hive,impala,informix,mdx,mssql,
184+
sqlserver,mysql,netezza,odbc,openedge,oracle,postgresql,postgres,redshift,snowflake,
185+
sybase,teradata,soql,vertica the default value is oracle
186+
187+
/env: 可选, 指定一个 metadata.json 来获取数据库元数据信息.
188+
189+
/transform: 可选, 输出关系转换码.
190+
191+
/coor: 可选, 输出关系转换坐标，但不输出代码.
192+
193+
/defaultDatabase: 可选, 指定默认database.
194+
195+
/defaultSchema: 可选, 指定默认schema.
196+
197+
/showImplicitSchema: 可选, 显示间接schema.
198+
199+
/showConstant: 可选, 显示常量.
200+
201+
/treatArgumentsInCountFunctionAsDirectDataflow: 可选,将 count 函数中的参数视为直接数据流.
202+
203+
/filterRelationTypes: 可选, 过滤关系类型，支持 fdd，fdr，join，call，er，如果有多个关系类型用英文半角逗号分隔.
204+
205+
/graph: 可选, 打开一个浏览器页面，图形化方式展示血缘分析结果
206+
/er: 可选, 打开一个浏览器页面，图形化方式展示ER图
207+
208+
209+
### Export metadata from various databases.
210+
You can export metadata from the database using [SQLFlow ingester](https://github.com/sqlparser/sqlflow_public/releases)
211+
and hand it over to Gudu SQLFlow for data lineage analysis.。
212+
213+
[Document of the SQLFlow ingester](https://docs.gudusoft.com/6.-sqlflow-ingester/introduction)

‎README_cn.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -105,6 +105,7 @@ python dlineage.py /t sqlserver /f samples/sqlserver_er.sql /graph /er
105105

106106
The [DDL script of the above ER diagram is here](samples/sqlserver_er.sql).
107107

108+
## Try your own SQL scripts
108109

109110
You may try more SQL scripts in your own computer without any internet connection by cloning [this python data lineage repo](https://github.com/sqlparser/python_data_lineage)
110111
```shell

0 commit comments

Comments
 (0)