tkdm_base_device_pay_info.sql 2.08 KB
Newer Older
1
set hive.skewjoin.key=10000;
mengdongxing committed
2 3 4 5
set hive.optimize.skewjoin=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
6
set hive.merge.mapredfiles=true;
7
set hive.auto.convert.sortmerge.join=true;
mengdongxing committed
8

9
insert overwrite table tkdm.tkdm_base_device_pay_info partition(ds='2017-02-24')
mengdongxing committed
10

11
select nvl(b.appid,a.appid) as appid,
mengdongxing committed
12
       nvl(b.deviceid,a.deviceid) as deviceid,
13 14
       nvl(b.first_payment_ds,'2017-02-24') as first_payment_ds,
       if(isnull(a.deviceid),'2017-02-24',nvl(b.last_payment_ds,'1970-01-01')) as last_payment_ds,
mengdongxing committed
15 16
       nvl(b.num_payment_cnt,0)+nvl(a.num_payment_cnt,0) as num_payment_cnt,
       nvl(b.num_payment_day,0)+nvl(a.num_payment_day,0) as num_payment_day,
17
       round(nvl(b.amt_payment,0) + nvl(a.amt_payment,0),2) as amt_payment,
18 19
       coalesce(a.ins_datetime,b.ins_datetime,'1970-01-01') as ins_datetime,
       nvl(a.amt_payment,0) as amt_payment_newest
mengdongxing committed
20 21 22
from 
(select appid,
        deviceid,
23
        ins_datetime,
24 25
        amt_payment,
        num_payment_cnt,
mengdongxing committed
26
        1 as num_payment_day
27 28
   from tkdm.tkdm_data_payment_detial_day
   where ds='2017-02-24' and 
mengdongxing committed
29 30 31
         deviceid!='00000000-0000-0000-0000-000000000000'
         and deviceid!='' and deviceid!='0'
) a 
32
full join 
mengdongxing committed
33 34 35 36 37 38 39

(select appid,
       deviceid,
       first_payment_ds,
       last_payment_ds,
       num_payment_cnt,
       num_payment_day,
40 41
       amt_payment,
       ins_datetime
mengdongxing committed
42
    from tkdm.tkdm_base_device_pay_info
43
    where ds= date_sub('2017-02-24',1)
mengdongxing committed
44
) b on a.appid=b.appid and a.deviceid=b.deviceid
45
cluster by deviceid
mengdongxing committed
46 47


48 49


50

51 52


mengdongxing committed
53 54 55 56 57 58 59 60 61 62
use tkdm;


create EXTERNAL table tkdm_base_device_pay_info(
appid string,
deviceid string,
first_payment_ds string,
last_payment_ds string,
num_payment_cnt int,
num_payment_day int,
63
amt_payment double,
64 65
ins_datetime string,
amt_payment_newest double
mengdongxing committed
66 67
)
PARTITIONED BY (ds string)
68
clustered by (deviceid) sorted by(deviceid,appid)  into 32 buckets
mengdongxing committed
69 70 71 72
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
STORED AS ORC
location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_base_device_pay_info'
73
;