BytedancePromotionVideoRelation.py 2.7 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : BytedancePromotionVideoRelation.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. import logging
  9. from math import ceil
  10. from Apietl.kwai_etl.report.ReplaceAccountInfo import ReplaceAccountInfo
  11. from Apietl.utlis.Logger import Logger
  12. from Apietl.utlis.Utils import Utils
  13. from Apietl.conn.TidbConn import TidbConn
  14. class BytedancePromotionVideoRelation:
  15. def __init__(self):
  16. self.conn = TidbConn.get_connection()
  17. self.parm = Utils.get_task_args()
  18. self.start_time = self.parm[0]
  19. def handler(self):
  20. try:
  21. sql = """select advertiser_id from media_api.bytedance_promotion_list where
  22. promotion_create_time>=date_format(date_sub({start_time},interval 30 day),'%Y-%m-%d')
  23. group by advertiser_id""".format(
  24. start_time=self.start_time)
  25. account_list = TidbConn.quary(self.conn, sql)
  26. for account_id in account_list:
  27. sql = """replace into media_api.bytedance_promotion_video_relation
  28. SELECT promotion_id,
  29. replace(trim(substring_index(
  30. SUBSTRING_INDEX(replace(replace(JSON_EXTRACT(json_extract(promotion_materials, '$.video_material_list'),
  31. '$[*].video_id'), ']', ''), '[', ''), ',', hp.id + 1),
  32. ',', - 1)), '"', '') as material_id
  33. FROM media_api.bytedance_promotion_list tp
  34. LEFT JOIN test.keyid hp ON hp.id <= length(replace(
  35. replace(JSON_EXTRACT(json_extract(promotion_materials, '$.video_material_list'),
  36. '$[*].video_id'), ']', ''), '[', '')) - length(REPLACE(
  37. replace(replace(JSON_EXTRACT(json_extract(promotion_materials, '$.video_material_list'),
  38. '$[*].video_id'), ']', ''), '[', ''), ',', ''))
  39. where tp.promotion_create_time >= date_format(date_sub({start_time},interval 7 day),'%Y-%m-%d')
  40. and json_extract(promotion_materials, '$.video_material_list') is not null
  41. and status != 'AUDIT_DENY'
  42. and advertiser_id = {account_id}""". \
  43. format(start_time=self.start_time, account_id=account_id[0])
  44. print(sql)
  45. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  46. self.conn.commit()
  47. TidbConn.conn_close(conn=self.conn)
  48. return 0
  49. except Exception as e:
  50. logging.error(e)
  51. return -1
  52. if __name__ == '__main__':
  53. status = BytedancePromotionVideoRelation().handler()
  54. if status == 0:
  55. print("作业执行成功")
  56. exit(0)
  57. else:
  58. print("作业执行失败")
  59. exit(-1)