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 )