数仓中根据etl脚本查找表的上游依赖关系
1、具体脚本
drug addict/Urs/nisj/Documents/wptDataGit-nisj/wptData/pyScript/batchDataForWpt/getDependsFromSql.py
# -*- coding=utf-8 -*-
import os
import re
import datetime
import warnings
import time
import threadpool
warnings.filterwarnings("ignore")
余切
suggestion是什么意思def getDepends(sqlPath):
sqlFile = open(sqlPath, "r")
sqlFile = ad()
sqlFile = re.sub(r'--.*\n', " ", sqlFile)
sqlFile = re.sub(' +', ' ', place('\n', ' ').replace(';' ,' ').replace(' ' ,' ').replace(')' ,' ').lower()).replace('( lect', '(lect').replace('from ', 'from|').replace('join sqlFileList = sqlFile.split(' ')
dependTabList = []
for sqlStr in sqlFileList:
if ('join|' in sqlStr or 'from|' in sqlStr) and '|(lect' not in sqlStr and 'convert.join' not in sqlStr:
# print sqlPath, sqlStr[5:],sqlStr[5:][:sqlStr[5:].index('.')],sqlStr[5:][sqlStr[5:].index('.')+1:]
dependTab = sqlStr[5:][sqlStr[5:].index('.') + 1:]
dependTabList.append(dependTab)
return dependTabList
def getTransitiveDepends(sqlNameFirst, dirPath):
sqlPathFirst = dirPath + sqlNameFirst + ".sql"
for dependTabFirst in getDepends(sqlPathFirst):
if 'ods_' in dependTabFirst:
print sqlNameFirst, "<---", dependTabFirst, "<---", '已经到达ODS级'
how you remind meel:
sqlPathSecond = dirPath + dependTabFirst + ".sql"
character是什么意思
# print sqlNameFirst, dependTabFirst, getDepends(sqlPathSecond)
核弹 英文for dependTabSecond in getDepends(sqlPathSecond):
if 'ods_' not in dependTabSecond:
sqlPathThird = dirPath + dependTabSecond + ".sql"
print sqlNameFirst, "<---", dependTabFirst, "<---", dependTabSecond, "<---", getDepends(virusbarrier
sqlPathThird)
公布英文
el:
print sqlNameFirst, "<---", dependTabFirst, "<---", dependTabSecond, "<---", '已经到达ODS级'
dirPath = "/Urs/nisj/WptWork/gitOnline/etl/sql/"
sqlNameFirst = "dwf_ur_refund_agr_full_1d"
getTransitiveDepends(sqlNameFirst, dirPath)
2、数据结果⽰例
/System/Library/Frameworks/Python.framework/Versions/2.7/bin/python2.7 /Urs/nisj/Documents/wptDataGit-nisj/wptData/pyScript/batchDataForWpt/get
dwf_ur_refund_agr_full_1d <--- dwf_ur_refund_agr_full_1d <--- dwf_ur_refund_agr_full_1d <--- ['dwf_ur_refund_agr_full_1d', 'dwf_ur_refund_agr_inc dwf_ur_refund_agr_full_1d <--- dwf_ur_refund_agr_full_1d <--- dwf_ur_refund_agr_incr_1d <--- ['dwt_trade_refund_ba_incr_1d']
垃圾分类英语作文dwf_ur_refund_agr_full_1d <--- dwf_ur_refund_agr_incr_1d <--- dwt_trade_refund_ba_incr_1d <--- ['ods_pay_bussiness_balance_incr_1d', 'dwt_trade_re Process finished with exit code 0
3、说明：
不能是：[lect a.id,b.name from tab1 a,tab2 b where a.id=b.id]这种⽼式写法格式。
2.数仓中各层命名相对规范
如：ods、dwt、dwf等固定分层前缀；表名前都带上库名。
3.本脚本⽀持的是查找下游的表依赖于上游的哪些表
如：查找集市层的某个表，分别依赖于整合层及ods层的哪些表。
4.在此案例中，所有的各层etl线上脚本都在⼀个⽬录⽂件夹下
实际⽣产环境中可能会有所不同
英语高分网
5.脚本主要是shell在python中进⾏调⽤
主要⽬的是满⾜数据项⽬中，有刷数等查找依赖的需要，提供⼀个相对⽅便的⽅式。
