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_payment_detial_day partition(ds='2017-02-25') select appid, deviceid, ins_datetime, count(1) as num_payment_cnt, round(sum(amount),2) as amt_payment from tkio.tkio_mid_pay_day where ds='2017-02-25' and deviceid!='00000000-0000-0000-0000-000000000000' and deviceid!='' and deviceid!='0' and lower(paymenttype)!='free' group by appid,deviceid,ins_datetime cluster by deviceid,appid create EXTERNAL table tkdm_data_payment_detial_day( appid string, deviceid string, ins_datetime string, num_payment_cnt int, amt_payment double ) PARTITIONED BY (ds string) clustered by (deviceid) sorted by(deviceid,appid) into 4 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS ORC location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_data_payment_detial_day' ;