10000 sample sql and pic · sqlparser/python_data_lineage@6cc2c17 · GitHub
[go: up one dir, main page]
Skip to content

Commit 6cc2c17

Browse files
sqlparsersqlparser
committed
sample sql and pic
1 parent af3bf39 commit 6cc2c17

File tree

4 files changed

+74
-1
lines changed

4 files changed

+74
-1
lines changed

‎README_cn.md

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,14 @@ $function$
8383

8484
![Postgres stored procedure data lineage sample](samples/images/postgresql_plsql_data_lineage.png)
8585

86+
### Nested CTE with star columns (Snowflake SQL sample)
87+
```
88+
python dlineage.py /t snowflake /f samlples/snowflake_nested_cte.sql /graph
89+
```
90+
91+
![Snowflake nested CTE data lineage sample](samples/images/snowflake_nested_cte_data_lineage.png)
92+
93+
The [source code of this sample Oracle PL/SQL](samples/snowflake_nested_cte.sql).
8694

8795
### 分析 DDL, 自动画出 ER Diagram
8896

‎samples/images/snowflake_nested_cte_data_lineage.png

138 KB
Loading

‎samples/snowflake_nested_cte.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
create or replace view CH_LATEST_JIRA_ISSUE(
2+
JIRA_ISSUE_ID,
3+
KEY,
4+
PARENT_ID,
5+
RESOLUTION_ID,
6+
LAST_VIEWED,
7+
_ORIGINAL_ESTIMATE,
8+
ASSIGNEE_ID,
9+
ISSUE_TYPE_ID,
10+
ENVIRONMENT,
11+
DUE_DATE,
12+
REMAINING_ESTIMATE,
13+
STATUS_ID,
14+
_REMAINING_ESTIMATE,
15+
CREATOR_ID,
16+
TIME_SPENT,
17+
_TIME_SPENT,
18+
WORK_RATIO,
19+
REPORTER_ID,
20+
PROJECT_ID,
21+
RESOLVED,
22+
UPDATED_AT,
23+
ORIGINAL_ESTIMATE,
24+
ISSUE_DESCRIPTION,
25+
ISSUE_SUMMARY,
26+
STATUS_CATEGORY_CHANGED,
27+
PRIORITY_ID,
28+
ISSUE_CREATED_AT,
29+
IS_DELETED,
30+
SYNCED_AT,
31+
FIRST_IN_AMT,
32+
FIRST_OUT_AMT
33+
) as (
34+
35+
36+
WITH tran_in_base1 AS (
37+
SELECT COMPANY_ID, min(CREATED_AT) CREATED_AT_MIN FROM tide.pres_core.cleared_transactions WHERE AMOUNT>0 GROUP BY COMPANY_ID
38+
),
39+
40+
tran_out_base1 AS (
41+
SELECT COMPANY_ID, min(CREATED_AT) CREATED_AT_MIN FROM tide.pres_core.cleared_transactions WHERE AMOUNT<0 GROUP BY COMPANY_ID
42+
),
43+
44+
tran_in_base2 AS (
45+
SELECT a.COMPANY_ID,MAX(a.AMOUNT) AS FIRST_IN_AMT FROM tide.pres_core.cleared_transactions a
46+
INNER JOIN tran_in_base1 b
47+
on a.COMPANY_ID=b.COMPANY_ID and a.CREATED_AT=b.CREATED_AT_MIN GROUP BY a.COMPANY_ID
48+
),
49+
50+
tran_out_base2 AS (
51+
SELECT a.COMPANY_ID,MAX(a.AMOUNT) AS FIRST_OUT_AMT FROM tide.pres_core.cleared_transactions a
52+
INNER JOIN tran_out_base1 b
53+
on a.COMPANY_ID=b.COMPANY_ID and a.CREATED_AT=b.CREATED_AT_MIN GROUP BY a.COMPANY_ID
54+
),
55+
56+
jira_issues_tab AS (
57+
SELECT *
58+
FROM tide.intg_jira.latest_jira_issues
59+
)
60+
61+
SELECT a.*, b.FIRST_IN_AMT, c.FIRST_OUT_AMT
62+
FROM jira_issues_tab a
63+
LEFT JOIN tran_in_base2 b ON cast(REGEXP_SUBSTR(a.issue_summary,'[0-9]+') AS bigint)=b.COMPANY_ID
64+
LEFT JOIN tran_out_base2 c ON cast(REGEXP_SUBSTR(a.issue_summary,'[0-9]+') AS bigint)=c.COMPANY_ID
65+
);

‎widget/json/lineageGraph.json

Lines changed: 1 addition & 1 deletion
Large diffs are not rendered by default.

0 commit comments

Comments
 (0)
0