Hive的分区表(静态分区、动态分区)、分桶表、四种排序方式和数据加载方式

目录

一、分区表

(一)静态分区

1.概念

2.示例

3.静态分区表练习

(二)动态分区

1.概念

2.插入动态分区表之前：要开启动态分区和开启非严格模式

3.动态分区练习

(三)分区表总结

二、分桶表

1.概念

2.分桶表示例

3.分桶抽样

4.分桶表练习

5.分桶表总结

三、分区表和分桶表的区别

四、Hive中的四种排序

1.Order By（全局排序）--可能会产生数据倾斜

2.Sort By（每个reduce内部排序）--全局则不一定有序

3.Distribute By（指定分区规则）

4.如果distribute by 和sort by的字段相同，且是升序排序，则可以简写成 cluster by

五、Hive的数据加载方式

---

一、分区表

 Hive分区分为：静态分区 & 动态分区

推荐文章：《hive详解（分区&分桶）》

(一)静态分区

1.概念

在加载数据的时候，已知数据是在哪一个分区里面，需要人工指定某一个分区去插入。

2.示例

构造两个数据文件，分区存放 202001以及202002的销量数据，以分区表的形式进行存储

加载数据数据到指定分区的语法：

LOAD DATA LOCAL INPATH 'path/to/datafile'
INTO TABLE your_table PARTITION (partition_column = 'partition_value');

创建分区表的分区字段不在表中： 

1.创建分区表
create table t_sales
(brand    string,price    int,quantity int
)partitioned by (month int)--指定表按照 month 列进行分区row format delimited fields terminated by ',';
--指定行格式为分隔值，字段由逗号分隔。-- 2.加载/home/1这个文件写入到分区表的202001这个分区--加载数据时要明确指定分区值
load data local inpath '/home/1' into table t_sales partition (month = 202001);-- 3.加载/home/2这个文件写入到分区表的202002这个分区
load data local inpath '/home/2' into table t_sales partition (month = 202002);select * from t_sales;

4.查询某个分区的数据

select * from t_sales where month = 202001;

 

5.添加分区

alter table t_sales add partition (month=202003) partition (month=202004);

6.查询所有分区

show partitions t_sales;

 

7.删除分区

alter table t_sales drop partition (month=202001);
alter table t_sales drop partition (month=202003),partition (month=202005);

 

3.静态分区表练习

-- 1.创建分区表 t_score (sno int,class string,score int)用 grade(int) 3个年级 (1/2/3)
create table t_score
(sno   int,class string,score int
)partitioned by (grade int)row format delimited fields terminated by ',';-- 2.构造数据文件
/*/home/11 (1年级的数据)：
101,yuwen,90
101,shuxue,88
102,yuwen,77
/home/22 (2年级的数据)：
201,yuwen,77
201,shuxue,8
202,yuwen,7*/-- 3.将两个数据文件分别加载到指定的分区表t_score
load data local inpath '/home/11' into table t_score partition (grade = 1);
load data local inpath '/home/22' into table t_score partition (grade = 2);select * from t_score where grade = 2;

(二)动态分区

1.概念

在加载数据的时候，数据涵盖很多个分区，需要计算机自动新建分区名，以及自动分配对应的分区去存储。

2.插入动态分区表之前：要开启动态分区和开启非严格模式

--1.这个设置允许在插入数据到动态分区表时
--如果插入的数据中包含一些Hive之前未知的分区值，Hive会自动创建这些新的分区而不会产生错误。
set hive.exec.dynamic.partition=true;--开启动态分区功能
set hive.exec.dynamic.partition.mode=nonstrict;--定义了动态分区的模式---可以使用 SET 命令查看当前设置的参数值，确保参数已正确设置。
SET hive.exec.dynamic.partition;
SET hive.exec.dynamic.partition.mode;

-- 2.创建分区表
create table emp_par
(empno    int,ename    string,job      string,mgr      int,hiredate string,sal      int,comm     int
)partitioned by (deptno int)row format delimited fields terminated by '\t';-- 3.加载emp表的数据到分区表 emp_par
-- 插入数据时不需要指定分区值
insert overwrite table emp_par select * from emp;select * from emp_par;

