set hive.exec.dynamic.partition.mode=nonstrict; set hive.exec.dynamic.partition=true; set mapred.max.split.size=536870912; set mapred.min.split.size.per.node=536870912; set mapred.min.split.size.per.rack=536870912; set mapreduce.map.memory.mb=3072; set mapreduce.map.java.opts=-Xmx2458m; set mapreduce.reduce.memory.mb=3072; set mapreduce.reduce.java.opts=-Xmx2458m; insert overwrite table dwh.gambling_chess_rt_data_weekly partition(dt='${dt_today}',business='gambling') select devices from ( select devices,daleis,count(1) as number from ( select t1.device_id as devices,t1.package_name as package,t2.dalei as daleis from (select device_id,package_name from dwh.dm_install_list_v2 where dt ='${dt_today}' and platform = 'ios' group by device_id,package_name) as t1 join (select package_name,platform, min(case when first_tag = 'hardcoregame' then 1 when first_tag = 'Games' and second_tag = 'Card' then 2 when first_tag = 'Games' and second_tag = 'Casino' then 3 when first_tag = 'lightgame' and tag_type = 'rules' then 4 when first_tag = 'hypercasual' and tag_type = 'rules' then 5 when first_tag = 'relax' then 6 when first_tag = 'Games' and second_tag = 'Word' then 7 when first_tag = 'Games' and second_tag = 'Puzzle' then 8 when first_tag = 'Games' and second_tag = 'Cleanup' then 9 when first_tag = 'elimination' then 10 when first_tag = 'moderategame' and tag_type = 'rules' then 11 when first_tag = 'Social' and second_tag = 'Forums' then 12 when first_tag = 'Video' then 13 when first_tag = 'Books' and second_tag = 'Online Reading' then 14 when first_tag = 'Tools' then 15 when first_tag = 'Children' and tag_type = 'rules' then 16 else 17 end) as dalei from dwh.dim_package_tags where platform = 'ios' group by package_name,platform) t2 on t1.package_name = t2.package_name group by t1.device_id,t1.package_name,t2.dalei) a group by devices,daleis) b where daleis = '3' and number >= 3 group by devices; insert overwrite table dwh.gambling_chess_rt_output_weekly partition(dt='${dt_today}') select device_id, 'idfa' as device_type, 'ios' as platform, '["202007020000"]' as package_name from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='gambling'; insert overwrite table dwh.gambling_chess_rt_ods_other_device_weekly partition(dt='${dt_today}') select device_id, 'idfa' as device_type, 'ios' as platform, 'UNKNOWN' as country from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='gambling'; insert overwrite table dwh.gambling_chess_rt_data_weekly partition(dt='${dt_today}',business='chess') select devices from ( select devices,daleis,count(1) as number from ( select t1.device_id as devices,t1.package_name as package,t2.dalei as daleis from (select device_id,package_name from dwh.dm_install_list_v2 where dt ='${dt_today}' and platform = 'ios' group by device_id,package_name) as t1 join (select package_name,platform, min(case when first_tag = 'hardcoregame' then 1 when first_tag = 'Games' and second_tag = 'Card' then 2 when first_tag = 'Games' and second_tag = 'Casino' then 3 when first_tag = 'lightgame' and tag_type = 'rules' then 4 when first_tag = 'hypercasual' and tag_type = 'rules' then 5 when first_tag = 'relax' then 6 when first_tag = 'Games' and second_tag = 'Word' then 7 when first_tag = 'Games' and second_tag = 'Puzzle' then 8 when first_tag = 'Games' and second_tag = 'Cleanup' then 9 when first_tag = 'elimination' then 10 when first_tag = 'moderategame' and tag_type = 'rules' then 11 when first_tag = 'Social' and second_tag = 'Forums' then 12 when first_tag = 'Video' then 13 when first_tag = 'Books' and second_tag = 'Online Reading' then 14 when first_tag = 'Tools' then 15 when first_tag = 'Children' and tag_type = 'rules' then 16 else 17 end) as dalei from dwh.dim_package_tags where platform = 'ios' group by package_name,platform) t2 on t1.package_name = t2.package_name group by t1.device_id,t1.package_name,t2.dalei) a group by devices,daleis) b where daleis = '2' and number >= 5 group by devices; insert into table dwh.gambling_chess_rt_output_weekly partition(dt='${dt_today}') select device_id, 'idfa' as device_type, 'ios' as platform, '["202006220000"]' as package_name from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='chess'; insert into table dwh.gambling_chess_rt_ods_other_device_weekly partition(dt='${dt_today}') select device_id, 'idfa' as device_type, 'ios' as platform, 'UNKNOWN' as country from dwh.gambling_chess_rt_data_weekly where dt='${dt_today}' and business='chess';