| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/17 3:40 下午# @Site    :# @File    : BytedanceMaterialMd5FristCostDateUpdate.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import datetimeimport loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.conn.TidbProConn import TidbProConnfrom Apietl.utlis.Utils import Utilsclass BytedanceMaterialMd5FristCostDateUpdate:    def __init__(self):        self.conn = TidbProConn.get_connection()        self.Innerconn = TidbConn.get_connection()        self.parm = Utils.get_args()        self.start_time = self.parm[0]        self.end_time = self.parm[1]    def TaskHandler(self):        try:            sql = """replace into application.bytedance_material_md5_frist_cost_timeselect a.material_id,a.signature,case when b.first_cost_time is null then a.stat_datetime when a.stat_datetime<b.first_cost_time then a.stat_datetimeelse b.first_cost_time end as first_cost_timefrom (select material_id,signature, stat_datetimefrom (         select material_id,signature, stat_datetime, row_number() over (partition by material_id order by stat_datetime) as rn         from application.bytedance_material_video_report_daily_dw         where  stat_datetime  between  {start_time} and  {end_time}     ) twhere t.rn = 1)aleft join (select material_id, first_cost_time from application.bytedance_material_md5_frist_cost_time)bon a.material_id=b.material_id            """.format(start_time=self.start_time, end_time=self.end_time)            print(sql)            # TidbProConn.upsert(conn=self.conn, sql_buff=sql)            TidbConn.upsert(conn=self.Innerconn, sql_buff=sql)            # self.conn.commit()            self.Innerconn.commit()            # TidbProConn.conn_close(self.conn)            TidbConn.conn_close(self.Innerconn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = BytedanceMaterialMd5FristCostDateUpdate().TaskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |