hive动态分区实践

一、业务需求：

数据从上一个接口传来，入hive库中的相应数据表时，需要根据数据中的某个时间字段值进行分区，分区字段为month_id(年月，如201901)，day_id(日，如07)，part_id(小时分钟，如1205)，并且part_id是每五分钟形成一个分区，如12:04的数据需要入part_id=1205的分区，12:08的数据需要入part_id=1210分区。

二、使用hive动态分区进行分区

• 创建临时表pz_partition_table_tmp，存储暂未正确分区的数据

–创建表pz_partition_table_tmp

create table pz_hive_test.pz_partition_table_tmp (
interface_name     string comment'接口名称',
interface_param_in string comment'接口入参',
interface_type string comment'接口类型',
invoke_time        string comment'调用时间'
)
partitioned by (month_id string, day_id string,part_id string)
ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY ','---数据列分隔符 
STORED AS TEXTFILE;

–加载数据，指定静态分区

load data local inpath '/home/newcs/pzTemporary/asdf.txt'
INTO TABLE pz_hive_test.pz_partition_table_tmp
PARTITION (month_id='999999',day_id=99,part_id=9999);

• 创建数据表，存储动态分区后的数据

–创建表pz_partition_table，表结构与临时表pz_partition_table_tmp相同

create table pz_hive_test.pz_partition_table like pz_hive_test.pz_partition_table_tmp;

–修改动态分区属性值

set hive.exec.dynamic.partition.mode=nonstrict

–加载数据，实现根据invoke_time时间字段进行动态分区

insert into table pz_hive_test.pz_partition_table
PARTITION (month_id,day_id,part_id)
select 
interface_name,interface_param_in,interface_type,invoke_time,
concat(substr(invoke_time,1,4),substr(invoke_time,6,2)),substr(invoke_time,9,2),
case 
--当十位<5并且个位>5，将十位置为5，个位置为0
when floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),3,1))>0 
and floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),4,1))<0
then concat(substr(invoke_time,12,2),substr('5',1,1),substr('0',1,1))
--当十位<5并且个位<5，将个位置为5
when floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),3,1))>0 
and floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),4,1))>0
then concat(substr(invoke_time,12,2),substr(invoke_time,15,1),substr('5',1,1))
--当十位=5并且个位>5，千位<>0，千位百位值+1，十位个位置为0
when floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),3,1))=0 
and floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),4,1))<0
and substr(invoke_time,12,1)<>0
then concat(cast (floor(substr(invoke_time,12,2)+1) as string),substr('00',1,2))
--当十位=5并且个位>5，千位=0，百位<9,将千位置为0，百位值+1，十位个位置为0
when floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),3,1))=0 
and floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),4,1))<0
and substr(invoke_time,12,1)=0
and substr(invoke_time,13,1)<9
then concat(substr('0',1,1),cast(floor(substr(invoke_time,13,1)+1) as string),substr('00',1,2))
--当十位=5并且个位>5，千位=0，百位=9,将千位置为0，百位值+1，十位个位置为0
when floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),3,1))=0 
and floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),4,1))<0
and substr(invoke_time,12,1)=0
and substr(invoke_time,13,1)=9
then concat(cast (floor(substr(invoke_time,12,2)+1) as string),substr('00',1,2))
--当十位=5并且个位<5，将个位置为5
when floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),3,1))=0 
and floor(5-substr(concat(substr(invoke_time,12,2),substr(invoke_time,15,2)),4,1))>0
then concat(substr(invoke_time,12,2),substr(invoke_time,15,1),substr('5',1,1))
else concat(substr(invoke_time,12,2),substr(invoke_time,15,2))
end
from pz_hive_test.pz_partition_table_tmp;
-----------------------------------
------注意：
part_id分区原则：（part_id为小时分钟，共四位数）
当十位<5并且个位>5，将十位置为5，个位置为0；
当十位<5并且个位<5，将个位置为5；
当十位=5并且个位>5，千位<>0，千位百位值+1，十位个位置为0；
当十位=5并且个位>5，千位=0，百位<9,将千位置为0，百位值+1，十位个位置为0；
当十位=5并且个位>5，千位=0，百位=9,将千位置为0，百位值+1，十位个位置为0；
当十位=5并且个位<5，将个位置为5

–如果对month_id和day_id静态分区，对part_id动态分区

insert into table dc_dwd.dwd_d_ecs_payment
PARTITION (month_id='201812',day_id='30',part_id)
select 
id,login_number,fee_number,total_fee,oper_date,oper_result,error_code,error_result,service_type,prov_id,area_code,channel_id,product_no,net_type,brand_id,pay_mode,
case 
--当十位<5并且个位>5，将十位置为5，个位置为0
when floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),3,1))>0 
and floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),4,1))<0
then concat(substr(oper_date,9,2),substr('5',1,1),substr('0',1,1))
--当十位<5并且个位<5，将个位置为5
when floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),3,1))>0 
and floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),4,1))>0
then concat(substr(oper_date,9,2),substr(oper_date,11,1),substr('5',1,1))
--当十位=5并且个位>5，千位<>0，千位百位值+1，十位个位置为0
when floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),3,1))=0 
and floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),4,1))<0
and substr(oper_date,9,1)<>0
then concat(cast (floor(substr(oper_date,9,2)+1) as string),substr('00',1,2))
--当十位=5并且个位>5，千位=0，百位<9,将千位置为0，百位值+1，十位个位置为0
when floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),3,1))=0 
and floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),4,1))<0
and substr(oper_date,9,1)=0
and substr(oper_date,10,1)<9
then concat(substr('0',1,1),cast(floor(substr(oper_date,10,1)+1) as string),substr('00',1,2))
--当十位=5并且个位>5，千位=0，百位=9,将千位置为0，百位值+1，十位个位置为0
when floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),3,1))=0 
and floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),4,1))<0
and substr(oper_date,9,1)=0
and substr(oper_date,10,1)=9
then concat(cast (floor(substr(oper_date,9,2)+1) as string),substr('00',1,2))
--当十位=5并且个位<5，将个位置为5
when floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),3,1))=0 
and floor(5-substr(concat(substr(oper_date,9,2),substr(oper_date,11,2)),4,1))>0
then concat(substr(oper_date,9,2),substr(oper_date,11,1),substr('5',1,1))
else concat(substr(oper_date,9,2),substr(oper_date,11,2))
end
from pz_hive_test.crbec0001
WHERE month_id='201812' and day_id='30';