Hive的分区

1、Hive分区(十分重要！！)

分区的目的：避免全表扫描，加快查询速度！

在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天或者每小时切分成一个个小的文件，这样去操作小的文件就会容易很多了。

1.2 静态分区（SP）

静态分区（SP）static partition–partition by (字段 类型)

​ 借助于物理的文件夹分区，实现快速检索的目的。

​ 一般对于查询比较频繁的列设置为分区列。

​ 分区查询的时候直接把对应分区中所有数据放到对应的文件夹中。

创建单分区表语法：

CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';
--  分区的字段不要和表的字段相同。相同会报错error10035


1,zhangsan01,1
2,zhangsan02,1
3,zhangsan03,1
4,zhangsan04,1
5,zhangsan05,1

6,zhangsan06,2
7,zhangsan07,2
8,zhangsan08,2

9,zhangsan09,3
10,zhangsan10,3
11,zhangsan11,3
12,zhangsan12,3
13,zhangsan13,3
14,zhangsan14,3
15,zhangsan15,3

16,zhangsan16,4
17,zhangsan17,4
18,zhangsan18,4
19,zhangsan19,4
20,zhangsan20,4
21,zhangsan21,4
-- 载入数据
-- 将相应年级一次导入
load data local inpath '/usr/local/soft/hive_test/grade2.txt' into table t_student partition(grade=2);
`

> 静态多分区表语法：

```sql
CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';

--注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。
1,zhangsan01,1,1
2,zhangsan02,1,1

3,zhangsan03,1,2
4,zhangsan04,1,2

5,zhangsan05,1,3
6,zhangsan06,1,3

7,zhangsan07,2,1
8,zhangsan08,2,1

9,zhangsan09,2,2

--载入数据
load data local inpath '/usr/local/soft/hive_test/hivedata/teacher_11.txt' into table t_teacher partition(grade=1,clazz=1);

分区表查询

select * from t_student where grade = 1;

// 全表扫描，不推荐，效率低
select count(*) from students_pt1;

// 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from students_pt1 where grade = 1;

// 也可以在where条件中使用非等值判断
select count(*) from students_pt1 where grade<3 and grade>=1;

查看分区

show partitions t_teacher;

• DDL

添加分区

alter table t_student add partition (grade=6);

alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/hive_test.db/t_teacher/grade=3/clazz=1';

删除分区

alter table t_student drop partition (grade=5);

1.3 动态分区（DP）

• 动态分区（DP）dynamic partition
• 静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
• 详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定。

开启动态分区首先要在hive会话中设置如下的参数

# 表示开启动态分区
hive> set hive.exec.dynamic.partition=true;

# 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nonstrict;

===================以下是可选参数======================

# 表示支持的最大的分区数量为1000，可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

其余的参数详细配置如下

设置为true表示开启动态分区的功能（默认为false）
--hive.exec.dynamic.partition=true;

设置为nonstrict，表示允许所有分区都是动态的（默认为strict）
-- hive.exec.dynamic.partition.mode=nonstrict; 
-- hive.exec.dynamic.partition.mode=strict; 

每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错
--hive.exec.max.dynamic.partition.pernode=100; 

一个动态分区创建可以创建的最大动态分区个数（默认值1000）
--hive.exec.max.dynamic.partitions=1000;

全局可以创建的最大文件个数（默认值100000）
--hive.exec.max.created.files=100000; 

当有空分区产生时，是否抛出异常（默认false） 
-- hive.error.on.empty.partition=false;  

• 案例1： 动态插入学生年级班级信息

--创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS t_teacher_e (
sno int,
sname string,
grade int,
clazz int
) 
row format delimited fields terminated by ','
location "/hive_test/teachers";

--创建分区表
CREATE TABLE IF NOT EXISTS t_teacher_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';

数据：

1,zhangsan01,1,1
2,zhangsan02,1,1
3,zhangsan03,1,1
4,zhangsan04,1,2
5,zhangsan05,1,2
6,zhangsan06,2,3
7,zhangsan07,2,3
8,zhangsan08,2,3
9,zhangsan09,3,3
10,zhangsan10,3,3
11,zhangsan11,3,3
12,zhangsan12,3,4
13,zhangsan13,3,4
14,zhangsan14,3,4
15,zhangsan15,3,4
16,zhangsan16,4,4
17,zhangsan17,4,4
18,zhangsan18,4,5
19,zhangsan19,4,5
20,zhangsan20,4,5
21,zhangsan21,4,5

如果静态分区的话，我们插入数据必须指定分区的值。

如果想要插入多个班级的数据，我要写很多SQL并且执行24次很麻烦。

而且静态分区有可能会产生数据错误问题

-- 会报错 
insert overwrite table t_student_d partition (grade=1,clazz=1) select * from t_student_e where grade=1;

如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。

insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;

优点：不用手动指定了，自动会对数据进行分区

缺点：可能会出现数据倾斜