58-66-hive-数据类型-分区-分桶

58-hive-数据类型-分区：

分区表（将数据分开放，理解市区，指定分区更快，避免全数据扫描，提高查询效率，对hive的一种优化）

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

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

准备数据
#dept_20200401.log
10 	ACCOUNTING 	1700
20 	RESEARCH 	1800
#dept_20200402.log
30 	SALES 	1900
40 	OPERATIONS 	1700
#dept_20200403.log
50 	TEST 	2000
60 	DEV 	1900

建表时添加分区
create table dept_partition(deptno int,dname string,loc string) partitioned by (day string) row format delimited fields terminated by '\t';

加载数据（修改1,2,3）
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');

查询
select * from dept_partition where day='20200401';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

增加分区

创建单个分区
hive (default)> alter table dept_partition add partition(day='20200404');
同时创建多个分区
hive (default)> alter table dept_partition add partition(day='20200405') partition(day='20200406');
1
2
3
4

删除分区

删除单个分区
hive (default)> alter table dept_partition drop partition (day='20200406');
同时删除多个分区
hive (default)> alter table dept_partition drop partition (day='20200404'),partition(day='20200405');
1
2
3
4

二级分区（两级目录）

创建
create table dept_partition2(deptno int, dname string, loc string) partitioned by (day string, hour string) row format delimited fields terminated by '\t';
加载数据到二级分区（修改1,2,3,hour12,13,14）
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='12');
1
2
3
4

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

（1）方式一：上传数据后修复,直接mkdir不会再元数据分区表中增加相关信息

hive (default)> dfs -mkdir -p /hive_db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/datas/dept_20200401.log /hive_db/dept_partition2/day=20200401/hour=13;
修复
msck repair table dept_partition2;
1
2
3
4

（2）方式二：上传数据后添加分区

hive (default)> dfs -mkdir -p /hive_db/dept_partition2/day=20200401/hour=14;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log /hive_db/dept_partition2/day=20200401/hour=14;
执行添加分区
hive (default)> alter table dept_partition2 add partition(day='20200401',hour='14');
1
2
3
4

（3）方式三：创建文件夹后 load 数据到分区，会直接在元数据中添加分区信息

创建目录
hive (default)> dfs -mkdir -p /hive_db/dept_partition2/day=20200401/hour=15;上传数据
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour='15');
1
2
3
4

load如果不指定分区信息，会有个默认的。了解即可。

load data local inpath ‘/opt/module/hive/datas/dept.txt’ into table dept_partition;

动态分区调整

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

1）开启动态分区参数设置

（1）开启动态分区功能（默认 true，开启） 
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 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 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
1
2
3
4
5
6
7
8
9
10
11
12

2）案例

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

（1）创建目标分区表

hive (default)> create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by ‘\t’;

（2）设置动态分区（分区信息是查询中的最后一个，loc的位置）

set hive.exec.dynamic.partition.mode = nonstrict;

hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

（3）查看目标分区表的分区情况

hive (default)> show partitions dept_partition;

分桶表（了解，分区表是分目录,分桶表是将数据分开）

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

#创建
create table stu_buck(id int, name string) clustered by(id) into 4 buckets	row format delimited fields terminated by '\t';
#查看表结构
desc fromatted stu_buck;
#导入数据，注意是不是local还是hdfs路径
load data local inpath '/opt/module/hive/datas/student.txt' into table stu_buck;

分桶规则：
根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方
式决定该条记录存放在哪个桶当中

#使用insert方式将数据导入分桶表
hive(default)>insert into table stu_buck select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13

抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。语法: TABLESAMPLE(BUCKET x OUT OF y)

查询表 stu_buck 中的数据。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
1

注意：x 的值必须小于等于 y 的值，否则

ve 可以通过对表进行抽样来满足这个需求。语法: TABLESAMPLE(BUCKET x OUT OF y)

查询表 stu_buck 中的数据。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
1

注意：x 的值必须小于等于 y 的值，否则