use dwh; insert overwrite table dwh.etl_af_org_daily_basic select device_id,case when substring(app_id,1,2) ='id' and length(app_id) = 11 then 'idfa' else 'gaid' end as device_type, case when substring(app_id,1,2) ='id' and length(app_id) = 11 then 'ios' else 'android' end as platform, case when substring(app_id,1,2) ='id' and length(app_id) = 11 then (case when category = 'All_GF_ADR_TransportNoFood-P2E_L365D' then '00000000001' else '00000000002' end ) else (case when category = 'All_GF_ADR_TransportNoFood-P2E_L365D' then 'grabandroid.target1' when category = 'Mobvista_VN_Adr_TransportNoFood_19092019' then 'grabandroid.target2' else 'grabandroid.target3' end ) end as package_names, category, split(input__file__name,'[._/]')[7] advertiser from dwh.etl_af_org_daily where dt='${dt_today}' and split(input__file__name,'[._/]')[10]='add' and device_id rlike '^[0-9a-fA-F]{8}(-[0-9a-fA-F]{4}){3}-[0-9a-fA-F]{12}$' and split(input__file__name,'[._/]')[7] = 'grabmtg1'; insert overwrite table dwh.etl_af_org_total partition(dt='${dt_today}') select device_id,max(device_type) device_type, max(platform) platform,max(package_names) package_names, max(category) category, max(advertiser) advertiser, '${update_date}' update_date from etl_af_org_daily_basic group by device_id; insert overwrite table ods_dmp_user_info partition(dt='${dt_today}', business='appsflyer') select device_id,max(device_type) device_type, max(platform) platform, 'VN' country, '' age, '' gender, '' tags, '${update_date}' first_req_day, '${update_date}' last_req_day from etl_af_org_daily_basic group by device_id