12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667 |
- # 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)
|