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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
use emr_doppler;
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 hive.map.aggr = true;
set hive.optimize.skewjoin=true;
DROP TABLE IF EXISTS player_checkpoint_${date_str_undline};
CREATE external TABLE IF NOT EXISTS player_checkpoint_${date_str_undline}
(
game_id int,
checkpoint date,
player_id string,
user_id string,
is_payer boolean,
is_day_payer boolean,
first_payed date,
installed date,
install_publisher string,
install_site string,
install_campaign string,
install_adgroup string,
install_ad string,
install_keyword string,
is_acquired boolean,
revenue float,
count_transaction_num int,
min_transaction_num int,
max_transaction_num int,
country_code string,
facebook_id string,
ios_id string,
ios_idfa string,
ios_idfv string,
android_id string,
google_aid string,
googleplus_id string,
is_ad_tracking_limited boolean,
is_logged_gamecenter boolean,
is_logged_googleplay boolean,
gender string,
birth_year string,
sdk_version string,
engine_version string,
os_version string,
manufacturer string,
device string,
platform string,
build string,
connection_type string,
is_jailbroken boolean,
count_session_length int,
min_session_length int,
max_session_length int,
sum_session_length int,
sum2_session_length bigint,
min_session_num int,
max_session_num int,
count_event int,
count_transaction int,
count_debug_error int,
count_info_error int,
count_warning_error int,
count_error_error int,
count_critical_error int
) row format delimited fields terminated by '|'
location 's3://mob-emr-test/dataplatform/emr/${prefix}/player_checkpoint/';
INSERT overwrite table tmp_full
select coalesce(a.game_id, b.game_id, c.game_id, d.game_id, e.game_id) as game_id,
coalesce(a.player_id, b.player_id, c.player_id, d.player_id, e.player_id) as player_id,
a.count_transaction as count_transaction,
a.is_day_payer as is_day_payer,
b.count_transaction_num as count_transaction_num,
c.min_transaction_num as min_transaction_num,
c.max_transaction_num as max_transaction_num,
c.first_payed as first_payed,
d.is_payer as is_payer,
e.count_debug_error as count_debug_error,
e.count_info_error as count_info_error,
e.count_warning_error as count_warning_error,
e.count_error_error as count_error_error,
e.count_critical_error as count_critical_error
from dim_player_count_transaction a
full join dim_player_count_transaction_num b on (a.game_id = b.game_id and a.player_id = b.player_id)
full join dim_player_min_transaction_num c
on (coalesce(a.game_id, b.game_id) = c.game_id and coalesce(a.player_id, b.player_id) = c.player_id)
full join dim_player_is_payer d on (coalesce(a.game_id, b.game_id, c.game_id) = d.game_id and
coalesce(a.player_id, b.player_id, c.player_id) = d.player_id)
full join dim_player_error_counts e on (coalesce(a.game_id, b.game_id, c.game_id, d.game_id) = e.game_id and
coalesce(a.player_id, b.player_id, c.player_id, d.player_id) =
e.player_id);
insert overwrite table player_checkpoint_${date_str_undline}
select pc.game_id,
pc.checkpoint,
pc.player_id,
pc.user_id,
x.is_payer,
x.is_day_payer,
x.first_payed,
pc.installed,
pc.install_publisher,
pc.install_site,
pc.install_campaign,
pc.install_adgroup,
pc.install_ad,
pc.install_keyword,
pc.is_acquired,
z.revenue,
x.count_transaction_num,
x.min_transaction_num,
x.max_transaction_num,
pc.country_code,
pc.facebook_id,
pc.ios_id,
pc.ios_idfa,
pc.ios_idfv,
pc.android_id,
pc.google_aid,
pc.googleplus_id,
pc.is_ad_tracking_limited,
pc.is_logged_gamecenter,
pc.is_logged_googleplay,
pc.gender,
pc.birth_year,
pc.sdk_version,
pc.engine_version,
substr(pc.os_version, 1, 30) os_version,
pc.manufacturer,
substr(pc.device, 1, 64) device,
pc.platform,
substr(pc.build, 1, 64) build,
pc.connection_type,
pc.is_jailbroken,
y.count_session_length,
y.min_session_length,
y.max_session_length,
y.sum_session_length,
y.sum2_session_length,
y.min_session_num,
y.max_session_num,
pc.count_event,
x.count_transaction,
x.count_debug_error,
x.count_info_error,
x.count_warning_error,
x.count_error_error,
x.count_critical_error
FROM dim_pc_more_tmp pc
full join tmp_full x on (pc.game_id = x.game_id and pc.player_id = x.player_id)
full join dim_player_session y on (coalesce(pc.game_id, x.game_id) = y.game_id and
coalesce(pc.player_id, x.player_id) = y.player_id)
full join dim_player_revenue z on (coalesce(pc.game_id, x.game_id, y.game_id) = z.game_id
and coalesce(pc.player_id, x.player_id, y.player_id) = z.player_id and pc.checkpoint = z.checkpoint);
DROP TABLE IF EXISTS revenue_${date_str_undline_old};
DROP TABLE IF EXISTS player_checkpoint_${date_str_undline_old};
DROP TABLE IF EXISTS payment_checkpoint_${date_str_undline_old};
DROP TABLE IF EXISTS progression_checkpoint_${date_str_undline_old};
DROP TABLE IF EXISTS session_checkpoint_${date_str_undline_old};
DROP table IF EXISTS mv_export_${date_str_undline_old};
drop table IF EXISTS exchange_rate_${date_str_undline_old};
drop table IF EXISTS payment_checkpoint_${date_str_undline}_tmp;
DROP table IF EXISTS event_${date_str_undline_old};