Hive分区表静态分区和动态分区使用

一、静态分区

创建一张表

create table info(
id    string
,sex  string
,dt   string
)
;

导入8条数据如下

insert into info select '001','male','201001';
insert into info select '002','female','201001';
insert into info select '003','male','201002';
insert into info select '004','female','201002';
insert into info select '005','male','201003';
insert into info select '006','female','201003';
insert into info select '007','male','201004';
insert into info select '008','female','201004';

创建一级分区表

create table info_pt1(
id    string
,sex  string
,dt   string
)
PARTITIONED BY (dt_pt string) 
;

1.指定分区，将数据导入分区中

insert overwrite table info_pt1 partition (dt_pt='201001')
select
id  
,sex
,dt 
from info
where dt='201001'
;

查询表，202001分区中确实有数据了，并且分区目录已创建好，分区目录中有数据文件

2.创建二级分区表

create table info_pt2(
id    string
,sex  string
,dt   string
)
PARTITIONED BY (dt_pt string,sex_pt string) 
;

将数据导入二级分区，注意分区字段值不要用中文

insert overwrite table info_pt2 partition (dt_pt='201001',sex_pt='male')
select
id  
,sex
,dt 
from info
where dt='201001' and sex='male'
;

查询二级分区表

 

二、动态分区

动态分区有两项需要设置，如下

set hive.exec.dynamici.partition=true;  --开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; --开启允许所有分区都是动态的

1.一级分区动态分区

删除之前一级分区表的分区，删除分区会将hdfs上的分区文件夹删掉，move到.trash文件夹中，默认保留7天，删除所有分区后表就会查询不到数据

ALTER TABLE info_pt1 DROP IF EXISTS PARTITION (dt_pt='201001');

动态分区插入数据

set hive.exec.dynamici.partition=true;  --开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; --开启允许所有分区都是动态的
insert overwrite table info_pt1
select
id  
,sex
,dt 
,dt as dt_pt
from info
;

查询发现数据都插入到对应的分区，在hdfs上数据落在不同的分区目录下

2.二级动态分区

二级全动态分区

set hive.exec.dynamici.partition=true;  --开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; --开启允许所有分区都是动态的
insert overwrite table info_pt2
select
id  
,sex
,dt 
,dt as dt_pt
,sex as sex_pt
from info
;

查询结果如下

一级静态分区，二级动态分区

ALTER TABLE info_pt2 DROP IF EXISTS PARTITION (dt_pt='201001');
ALTER TABLE info_pt2 DROP IF EXISTS PARTITION (dt_pt='201002');
ALTER TABLE info_pt2 DROP IF EXISTS PARTITION (dt_pt='201003');
ALTER TABLE info_pt2 DROP IF EXISTS PARTITION (dt_pt='201004');
set hive.exec.dynamici.partition=true;  --开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; --开启允许所有分区都是动态的
insert overwrite table info_pt2 partition(dt_pt='201001',sex_pt)
select
id  
,sex
,dt 
,sex as sex_pt
from info
where dt='201001'
;

查询结果如下

 

三、注意项：

1.分区字段就是字段，可以用来取值和筛选，分区字段不同点表现在hdfs有分区字段目录，对于内部表来说表目录下就是数据文件，分区表的表下面有分区目录，分区目录下才是文件。

2.分区表相对于内部表的优点：内部表查询时扫描表目录下的所有文件，分区表筛选对应分区，会到分区对应的目录下扫描文件，查询效率大大提升。

3.静态分区适用可以传参的情况下，shell中hive --hiveconf和--hivevar可以实现传参。只能写sql的话建议适用动态分区，动态分区还可用来刷数据，在维度表变化的情况下，分区表使用动态分区可以快速刷新历史数据。