今天领导分配个任务
把所有作业的mapping整理下
要死人啊!用户自己不会看FS吗 - -!
还好能导出XML文件,用python解析了一下,作为记录,后续增强。
-- Create table
create table INTEGRATION_MAPPING
(
downstream_col VARCHAR2(100),
mapping VARCHAR2(4000),
jobname VARCHAR2(100)
)
#author: AzzoChen
import xml.etree.cElementTree as ET
import cx_Oracle
conn = cx_Oracle.connect('scott/tiger')
cursor = conn.cursor()
tree = ET.ElementTree(file = r'E:\test114.xml')
root = tree.getroot()
for job in root.iter('Job'):
jobname = job.get('Identifier')
if jobname[0:4] == 'PJob':
#if jobname == 'PJob_CSM_SFT_PH_Load_Data': # use to debug single job
for record in job.iter('Record'):
collection = record.find('Collection')
prop = record.find('Property')
if prop.get('Name') == 'Name': # different downstream LINK
dslink = prop.text
identifier = record.get('Type')
#print record.get('Type')
if identifier == 'TrxOutput': # find transformer stage output LINK
for subrecord in collection.iter('SubRecord'):
for property in subrecord.iter('Property'):
name = property.get('Name')
if name == 'Name': #downstream column name
colname = property.text
if name =='Derivation': #mapping
mapping = property.text.replace('\'','\'\'')
sql1 = "insert into integration_mapping(jobname,downstream_col,mapping) select " +"'"+jobname+"'"+","+"'"+dslink+"."+colname+"'"+ "," +" '"+mapping+"'"+" from dual "
print sql1
cursor.execute(sql1)
conn.commit()
conn.close ()