hive动态分区注意事项

2019独角兽企业重金招聘Python工程师标准>>>

注意：插入数据的时候，字段顺序需与创建分区表时的顺序一致。

#################创建分区表

drop table if exists default.rpt_customer_coupon_details_partition;

CREATE TABLE default.rpt_customer_coupon_details_partition (
  `id` int,
  `cname` string,
  `cphone` string,
  `ccity` string,
  `couponid` int,
  `couponname` string,
  `accity` string,
  `ctype` int,
  `couponvalue` double ,
  `duration` string ,
  `pagename` string ,
  `expired_at` string ,
  `status` int ,
  `minCost` double ,
  `maxDiscount` double ,
  `minPay` double ,
  `created_at` string ,
  `updated_at` string,
  `source` string ,
  `actionname` string ,
  `carprice` double,
  `taxiprice` double,
  `ticketprice` double,
  `buspoolprice` double
)
comment 'rpt_customer_coupon_details'
partitioned by (year string,month string,date_id string)
row format delimited fields terminated by '\t'
stored as textfile;

########设置动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.max.created.files=150000; 
set dfs.datanode.max.xcievers=8192;

######向分区表插入数据
insert into default.rpt_customer_coupon_details_partition
partition (year,month,date_id)
select 
a.id,
b.`name` cname,
b.phone cphone,
a.city ccity,
a.coupon_id couponid,
c.`name` couponname,
ac.city accity,
a.type AS ctype ,
a.`value` couponvalue,
c.duration,
g.`name` pagename,
a.expire_at expired_at,
a.status,
c.minCost,
c.maxDiscount,
c.minPay,
a.created_at,
a.updated_at,
a.source,
ac.title actionname,
nvl((case when r.status=1 then nvl(price,0) else 0 end),0) carprice,
nvl((case when r.status=2 then nvl(price,0) else 0 end),0) taxiprice,
nvl((case when r.status=3 then nvl(price,0) else 0 end),0) ticketprice,
nvl((case when r.status=4 then nvl(price,0) else 0 end),0) buspoolprice,
substr(to_date(a.created_at),1,4) year,
substr(to_date(a.created_at),1,7) month,
cast(to_date(a.created_at) as string) date_id
from 
(select 
x.id,
x.customer_id,
x.coupon_id,
x.giftpack_id,
x.action_id,
x.source,
x.type,
x.source_type,
x.source_id,
x.status,
x.created_at,
x.updated_at,
x.city,
x.value,
x.remark,
x.expire_at 
from default.fct_customer_coupon x 
) a 
LEFT JOIN default.dim_customer b on a.customer_id= b.id
LEFT JOIN default.dim_coupon c on a.coupon_id=c.id
LEFT JOIN default.dim_giftpack g on a.giftpack_id=g.id 
LEFT JOIN default.dim_action ac on a.action_id=ac.id 
LEFT JOIN (
     SELECT 
           p.coupon_id,
           p.price,
           p.status
     from default.fct_order_payment_deduction p 
) r on a.id=r.coupon_id
;

转载于:https://my.oschina.net/zhouwang93/blog/1619884