BytedanceMaterialMd5FristCostDateUpdate.py 2.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/17 3:40 下午
  4. # @Site :
  5. # @File : BytedanceMaterialMd5FristCostDateUpdate.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import logging
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.conn.TidbProConn import TidbProConn
  13. from Apietl.utlis.Utils import Utils
  14. class BytedanceMaterialMd5FristCostDateUpdate:
  15. def __init__(self):
  16. self.conn = TidbProConn.get_connection()
  17. self.Innerconn = TidbConn.get_connection()
  18. self.parm = Utils.get_args()
  19. self.start_time = self.parm[0]
  20. self.end_time = self.parm[1]
  21. def TaskHandler(self):
  22. try:
  23. sql = """
  24. replace into application.bytedance_material_md5_frist_cost_time
  25. select a.material_id,a.signature,
  26. case when b.first_cost_time is null then a.stat_datetime
  27. when a.stat_datetime<b.first_cost_time then a.stat_datetime
  28. else b.first_cost_time end as first_cost_time
  29. from
  30. (select material_id,signature, stat_datetime
  31. from (
  32. select material_id,signature, stat_datetime, row_number() over (partition by material_id order by stat_datetime) as rn
  33. from application.bytedance_material_video_report_daily_dw
  34. where stat_datetime between {start_time} and {end_time}
  35. ) t
  36. where t.rn = 1)a
  37. left join
  38. (select material_id, first_cost_time from application.bytedance_material_md5_frist_cost_time)b
  39. on a.material_id=b.material_id
  40. """.format(start_time=self.start_time, end_time=self.end_time)
  41. print(sql)
  42. # TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  43. TidbConn.upsert(conn=self.Innerconn, sql_buff=sql)
  44. # self.conn.commit()
  45. self.Innerconn.commit()
  46. # TidbProConn.conn_close(self.conn)
  47. TidbConn.conn_close(self.Innerconn)
  48. return 0
  49. except Exception as e:
  50. logging.error(e)
  51. return -1
  52. if __name__ == '__main__':
  53. status = BytedanceMaterialMd5FristCostDateUpdate().TaskHandler()
  54. if status == 0:
  55. print("作业执行成功")
  56. exit(0)
  57. else:
  58. print("作业执行失败")
  59. exit(-1)