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 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 emr_doppler_v1.event_${date_str_undline_old};