本文目录如下：

  • 第7章 分区表和分桶表
  • 7.1 分区表
  • 7.1.1 分区表基本操作
  • 7.1.1.1 引入分区表
  • 7.1.1.2 创建分区表语法
  • 7.1.1.3 加载数据到分区表中
  • 7.1.2 二级分区
  • 7.1.2.1 创建二级分区表
  • 7.1.2.2 正常的加载数据
  • 7.1.2.3 把数据直接上传到分区目录上
  • 7.1.3 动态分区调整
  • 7.1.3.1 开启动态分区参数设置
  • 7.1.3.2 案例实操
  • 7.2 分桶表
  • 7.2.1 先创建分桶表
  • 7.2.2 分桶表操作需要注意的事项:
  • 7.2.3 insert 方式将数据导入分桶表
  • 7.3 抽样查询


第7章 分区表和分桶表

7.1 分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

7.1.1 分区表基本操作

7.1.1.1 引入分区表

  • 需要根据日期对日志进行管理, 通过部门信息模拟
dept1.txt
dept2.txt
dept3.txt

7.1.1.2 创建分区表语法

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

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

7.1.1.3 加载数据到分区表中

  • (1) 数据准备
`dept1.txt`
10	ACCOUNTING	1700
20	RESEARCH	1800

`dept2.txt`
30	SALES	1900
40	OPERATIONS	1700

`dept3.txt`
50	TEST	2000
60	DEV	1900
  • (2) 加载数据
hive (default)> load data local inpath '/opt/module/hive/datas/dept1.txt'
into table dept_par partition(day='2020-8-25');

hive (default)> load data local inpath '/opt/module/hive/datas/dept2.txt'
into table dept_par partition(day='2020-8-26');

hive (default)> load data local inpath '/opt/module/hive/datas/dept3.txt'
into table dept_par partition(day='2020-8-27');

注意：分区表加载数据时，必须指定分区

hive 多分区 插入数据 hive多级分区_大数据

  • (3) 查询分区表中数据

单分区查询

hive (default)> select * from dept_par where day='2020-8-25';

多分区联合查询

hive (default)> select * from dept_par where day='2020-8-25'
union
select * from dept_par where day='2020-8-26'
union
select * from dept_par where day='2020-8-27';

hive (default)> select * from dept_par where day='2021-8-25' or day='2020-8-26' or day='2020-8-27';
  • (4) 增加分区

创建单个分区

hive (default)> alter table dept_par add partition(day='2020-8-28');
# 这样增加的分区里面没有内容, 产生的是一个空文件夹。下同。

同时创建多个分区

hive (default)> alter table dept_par add partition(day='2020-8-29') partition(day='2020-8-30');
  • (5) 删除分区

删除单个分区

hive (default)> alter table dept_par drop partition (day='2020-8-28');

同时删除多个分区

hive (default)> alter table dept_par drop partition (day='2020-8-29'),partition(day='2020-8-30');
  • (6) 查看分区表有多少分区
hive> show partitions dept_par;
  • (7) 查看分区表结构
hive> desc formatted dept_partition;

7.1.2 二级分区

思考: 如何一天的日志数据量也很大，如何再将数据拆分?

7.1.2.1 创建二级分区表

hive (default)> create table dept_par2(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
# 二级分区, partitioned by 后面的括号里有两个参数

7.1.2.2 正常的加载数据

  • (1) 加载数据到二级分区表中
hive (default)> load data local inpath '/opt/module/hive/datas/dept1.log' 
into table dept_par2 partition(day='2020-8-25', hour='12');
  • (2) 查询分区数据(***)
hive (default)> select * from dept_par2 where day='2020-8-25' and hour='12';

7.1.2.3 把数据直接上传到分区目录上

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

  • (1) 方式一：上传数据后修复

上传数据

hive (default)> hadoop fs -mkdir /user/hive/warehouse/dept_par/day=2021-08-25;
hive (default)> hadoop fs -put /opt/module/hive/datas/dept1.log /user/hive/warehouse/dept_par/day=2021-08-25;

查询数据（查询不到刚上传的数据）(因为数据库中没有这块分区的元数据信息)

hive (default)> select * from dept_par where day='2021-08-25';

执行修复命令

hive> msck repair table dept_par;

再次查询数据

hive (default)> select * from dept_par where day='2021-08-25';
  • (2) 方式二：上传数据后添加分区

上传数据

hive (default)> hadoop fs -mkdir /user/hive/warehouse/dept_par/day=2021-08-26;
hive (default)> hadoop fs -put /opt/module/hive/datas/dept1.log /user/hive/warehouse/dept_par/day=2021-08-26;

执行添加分区

hive (default)> alter table dept_par2 add partition(day='2021-08-26');

查询数据

hive (default)> select * from dept_par2 where day='2021-08-26';
  • (3)方式三：创建文件夹后 load 数据到分区

创建目录

hive (default)> hadoop fs -mkdir /user/hive/warehouse/dept_par2/day=2021-08-27;

上传数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept1.log' 
into table dept_part2 partition(day='2021-08-27',hour='15');

查询数据

hive (default)> select * from dept_par2 where day='2021-08-27';

7.1.3 动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置。

7.1.3.1 开启动态分区参数设置

  • (1) 开启动态分区功能（默认 true，开启）
hive (default)> set hive.exec.dynamic.partition=true
  • (2) 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
hive (default)> set hive.exec.dynamic.partition.mode=nonstrict
  • (3) 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000 
hive (default)> set hive.exec.max.dynamic.partitions=1000

一般情况下只修改上面这个属性就行了，下面的这些属性不用修改，因为数据很难达到下面描述的那种规模。

  • (4) 在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
hive.exec.max.dynamic.partitions.pernode=100
  • (5) 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000 
hive.exec.max.created.files=100000
  • (6) 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false 
hive.error.on.empty.partition=false

注: Hive3.X版本新特性: 既可以不用开非严格模式, 插入数据时也不用指定分区字段(如partitioned by (loc int))。

7.1.3.2 案例实操

需求：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应分区中。

  • (1) 创建目标分区表
hive (default)> create table dept_partition(id int, name string) 
partitioned by (loc int) 
row format delimited fields terminated by '\t';
  • (2) 设置动态分区
set hive.exec.dynamic.partition.mode = nonstrict;

hive (default)> insert into table dept_partition
partition(loc) 
select deptno, dname, loc from dept;
  • (3) 查看目标分区表的分区情况
hive (default)> show partitions dept_partition;

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

7.2 分桶表

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

7.2.1 先创建分桶表

  • (1) 数据准备
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
  • (2) 创建分桶表
hive (default)> create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';
  • (3) 查看表结构
hive (default)> desc formatted stu_buck;
Num Buckets: 4
  • (4) 导入数据到分桶表中，load 的方式
hive (default)> load data inpath '/student.txt' into table stu_buck;
  • (5) 查看创建的分桶表中是否分成 4 个桶
  • (6) 查询分桶的数据
hive(default)> select * from stu_buck;
  • (7) 分桶规则：
    根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

7.2.2 分桶表操作需要注意的事项:

  • (1) reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数
  • (2) 从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
  • (3) 不要使用本地模式

7.2.3 insert 方式将数据导入分桶表

hive(default)> insert into table stu_buck select * from student_insert;

7.3 抽样查询

  • 对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。
  • 语法: TABLESAMPLE(BUCKET x OUT OF y)
  • 查询表 stu_buck 中的数据。
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
  • 注意：x 的值必须小于等于 y 的值，否则会报如下错误:
FAILED: SemanticException [Error 10061]: Numerator should not be bigger 
than denominator in sample clause for table stu_buck