六七, Hive 数据查询实操, 以及Hive的分区和分桶

六, Hive 查询操作

前置操作(准备数据+建表+导入数据)

1. 准备数据
   emp.txt 和 dept.txt

2. 建表emp和dept

• 建立emp表, 对照字段
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JanzVMIN-1628242050249)(2021-08-04-20-38-29.png )]

create table emp(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double.
    deptno int
)
row format delimited fields terminated by '\t'

• 建立dept表, 对照字段
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M28MfuEZ-1628242050253)(2021-08-04-20-38-55.png)]

create table dept(
    detpno int,
    dname string,
    loc int
)
    row format delimited fields terminated by '\t'

3. 导入本地文件中的数据到表中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tUszN1iz-1628242050255)(2021-08-04-21-01-49.png)]

6.1 基本查询

比较运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v8PMJUWc-1628242050257)(2021-08-05-08-13-05.png)]

• 此小节内容可回顾Mysql专栏文章;

6.2 四个 By

6.2.1 Order By–(全局排序)

1. OrderBy: 全局排序, 只有一个Reducer.
2. Order By, 默认是升序(ASC), 也可设置为升序(DESC).Order By使用在Select语句的末尾.

6.2.2 Sort By–(每个Reducer内部排序)

对于大规模的数据集 Order by的效率非常低, 在很多情况下, 并不需要全局排序, 此时可以使用 Sort By;
Sort By为每个reducer产生一个排序文件, 每个Reducer 内部进行排序, 对全局结果集来说不是排序.

[案例实操]

我们手动设置 Reducer 的个数为3个, 下面查看执行sort by后的生成结果.

• 步骤1. 设置并查看ruducer的个数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bz6WcGNA-1628242050259)(2021-08-05-14-17-51.png)]

• 步骤2. 使用Sort By根据部门编号降序查看员工信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZmbENHvP-1628242050260)(2021-08-05-14-36-57.png)]

• 步骤3. 将输出结果导入到本地文件中查看

insert overwrite local directory
'/opt/module/data/hive-data/sortby-result' 
select ename,deptno from emp sort by deptno desc;

6.2.3 Distribute By–(自定义分区排序, 控制某个行到某个reduce)

1. 在有些情况下, 我们需要控制某个特定行应该到哪个Reducer, 通常是为了进行后续的聚集操作. distribute by字句可以做这件.
2. distribute by 类似于MR中partition by(自定义分区) 进行分区, 结合sort by使用
3. 对于 distribute by 进行测试, 一定要分配多reduce进行处理, 否则无法看到distribute by的效果.

[案例实操]

0. 设置reducer 的个数

1. 按照部门编号分区, 再按照员工编号降序排列.

select empno, ename, deptno
from emp
distribute by deptno
sort by empno;

2. 输出结果到文件:

insert overwrite local directory
'/opt/module/data/hive-data/distribute-result' 
select empno, ename, deptno
from emp
distribute by deptno 
sort by empno desc;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4KzJ5SaR-1628242050262)(2021-08-05-16-07-14.png)]

注意:

1. distribute by的分区规则是根据分区字段的hash码%reduce的个数得到的余数, 把余数相同的分到同一个分区.
2. hive要求 distribute by 语句写在 sort by语句之前.

6.2.4 Cluster By–(分区+区内排序)

当 distribute by 和 sort by的目标字段相同是, 可以使用 cluster by 方式.

cluster by除了具有 distribute(按字段分区排列)的功能外, 还兼具sort by(分区内按字段进行排序)的功能, 但是排序只能是升序排序, 不能制定规则为ASC还是DESC.

1. 举个栗子:(下面两种写法是等价的!)

# 1. distribute by 分区, sort by 区内排序
select * from emp distribute by deptno sort by deptno;
# 2. 直接使用 Cluster by (分区+区内排序一步到位)
select * from emp cluster by deptno;

注意: 按照部门编号deptno分区, 不一定就是固定死的数值, 可以是20号和30号部门分到一个分区里面.

七, 分区表和分桶表

• Hive中的分区就是在HDFS上分目录存数据, 把一个大的数据集根据业务需要分割成小的数据集;
• 分区表对应于HDFS文件系统上的独立的文件夹, 该文件夹下是该分区所有的数据文件
• 在查询时通过Where 子句中的表达式选择查询所需要的制定分区, 这样的查询效率会提高很多.
• 分区表的目的: 缩小数据查询范围, 提高查询效率和性能.

