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;