insert overwrite table tkdm.tkdm_data_active_summary_day partition(ds='2017-02-26') select /*+ mapjoin(b) */ a.appid, a.cid, a.dupnum_click_all, a.num_install, round(a.num_install / a.dupnum_click_all,4) as ins_rate, b.category_id, b.category_name, b.isgame from (select appid, cid, sum(dupnum_click_all) as dupnum_click_all, sum(num_install) as num_install from tkio.tkio_rpt_channel_compare_day where ds='2017-02-26' and dt='2017-02-26' and campaignid !='_default_' and cid!=-2 group by appid,cid having sum(dupnum_click_all) > 10 ) as a join (select appid, category_id, category_name, isgame from tkio_v2.tkio_dp_app_info where ds ='2017-02-26' ) as b on a.appid=b.appid create EXTERNAL table tkdm_data_active_summary_day( appid string, cid int, dupnum_click_all int, num_install int, ins_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_active_summary_day' ;