1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
# -*- coding: utf-8 -*-
'''
用户登录手机型号,定时任务,每2个小时运行一次
'''
import logging
import os
import sys
from common.mysql_uitl import fetch_all, save_result, save_etl_log
from common.time_util import now_str, YMDHMS_FORMAT
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()
file_name = sys.argv[0]
task_file = os.path.split(__file__)[-1].split(".")[0]
def dwd_user_login_phone_mode(data_dt):
start_time = str(data_dt) + ' 00:00:00'
end_time = str(data_dt) + ' 23:59:59'
login_phone_dict = query_user_login_phone_mode(start_time, end_time)
row = save_result('tamp_data_dwd', 'dwd_user_login_phone_mode', login_phone_dict, file_name)
now_time = now_str(YMDHMS_FORMAT)
save_etl_log('tamp_data_dwd', 'dwd_user_login_phone_mode', data_dt, row, 'done', task_file, now_time)
def query_user_login_phone_mode(start_time, end_time):
logging.info(f'query_user_login_phone_mode start')
sql = '''
select p.data_dt
,p.user_id
,coalesce(t.real_name, '') as real_name
,coalesce(t.user_name, '') as user_name
,coalesce(t.nickname, '') as nickname
,t.team_id
,p.phone_mode
,coalesce(q.phone_name, '未知') as phone_name
,coalesce(q.phone_brand, '未知') as phone_brand
,p.start_time
,p.end_time
from
(
select date_format(server_time, '%%Y-%%m-%%d') as data_dt
,uid as user_id
,trim(md) as phone_mode
,min(local_time) as start_time
,max(local_time) as end_time
from tamp_analysis.access_log
where server_time between %s and %s
and md <> ''
and uid <> ''
and md is not null
and uid is not null
and local_time is not null
group by data_dt, uid, trim(md)
) p
left join tamp_analysis.user_info_view t
on p.user_id = t.user_id
left join tamp_data_dwd.dwd_phone_mode q
on p.phone_mode = q.phone_mode
'''
login_phone_dict = fetch_all(sql, (start_time, end_time))
logging.info(f'query_user_login_phone success')
return login_phone_dict
if __name__ == '__main__':
import datetime
begin = datetime.date(2021, 9, 22)
end = datetime.date(2021, 9, 22)
data_dt = begin
delta = datetime.timedelta(days=1)
while data_dt <= end:
print(data_dt.strftime("%Y-%m-%d"))
dwd_user_login_phone_mode(data_dt)
data_dt += delta