目录
2.Sort By（每个reduce内部排序）--全局则不一定有序
4.如果distribute by 和sort by的字段相同，且是升序排序，则可以简写成 cluster by
一、分区表
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 buckets
row 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.分桶字段，在数据文件中是存在的；
三、分区表和分桶表的区别
- 分区是通过partitioned by (新字段) 进行分区设置的，分桶是通过clustered by (已有字段) 进行分桶设置的
- 分区是以文件夹的方式分开保存数据的，分桶是分成不同的文件保存数据的
- 分区是在查询分区字段的时候加快查询速度，分桶是对分桶字段进行表格的联合查询的时候进行联合查询加速的
- 分区字段是虚拟的，在表中不存在 ；分桶字段在表中存在（主要区别）
- 分区表的优点是：提高查询效率；
- 分桶表的优点是：提高join效率和用于数据取样；
- 分区可以让数据的部分查询变得更快，也就是说，在加载数据的时候可以指定加载某一部分数据，并不是全量的数据。
- 分桶表通常是在原始数据中加入一些额外的结构，这些结构可以用于高效的查询，例如，基于ID的分桶可以使得用户的查询非常的块。
- 分区：细化数据管理，直接读对应目录，缩小mapreduce程序要扫描的数据量
- 分桶：提高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 值的行会被发送到同一个reducer
SELECT * 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 overwrite
7.hive -e "hive命令"
8.hive -f .sql 文件 -- hive读取sql文件
9.mysql -uroot -p123456 -e "select * from lee.t_id"