7.1 分区表基本操作

1. 创建分区表语法

create table deptpartition(
    deptno int,
    dname string,
    loc int
)
partitioned by (day string)
row format delimited fields terminated by '\t';

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

2. 加载原始数据到分区表中

• 准备数据, 放到’/opt/module/data/hive-data/input/partition’中

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AukM6imW-1628242050263)(2021-08-05-17-24-16.png)]

• 加载数据(分区表加载数据时, 必须指定分区)

##第一个分区
load data local inpath '/opt/module/data/hive-data/input/partition/dept_20200401.log'
into table deptpartition partition(day='20200401');

##第二个分区
load data local inpath '/opt/module/data/hive-data/input/partition/dept_20200401.log'
into table deptpartition partition(day='20200402');
##第三个分区
load data local inpath '/opt/module/data/hive-data/input/partition/dept_20200401.log'
into table deptpartition partition(day='20200403');

• 完成了建表(添加分区标识), 导入数据之后, 在HDFS上就可以看到以目录划分的表的数据集了
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-51CV48bq-1628242050264)(2021-08-05-17-25-44.png)]

3.查询分区表中的数据

• 单分区查询 + 多分区联合查询(union)
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ayZr9iJD-1628242050265)(2021-08-05-17-38-42.png)]

4. 修改分区

4.1 增加分区(alter table xx add partition (分区列=‘值’) (分区列=‘值’) …)

1. 创建单个分区

alter table deptpartition add partition(day='20210805');

2. 创建多个分区

注意: 创建多个分区之间是空格!!

alter table deptpartition add partition(day='20210806') partition(day='20210807');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kaLj9JvY-1628242050266)(2021-08-05-18-07-29.png)]

4.2 删除分区(alter table xx drop partition(分区列=‘值’),分区列=‘值’)…)

1. 删除单个分区

alter table deptpartition drop partition(day='20210805');

2. 同时删除多个分区

注意: 删除多个分区之间是逗号

alter table deptpartition drop partition(day='20210805'), partition(day='20210806');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qoeXxRZF-1628242050267)(2021-08-05-18-09-32.png)]

5. 查看分区

1. 查看分区表有多少个分区

show partitions deptpartition;

2. 查看分区表的结构

desc deptpartition;
#或者

desc formatted deptpartition;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XDMSaBHQ-1628242050268)(2021-08-05-18-13-11.png)]

7.2 二级分区

如果一天的日志数据量也很大, 如何再将数据拆分? --二级分区.

1. 创建二级分区表

create table deptpartition2(
    deptno int,
    dname string,
    loc int
)
partitioned by(day string, hour string)
row format delimited fields terminated by '\t';

2. 加载数据到表中

load data local inpath 
'/opt/module/data/hive-data/input/partition/dept_20200401.log' 
into table deptpartition2 partition(day='20200401', hour='12');

• 其他两表如法炮制.
• 查询分区数据如下:
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JUHXnV0V-1628242050269)(2021-08-06-09-46-32.png)]

3. 把数据直接上传到分区目录上, 让分区表和数据产生关联的三种方式:

方法一: 上传数据后进行修复(msck repair)

1. 在HDFS上创建多级文件夹(分区), 并往里放入数据(非hive客户端!!!)

hdfs dfs -mkdir -p /user/hive/warehouse/deptpartition2/day=20200806/hour=12;

hdfs dfs -put /opt/module/data/hive-data/input/deptpartition/ /user/hive/warehouse/deptpartition2/day=20200806/hour=12;

2. 执行修复分区

msck repair table deptpartition2;

方法二: 上传数据后添加分区

1. 在HDFS中创建代表分区的文件夹, 并上传好数据(非hive客户端!!!)

hdfs dfs -mkdir -p /user/hive/warehouse/deptpartition2/day=20210807/hour=12;


hdfs dfs -put /opt/module/data/hive-data/input/partition/dept_20200402.log /user/hive/warehouse/deptpartition2/day=20210807/hour=12

2. 添加对应的新分区

alter table deptpartition2 add partition(day=20210807, hour=12);

方法三: 创建文件夹后load数据到分组

1. 在HDFS中创建代表分区的文件夹, 并上传好数据(非hive客户端!!!)

hdfs dfs -mkdir -p /user/hive/warehouse/deptpartition2/day=20210808/hour=12;


