payment_checkpoint.sql 18.5 KB
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 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473
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.strict.checks.cartesian.product=false;
set hive.mapred.mode=nostrict;

drop table IF EXISTS exchange_rate_${date_str_undline};
CREATE external TABLE IF NOT EXISTS exchange_rate_${date_str_undline}
(AED string,
    AFN string,
    `ALL` string,
    AMD string,
    ANG string,
    AOA string,
    ARS string,
    AUD string,
    AWG string,
    AZN string,
    BAM string,
    BBD string,
    BDT string,
    BGN string,
    BHD string,
    BIF string,
    BMD string,
    BND string,
    BOB string,
    BRL string,
    BSD string,
    BTC string,
    BTN string,
    BWP string,
    BYN string,
    BZD string,
    CAD string,
    CDF string,
    CHF string,
    CLF string,
    CLP string,
    CNH string,
    CNY string,
    COP string,
    CRC string,
    CUC string,
    CUP string,
    CVE string,
    CZK string,
    DJF string,
    DKK string,
    DOP string,
    DZD string,
    EGP string,
    ERN string,
    ETB string,
    EUR string,
    FJD string,
    FKP string,
    GBP string,
    GEL string,
    GGP string,
    GHS string,
    GIP string,
    GMD string,
    GNF string,
    GTQ string,
    GYD string,
    HKD string,
    HNL string,
    HRK string,
    HTG string,
    HUF string,
    IDR string,
    ILS string,
    IMP string,
    INR string,
    IQD string,
    IRR string,
    ISK string,
    JEP string,
    JMD string,
    JOD string,
    JPY string,
    KES string,
    KGS string,
    KHR string,
    KMF string,
    KPW string,
    KRW string,
    KWD string,
    KYD string,
    KZT string,
    LAK string,
    LBP string,
    LKR string,
    LRD string,
    LSL string,
    LYD string,
    MAD string,
    MDL string,
    MGA string,
    MKD string,
    MMK string,
    MNT string,
    MOP string,
    MRO string,
    MRU string,
    MUR string,
    MVR string,
    MWK string,
    MXN string,
    MYR string,
    MZN string,
    NAD string,
    NGN string,
    NIO string,
    NOK string,
    NPR string,
    NZD string,
    OMR string,
    PAB string,
    PEN string,
    PGK string,
    PHP string,
    PKR string,
    PLN string,
    PYG string,
    QAR string,
    RON string,
    RSD string,
    RUB string,
    RWF string,
    SAR string,
    SBD string,
    SCR string,
    SDG string,
    SEK string,
    SGD string,
    SHP string,
    SLL string,
    SOS string,
    SRD string,
    SSP string,
    STD string,
    STN string,
    SVC string,
    SYP string,
    SZL string,
    THB string,
    TJS string,
    TMT string,
    TND string,
    TOP string,
    TRY string,
    TTD string,
    TWD string,
    TZS string,
    UAH string,
    UGX string,
    USD string,
    UYU string,
    UZS string,
    VEF string,
    VES string,
    VND string,
    VUV string,
    WST string,
    XAF string,
    XAG string,
    XAU string,
    XCD string,
    XDR string,
    XOF string,
    XPD string,
    XPF string,
    XPT string,
    YER string,
    ZAR string,
    ZMW string,
    ZWL string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
with SERDEPROPERTIES("ignore.malformed.json" = "true")
    STORED AS TEXTFILE
    location 's3://live-ga-exchange-rates/${old_prefix}';

DROP TABLE IF EXISTS payment_checkpoint_${date_str_undline};

CREATE external TABLE IF NOT EXISTS payment_checkpoint_${date_str_undline}
(
    game_id int ,
    checkpoint date ,
    player_id string ,
    first_payed string ,
    currency string ,
    amount float ,
    cart_type string ,
    transaction_num int ,
    session_num int ,
    f1 string ,
    f2 string ,
    f3 string ,
    f4 string ,
    f5 string ,
    is_valid boolean
)
row format delimited  fields terminated by '|'
location 's3://mob-emr-test/dataplatform/emr/${prefix}/payment_checkpoint/';

INSERT overwrite table payment_checkpoint_${date_str_undline}
SELECT game_id,
       to_date(arrival_ts)                                       AS checkpoint,
       player_id                                                 AS player_id,
       null,
       currency,
       amount,
       cart_type,
       transaction_num,
       (session_num % 2147483647)                                as session_num,
       split(event_id, ':')[0]                                   AS f1,
       split(event_id, ':')[1]                                   AS f2,
       split(event_id, ':')[2]                                   AS f3,
       split(event_id, ':')[3]                                   AS f4,
       split(event_id, ':')[4]                                   AS f5,
       CASE receipt_status WHEN 'valid' THEN TRUE ELSE FALSE END AS is_valid
FROM emr_doppler_v1.event_${date_str_undline} t1 left semi join emr_doppler.currency_tmp t2
on t1.currency = t2.code
WHERE category = 'business' AND amount > 0 AND currency IS NOT NULL;

INSERT overwrite table dim_payment_first_payed
SELECT X.game_id, x.player_id, x.first_payed
FROM (SELECT game_id, player_id as player_id, from_unixtime(cast(pay_ft as bigint), 'yyyy-MM-dd') as first_payed
      FROM emr_doppler_v1.event_${date_str_undline}
      WHERE pay_ft IS NOT NULL
        AND pay_ft != ''
        AND cast(pay_ft as string) != 'undefined'
      GROUP BY game_id, player_id, pay_ft ) X
group by X.game_id, x.player_id, x.first_payed;

drop table IF EXISTS payment_checkpoint_${date_str_undline}_tmp;

use emr_doppler_v1;

create table emr_doppler_v1.payment_checkpoint_${date_str_undline}_tmp
    location 's3://mob-emr-test/dataplatform/emr/payment_checkpoint_${date_str_undline}_tmp' as
SELECT pcd.game_id,
       pcd.checkpoint,
       pcd.player_id,
       case
           when x.first_payed is not null then x.first_payed
           else pcd.first_payed end as first_payed,
       pcd.currency,
       pcd.amount,
       pcd.cart_type,
       pcd.transaction_num,
       pcd.session_num,
       pcd.f1,
       pcd.f2,
       pcd.f3,
       pcd.f4,
       pcd.f5,
       pcd.is_valid
from payment_checkpoint_${date_str_undline} pcd
         left join dim_payment_first_payed x
                   on (pcd.game_id = x.game_id AND pcd.player_id = x.player_id);

insert overwrite table payment_checkpoint_${date_str_undline}
select *
from payment_checkpoint_${date_str_undline}_tmp;

INSERT overwrite table dim_player_revenue
SELECT pcd.checkpoint,
       game_id,
       player_id,
       SUM(case
               when currency = 'MWK' then amount * er.usd / er.mwk
               when currency = 'MGA' then amount * er.usd / er.mga
               when currency = 'XDR' then amount * er.usd / er.xdr
               when currency = 'BZD' then amount * er.usd / er.bzd
               when currency = 'BAM' then amount * er.usd / er.bam
               when currency = 'EGP' then amount * er.usd / er.egp
               when currency = 'MOP' then amount * er.usd / er.mop
               when currency = 'NAD' then amount * er.usd / er.nad
               when currency = 'SSP' then amount * er.usd / er.ssp
               when currency = 'NIO' then amount * er.usd / er.nio
               when currency = 'PEN' then amount * er.usd / er.pen
               when currency = 'NZD' then amount * er.usd / er.nzd
               when currency = 'WST' then amount * er.usd / er.wst
               when currency = 'TMT' then amount * er.usd / er.tmt
               when currency = 'CLF' then amount * er.usd / er.clf
               when currency = 'BRL' then amount * er.usd / er.brl
               when currency = 'FJD' then amount * er.usd / er.fjd
               when currency = 'MXN' then amount * er.usd / er.mxn
               when currency = 'STD' then amount * er.usd / er.std
               when currency = 'SCR' then amount * er.usd / er.scr
               when currency = 'CDF' then amount * er.usd / er.cdf
               when currency = 'BBD' then amount * er.usd / er.bbd
               when currency = 'GTQ' then amount * er.usd / er.gtq
               when currency = 'CLP' then amount * er.usd / er.clp
               when currency = 'HNL' then amount * er.usd / er.hnl
               when currency = 'UGX' then amount * er.usd / er.ugx
               when currency = 'ZAR' then amount * er.usd / er.zar
               when currency = 'TND' then amount * er.usd / er.tnd
               when currency = 'STN' then amount * er.usd / er.stn
               when currency = 'CUC' then amount * er.usd / er.cuc
               when currency = 'BSD' then amount * er.usd / er.bsd
               when currency = 'SLL' then amount * er.usd / er.sll
               when currency = 'SDG' then amount * er.usd / er.sdg
               when currency = 'IQD' then amount * er.usd / er.iqd
               when currency = 'CUP' then amount * er.usd / er.cup
               when currency = 'GMD' then amount * er.usd / er.gmd
               when currency = 'TWD' then amount * er.usd / er.twd
               when currency = 'RSD' then amount * er.usd / er.rsd
               when currency = 'DOP' then amount * er.usd / er.dop
               when currency = 'KMF' then amount * er.usd / er.kmf
               when currency = 'MYR' then amount * er.usd / er.myr
               when currency = 'FKP' then amount * er.usd / er.fkp
               when currency = 'XOF' then amount * er.usd / er.xof
               when currency = 'GEL' then amount * er.usd / er.gel
               when currency = 'BTC' then amount * er.usd / er.btc
               when currency = 'UYU' then amount * er.usd / er.uyu
               when currency = 'MAD' then amount * er.usd / er.mad
               when currency = 'CVE' then amount * er.usd / er.cve
               when currency = 'AZN' then amount * er.usd / er.azn
               when currency = 'TOP' then amount * er.usd / er.top
               when currency = 'OMR' then amount * er.usd / er.omr
               when currency = 'PGK' then amount * er.usd / er.pgk
               when currency = 'KES' then amount * er.usd / er.kes
               when currency = 'SEK' then amount * er.usd / er.sek
               when currency = 'CNH' then amount * er.usd / er.cnh
               when currency = 'BTN' then amount * er.usd / er.btn
               when currency = 'UAH' then amount * er.usd / er.uah
               when currency = 'GNF' then amount * er.usd / er.gnf
               when currency = 'ERN' then amount * er.usd / er.ern
               when currency = 'MZN' then amount * er.usd / er.mzn
               when currency = 'SVC' then amount * er.usd / er.svc
               when currency = 'ARS' then amount * er.usd / er.ars
               when currency = 'QAR' then amount * er.usd / er.qar
               when currency = 'IRR' then amount * er.usd / er.irr
               when currency = 'MRO' then amount * er.usd / er.mro
               when currency = 'XPD' then amount * er.usd / er.xpd
               when currency = 'CNY' then amount * er.usd / er.cny
               when currency = 'THB' then amount * er.usd / er.thb
               when currency = 'UZS' then amount * er.usd / er.uzs
               when currency = 'XPF' then amount * er.usd / er.xpf
               when currency = 'MRU' then amount * er.usd / er.mru
               when currency = 'BDT' then amount * er.usd / er.bdt
               when currency = 'LYD' then amount * er.usd / er.lyd
               when currency = 'BMD' then amount * er.usd / er.bmd
               when currency = 'KWD' then amount * er.usd / er.kwd
               when currency = 'PHP' then amount * er.usd / er.php
               when currency = 'XPT' then amount * er.usd / er.xpt
               when currency = 'RUB' then amount * er.usd / er.rub
               when currency = 'PYG' then amount * er.usd / er.pyg
               when currency = 'ISK' then amount * er.usd / er.isk
               when currency = 'JMD' then amount * er.usd / er.jmd
               when currency = 'COP' then amount * er.usd / er.cop
               when currency = 'MKD' then amount * er.usd / er.mkd
               when currency = 'USD' then amount * er.usd / er.usd
               when currency = 'DZD' then amount * er.usd / er.dzd
               when currency = 'PAB' then amount * er.usd / er.pab
               when currency = 'GGP' then amount * er.usd / er.ggp
               when currency = 'SGD' then amount * er.usd / er.sgd
               when currency = 'ETB' then amount * er.usd / er.etb
               when currency = 'JEP' then amount * er.usd / er.jep
               when currency = 'KGS' then amount * er.usd / er.kgs
               when currency = 'SOS' then amount * er.usd / er.sos
               when currency = 'VEF' then amount * er.usd / er.vef
               when currency = 'VUV' then amount * er.usd / er.vuv
               when currency = 'LAK' then amount * er.usd / er.lak
               when currency = 'BND' then amount * er.usd / er.bnd
               when currency = 'XAF' then amount * er.usd / er.xaf
               when currency = 'LRD' then amount * er.usd / er.lrd
               when currency = 'XAG' then amount * er.usd / er.xag
               when currency = 'ALL' then amount * er.usd / er.`all`
               when currency = 'CHF' then amount * er.usd / er.chf
               when currency = 'HRK' then amount * er.usd / er.hrk
               when currency = 'DJF' then amount * er.usd / er.djf
               when currency = 'VES' then amount * er.usd / er.ves
               when currency = 'ZMW' then amount * er.usd / er.zmw
               when currency = 'TZS' then amount * er.usd / er.tzs
               when currency = 'VND' then amount * er.usd / er.vnd
               when currency = 'XAU' then amount * er.usd / er.xau
               when currency = 'AUD' then amount * er.usd / er.aud
               when currency = 'ILS' then amount * er.usd / er.ils
               when currency = 'GHS' then amount * er.usd / er.ghs
               when currency = 'GYD' then amount * er.usd / er.gyd
               when currency = 'KPW' then amount * er.usd / er.kpw
               when currency = 'BOB' then amount * er.usd / er.bob
               when currency = 'KHR' then amount * er.usd / er.khr
               when currency = 'MDL' then amount * er.usd / er.mdl
               when currency = 'IDR' then amount * er.usd / er.idr
               when currency = 'KYD' then amount * er.usd / er.kyd
               when currency = 'AMD' then amount * er.usd / er.amd
               when currency = 'BWP' then amount * er.usd / er.bwp
               when currency = 'SHP' then amount * er.usd / er.shp
               when currency = 'TRY' then amount * er.usd / er.try
               when currency = 'LBP' then amount * er.usd / er.lbp
               when currency = 'TJS' then amount * er.usd / er.tjs
               when currency = 'JOD' then amount * er.usd / er.jod
               when currency = 'HKD' then amount * er.usd / er.hkd
               when currency = 'RWF' then amount * er.usd / er.rwf
               when currency = 'AED' then amount * er.usd / er.aed
               when currency = 'EUR' then amount * er.usd / er.eur
               when currency = 'LSL' then amount * er.usd / er.lsl
               when currency = 'DKK' then amount * er.usd / er.dkk
               when currency = 'CAD' then amount * er.usd / er.cad
               when currency = 'BGN' then amount * er.usd / er.bgn
               when currency = 'MMK' then amount * er.usd / er.mmk
               when currency = 'MUR' then amount * er.usd / er.mur
               when currency = 'NOK' then amount * er.usd / er.nok
               when currency = 'SYP' then amount * er.usd / er.syp
               when currency = 'IMP' then amount * er.usd / er.imp
               when currency = 'ZWL' then amount * er.usd / er.zwl
               when currency = 'GIP' then amount * er.usd / er.gip
               when currency = 'RON' then amount * er.usd / er.ron
               when currency = 'LKR' then amount * er.usd / er.lkr
               when currency = 'NGN' then amount * er.usd / er.ngn
               when currency = 'CRC' then amount * er.usd / er.crc
               when currency = 'CZK' then amount * er.usd / er.czk
               when currency = 'PKR' then amount * er.usd / er.pkr
               when currency = 'XCD' then amount * er.usd / er.xcd
               when currency = 'HTG' then amount * er.usd / er.htg
               when currency = 'ANG' then amount * er.usd / er.ang
               when currency = 'BHD' then amount * er.usd / er.bhd
               when currency = 'KZT' then amount * er.usd / er.kzt
               when currency = 'SRD' then amount * er.usd / er.srd
               when currency = 'SZL' then amount * er.usd / er.szl
               when currency = 'SAR' then amount * er.usd / er.sar
               when currency = 'TTD' then amount * er.usd / er.ttd
               when currency = 'YER' then amount * er.usd / er.yer
               when currency = 'AFN' then amount * er.usd / er.afn
               when currency = 'MVR' then amount * er.usd / er.mvr
               when currency = 'INR' then amount * er.usd / er.inr
               when currency = 'AWG' then amount * er.usd / er.awg
               when currency = 'KRW' then amount * er.usd / er.krw
               when currency = 'NPR' then amount * er.usd / er.npr
               when currency = 'AOA' then amount * er.usd / er.aoa
               when currency = 'JPY' then amount * er.usd / er.jpy
               when currency = 'MNT' then amount * er.usd / er.mnt
               when currency = 'PLN' then amount * er.usd / er.pln
               when currency = 'GBP' then amount * er.usd / er.gbp
               when currency = 'SBD' then amount * er.usd / er.sbd
               when currency = 'BYN' then amount * er.usd / er.byn
               when currency = 'HUF' then amount * er.usd / er.huf
               when currency = 'BIF' then amount * er.usd / er.bif
           end) as revenue
FROM payment_checkpoint_${date_str_undline} pcd
         LEFT JOIN ( SELECT * FROM exchange_rate_${date_str_undline} ) er
                   ON pcd.checkpoint = '${date_str_midline}'
GROUP BY pcd.checkpoint, game_id, player_id;


INSERT overwrite table dim_player_count_transaction
SELECT game_id, player_id, COUNT(*) as count_transaction, true as is_day_payer
FROM payment_checkpoint_${date_str_undline}
GROUP BY game_id, player_id;

INSERT overwrite table dim_player_count_transaction_num
SELECT game_id, player_id, COUNT(*) AS count_transaction_num
FROM payment_checkpoint_${date_str_undline}
WHERE transaction_num IS NOT NULL
GROUP BY game_id, player_id;

INSERT overwrite table dim_player_min_transaction_num
SELECT game_id,
       player_id,
       MIN(transaction_num) AS min_transaction_num,
       MAX(transaction_num) AS max_transaction_num,
       MIN(first_payed)     AS first_payed
FROM payment_checkpoint_${date_str_undline} pcd
WHERE coalesce(transaction_num, cast(first_payed as int)) IS NOT NULL
GROUP BY game_id, player_id;