INSERT INTO TABLE emp_par PARTITION (deptno)
VALUES (1111, 'yyyy', 'teacher', 1112, '20220202', 1234, 1234, 50);show partitions emp_par;

 

select * from emp_par where deptno = 50;

注意：分区表插入数据的时候，一定要 保证SELECT的最后一个字段是分区字段。

3.动态分区练习

-- 创建分区表
create table dept_par
(dname string,loc   string
) partitioned by (deptno int)row format delimited fields terminated by '\t';truncate table dept_par;select * from dept;insert overwrite table dept_par select dname, loc, deptno from dept;-- 插入新的分区数据
insert into dept_par partition (deptno) values ('科技部', 'shanghai', 50);select * from dept_par;show partitions dept_par;

                 

分区是分到一个个文件夹中： 

(三)分区表总结

1.Hive的分区，分区字段在数据文件中不存在的，相当于是一个伪列；
2.Hive的分区，是有规则的将数据存放到一起的；

二、分桶表

1.概念

• 定义：分桶表按指定列的哈希值将数据分散到多个物理文件（桶 / Bucket）中
• 原理：桶数量 = 哈希值 % 分桶数，相同分桶键的值会被分配到同一桶
• 存储形式：每个桶对应 HDFS 中的一个文件，文件名格式为000000_0、000000_1等

将数据按照 对应行的哈希值进行取模分桶(MOD)
比如：表有14条数据，然后分成3个桶。每一条数据会有一个哈希值，通过哈希值进行MOD(哈希值,3)，得到的结果相同，放到同一个文件中存储。

实现的效果：当数据量很大的时候，每个桶的数据量都很接近。

假设：

2019 有1亿条数据
2020 有2000万条数据
分区的话  一个文件存一亿条  一个文件存2000万

分桶存储的话  
第一个桶接近 ： 6000万
第二个桶接近 ： 6000万

--在创建表时定义桶键和桶的数量。Hive根据桶键的散列值将数据分配到不同的桶。
--与分区不同，分桶通常是基于某个或某些列的散列值(哈希值)，而不是直接基于列值。

2.分桶表示例

将emp表的数据，按照empno，分桶写入分桶表 emp_ft，分成2个桶

create table emp_ft
(empno    int,ename    string,job      string,mgr      int,hiredate string,sal      int,comm     int,deptno   int
)clustered by (empno) into 2 buckets --使用 empno 列进行分桶，并且将数据分布到2个桶中row format delimited fields terminated by '\t';

将emp表的数据，分桶存储到 emp_ft

insert overwrite table emp_ft select * from emp;select * from emp_ft;

3.查看分桶后的数据

 

-- /user/hive/warehouse/s0616.db/emp_ft 会被分成两个
dfs -text /user/hive/warehouse/s0616.db/emp_ft/000000_0;
dfs -text /user/hive/warehouse/s0616.db/emp_ft/000001_0;

第一个桶10条数据，第二个桶4条数据 

 

3.分桶抽样

tablesample (bucket x out of y ON columnnanme)
x： 表示从第几个桶开始抽取，桶号是从1开始
y：抽取桶的比例，（总的桶数/2）=抽取桶的个数， 如10个桶，y=2即10/2=5个桶；
（y 必须是桶个数的因子或者倍数，如10个桶，y可以取2、5不能取3）
（x 一定要小于等于 y ！）
columnname：表示按照哪个字段进行抽样（分桶字段）--此时定义为8桶
select * from cq01 tablesample(bucket 1 out of 4 on id);
-- 这里的x=1，y=4。 
-- 由上述的语句格式介绍可以用y推导出,此次取的桶数为8/4=2桶。
--从x开始取，那么就是从第1桶开始取，然后取第1+y=5。即此次取第一和第五桶的数据。select * from cq01 tablesample(bucket 1 out of 8 on id);
-- 这里的x=1，y=8。 
-- 由上述的语句格式介绍可以用y推导出,此次取的桶数为8/8=1桶。
--从x开始取，那么就是从第1桶开始取，然后取第1桶的数据。即此次取第一桶的数据。

4.分桶表练习

