【Hive-基础】分区表，二级分区，动态分区，分桶表

一、分区表与分桶表的区别？

1）
分区表：使用的是表外字段（表中不存在的字段），需要指定字段类型；
分桶表：使用的是表内字段（表中存在的字段），已经知道字段类型，不需要指定。
2）
分区表：通过关键字partitioned by(partition_name string)声明，
分桶表：通过关键字clustered by(column_name) into 3 buckets声明。
3)
分区表：划分的是hdfs的存储路径（文件夹），是个伪列，只对应着文件存储路径上的一个层级
分桶表：划分的是hdfs的数据文件

二、分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，分区目的主要是避免全表扫描，从而提升查询和计算效率。
按分区类型划分，可以分为静态分区、动态分区。

2.1 分区表基本操作

1）引入分区表（需要根据日期对日志进行管理, 通过部门信息模拟）

dept_20200701.log
dept_20200702.log
dept_20200703.log

2）创建分区表语法

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

• 注意：分区字段不能是表中已经存在的字段，可以将分区字段看作表的伪列。

3）加载数据到分区表中

1. 数据准备

dept_20200701.log
10	ACCOUNTING	1700
20	RESEARCH	1800

dept_20200702.log
30	SALES	1900
40	OPERATIONS	1700

dept_20200703.log
50	TEST	2000
60	DEV	1900

1. 加载数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200701.log' into table dept_partition partition(day='20200701');

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200702.log' into table dept_partition partition(day='20200702');

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200703.log' into table dept_partition partition(day='20200703');

• 注意：静态分区加载数据时，必须要指定分区

4）查询分区表中数据

单分区查询

hive (default)> select * from dept_partition where day='20200701';

多分区联合查询

select * from dept_partition where day='20200701'
union
select * from dept_partition where day='20200702'
union
select * from dept_partition where day='20200703';

elect * from dept_partition where day='20200701' or day='20200702' or day='20200703' ;

5）查看分区表有多少分区

hive> show partitions dept_partition;

6）增加分区

创建单个分区

hive (default)> alter table dept_partition add partition(day='20200704');

同时创建多个分区（分区之间不能有逗号）

alter table dept_partition add partition(day='20200705') partition(day='20200706');

7）删除分区（分外部表（删除元数据，就是mysql的数据，hdfs的文件夹和数据不会删除）和内部表（删除元数据，同时也删除hdfs的文件夹和数据））

删除单个分区

hive (default)> alter table dept_partition drop partition (day='20200706');

同时删除多个分区（分区之间必须有逗号）

alter table dept_partition drop partition (day='20200704'), partition(day='20200705');

8）查看分区表结构

hive> desc formatted dept_partition;

# Partition Information          
# col_name              data_type               comment             
month                   string  

2.2 分区表二级分区

思考: 如何一天的日志数据量也很大，如何再将数据拆分?

1）创建二级分区表

• 注意：如果删除所有的二级分区，那它对应的一级分区会自动删除（因为删除所有二级分区，那么一级分区里面是空的）

create table dept_partition2(
      deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

2）正常的加载数据

（1）加载数据到二级分区表中

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200701.log' into table
dept_partition2 partition(day='20200701', hour='12');

（2）查询分区数据

hive (default)> select * from dept_partition2 where day='20200701' and hour='12';

3）把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

（1）方式一：上传数据后修复

上传数据
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=13;
hive (default)> dfs -put /opt/module/datas/dept_20200701.log  /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=13;

查询数据（查询不到刚上传的数据）
hive (default)> select * from dept_partition2 where day='20200701' and hour='13';

执行修复命令（扫描表下所有的分区文件夹）
hive> msck repair table dept_partition2;

再次查询数据
hive (default)> select * from dept_partition2 where day='20200701' and hour='13';

（2）方式二：上传数据后添加分区

上传数据

上传数据
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=14;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200701.log  /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=14;

执行添加分区
hive (default)> alter table dept_partition2 add partition(day='20200701',hour='14');
	
查询数据
hive (default)> select * from dept_partition2 where day='20200701' and hour='14';

（3）方式三：创建文件夹后load数据到分区

创建目录
hive (default)> dfs -mkdir -p
 /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=15;

上传数据
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200701.log' into table
 dept_partition2 partition(day='20200701',hour='15');

查询数据
hive (default)> select * from dept_partition2 where day='20200701' and hour='15';

2.3 动态分区调整

关系型数据库中，对分区表Insert数据时候，数据库会自动根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

1）开启动态分区参数设置

（1）开启动态分区功能（默认是false）
hive.exec.dynamic.partition=true

（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict

（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
hive.exec.max.dynamic.partitions=1000

（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
hive.exec.max.dynamic.partitions.pernode=100

（5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000

（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false

2）案例实操

需求：将dept表中的数据按照地区（loc字段），插入到目标表dept_partition的相应分区中。

（1）创建目标分区表
create table dept_partition_dy(id int, name string) 
partitioned by (loc int) 
row format delimited fields terminated by '\t';

（2）设置动态分区
set hive.exec.dynamic.partition.mode = nonstrict;

insert into table dept_partition_dy 
partition(loc) 
select deptno, dname, loc from dept;

（3）查看目标分区表的分区情况
hive (default)> show partitions dept_partition_dy;

三、分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。（分桶的字段一定要是表里存在的字段）

• 分桶是将数据集分解成更容易管理的若干部分的另一个技术。

• 分区针对的是数据的存储路径（文件夹）；分桶针对的是数据文件

1）先创建分桶表，通过直接导入数据文件的方式

--（1）数据准备
1001	qq1
1002	qq2
1003	qq3
1004	qq4
1005	qq5
1006	qq6
1007	qq7
1008	qq8
1009	qq9

（2）创建分桶表
create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

（3）查看表结构
desc formatted stu_buck;
Num Buckets:   4  

（4）导入数据到分桶表中（hive新版本load数据可以直接跑mr(也会有点问题)，老版的hive需要将数据传到一张表里，再通过查询的方式导入到分桶表里面。）
hive (default)> load data local inpath '/opt/module/hive/datas/student.txt' into table stu_buck;
 
（6）查询分桶的数据
hive(default)> select * from stu_buck;

• 分桶规则：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

2）分桶表操作需要注意的事项:

（1）reduce的个数设置为-1，让Job自行决定需要用多少个reduce 或者 将reduce的个数设置为大于等于分桶表的桶数

（2）从hdfs中load数据到分桶表中，避免本地文件找不到问题

（3）不要使用本地模式