tkdm_data_app_summary_day.sql 2.23 KB
Newer Older
mengdongxing committed
1 2 3 4 5 6 7 8 9 10 11
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,
12 13
       a.num_click,
       a.dupnum_click_day,
mengdongxing committed
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
       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,
29 30
       sum(num_click) as num_click,
       sum(dupnum_click_day) as dupnum_click_day,
mengdongxing committed
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
       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,
63 64
num_click int ,
dupnum_click_day int ,
mengdongxing committed
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
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'
82
;