insert overwrite table tkdm.tkdm_data_retention_summary_day partition(ds='2017-02-28') select /*+ mapjoin(b) */ a.appid, a.cid, date_sub('2017-02-28',1) as ds_user, a.num_user, a.d1_user, a.d1_rate, b.category_id, b.category_name, b.isgame from (select appid, cid, sum(num_install) as num_user, sum(num_newuser_reten_d1) as d1_user, round(sum(num_newuser_reten_d1)/sum(num_install),4) as d1_rate from tkio.tkio_rpt_channel_compare_day where ds between date_sub('2017-02-28',1) and '2017-02-28' and dt=date_sub('2017-02-28',1) and campaignid !='_default_' and cid!=-2 group by appid,cid having sum(num_install)>0 ) as a join (select appid, category_id, category_name, isgame from tkio_v2.tkio_dp_app_info where ds =date_sub('2017-02-28',1) ) as b on a.appid=b.appid create EXTERNAL table tkdm_data_retention_summary_day( appid string, cid int, ds_user string, num_user int, d1_user int, d1_rate double, category_id int, category_name string, isgame int ) PARTITIONED BY (ds string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_data_retention_summary_day' ;