def starrun(etldate):
DimManage().refresh_cash()
d1 = datetime.now()
Config().etldate = etldate
manager = multiprocessing.Manager()
q1 = manager.Queue()
pw1 = Process(target=util.queue2db, args=(q1, ))
pw1.start()
info("update_balance_manage_load q1_proces_id=%s" % (str(pw1.pid)))
DimManage().setQueue(q1)
run(Config().etldate)
q1.put(None)
pw1.join()
db = util.DBConnect()
if Config().etldate % 100 <= 8:
ldate = int(util.daycalc(Config().etldate, 0 - Config().etldate % 100))
ldate = int(util.daycalc(ldate, 1 - ldate % 100))
else:
ldate = int(util.daycalc(Config().etldate, 1 - Config().etldate % 100))
mer_sql = """
merge into YDW.F_BALANCE f
using BALANCE_MANAGE_ID b
on f.date_id = ? and f.account_id = b.acct_id
when matched then update set f.manage_id=b.manage_id
"""
begin = thisdatetime.date(int(str(ldate)[0:4]), int(str(ldate)[4:6]),
int(str(ldate)[6:8]))
end = thisdatetime.date(int(str(Config().etldate)[0:4]),
int(str(Config().etldate)[4:6]),
int(str(Config().etldate)[6:8]))
for i in range((end - begin).days + 1):
mer_date = int(util.daycalc(ldate, i))
db.cursor.execute(mer_sql, mer_date)
db.conn.commit()
print mer_date, i, datetime.now() - d1
mer_sql = """
merge into YDW.F_BALANCE f
using BALANCE_MANAGE_ID b
on f.date_id = ? and f.account_id = b.acct_id
when matched then update set f.manage_id=b.manage_id
"""
db.cursor.execute(mer_sql, Config().etldate)
db.conn.commit()
print Config().etldate, datetime.now() - d1
db.closeDB()
print "over time =", datetime.now() - d1
def get_credit_account_manage(db, sql, etldate):
db.cursor.execute(sql, etldate)
row = db.cursor.fetchone()
while row:
m_id, flag = DimManage().find_credit_account_key(row[4], row[0])
data = (row[2], row[3], m_id)
yield (data, True)
row = db.cursor.fetchone()
yield (False, False)
def get_ebank_cust_manage(db, sql, etldate):
db.cursor.execute(sql, etldate)
row = db.cursor.fetchone()
while row:
m_id, flag = DimManage().find_contract_cust_key2(row[3],
row[0]) #不考虑子类型
data = (row[1], row[2], m_id)
yield (data, True)
row = db.cursor.fetchone()
yield (False, False)
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 get_loan_acct_manage(db, sql, etldate):
db.cursor.execute(sql, etldate)
row = db.cursor.fetchone()
while row:
#m_id,flag = DimManage().find_loan_acct_key(row[4],row[1])
#if not flag:
m_id, flag = DimManage().find_loan_cust_key(row[4], row[1])
data = (row[2], row[3], m_id)
yield (data, True)
row = db.cursor.fetchone()
yield (False, False)
def starrun(etldate):
d1 = datetime.now()
Config().etldate = etldate
manager = multiprocessing.Manager()
q1 = manager.Queue()
pw1 = Process(target=util.queue2db, args=(q1, ))
pw1.start()
DimManage().setQueue(q1)
run(etldate)
q1.put(None)
pw1.join()
print "over time =", datetime.now() - d1
def get_loan_acct_manage(db, sql, etldate):
info("get_loan_acct_manage:sql[%s],etldate[%s]" % (sql, str(etldate)))
testfile = "/tmp/loan_%s.del" % (str(etldate))
if os.path.exists(testfile): os.remove(testfile)
test_file = file(testfile, 'a')
test_csv = csv.writer(test_file)
db.cursor.execute(sql, etldate)
row = db.cursor.fetchone()
while row:
#m_id,flag = DimManage().find_loan_acct_key(row[4],row[1])
#if not flag:
test_csv.writerow(list(row))
m_id, flag = DimManage().find_loan_cust_key(row[4], row[1])
data = (row[2], row[3], m_id)
test_csv.writerow(data)
yield (data, True)
row = db.cursor.fetchone()
test_file.close()
yield (False, False)
def load_dims(quenu1):
DimCust().setQueue(quenu1)
DimCustType().setQueue(quenu1)
DimCustStatus().setQueue(quenu1)
DimCustContract().setQueue(quenu1)
DimEPay().setQueue(quenu1)
DimEPay2().setQueue(quenu1)
DimKJ().setQueue(quenu1)
#DimPFinProduct().setQueue(quenu1)
#DimFinAcct().setQueue(quenu1)
DimAccount().setQueue(quenu1)
DimManage().setQueue(quenu1)
DimAccountType().setQueue(quenu1)
DimAccountStatus().setQueue(quenu1)
DimAccountPrice().setQueue(quenu1)
#DimTime().setQueue(quenu1)
DimChannel().setQueue(quenu1)
DimAcctTranType().setQueue(quenu1)
DimOrg()
DimSalesTemp()
db.cursor.execute(mer_sql, etldate)
db.conn.commit()
"""
分割d_manage 测试放在这里，之后是放在etl每日任务的最后
"""
OUT_SQLCODE = 1
OUT_MSG = ''
msg = db.cursor.callproc("P_D_SALE_MANAGE_RELA",
[str(20150101), OUT_SQLCODE, OUT_MSG])
if msg[1] != 0:
print "error msg:%s" % msg[2]
db.conn.commit()
finally:
db.closeDB()
if __name__ == '__main__':
d1 = datetime.now()
Config().etldate = 20150702
manager = multiprocessing.Manager()
q1 = manager.Queue()
pw1 = Process(target=util.queue2db, args=(q1, ))
print "start queue2db 1"
pw1.start()
DimManage().setQueue(q1)
run(Config().etldate)
q1.put(None)
pw1.join()
print "over time =", datetime.now() - d1