HiveSQL中的分区与分桶

 1.分区

 1.1为什么要分区?

 举例:

 如果我们有一个一年级的学生表,这个年级有10个班,那需要查看一班的学生信息,又或者是二班等等,我们的SQL语句为

 select * from student where class = '一班';

 这种方式可以查询出一班的学生,但我们的查询语句底层会转mr任务,这样会进行一个全表的查询,查询效率比较低.

 那有什么办法可以避免全表查询呢?

 如果我们能把学生表信息按照每个班级分开,分成多个文件夹,那在查询的时候就只需要检索每一个文件夹,这样就能大大提高我们查询效率了

 例如我们第一个文件夹t_class = 1 班,这个文件夹里的数据都是一班的数据(注:这里的t_class是我们的分区字段,与上文的class不一样,因为class是

 表中字段,而我们的分区字段必须是表中没有的字段,不理解的朋友可以去看看我的上一篇博客).

 所有我们就引入了分区

 分区的作用就是避免我们写HIVESQL时进行全表扫描,减少扫描次数,提高查询效率.

1.2分区表-静态分区

1.2.1创建分区表格式

create table 表名(
    列名1 类型 comment '字段说明',
    列名2 类型 comment '字段说明',
    ......,
    ......
 )comment '表说明'
 partitioned by(分区字段 类型 comment '字段说明')  --细节:分区字段必须是表中没有的字段.
 ......;

 需要注意的是我们表的字段个数为:表中字段数+分区字段

1.2.2分区表中添加数据

 格式:

​​​​​​​load data local inpath '文件位置(指的是Linux中文件位置)' into table 分区表名 partition(分区字段='xxx');

 1.2.3查询语句

 

 分区前:select * from student from class = '一班'   --对全表进行扫描
 分区后:select * from student from class = '一班'   --依旧是对全表进行扫描
       select * from student from t_class = '1班'    --精准扫描分区,避免了全表扫描

 1.3分区表-动态分区

 1.3.1建表

格式同上

 1.3.2动态分区由来

我们可以用静态分区就可以直接实现分区了,为什么还有动态分区呢?

 试想一下,如果我们手动指定分区字段和值,分区比较多的时候,我们就会写很多,不仅效率比较慢,而且可能其中会写错的可能,所有我们就有了动态分区.

 1.3.3动态分区:

 即手动指定分区格式即可,该值一样的数据,就会被自动分到一个区

 1.3.4添加数据格式:

 insert into table 分区表名 partition(分区字段名)
 select *, 新建列名 from 数据总表名;

解释: 因为我们动态分区不支持load语法,所有这里用insert into ; 为什么这里是新建列名,在说静态分区的时候我们说过,分区表的字段个数为:表中字段数+分区字段

 而我们的数据总表没有分区字段,所有我们这里需要自己添加一个列名,作为存放分区的数据,表明是用什么来分区的(例如1班,2班......)

 1.3.5严格模式

注意:我们在进行动态分区前需要手动关闭严格模式

 set hive.exec.dynamic.partition.mode=nonstrict;     -- nonstrict 非严格模式, strict: 严格模式(默认)

 1.4分区相关操作

 1.4.1添加分区

 alter table 分区表名 add partition(分区字段 = '具体按照什么划分');

在我们对大量数据处理时,我们可能经常会对时间进行分区 分区字段就可以写year(年),month(月)......等等,这里的具体按照什么划分就是指的是2020

 比如 year = 2020等等

 1.4.2添加多个分区

 alter table 分区表名 add partition(分区字段 = '具体按照什么划分') partition(分区字段 = '具体按照什么划分') partition(分区字段 = '具体按照什么划分')......;

 1.4.3修改分区

 alter table products partition(分区字段 = '具体按照什么划分') rename to partition(分区字段 = '具体按照什么划分');

 1.4.4删除分区

 alter table products drop partition(分区字段 = '具体按照什么划分');

2.分桶

 2.1为什么要分桶?

 简单来说就是对于我们在处理大文件时,可能会让系统崩溃而无法实现我们的需求,我们这时候就可以采用分桶.

 如果两个表join的时候,两个表都是分桶表的话,我们就不需要再去扫描整个表,只需要按照对应的桶进行匹配,从而提升效率.

 2.2分桶的原理

 分桶就是将我们的数据按照分桶字段拆分成N个小文件(注意:分区是分成文件夹).

 2.3分桶的规则

 哈希取模分桶法:根据分桶字段计算它的哈希值, 然后和桶的个数取余, 余数为几, 就进哪个桶.

 2.4分桶表创建格式

 create table 表名(
    列名1 类型 comment '字段说明',
    列名2 类型 comment '字段说明',
    ......,
    ......
 )comment '表说明'
 clustered by (分桶字段) sorted by (排序字段) into N buckets;   --细节:分桶字段必须是表中已经有的字段.

 注意:这里的 clustered by 是用来分桶的,分桶字段后面没有写类型,因为分桶字段必须是表中已经有的字段,类型我们已经定义过了

 sorted by 是用来排序的,这个可以省略,相当于我们可以直接写成 clustered by (分桶字段) into N buckets.

 2.5添加数据格式

 insert into 分桶表名 select * from 原数据表名;

3.建表图解

中括号代表可以省略

 4.总结

分区与分桶不同点:1.分区是分文件夹,而分桶是分文件 2.分区是避免全表扫描,分桶是方便数据采集,减少join次数

 相同点:它们的最终目的都是为了提升效率