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
use emr_doppler_v1;
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapreduce.map.memory.mb=2048;
set mapreduce.reduce.memory.mb=3072;
set mapred.reduce.tasks=200;
INSERT overwrite table dim_pc_more_tmp
select game_id,
to_date(arrival_ts) AS checkpoint,
player_id AS player_id,
user_id,
max(country_code) as country_code,
max(facebook_id) as facebook_id,
max(ios_id) as ios_id,
coalesce(max(android_id), max(v1_android_id)) as android_id,
coalesce(max(google_aid), max(v1_google_aid)) as google_aid,
max(ios_idfa) as ios_idfa,
max(ios_idfv) as ios_idfv,
max(googleplus_id) as googleplus_id,
coalesce(max(gender), max(v1_gender)) as gender,
max(substr(birth_year, 1, 4)) as birth_year,
coalesce(max(sdk_version), max(v1_sdk_version)) as sdk_version,
max(engine_version) as engine_version,
coalesce(max(os_version), max(v1_os_major)) as os_version,
max(substr(manufacturer, 1, 32)) as manufacturer,
coalesce(max(device), max(v1_device)) as device,
coalesce(max(platform), max(v1_platform)) as platform,
max(build) as build,
max(connection_type) as connection_type,
max(is_jailbroken) as is_jailbroken,
max(is_ad_tracking_limited) as is_ad_tracking_limited,
max(is_logged_gamecenter) as is_logged_gamecenter,
max(is_logged_googleplay) as is_logged_googleplay,
max(install_ts) as installed,
max(install_publisher) as install_publisher,
max(install_site) as install_site,
max(install_campaign) as install_campaign,
max(install_adgroup) as install_adgroup,
max(install_ad) as install_ad,
max(install_keyword) as install_keyword,
case
when coalesce(max(install_publisher), max(install_site), max(install_campaign), max(install_adgroup),
max(install_ad), max(install_keyword)) is not null then true
else false end as is_acquired,
count(*) as count_event
from emr_doppler_v1.event_${date_str_undline}
group by game_id, user_id, player_id, to_date(arrival_ts);
INSERT overwrite table dim_player_error_counts
SELECT game_id,
player_id AS player_id,
SUM(CASE WHEN severity = 'debug' THEN 1 ELSE 0 END) AS count_debug_error,
SUM(CASE WHEN severity = 'info' THEN 1 ELSE 0 END) AS count_info_error,
SUM(CASE WHEN severity = 'warning' THEN 1 ELSE 0 END) AS count_warning_error,
SUM(CASE WHEN severity = 'error' THEN 1 ELSE 0 END) AS count_error_error,
SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) AS count_critical_error
FROM emr_doppler_v1.event_${date_str_undline}
WHERE category = 'error'
AND severity IS NOT NULL
GROUP BY game_id, player_id;