DataCenterGame/app/etl/sync_to_ck_task.py

from model.DataBaseUtils import MysqlUtils,CkUtils
from model.DateUtils import DateUtils
from model.log import logger
log=logger()
db = MysqlUtils()
ck = CkUtils()
dt = DateUtils()



def order_sync_ck(ymd):
    log.debug("sync order")
    col = "date,stage,platform,channel,channel_id,user_id,order_time,reg_time,amount,from_novel,order_id"
    sql ="select * from ods_order where date='{}'".format(ymd)
    data = db.quchen_text.getData(sql)
    data1 = []
    for x in data:
        li = list(x)

        li[0]=str(li[0])
        li[5]='' if li[5]==None else li[5]
        li[6]=str(li[6])
        li[7]='0000-00-00 00:00:00' if li[7]==None else str(li[7])
        li[9]='' if li[9]==None else li[9]

        data1.append(tuple(li))

    # 删除分区
    ck.execute("alter table order drop  partition '{}' ".format(ymd))
    ck.insertMany("order", col, tuple(data1))


# 广告计划
def daily_vx_campaign(ymd):
    table = 'daily_vx_campaign'
    sql = 'select * from ' + table + ' where date="{} 00:00:00" '.format(ymd)
    data = db.quchen_text.getData(sql)
    data1 = []
    for i in data:
        li = list(i)
        li[1] = str(li[1])
        li[5] = round(li[5], 4)
        li[6] = round(li[6], 4)
        li[7] = round(li[7], 4)
        li[8] = round(li[8], 4)
        li[12] = round(li[12], 4)
        li[13] = round(li[13], 4)
        li[15] = round(li[15], 4)
        data1.append(tuple(li))
    col = db.quchen_text.getColumn("daily_vx_campaign")
    ck.execute("alter table order drop  partition '{}' ".format(ymd))
    ck.insertMany(table, col, tuple(data1))


def adcreative():
    """todo:表需要重新设计 无法分区"""
    pass


def campaign_vx():
    """todo:表需要重新设计 无法分区"""
    # table="campaign_vx"
    # sql="select * from {}".format(table)
    # data=db.quchen_text.getData(sql)
    pass

def dw_order_channel_sync_ck(ymd):
    table='dw_daily_channel'
    sql = f"select * from {table} where dt='{ymd}'"
    col=ck.getColumns(f"{table}",str=True)
    data = db.quchen_text.get_data_list(sql)
    data1=[]
    for i in data:
        i[0]=str(i[0])
        i[8]=str(i[8])
        i[11]=str(i[11])
        i[15]=str(i[15])
        i[-1]='' if i[-1]==None else i[-1]
        data1.append(tuple(i))
    ck.execute(f"alter table {table} drop  partition '{ymd}' ")
    ck.insertMany(f"{table}", col, tuple(data1))




if __name__ == '__main__':
    dw_order_channel_sync_ck(dt.get_n_days(-2))
    # for i in dt.getDateLists('2019-03-18','2020-12-17'):
    # #     order(i)
    #     dw_order_channel_sync_ck(i)