hive学习之分区表与分桶表

hive学习之分区表与分桶表

重点掌握分区表

分区表

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

分区表基本操作

引入分区表（需要根据日期对日志进行管理，通过部门信息模拟）

dept_20200401.log
dept_20200402.log
dept_20200403.log
……

创建分区表语法

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

数据加载

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');

查询分区中的数据

单分区查询
hive (default)> select * from dept_partition where day='20200401';
多分区联合查询
hive (default)> select * from dept_partition where day='20200401'
              union
              select * from dept_partition where day='20200402'
              union
              select * from dept_partition where day='20200403';
hive (default)> select * from dept_partition where day='20200401' or
                day='20200402' or day='20200403' ;	

查看分区表有多少分区

对应到hdfs就是目录

show partitions dept_partition;

添加分区

alter table dept_partition add partition(day='20200404');
# 添加多个分区
alter table dept_partition add partition(day='20200404') partition(day='20200405');

删除分区

# 注意：删除分区之后hdfs中对应的目录是否也删除了呢？这个取决于你的表是内部表还是外部表，如果是内部表，则hdfs对应的目录也会删除
alter table dept_partition drop partition(day='20200404');
# 删除多个的时候需要加逗号
alter table dept_partition drop partition(day='20200404'),partition(day='20200405');

二级分区

思考：如果一天的日志数据量也很大，如何再将数据拆分呢？

创建二级分区表

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

正常的加载数据

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

查询分区数据

select * from dept_partition where day='20200401' and hour='12';

把数据和分区表关联的三种方式

方式一：手动创建分区目录，手动上传数据，进行分区修复操作

# 创建分区
dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
# 上传数据
dfs -put /opt/module/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

# 注意：这个时候直接 show partitions dept_partition;是查不到新创建的

# 进行分区修复操作
msck repair table dept_partition;

方式二：手动创建分区目录，手动上传数据，手动添加分区

alter table dept_partition add partition(day='20200401',hour='14');

方式三：手动创建分区目录，load数据到分区

# 创建分区目录
dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;
# load数据到分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table 
dept_partition2 partition(day='20200401',hour='15');

动态分区

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

开启动态分区参数设置

（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

案例实操

需求：将dept表中的数据按照日期day字段，插入到目标表dept_partition_dy的相应分区中

-- 创建目标分区表
create table dept_partition_dy(
 deptno int,
 dname string,
 loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

-- 数据准备 dept_partition_dy.txt
10	ACCOUNTING	1700	20211006
20	RESEARCH	1800	20211007

-- 这里需要把数据先上传到hdfs上，然后再load,如果在本地load的话，可能存在跑MR的主机不是当前数据存在的这个机器，导致文件不存在报错

-- 加载数据
load data inpath '/dept_partition_dy.txt' into table dept_partition_dy;

-- ok

注意：以前老版本的hive不支持load这种跑MR的命令，可以先创建一个临时表，然后通过insert select的这种方式创建动态分区

分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径；分桶针对的是数据文件。

创建分桶表

create table stu_buck(
 id int,
 name string
)
clustered by(id) -- 指定分桶字段
into 4 buckets  -- 指定桶的数量
row format delimited fields terminated by '\t';

数据准备 student.txt

1001	student1
1002	student2
1003	student3
1004	student4
1005	student5

数据上传到hdfs,并load

hadoop fs -put student.txt /
load data inpath '/student.txt' into table stu_buck;

分桶规则

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

抽样查询

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

语法: TABLESAMPLE(BUCKET x OUT OF y)

查询表stu_buck中的数据。

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