hive分区之《静态分区》和《动态分区》

先准备一个基础表student

select * from student;

id  name        age     sex
-----------------------------
1   zhangsan    19      boy
2   lisi        18      girl
3   wangwu      17      boy
4   lucy        20      girl
6   lilei       20      boy

静态分区

--创建静态分区表
create table student_p_1 (
	id int,
	name string
)
partitioned by(
	sex string,
	age int
)
row format delimited 
fields terminated by ',';

--导入数据到静态分区表
insert overwrite table student_p_1 partition(sex='boy',age=17) 
 select id, name from student where sex='boy' and age = 17; 

insert overwrite table student_p_1 partition(sex='boy',age=18) 
 select id, name from student where sex='boy' and age = 18; 

insert overwrite table student_p_1 partition(sex='boy',age=19) 
 select id, name from student where sex='boy' and age = 19; 

insert overwrite table student_p_1 partition(sex='boy',age=20) 
 select id, name from student where sex='boy' and age = 20; 

insert overwrite table student_p_1 partition(sex='girl',age=17)
 select id, name from student where sex='girl' and age = 17; 

insert overwrite table student_p_1 partition(sex='girl',age=18) 
 select id, name from student where sex='girl' and age = 18; 

insert overwrite table student_p_1 partition(sex='girl',age=19)
 select id, name from student where sex='girl' and age = 19; 

insert overwrite table student_p_1 partition(sex='girl',age=20)
 select id, name from student where sex='girl' and age = 20; 

 

#在看一下hdfs中的文件 
-> hadoop dfs -lsr /user/hive/warehouse/student_p_1

/user/hive/warehouse/student_p_1/sex=boy
/user/hive/warehouse/student_p_1/sex=boy/age=17
/user/hive/warehouse/student_p_1/sex=boy/age=17/000000_0
/user/hive/warehouse/student_p_1/sex=boy/age=18
/user/hive/warehouse/student_p_1/sex=boy/age=18/000000_0
/user/hive/warehouse/student_p_1/sex=boy/age=19
/user/hive/warehouse/student_p_1/sex=boy/age=19/000000_0
/user/hive/warehouse/student_p_1/sex=boy/age=20
/user/hive/warehouse/student_p_1/sex=boy/age=20/000000_0
/user/hive/warehouse/student_p_1/sex=girl
/user/hive/warehouse/student_p_1/sex=girl/age=17
/user/hive/warehouse/student_p_1/sex=girl/age=17/000000_0
/user/hive/warehouse/student_p_1/sex=girl/age=18
/user/hive/warehouse/student_p_1/sex=girl/age=18/000000_0
/user/hive/warehouse/student_p_1/sex=girl/age=19
/user/hive/warehouse/student_p_1/sex=girl/age=19/000000_0
/user/hive/warehouse/student_p_1/sex=girl/age=20
/user/hive/warehouse/student_p_1/sex=girl/age=20/000000_0

#查看student_p_1的分区
hive > show partitions student_p_1;
sex=boy/age=17
sex=boy/age=18
sex=boy/age=19
sex=boy/age=20
sex=girl/age=17
sex=girl/age=18
sex=girl/age=19
sex=girl/age=20

可以看到静态表每次导入数据的时候都要手动为分区字段赋值。

动态分区

--创建动态分区表student_p_2（建表语句和静态分区无区别）
create table student_p_2 (
    id int,
    name string
) 
partitioned by(
    sex string,
    age int
) 
row format delimited 
fields terminated by ',';

#动态分区相关参数设置

#开始动态分区（true:打开动态分区 false:关闭,默认true）
hive > set hive.exec.dynamic.partition=true;

#设置动态分区模式(nostrick:分区字段可以全部为动态,strick:分区字段中必须要至少要有一个静态字段,默认strick)
hive > set hive.exec.dynamic.partition.mode=nostrick;

 

--导入数据
insert overwrite table student_p_2 partition(sex,age) select id,name,sex,age from student;

--查看数据
select * from student_p_1;

3	wangwu	boy	17
1	zhangsan	boy	19
6	lilei	boy	20
2	lisi	girl	18
4	lucy	girl	20

 

查看stuent_p_2分区

hive > show partitions student_p_2;
OK
sex=boy/age=17
sex=boy/age=19
sex=boy/age=20
sex=girl/age=18
sex=girl/age=20

#hsfs 中的目录
./hadoop dfs -lsr /user/hive/warehouse/student_p_2

/user/hive/warehouse/student_p_4/sex=boy
/user/hive/warehouse/student_p_4/sex=boy/age=17
/user/hive/warehouse/student_p_4/sex=boy/age=17/000000_0
/user/hive/warehouse/student_p_4/sex=boy/age=19
/user/hive/warehouse/student_p_4/sex=boy/age=19/000000_0
/user/hive/warehouse/student_p_4/sex=boy/age=20
/user/hive/warehouse/student_p_4/sex=boy/age=20/000000_0
/user/hive/warehouse/student_p_4/sex=girl
/user/hive/warehouse/student_p_4/sex=girl/age=18
/user/hive/warehouse/student_p_4/sex=girl/age=18/000000_0
/user/hive/warehouse/student_p_4/sex=girl/age=20
/user/hive/warehouse/student_p_4/sex=girl/age=20/000000_0

动态分区在导入数据的时候不用指定分区字段，hive会自动映射到分区

优缺点

动态分区会自动映射，性能可以会差点。

静态分区需要手动指定，性能会好点。

动态分区其他参数

• hive.exec.max.dynamic.partitions.pernode=100 （默认100）表示每个maper或reducer可以允许创建的最大动态分区个数，超出则会报错。
• hive.exec.max.dynamic.partitions =1000(默认值) 表示一个动态分区语句可以创建的最大动态分区个数，超出报错
• hive.exec.max.created.files =10000(默认) 全局可以创建的最大文件个数，超出报错。