ods_user_watch_live.py

# -*- coding: utf-8 -*-
import datetime
import logging

from phone import Phone

from common.file_uitil import get_file_name, get_file_path
from common.mysql_uitl import fetch_all, insert, insert_batch

'''
用户观看直播数据
'''

logging.basicConfig(format="%(asctime)s %(name)s:%(levelname)s:%(message)s", datefmt="%Y-%m-%d %H:%M:%S", level=logging.INFO)

file_path = get_file_path()
file_name = get_file_name()


# def ods_user_watch_live():
#     pass



def ods_user_watch_live(data_dt):
    logging.info(f"ods_user_watch_live start")
    start_time = str(data_dt) + ' 00:00:00'
    end_time = str(data_dt) + ' 23:59:59'
    del_live_data(data_dt)
    get_live_data(start_time, end_time)


# 先删除当天的数据，在插入新的数据
def del_live_data(data_dt):
    sql = 'delete from tamp_data_ods.ods_user_watch_live where data_dt = %s'
    fetch_all(sql, data_dt)


def get_live_data(start_time, end_time):
    sql = '''
    insert  into tamp_data_ods.ods_user_watch_live 
    (
        data_dt
        ,user_id
        ,province
        ,city
        ,res_id
        ,dur
        ,start_time
        ,end_time
        ,event_type
        ,is_share
        ,env
    )
    select  date_format(server_time, '%%Y-%%m-%%d') as data_dt
            ,uid as user_id
            ,province
            ,city
            ,res_id
            ,dur
            ,start_time
            ,end_time
            ,event_type
            ,is_share
            ,env
    from    tamp_analysis.access_log
    where   server_time between %s and %s
    and     event_type in ('1014', '1015')
    and     res_id <> ''
    and     res_id is not null 
    and     uid <> ''
    and     uid is not null
    '''
    fetch_all(sql, (start_time, end_time))



if __name__ == '__main__':
    # ods_user_watch_live()
    # each_platform_user_active()
    import datetime
    begin = datetime.date(2021, 8, 26)
    end = datetime.date(2021, 8, 26)
    data_dt = begin
    delta = datetime.timedelta(days=1)
    while data_dt <= end:
        print(data_dt.strftime("%Y-%m-%d"))
        ods_user_watch_live(data_dt)
        data_dt += delta