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