export_hours.sql

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;

drop table IF EXISTS mv_export_${date_str_undline};
CREATE TABLE IF NOT EXISTS mv_export_${date_str_undline}
(
    hour            int,
    google_aid      string,
    android_id      string,
    ios_idfa        string,
    os_version      string,
    device          string,
    ip              string,
    connection_type string
)
row format delimited fields terminated by '|'
location 's3://mob-emr-test/dataplatform/emr/${prefix}/mv_export';

set mapred.reduce.tasks=200;

INSERT OVERWRITE table mv_export_${date_str_undline}
SELECT DISTINCT
HOUR(arrival_ts) as hour,
COALESCE(google_aid, v1_google_aid) as gaid,
COALESCE(android_id, v1_android_id) as androidid,
COALESCE(ios_idfa, ios_id) as idfa,
COALESCE(os_version, v1_os_major) as os,
COALESCE(device, v1_device) as device,
ip,
connection_type
FROM event_${date_str_undline};

insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/0/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 0 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/1/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 1 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/2/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 2 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/3/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 3 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/4/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 4 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/5/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 5 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/6/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 6 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/7/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 7 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/8/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 8 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/9/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 9 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/10/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 10 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/11/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 11 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/12/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 12 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/13/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 13 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/14/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 14 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/15/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 15 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/16/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 16 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/17/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 17 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/18/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 18 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/19/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 19 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/20/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 20 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/21/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 21 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/22/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 22 ;
insert overwrite directory 's3://mob-emr-test/dataplatform/emr/event_export/event_export_${date_str_undline}/23/' SELECT * FROM mv_export_${date_str_undline} WHERE `hour` = 23 ;