| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667 | # Author renyupeng# coding=utf-8# @Time    : 2021/9/15 5:10 下午# @Site    :# @File    : BytedancePromotionVideoRelation.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cnimport loggingfrom math import ceilfrom Apietl.kwai_etl.report.ReplaceAccountInfo import ReplaceAccountInfofrom Apietl.utlis.Logger import Loggerfrom Apietl.utlis.Utils import Utilsfrom Apietl.conn.TidbConn import TidbConnclass BytedancePromotionVideoRelation:    def __init__(self):        self.conn = TidbConn.get_connection()        self.parm = Utils.get_task_args()        self.start_time = self.parm[0]    def handler(self):        try:            sql = """select advertiser_id from media_api.bytedance_promotion_list where             promotion_create_time>=date_format(date_sub({start_time},interval 30 day),'%Y-%m-%d')              group by advertiser_id""".format(                start_time=self.start_time)            account_list = TidbConn.quary(self.conn, sql)            for account_id in account_list:                sql = """replace into media_api.bytedance_promotion_video_relationSELECT promotion_id,       replace(trim(substring_index(               SUBSTRING_INDEX(replace(replace(JSON_EXTRACT(json_extract(promotion_materials, '$.video_material_list'),                                                            '$[*].video_id'), ']', ''), '[', ''), ',', hp.id + 1),               ',', - 1)), '"', '') as material_idFROM media_api.bytedance_promotion_list tp         LEFT JOIN test.keyid hp ON hp.id <= length(replace(        replace(JSON_EXTRACT(json_extract(promotion_materials, '$.video_material_list'),                             '$[*].video_id'), ']', ''), '[', '')) - length(REPLACE(        replace(replace(JSON_EXTRACT(json_extract(promotion_materials, '$.video_material_list'),                                     '$[*].video_id'), ']', ''), '[', ''), ',', ''))where tp.promotion_create_time >= date_format(date_sub({start_time},interval 7 day),'%Y-%m-%d')  and json_extract(promotion_materials, '$.video_material_list') is not null  and status != 'AUDIT_DENY'  and advertiser_id = {account_id}""". \                    format(start_time=self.start_time, account_id=account_id[0])                print(sql)                TidbConn.upsert(conn=self.conn, sql_buff=sql)                self.conn.commit()            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = BytedancePromotionVideoRelation().handler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |