目录
分区表和分桶表涉及到了HQL优化：对底层数据的存储优化是分区表、分桶表、文件格式和压缩，另一种优化方式就是执行计划调参。分区表和分桶表没有必然的联系，二者是不同的概念。
一、分区表
分区表十分重要。Hive中的分区(与MapReduce中的分区不是一个概念)就是把一张大表的数据按照业务需要分散地存储到多个目录，每个目录就称为该表的一个分区，在查询时通过where子句中的表达式选择查询所需要的分区，这样的查询效率会提高很多。 分区如果设计合理，将来查询的时候就可以避免全表扫描，而是定位到一个具体的目录。
(一)分区表基本语法
1.创建分区表
这里我们没有指定分区表的存储路径，就会存储在默认路径/opt/soft/hive312/warehouse目录下。
分区字段不可以选择表中的字段。
create table dept_partition
(
deptno int,--部门编号
dname string,-- 部门名称
loc string-- 部门位置
) partitioned by (day string) -- 分区字段，当做表中的字段
row format delimited fields terminated by '\t';2.往分区表中写入数据的两种方法
源数据：
[root@lxm147 atguigu]# vim /opt/atguigu/dept_20220401.log
10 行政部 1700
20 财务部 1800
(1)load装载本地数据
load data local inpath '/opt/atguigu/dept_20220401.log'
into table dept_partition
partition (day = '2023-02-20');查询结果
(2)insert...select...(常用)
需求：将2023-02-20分区中的数据写入到2022-04-02分区中。
这里用到insert和select，所以会用到mapReduce，速度会比较慢。需要指定mr运行在本地，而不是在yarn上。
-- 设置mr运行在本地
set mapreduce.framework.name=local;
insert overwrite table dept_partition partition (day = '2022-04-02')
select deptno,
dname,
loc
from dept_partition
where day = '2023-02-20';查询结果
3.读取分区表数据
查询分区表数据时，可以将分区字段看作表的伪列，可像使用其他字段一样使用分区字段。
select *
from dept_partition where day='2022-04-02';
select
deptno,
dname,
loc
from dept_partition
where day = '2023-02-20';4. Hive分区表的存储路径规划：分区字段=分区值
分区中的字段是分区表的三个字段对应的内容
5.分区表基本操作
(1)查看所有分区信息
show partitions dept_partition;
(2)新增分区
新增分区后，hdfs上会创建路径，在Hive的元数据中新增一条分区信息
查看元数据中新增的分区信息：在mysql中存放hive元数据的数据库中的partitions表内
1)创建单个分区
alter table dept_partition add partition (day = '2023-01-01');
2)创建多个分区
给一个表同时增加多个分区，注意多个partition之间没有逗号
alter table dept_partition add
partition (day='2018-12-31')
partition (day='2019-12-31');
元数据中的分区信息也会进行相应的新增
(3)删除分区
1)删除单个分区
alter table dept_partition drop partition (day='2018-12-31');
2)删除多个分区
注意这里的多个partition之间有逗号
alter table dept_partition drop
partition (day='2019-12-31'),
partition (day='2023-01-01');元数据中的分区信息也会进行相应的删除
同样，对应的hdfs分区下的数据也会进行相应的删除
注意：我们这里建立的分区表属于内部表，删除分区时，对应的元数据也会删除，如果建立的分区表属于外部表，删除分区时，对应的元数据不会被删除！！！
create external table if not exists dept_partition_wb ( deptno int,--部门编号 dname string,-- 部门名称 loc string-- 部门位置 ) partitioned by (day date)-- 分区字段，当做表中的字段 row format delimited fields terminated by '\t' location '/tables/practices'; select * from dept_partition_wb;-- 外部分区表没有数据
-- 加载数据到外部分区表 load data local inpath '/opt/atguigu/dept_20220401.log' into table dept_partition_wb partition (day = '2023-02-20'); -- 加载后有数据
-- 再新增一个分区，可以从其他表中查询数据，加载到该外部分区表中 insert overwrite table dept_partition_wb partition (day = '2022-04-02') select deptno, dname, loc from dept_partition where day = '2023-02-20';
-- 删除外部分区表的某个分区，对应表中的分区数据不存在，但是hdfs上的分区，以及分区下的数据还存在。
drop table dept_partition_wb;
6.修复分区
Hive将分区表的所有分区信息都保存在了元数据中，只有元数据与HDFS上的分区路径一致时，分区表才能正常读写数据。若用户手动创建/删除分区路径，Hive都是感知不到的，这样就会导致Hive的元数据和HDFS的分区路径不一致。
再比如，若分区表为外部表，用户执行drop partition命令后，分区元数据会被删除，而HDFS的分区路径不会被删除，同样会导致Hive的元数据和HDFS的分区路径不一致。
(1)add partition
当我们在hadoop的web端手动创建一个分区，hive并不会感知到新增的分区信息，就会导致Hive的元数据和HDFS的分区路径不一致，需要我们进行分区的修复，保证Hive的元数据和HDFS的分区路径一致。
alter table dept_partition add partition (day='2021-01-09');
修复前：
修复后：
(2)drop partition
同样地，手动删除一个分区，也需要进行分区的修复。
alter table dept_partition drop partition (day='2021-01-09');
(3)msck(metastore check)
语法:
msck repair table table_name [add/drop/sync partitions];
msck repair table table_name;
-- 等价于
msck repair table table_name add partitions;msck不需要手动修复指定的分区路径
msck repair table dept_partition add partitions ;
msck repair table dept_partition drop partitions ;
msck repair table dept_partition sync partitions ;说明：
- msck repair table table_name add partitions：该命令会增加HDFS路径存在但元数据缺失的分区信息。
- msck repair table table_name drop partitions：该命令会删除HDFS路径已经删除但元数据仍然存在的分区信息。
- msck repair table table_name sync partitions：该命令会同步HDFS路径和元数据分区信息，相当于同时执行上述的两个命令。
- msck repair table table_name：等价于msck repair table table_name add partitions命令。
(二)二级分区表
- hive一般都是批处理，生产上时间的分区一般是按照天进行分区；
- 二级分区与普通分区相比，声明两个即可，同样，三级分区声明三个分区，以此类推；
- 注意：二级分区第一个字段是作为表下面的第一级路径的，第二个字段就是第二级路径；
create table dept_partition2
(
deptno int, -- 部门编号
dname string, -- 部门名称
loc string -- 部门位置
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
-- 加载数据
load data local inpath '/opt/atguigu/dept_20220401.log' into table dept_partition2
partition (day = '20220401',hour = '12');
-- 查看分区数据
select * from dept_partition2 where day='20220401' and hour='12';
(三)动态分区
- 动态分区是指向分区表insert数据时，被写往的分区不由用户指定，而是由每行数据的最后一个字段的值来动态的决定。使用动态分区，可只用一个insert语句将数据写入多个分区。
- hive中分区不宜太多，分区信息会记录在mysql的分区表中，过多的分区会导致mysql的压力过大，也会影响namenode的内存。
- 动态分区只需要声明分区字段即可，每条数据的去往哪个分区，是由最后一个字段的值决定的。
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.partitions=1000;（4）单个Mapper或者Reducer可同时创建的最大的分区个数，默认为100。
set hive.exec.max.dynamic.partitions.pernode=100;（5）一条insert语句可以创建的最大的文件个数，默认100000。
set hive.exec.max.created.files=100000;（6）当查询结果为空时且进行动态分区时，是否抛出异常，默认false。
set hive.error.on.empty.partition=false;案例：
use atguigu; -- 创建分区表 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=true; set hive.exec.dynamic.partition.mode = nonstrict; insert into table dept_partition_dynamic partition(loc) select deptno, dname, loc from default.dept; select * from dept_partition_dynamic;dept_partition_dynamic表中的数据：
进行动态分区后：
二、分桶表
分桶表是将Hive中一张表的数据划分到不同的文件当中；
一张表既可以是分区表，也可以是分桶表，即对每一个分区进行分桶；
不可以对一个字段又分区又分桶，这样没有意义；
声明按照哪个字段进行分桶(即按照哪个字段进行hash分区)，字段要选择表中的普通字段，分为几个桶。
1.普通分桶表
drop table stu_bucket;
create table stu_bucket(
id int,
name string
)
clustered by (id) into 4 buckets
row format delimited fields terminated by '\t';
set mapreduce.framework.name=local;
-- 加载数据
set mapreduce.map.memory.mb=4096; --设置map内存
set yarn.scheduler.minimum-allocation-mb=4096; --设置yarn容器最小内存
set mapreduce.map.java.opts=-Xmx3600m; --如果有gc异常可以设置该参数
load data local inpath '/opt/atguigu/student.txt' into table stu_bucket;
select * from stu_bucket;分桶内容不排序
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/atguigu/student.txt'
into table stu_buck_sort;分桶内容排序
三、分区表和分桶表的区别
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
分区在HDFS上的表现形式是一个目录，分桶则是一个单独的文件。分桶则是指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。
1422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
