insert overwrite table dwh.dm_install_list_v2 partition(dt='${dt_today}',business='mparticle') select max(case when platform='ios' then UPPER(idfa) else LOWER(gaid) end ) as device_id, max(case when platform='ios' then 'idfa' else 'gaid' end ) as device_type, max(platform) platform, max(package_name) package_name, '${update_date}' update_date from dwh.etl_mparticle_king_audience_org where dt ='${dt_today}' group by case when platform='ios' then UPPER(idfa) else LOWER(gaid) end union select max(case when platform='ios' then UPPER(idfa) else LOWER(gaid) end ) as device_id, max(case when platform='ios' then 'idfa' else 'gaid' end ) as device_type, max(platform) platform, case when package_name = '00100165568' then '553834731' when package_name = 'mtgdmp.retargeting.mparticle.5568' then 'com.king.candycrushsaga' end as package_name, '${update_date}' update_date from dwh.etl_mparticle_king_audience_org where dt ='${dt_today}' and package_name in ('00100165568','mtgdmp.retargeting.mparticle.5568') group by case when platform='ios' then UPPER(idfa) else LOWER(gaid) end,package_name; insert overwrite table dwh.ods_dmp_user_info partition(dt='${dt_today}', business='mparticle') select max(case when platform='ios' then UPPER(idfa) else LOWER(gaid) end ) as device_id, max(case when platform='ios' then 'idfa' else 'gaid' end ) as device_type, max(platform) platform, 'UNKNOWN' country, '' age, '' gender, '' tags, '${update_date}' first_req_day, '${update_date}' last_req_day from dwh.etl_mparticle_king_audience_org where dt ='${dt_today}' group by case when platform='ios' then UPPER(idfa) else LOWER(gaid) end;