BytedanceMaterialMd5FristCostTime.py 2.1 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/17 3:40 下午
  4. # @Site :
  5. # @File : BytedanceMaterialMd5FristCostTime.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from math import ceil
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.conn.TidbProConn import TidbProConn
  13. class BytedanceMaterialMd5FristCostTime:
  14. def __init__(self):
  15. self.conn = TidbProConn.get_connection()
  16. self.Innerconn = TidbConn.get_connection()
  17. def TaskHandler(self):
  18. try:
  19. sql = """
  20. select count(1) from (
  21. select material_id,signature,stat_datetime
  22. from (
  23. select material_id,signature, stat_datetime, row_number() over (partition by signature order by stat_datetime) as rn
  24. from application.bytedance_material_video_report_daily_dw
  25. )t
  26. where t.rn=1)t
  27. """
  28. totalNum = TidbConn.quary(self.conn, sql)[0][0]
  29. pageCount = ceil(totalNum / 20000)
  30. for i in range(pageCount):
  31. try:
  32. sql = """
  33. replace into application.bytedance_material_md5_frist_cost_time
  34. select material_id,signature,stat_datetime
  35. from (
  36. select material_id,signature, stat_datetime, row_number() over (partition by signature order by stat_datetime) as rn
  37. from application.bytedance_material_video_report_daily_dw
  38. )t
  39. where t.rn=1
  40. order by material_id,stat_datetime limit {num},20000
  41. """.format(num=i * 20000)
  42. print(sql)
  43. # TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  44. TidbConn.upsert(self.Innerconn, sql_buff=sql)
  45. # self.conn.commit()
  46. self.Innerconn.commit()
  47. except Exception as e:
  48. logging.error(e)
  49. return -1
  50. # TidbProConn.conn_close(conn=self.conn)
  51. TidbConn.conn_close(conn=self.Innerconn)
  52. return 0
  53. except Exception as e:
  54. logging.error(e)
  55. if __name__ == '__main__':
  56. BytedanceMaterialMd5FristCostTime().TaskHandler()