hdfs dfs -put /opt/module/data/hive-data/input/partition/dept_20200402.log /user/hive/warehouse/deptpartition2/day=20210808/hour=12

2. load数据到创建好的分组

load data local inpath '/opt/module/data/hive-data/input/partition/dept_20200403.log' into table deptpartition2 partition(day=20210808, hour=12);

7.3 动态分区

往hive分区表中插入数据时，如果需要创建的分区很多，比如以表中某个字段进行分区存储，则需要复制粘贴修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，其可以基于查询参数的位置和个数去推断分区的名称，从而自动建立分区。
在使用Hive 的动态分区之前, 需要进行相应的配置.

[开启动态分区参数设置]

1. 开启动态分区功能(Hive中默认是开启动态分区的, 为True)
   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-09DIIYaF-1628242050270)(2021-08-06-16-41-49.png)]

hive.exec.dynamic.partition=true

2. 设置为非严格模式

动态分区的模式, 默认为strict, 表示必须制定一个静态分区; nonstrict模式表示允许所有的分区字段都可以使用动态分区.

hive.exec.dynamic.partition.mode=nonstrict

3. 在所有执行MR的结点上, 最大一共可以创建多少个分区, 默认为1000.

hive.exec.max.dynamic.partitions=1000

4. 在每个执行MR的节点上, 最大可以创建多少个动态分区. 该参数需要根据实际的数据来设定.

hive.exec.max.dynamic.partitions.pernode=100

比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

5. 整个MR Job中, 最大可以创建多少个HDFS文件, 默认为100000.

hive.exec.max.created.files=100000

6. 当有空分区生成时, 是否抛出异常, 一般不需要设置, 默认为false.

hive.error.on.emptypartition=false

[案例实操]

需求: 将dept表中的数据按照地区(loc字段), 插入到目标表 dept_partition的相应分区中.

1. 创建目标分区表

create table deptpartition3(
    id int,
    name string
)
partitioned by (loc int)
row format delimited fields terminated by '\t' 

2. 设置动态分区

# 设置动态分区模式为非严格模式(所有分区字段都可以用作是动态分区)
set hive.exec.dynamic.partition.mode=nonstrict;

# 设置动态分区
insert into table deptpartition3 partition(loc)
select deptno,dname,loc from dept;

注意: Hive 3.0 之后不用特意开启非严格模式了, 并且在设置动态分区时, 可以忽略指定分区字段(即 上面 partition(loc) 可以省去)

3. 查看目标分区表的分区情况

show partitions deptpartition3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nXqyfU7U-1628242050271)(2021-08-06-15-56-11.png)]

思考：目标分区表是如何匹配到分区字段的？

根据查询待分区表的字段个数和位置推断分区名的，而不是字段名称。比如我们指定一个分区字段, 那么就拿原有表字段中的最后一个字段作为分区的字段, 如果指定两个的话, 就是原表的最后两个字段作为分区字段.

7.4 分桶表

1. 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围 划分。
2. 分桶是将数据集分解成更容易管理的若*分的另一个技术。分区针对的是数据的存储路径；分桶针对的是数据文件。

[案例实操]
0. 准备数据(stu.txt)

1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

1. 创建分桶表(分为了-1, 根据生成的文件分桶)

create table stu_buc(
    id int,
    name string
)
clustered by(id) into -1 buckets
row format delimited fields terminated by '\t';

2. 查看表结构

desc formatted stu_buc;

3. 导入数据

load data local inpath '/opt/module/data/hive-data/input/bucket/stu.txt'
into table stu_buc;

4. 验证

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MVpSrQgC-1628242050272)(2021-08-06-17-23-39.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h623tBqR-1628242050273)(2021-08-06-17-25-57.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sdVCPzWM-1628242050274)(2021-08-06-17-26-11.png)]

7.5 抽样查询

id int,
name string
)
clustered by(id) into -1 buckets
row format delimited fields terminated by ‘\t’;

2. 查看表结构

```sql
desc formatted stu_buc;

3. 导入数据

load data local inpath '/opt/module/data/hive-data/input/bucket/stu.txt'
into table stu_buc;

4. 验证

[外链图片转存中…(img-MVpSrQgC-1628242050272)]

[外链图片转存中…(img-h623tBqR-1628242050273)]
[外链图片转存中…(img-sdVCPzWM-1628242050274)]

7.5 抽样查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xrWZBBEP-1628242050275)(2021-08-06-17-21-50.png)]