tkdm_data_payment_detial_day.sql 1.1 KB
Newer Older
mengdongxing committed
1 2 3 4 5 6 7
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;

8
insert overwrite table tkdm.tkdm_data_payment_detial_day partition(ds='2017-02-25')
mengdongxing committed
9 10
select  appid,
        deviceid,
11
        ins_datetime,
mengdongxing committed
12 13 14
        count(1) as num_payment_cnt,
        round(sum(amount),2) as amt_payment
   from tkio.tkio_mid_pay_day
15
   where ds='2017-02-25' and 
mengdongxing committed
16 17
         deviceid!='00000000-0000-0000-0000-000000000000'
         and deviceid!='' and deviceid!='0'
18
   group by appid,deviceid,ins_datetime
19
   cluster by deviceid,appid
mengdongxing committed
20 21 22 23 24


create EXTERNAL table tkdm_data_payment_detial_day(
appid string,
deviceid string,
25
ins_datetime string,
mengdongxing committed
26 27 28 29
num_payment_cnt int,
amt_payment double
)
PARTITIONED BY (ds string)
30
clustered by (deviceid) sorted by(deviceid,appid)  into 4 buckets
mengdongxing committed
31 32 33 34
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
STORED AS ORC
location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_data_payment_detial_day'
35
;