# Author renyupeng # coding=utf-8 # @Time : 2021/11/17 3:40 下午 # @Site : # @File : BytedanceMaterialMd5FristCostTime.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from math import ceil from Apietl.conn.TidbConn import TidbConn from Apietl.conn.TidbProConn import TidbProConn class BytedanceMaterialMd5FristCostTime: def __init__(self): self.conn = TidbProConn.get_connection() self.Innerconn = TidbConn.get_connection() def TaskHandler(self): try: sql = """ select count(1) from ( select material_id,signature,stat_datetime from ( select material_id,signature, stat_datetime, row_number() over (partition by signature order by stat_datetime) as rn from application.bytedance_material_video_report_daily_dw )t where t.rn=1)t """ totalNum = TidbConn.quary(self.conn, sql)[0][0] pageCount = ceil(totalNum / 20000) for i in range(pageCount): try: sql = """ replace into application.bytedance_material_md5_frist_cost_time select material_id,signature,stat_datetime from ( select material_id,signature, stat_datetime, row_number() over (partition by signature order by stat_datetime) as rn from application.bytedance_material_video_report_daily_dw )t where t.rn=1 order by material_id,stat_datetime limit {num},20000 """.format(num=i * 20000) print(sql) # TidbProConn.upsert(conn=self.conn, sql_buff=sql) TidbConn.upsert(self.Innerconn, sql_buff=sql) # self.conn.commit() self.Innerconn.commit() except Exception as e: logging.error(e) return -1 # TidbProConn.conn_close(conn=self.conn) TidbConn.conn_close(conn=self.Innerconn) return 0 except Exception as e: logging.error(e) if __name__ == '__main__': BytedanceMaterialMd5FristCostTime().TaskHandler()