【大数据之Hive】十八、Hive之分区表和分桶表

1 分区表

Hive的分区指把大表按需求分散存储到多个目录，每个目录称为该表的一个分区，使用where查询所需的分区。

1.1 分区表基本语法

1.1.1 创建分区表

--创建分区表
create table dept_partition
(
    deptno int,    --部门编号
    dname  string,    --部门名称
    loc    string    --部门位置
)
partitioned by (day string)
row format delimited fieldsterminated by '\t';

1.1.2 分区表读写数据

写数据 ：
（1）load
数据准备，在/opt/module/hive/datas/下创建文件dept_20230524.log，并添加以下内容：

10 行政部  1700
20 财务部  1800

--给分区表装载数据
load data local inpath '/opt/module/hive/datas/dept_20230524.log' 
into table dept_partition partition(day='20230524');

（2）insert

--将day='20230524'分区的数据插入到day='20230525'分区：
insert overwrite table dept_partition partition(day='20230525')
select deptno,dname,loc
from dept_partition
where day='20230524';

读数据：
  在查询分区表数据时，可以把分区字段看作是伪列，像平常操作字段一样操作分区字段。

--读数据
select deptno, dname, loc ,day
from dept_partition
where day = '20230524';

1.1.3 分区表基本操作

--查看所有分区信息
show partitions dept_partition;

--增加分区：
--(1)创建单个分区：
alter table dept_partition add partition(day='20230526');
--(2)同时创建多个分区(分区之间不能有括号)
alter table dept_partition add partition(day='20230527') partition(day='20230528');

--删除分区
--(1)删除单个分区
alter table dept_partition drop partition (day='20230526');
--(2)同时删除多个分区(分区之间必须有逗号)
alter table dept_partition drop partition (day='20230527'),partition(day='20230528');

修复分区
  Hive将分区表的所有分区信息都保存在元数据中，只有元数据和HDFS上的分区路径一致时，分区表才能正常读写。

导致Hive元数据和HDFS分区路径不一致的情况：
（1）用户在HDFS上手动删除分区路径，此时Hive是不能感知删除的。
（2）当分区表为外部表时，用户执行 drop partition 命令后只删除分区元数据，HDFS的分区路径不会被删除。

解决方法：
（1）add partition
  如果手动创建HDFS的分区路径Hive无法识别，可以通过 add partition 命令重新创建分区元数据信息，使得元数据和分区路径保持一致。

alter table partition_table_name 
add partition (分区字段='分区标识') localtion 'HDFS路径';

（2）drop partition
  如果手动删除分区路径Hive无法识别，可以通过 drop partition 命令删除分区元数据信息，使得元数据和分区路径保持一致。

alter table partition_table_name 
drop partition (分区字段='分区标识');

（3）msck
  分区元数据和HDFS分区路径不一致，使用msck进行修复，且不需要手动增加元数据的信息：

msck repair table partition_table_name [add/drop/sync partitions];

--会增加HDFS路径存在但元数据缺失的分区信息：
msck repair table partition_table_name add partitions;
--等价于
msck repair table partition_table_name;

--会删除HDFS路径不存在但元数据存在的分区信息：
msck repair table partition_table_name drop partitions;

--同步HDFS路径和元数据分区信息，相当于同时执行上述add和drop两个操作
msck repair table partition_table_name sync partitions;

1.2 二级分区表

  假如一天的日志数据量很大，可以在按天分区的基础上再对每天的数据按照小时进行分区，即在一级分区的基础上继续进行二级分区。
例如：

--二级分区建表：
create table dept_partition2
(
    deptno int,    --部门编号
    dname  string,    --部门名称
    loc    string    --部门位置
)
partitioned by (day string,hour string)
row format delimited fieldsterminated by '\t';

--数据装载
load data local inpath '/opt/module/hive/datas/dept_20230524.log'
into table dept_partition2
partition(day='20230524',hour='12');

--查询分区数据
select
    *
from dept_partition2
where day='20230524' and hour='12';

1.3 动态分区

  在向分区表插入(insert)数据时，写入的分区由每行数据的最后一个字段的值来动态决定。

使用动态分区的好处：
  只用一个insert语句可以将数据写入到多个分区中。

1.3.1 动态分区相关参数

  以下任何一个参数都可能导致动态分区执行失败。

--(1)动态分区功能总开关（默认为true，表示开启）
set hive.exec.dynamic.partition=true;

--(2)严格模式和非严格模式
--默认为严格模式strict，要求必须指定至少一个分区为静态分区，也就是必须要有一个分区字段指定值。
--非严格模式nonstrict允许所有的分区字段都使用动态分区。
set hive.exec.dynamic.partition.mode=nonstrict;

--(3)设置一条insert语句可同时创建的最大的分区数，默认为1000.
set hive.exec.max.dynamic.partition=1000;

--(4)单个mapper或者reducer可以同时创建的最大分区个数，默认为100.
set hive.exec.max.dynamic.partition.pernode=100;

--(5)一条insert语句可以创建的最大的文件个数，默认为100000.
set hive.exec.max.create.file=100000;

--(6)当查询结果为空且进行动态分区时，是否抛出异常，默认为false。
set hive.error.on.empty.partition=false;

1.3.2 动态分区案例

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

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

--设置动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table dept_partition_dynamic
partition(loc)
select
    deptno,
    dname,
    loc
from dept_partition;

--查看目标分区表的分区情况
show partition dept_partition_dynamic;

  分区能够隔离数据和优化查询，但是对于某些数据集进行分区可能不太合理，需要更细粒度的数据范围划分。

2 分桶表

  对于一张表或者分区，进一步分桶，即解决一些数据集分区不合理的问题，更细粒度划分数据范围。
  分区是针对数据的存储路径，分桶是针对数据文件；分桶分区可以结合使用，对每一个分区的数据进行分桶。

分桶基本原理：
  每行数据计算一个指定字段的hash值，然后模以一个指定的分桶数，把取模运算结果相同的行写到同一个文件中，则该文件为一个分桶bucket。

2.1 分桶表基本语法

数据准备：在/opt/module/hive/datas/下创建student.txt文件，添加以下内容：

1001   student1
1002   student2
1003   student3
1004   student4
1005   student5
1006   student6
1007   student7
1008   student8
1009   student9
1010   student10
1011   student11
1012   student12
1013   student13
1014   student14
1015   student15
1016   student16

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

--导入数据到分桶表中
load data local inpath '/opt/module/hive/datas/student.txt'
into table stu_buck;

  在HDFS中可以看到有4个分桶。
  Hive3.x对load进行增强，如果load的目标是分桶表，会按照分桶字段和分桶个数把文件里的内容hash到多个文件中。
  Hive2.x中load只进行移动文件，不计算；在导入数据到分桶中需要先创建一个普通的没有分桶的表，把文件的数据load到普通的表中，然后再通过insert和select往分桶里写数据。

2.2 分桶排序表

--创建分桶排序表
create table stu_buck_sort(
    id int,
    name string
)
clustered by(id) sorted by(id)
into 4 buckets
row format delimited fields terminated by '\t';

--导入数据到分桶表中：
load data local inpath '/opt/module/hive/datas/student.txt'
into table stu_buck_sort;