| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/17 3:40 下午# @Site    : # @File    : BytedanceMaterialMd5FristCostTime.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom math import ceilfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.conn.TidbProConn import TidbProConnclass 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_datetimefrom (         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     )twhere 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_datetimefrom (         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     )twhere t.rn=1order 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()
 |