def etl_task_check(etldate, name):
try:
db = util.DBConnect()
sql = "select status from p_etl_task where date_id=? and task_name = ? "
db.cursor.execute(sql, int(etldate), name)
rows = db.cursor.fetchall()
if len(rows) > 1:
raise Exception("任务名不能重复")
if len(rows) == 0:
insertsql = "insert into p_etl_task(date_id,task_name,status,start_time) values (?,?,?,?)"
s = str(datetime.now())
db.cursor.execute(insertsql, int(etldate), name, "启动", s)
db.conn.commit()
process_info()
else:
status = rows[0][0]
process_info()
if status == "成功":
nn = "%s已完成，跳过该任务" % (name)
print nn
info(nn)
return False
else:
dsql = "delete from p_etl_task where date_id=? and task_name = ? "
db.cursor.execute(dsql, int(etldate), name)
s = str(datetime.now())
insertsql = "insert into p_etl_task(date_id,task_name,status,start_time) values (?,?,?,?)"
db.cursor.execute(insertsql, int(etldate), name, "启动", s)
db.conn.commit()
return True
finally:
db.closeDB()
def update_orgid(startdate,enddate):
info("update_orgid:%d-%d"%(startdate,enddate))
try :
db = util.DBConnect()
etldate=startdate
while int(etldate)<=int(enddate):
m4_sql="""
MERGE INTO YDW.F_CONTRACT_STATUS F
USING (SELECT O.ID DOID ,D.ID COID FROM D_ORG O JOIN D_CUST_CONTRACT D ON O.ORG0_CODE =D.OPEN_BRANCH_NO WHERE D.BUSI_TYPE<>'企业网上银行' and D.BUSI_TYPE<>'ETC') A
ON A.COID = F.CONTRACT_ID AND F.DATE_ID =?
WHEN MATCHED THEN UPDATE SET F.ORG_ID=A.DOID
"""
m6_sql="""
MERGE INTO YDW.F_CONTRACT_STATUS F
USING (SELECT O.ID DOID ,C.ID COID FROM D_CUST_CONTRACT C
JOIN EBANK_ORG D ON D.SUB_TYP in ('企业网上银行', 'ETC') AND C.BUSI_TYPE in ('企业网上银行', 'ETC') AND D.CUST_NET_NO=C.NET_CST_NO AND D.STATUS IN ('正常')
JOIN D_ORG O ON O.ORG0_CODE=D.ORG_NO) A
ON A.COID = F.CONTRACT_ID AND F.DATE_ID =?
WHEN MATCHED THEN UPDATE SET F.ORG_ID=A.DOID
"""
db.cursor.execute(m4_sql,etldate)
db.cursor.execute(m6_sql,etldate)
print etldate
etldate=int(util.daycalc(etldate,1))
db.conn.commit()
finally :
db.closeDB()
def delete_accounthook_by_account_no(account_no):
db = DBConnect()
info("delete from ACCOUNT_HOOK with account_no = " + str(account_no))
db.cursor.execute("DELETE from ACCOUNT_HOOK where ACCOUNT_NO = ?",
(account_no))
db.conn.commit()
db.closeDB()
def delete_accounthook_by_id(hook_id):
raise Exception("not support ")
db = DBConnect()
info("delete from ACCOUNT_HOOK with id = " + str(hook_id))
db.cursor.execute("DELETE from ACCOUNT_HOOK where id = ?", (hook_id))
db.conn.commit()
db.closeDB()
def process_info():
from etl.base.logger import info
msg = os.popen(
"ps -ef|grep python|grep -v grep |grep -v gun|grep -v report_proxy|grep -v WebSphere "
)
for m in msg:
info(u"当前执行的Python进程:\n%s\n" % (m))
def files2fact(self, ds, q1):
d0 = datetime.now()
insertsql = ds.fact_sql()
idx = 0
d1 = datetime.now()
print "start ,times=", datetime.now() - d1
rs = []
qwork = q1
count = 1
for row, newflag in ds.to_fact_row():
if row is None: break
r = ds.get_one_fact(row)
print r
idx = idx + 1
if idx > 10000:
print "to_fact_row ,times=", datetime.now() - d1
info("insert into custvies account 10000:" +
str(datetime.now() - d0))
d0 = datetime.now()
idx = 0
if q1 is None:
if r is not None:
print "####CUST###", r, insertsql
pass
else:
if r != None:
print "##########text0###########"
qwork.put((insertsql, r))
print "##########text1##########"
else:
pass
def files2fact(self, ds):
try:
db = util.DBConnect()
custsql = ds.cust_sql()
rs = []
idx = 0
for row, flag in ds.to_fact_row():
if flag == False: continue
if row is None: break
r = ds.transfor_one_fact(row, self.table_desc)
if not r: continue
idx = idx + 1
rs.append(r)
if idx > 10000:
print "to_fact_row ,times=", datetime.now() - d1
info("insert into loan_hook 100:" +
str(datetime.now() - d0))
idx = 0
#print rs
db.cursor.executemany(custsql, rs)
db.conn.commit()
rs = []
if len(rs) > 0:
#print rs
db.cursor.executemany(custsql, rs)
db.conn.commit()
rs = []
finally:
db.closeDB()
def files2fact(self, ds, q1, q2):
d0 = datetime.now()
accountsql = ds.account_sql()
custsql = ds.cust_sql()
idx = 0
d1 = datetime.now()
print "start ,times=", datetime.now() - d1
#ds.berfore_transfor()
rs = []
rs1 = []
que = [q1, q2]
qwork = q1
#load_dims(qwork)#进程切换
count = 1
for row, flag in ds.to_fact_row():
if flag == False: continue
if row is None: break
temp = ds.transfor_one_fact(row, self.table_desc)
r = temp[0]
r1 = temp[1]
#print r1
idx = idx + 1
if idx > 10000:
print "to_fact_row ,times=", datetime.now() - d1
info("insert into loan_hook 100:" + str(datetime.now() - d0))
idx = 0
if q1 is None:
#print "####插入语句测试###",r,accountsql
pass
else:
if r is not None:
rs.append(r)
if len(rs) > 10000:
for ir in rs:
qwork.put((accountsql, ir))
rs = []
qwork = que[count % 2]
#load_dims(qwork)#维度表进程切换
count = count + 1
else:
pass
if r1 is not None:
rs1.append(r1)
#print r1
if len(rs) > 10000:
for ir in rs1:
qwork.put((custsql, ir))
rs1 = []
qwork = que[count % 2]
#load_dims(qwork)#维度表进程切换
count = count + 1
else:
pass
if len(rs) > 0:
for ir in rs:
q1.put((accountsql, ir))
if len(rs1) > 0:
for ir in rs1:
q1.put((custsql, ir))
def delete_blfmmtrn(etldate, enddate):
db = DBConnect()
info("lock table F_CORE_BLFMMTRN")
db.cursor.execute(
"DELETE FROM F_CORE_BLFMMTRN WHERE WORKDATE>=? and WORKDATE<=?",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def delete_blta(etldate, enddate):
db = DBConnect()
info("lock table FF_CORE_BLTA03LC")
db.cursor.execute(
"DELETE FROM FF_CORE_BLTA03LC WHERE WORKDATE>=? and WORKDATE<=?",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def start(self):
self.init_header()
info("LOAND ids")
maxid=0
( self.custids,maxid) = self.load_dim_bysql(" select id,CUST_KEY from D_CUST ")
self.maxid = maxid
self.closeDB()
info("LOAD finish IDS,maxid:%d"%(maxid))
def loadtofact(self, q1, q2):
info("db2fact ,custview")
#self.files2fact(CurTrans(),q1)#活期交易明细
#self.files2fact(FixTrans(),q1)#定期交易明细
#self.files2fact(LoanTrans(),q2)#贷款交易明细
# self.files2fact(Transdetails(),q2)#交易明细
#self.files2fact(Posp_auth_histroy(),q2)
self.files2fact(BLFMMTRN(), q2)
def delete_custhook_by_ebank_cb(cust_in_no):
db = DBConnect()
info("delete from cust_in_no_hook with cust_in_no= " + str(cust_in_no))
db.cursor.execute(
"DELETE from CUST_HOOK where CUST_IN_NO= ? AND SUB_TYP='企业网上银行'",
(cust_in_no))
db.conn.commit()
db.closeDB()
def delete_custhook(etldate, enddate, atype=None):
db = DBConnect()
info("lock table cust_hook")
db.cursor.execute(
"DELETE from CUST_HOOK where ETL_DATE>=? and ETL_DATE<=? and TYP=? and SRC='批量'",
(etldate, enddate, atype))
db.conn.commit()
db.closeDB()
def delete_CORE_BHFMCMRM_DIRECT(etldate, enddate):
db = DBConnect()
info("lock table CORE_BHFMCMRM_DIRECT")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE FROM CORE_BHFMCMRM_DIRECT WHERE WORKDATE>=? and WORKDATE<=? ",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def delete_fcacctjrnl(etldate, enddate):
db = DBConnect()
info("lock table F_C_ACCTJRNL")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE from F_T_LOAN_JRNL where date_id>=? and date_id<=? ",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def load_dim_bysql(self,sql):
info("load dim ,sql:%s"%(sql))
self.db.cursor.execute(sql)
row = self.db.cursor.fetchone()
d = {}
while row:
d[ row[1] ] = row[0]
row = self.db.cursor.fetchone()
return d
def delete_accounthook(etldate, enddate, atype=None):
raise Exception("not support ")
db = DBConnect()
info("lock table account_hook")
db.cursor.execute(
"DELETE from ACCOUNT_HOOK where ETL_DATE>=? and ETL_DATE<=? and TYP=? and SRC='批量'",
(etldate, enddate, atype))
db.conn.commit()
db.closeDB()
def delete_ebills_qry_settlement_corp(etldate, enddate):
db = DBConnect()
info("lock table ebills_qry_settlement_corp")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE FROM ebills_qry_settlement_corp WHERE WORKDATE>=? and WORKDATE<=? ",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def delete_qry_settlementmanager(etldate, enddate):
db = DBConnect()
info("lock table QRY_SETTLEMENTMANGER")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE FROM EBILLS_QRY_SETTLEMENT_MANAGER WHERE WORKDATE>=? and WORKDATE<=?",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def delete_atm_insert(etldate, enddate):
db = DBConnect()
info("lock table FARM_MON_LIVING")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE FROM T_BSMP_JRNL WHERE WORKDATE>=? and WORKDATE<=?",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def delete_new_ebills_bu_transactioninfo(etldate, enddate):
db = DBConnect()
info("lock table ebills_pa_managercorpinfo")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE FROM new_ebills_bu_transactioninfo WHERE WORKDATE>=? and WORKDATE<=? ",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def delete_ebills_pa_quotePricearv(etldate, enddate):
db = DBConnect()
info("lock table ebills_pa_quotePricearv")
#db.cursor.execute(lock)
db.cursor.execute(
"DELETE FROM EBILLS_PA_QUOTEPRICEARV WHERE WORKDATE>=? and WORKDATE<=?",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def get_acpt_acct_manage(db, sql, etldate):
info("get_acpt_acct_manage:sql[%s],etldate[%s]" % (sql, str(etldate)))
db.cursor.execute(sql, etldate)
row = db.cursor.fetchone()
while row:
m_id, flag = DimManage().find_acpt_cust_key(row[4], row[1])
data = (row[2], row[3], m_id)
yield (data, True)
row = db.cursor.fetchone()
yield (False, False)
def files2fact(self, ds, q1, q2):
d0 = datetime.now()
insertsql = ds.fact_sql()
idx = 0
d1 = datetime.now()
print "start ,times=", datetime.now() - d1
#print 123
#ds.berfore_transfor()
rs = []
que = [q1, q2]
qwork = q1
load_dims(qwork) #进程切换
print "after load_dims,times=", datetime.now() - d1
count = 1
for row, flag in ds.to_fact_row():
if flag == False: continue
if row is None: break
r = ds.transfor_one_fact(row, self.table_desc)
idx = idx + 1
if idx > 10000:
print "to_fact_row ,times=", datetime.now() - d1
info("insert into f_contract_status 10000:" +
str(datetime.now() - d0))
idx = 0
if q1 is None:
print "####丰收e支付###", r, insertsql
pass
else:
if r is not None:
rs.append(r)
if len(rs) > 1000:
for ir in rs:
qwork.put((insertsql, ir))
rs = []
qwork = que[count % 2]
load_dims(qwork) #进程切换
count = count + 1
else:
pass
rows = ds.contract_fact.values()
print "data length :", len(rows)
for r in rows:
rs.append(r)
if len(rs) > 1000:
for ir in rs:
qwork.put((insertsql, ir))
rs = []
qwork = que[count % 2]
load_dims(qwork) #进程切换
count = count + 1
if len(rs) > 0:
for ir in rs:
q1.put((insertsql, ir))
def delete_fcustview(etldate, enddate, atype=None):
db = DBConnect()
info("lock tableF_C_CUSTVIEW")
lock = "lock table F_C_CUSTVIEW in exclusive mode"
#db.cursor.execute(lock)
info("delete F_C_CUSTVIEW")
if atype is None:
db.cursor.execute(
"DELETE from F_C_CUSTVIEW where date_id>=? and date_id<=? ",
(etldate, enddate))
db.conn.commit()
db.closeDB()
def etl_run3(fun, msg, startdate, etldate, p1):
if etl_task_check(etldate, msg) == False: return
try:
d1 = datetime.now()
info("star %s :%s-%s " % (msg, str(etldate), datetime.now()))
fun(startdate, etldate, p1)
info("end %s:%s-%s " % (msg, str(etldate), datetime.now() - d1))
etl_task_finish(etldate, msg)
except Exception as e:
etl_task_finish(etldate, msg, "失败", str(e))
traceback.print_exc()
raise e
def writer_manage_id(db, func, s_sql, etldate):
d1 = datetime.now()
d_file = file(tfile, 'a')
d_file_csv = csv.writer(d_file)
count = 0
info("writer_manage_id:sql[%s],etldate[%s]" % (s_sql, str(etldate)))
for (data, flag) in func(db, s_sql, etldate):
count = count + 1
if not flag: break
d_file_csv.writerow(data)
d_file.close()
print "data time =", datetime.now() - d1, count
def loadtofact(self,q1,q2):
info("EBANK HOOK")
d = util.get_ebankhook_typ()
pool = EbankPool()
pool.cust_info = d
self.files2fact(Ebank_kjzf(),q1,q2)#
self.files2fact(Ebank_epay(),q1,q2)#
self.files2fact(Ebank_zfb(),q1,q2)#
self.files2fact(Ebank_pb(),q1,q2)#
self.files2fact(Ebank_mb(),q1,q2)#
self.files2fact(Ebank_cb(),q1,q2)#
self.files2fact(Ebank_etc(),q1,q2)#
def load_dim_bysql(self,sql):
info("load dim ,sql:%s"%(sql))
maxid = 0
self.db.cursor.execute(sql)
row = self.db.cursor.fetchone()
d = {}
while row:
if maxid < row[0] : maxid = row[0]
if len(row) > 2 :
d[ row[1] ] = (row[0],row[2])
else:
d[ row[1] ]=row[0]
row = self.db.cursor.fetchone()
return ( d, maxid )
