# Author renyupeng # coding=utf-8 # @Time : 2021/9/15 5:10 下午 # @Site : # @File : BytedancePromotionVideoRelation.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn import logging from math import ceil from Apietl.kwai_etl.report.ReplaceAccountInfo import ReplaceAccountInfo from Apietl.utlis.Logger import Logger from Apietl.utlis.Utils import Utils from Apietl.conn.TidbConn import TidbConn class 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_relation SELECT 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_id FROM 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 -1 if __name__ == '__main__': status = BytedancePromotionVideoRelation().handler() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)