player_checkpoint.sql 7.08 KB
Newer Older
WangJinfeng committed
1
use emr_doppler_v1;
wang-jinfeng committed
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

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;
WangJinfeng committed
167
DROP table IF EXISTS emr_doppler_v1.event_${date_str_undline_old};