tkdm_data_app_summary_day.sql 2.06 KB
Newer Older
mengdongxing committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  
set mapred.min.split.size.per.rack=100000000;  
set mapred.min.split.size.per.node=100000000;  
set mapred.max.split.size=256000000;  
set hive.merge.mapfiles = true;
set hive.merge.smallfiles.avgsize=16000000;

insert overwrite table tkdm.tkdm_data_app_summary_day partition(ds='$ds') 

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,
       a.dupnum_reged_day,
       a.dupnum_reged_mon,
       round(a.amt_income_new_user,2) as amt_income_new_user,
       a.num_payer_new_user,
       round(a.amt_income_all_user) as amt_income_all_user,
       a.num_payer_all_user,
       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,
       sum(dupnum_reged_day) as dupnum_reged_day,
       sum(dupnum_reged_mon) as dupnum_reged_mon,
       sum(amt_income_new_user) as amt_income_new_user,
       sum(num_payer_new_user) as num_payer_new_user,
       sum(amt_income_all_user) as amt_income_all_user,
       sum(num_payer_all_user) as num_payer_all_user
     from tkio.tkio_rpt_channel_compare_day
     where dt='$ds' 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 ='$ds'
) as b on a.appid=b.appid




create EXTERNAL table tkdm_data_app_summary_day(
appid string,
cid int,
dupnum_click_all int,
num_install int,
ins_rate double,
dupnum_reged_day int,
dupnum_reged_mon int,
amt_income_new_user double,
num_payer_new_user int,
amt_income_all_user double,
num_payer_all_user int,
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_app_summary_day'
;