-- 1.创建分桶表 emp_fft , 按照 姓名 分桶,分成4个桶;
drop table emp_fft;
create table emp_fft
(empno    int,ename    string,job      string,mgr      int,hiredate string,sal      int,comm     int,deptno   int
)clustered by (ename) into 4 bucketsrow format delimited fields terminated by '\t';-- 2.将emp表的数据写入到 emp_fft;
insert overwrite table emp_fft select * from emp;-- 3.查看HDFS上的 emp_fft 表目录下的文件信息;
dfs -ls /user/hive/warehouse/s0616.db/emp_fft/*;

 

5.分桶表总结

1.分桶是按照哈希值进行取模分桶，没有规律；
2.分桶字段，在数据文件中是存在的；

三、分区表和分桶表的区别

1. 分区是通过partitioned by (新字段) 进行分区设置的，分桶是通过clustered by (已有字段) 进行分桶设置的
2. 分区是以文件夹的方式分开保存数据的，分桶是分成不同的文件保存数据的
3. 分区是在查询分区字段的时候加快查询速度，分桶是对分桶字段进行表格的联合查询的时候进行联合查询加速的
4. 分区字段是虚拟的，在表中不存在 ；分桶字段在表中存在（主要区别）
5. 分区表的优点是：提高查询效率；
6. 分桶表的优点是：提高join效率和用于数据取样；
7. 分区可以让数据的部分查询变得更快，也就是说，在加载数据的时候可以指定加载某一部分数据，并不是全量的数据。
8. 分桶表通常是在原始数据中加入一些额外的结构，这些结构可以用于高效的查询，例如，基于ID的分桶可以使得用户的查询非常的块。
9. 分区：细化数据管理，直接读对应目录，缩小mapreduce程序要扫描的数据量
10. 分桶：提高join查询的效率（用分桶字段做连接字段）；提高采样的效率。

四、Hive中的四种排序

推荐文章：《hive 中 order by, sort by, distribute by, cluster by 的区别【详细】》 

1.Order By（全局排序）--可能会产生数据倾斜

order by 是全局有序，但是底层的mapreduce只能有一个reduce并行度 

SELECT * FROM emp ORDER BY sal;

2.Sort By（每个reduce内部排序）--全局则不一定有序

SELECT * FROM emp SORT BY sal;

3.Distribute By（指定分区规则）

DISTRIBUTE BY 子句用于指定数据应该根据哪些列的值，被分布到不同的reducer。

SELECT * FROM emp 
DISTRIBUTE BY deptno;
--根据 deptno 列的值进行分布，有相同 deptno 值的行会被发送到同一个reducerSELECT * FROM emp 
DISTRIBUTE BY deptno 
SORT BY sal;         --在每个reducer内部对数据进行排序，基于sal（薪水）列的值

4.如果distribute by 和sort by的字段相同，且是升序排序，则可以简写成 cluster by

SELECT * FROM emp 
CLUSTER BY deptno;   --按照 deptno 列的值进行物理排序和分区

操作	数据分布逻辑	排序逻辑	是否可指定排序方向
DISTRIBUTE BY id	按id分区到不同 Reducer	无排序	否
SORT BY id	不分区（或按默认规则分区）	每个 Reducer 内按id排序	可指定ASC/DESC
CLUSTER BY id	等价于DISTRIBUTE BY id	每个 Reducer 内按id升序	否（固定升序）
DISTRIBUTE BY id + SORT BY id	同CLUSTER BY id	同CLUSTER BY id	可指定ASC/DESC

五、Hive的数据加载方式

1. hadoop fs -put 'Linux的路径' 'hdfs的路径' （linux 窗口）2.dfs -put 'linux本地路径' 'hdfs路径'  （hive 窗口）3.load data LOCAL inpath 'linux路径' (overwrite)  into table 表名 (hive窗口)4.load data inpath 'hdfs路径' (overwrite) into table 表名 (hive窗口)5. create table t1 as select * from t2;CREATE TABLE t1 LIKE t2; ---表结构备份6. insert into 和 insert overwrite7.hive -e "hive命令"8.hive -f   .sql 文件  -- hive读取sql文件9.mysql -uroot -p123456 -e "select * from lee.t_id"