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 ;