Hive分区表和分桶表

分区表

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

将表中的数据以某种维度进行划分文件夹管理,当要查询数据的时候,根据维度直接加载对应文件夹下的数据!不用加载整张表所有的数据再进行过滤,从而提升处理数据的效率!

静态分区

一级静态分区演示

数据准备

bj.csv
1,zs,bj
2,ls,bj

nj.csv
1,zs,nj
2,ls,nj
3,ww,nj

sh.csv
1,zs,sh
2,ls,sh
3,ww,sh

--创建分区表
create table if not exists tb_static_partition01(
id int,
name string,
city string
)
partitioned by(city_name string comment '分区字段')
row format delimited fields terminated by ','
;


--导入数据
load data inpath '/data/partition/bj.csv' into table tb_static_partition01 
partition(city_name='bj');

load data inpath '/data/partition/nj.csv' into table tb_static_partition01 
partition(city_name='nj');

load data inpath '/data/partition/sh.csv' into table tb_static_partition01 
partition(city_name='sh');

--可以查询SQL的执行计划
explain select * from tb_static_partition01 where city_name='bj';

+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-0 is a root stage                          |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         TableScan                                  |
|           alias: tb_static_partition01             |
|           Statistics: Num rows: 1 Data size: 160 Basic stats: PARTIAL Column stats: NONE |
|           Select Operator                          |
|             expressions: id (type: int), name (type: string), city (type: string), 'bj' (type: string) |
|             outputColumnNames: _col0, _col1, _col2, _col3 |
|             Statistics: Num rows: 1 Data size: 160 Basic stats: PARTIAL Column stats: NONE |
|             ListSink                               |
|                                                    |
+----------------------------------------------------+



explain select * from tb_static_partition01 where city='bj';
+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-0 is a root stage                          |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         TableScan                                  |
|           alias: tb_static_partition01             |
|           Statistics: Num rows: 1 Data size: 640 Basic stats: PARTIAL Column stats: NONE |
|           Filter Operator                          |
|             predicate: (city = 'bj') (type: boolean) |
|             Statistics: Num rows: 1 Data size: 640 Basic stats: PARTIAL Column stats: NONE |
|             Select Operator                        |
|               expressions: id (type: int), name (type: string), 'bj' (type: string), city_name (type: string) |
|               outputColumnNames: _col0, _col1, _col2, _col3 |
|               Statistics: Num rows: 1 Data size: 640 Basic stats: PARTIAL Column stats: NONE |
|               ListSink                             |
|                                                    |
+----------------------------------------------------+

• 已经存在了静态的数据在不同的文件中

• 加载数据到不同的分区下(文件夹)

• 在查询的时候指定分区字段作为查询字段维度 [可以直接定位数据的分区文件夹,加载数据返回,不需要加载所有数据再进行过滤]

二级静态分区演示

--数据准备
2021-07-01.log
a,2021-07-01
b,2021-07-01
c,2021-07-01
d,2021-07-01
e,2021-07-01
2021-07-02.log
a,2021-07-02
b,2021-07-02
c,2021-07-02
d,2021-07-02
e,2021-07-02
2021-08-01.log
a,2021-08-01
b,2021-08-01
c,2021-08-01
d,2021-08-01
e,2021-08-01
2021-08-02.log
a,2021-08-02
b,2021-08-02
c,2021-08-02
d,2021-08-02
e,2021-08-02
2021-08-03.log
a,2021-08-03
b,2021-08-03
c,2021-08-03
d,2021-08-03
e,2021-08-03

--创建二级分区表
create table if not exists tb_static_partition02(
name string,
`date` string
)
partitioned by(`month` string,`day` string)
row format delimited fields terminated by ','
;

--加载数据
load data inpath '/data/partition2/2021-07-01.log' into table tb_static_partition02
partition(month='07',day='01');

load data inpath '/data/partition2/2021-07-02.log' into table tb_static_partition02
partition(month='07',day='02');

load data inpath '/data/partition2/2021-08-01.log' into table tb_static_partition02
partition(month='08',day='01');

load data inpath '/data/partition2/2021-08-02.log' into table tb_static_partition02
partition(month='08',day='02');

load data inpath '/data/partition2/2021-08-03.log' into table tb_static_partition02
partition(month='08',day='03');

HDFS储存目录

 

 

 注意:静态分区,直接load文件到指定的分区下,不会对文件中的数据内容进行校验,有可能出现数据错误.

动态分区

按照某个字段自动的将数据加载到指定的分区中

数据没有按照我们的规则存储在不同的文件中/而是在同一个文件中

期望根据一个字段的值进行分区

操作步骤

        创建普通表  导入数据

        开启动态分区

        开启分严格模式

        创建分区表

        通过insert into partition select from 语法导入数据

--数据准备
1,zss,bj
2,lss,bj
3,tg,sh
4,xg,bj
5,ln,sd
6,yg,sh

--创建普通表导入数据
create table tb_dynamic_partition_source(
id int,
name string,
city string
)row format delimited fields terminated by ','
;

load data inpath '/data/dynamic_partition/' into table tb_dynamic_parttion_source;

--设置相关参数
set hive.exec.dynamic.partition=true;    --开启动态分区
set hive.exec.dynamic.partition.mode=nonstrick;    --开启非严格模式

--创建分区表
create table tb_dynamic_partition(
id int,
name string,
city string 
)partitioned by(city_name string);

--将普通表的数据导入分区表
insert into tb_dynamic_partition partition(city_name)
select id,name,city,city as city_name from tb_dynamic_partition_source;


--查看分区
show partitions tb_dynamic_partition ;
+---------------+
|   partition   |
+---------------+
| city_name=bj  |
| city_name=sd  |
| city_name=sh  |
+---------------+

 修改分区表结构

--查看所有分区
show partitions tb_dynamic_partition ;


--添加分区
alter table tb_dynamic_partition add partition(city_name='zj');

--删除分区    分区下的文件也会删除
alter table tb_dynamic_partition drop partition(city_name='bj');

分桶表

对Hive表分桶可以将表中记录按分桶字段的哈希值分散进多个文件中,这些小文件称为桶.桶以文件为单位管理数据!分区针对的是数据的存储路径;分桶针对的是数据文件

分区提供一个隔离数据和优化查询的便利方式.不过,并非所有的数据集都可形成合理的分区,特别是要确定合适的划分大小这个疑虑.

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

        如果查询的维度是分桶字段,查询的时候先确定数据所在的痛,提升查询效率

        进行join的时候,关联字段就是分桶字段,只有相同的桶之间的数据进行关联,从而提升关联效率

操作步骤

1创建普通表 导入数据

2 创建分桶表

3 设置相关参数

4 导入数据到分桶表中

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

--创建普通表,导入数据
create table tb_student(
sid int ,
name string
)
row format delimited fields terminated by '\t'  ;


load data local inpath '/data/student.csv' into table  tb_student ;


--创建分桶表
create table tb_buck_student(
sid int,
name string
)clustered by(sid)
into 3 buckets; 


--设置相关参数
 set hive.enforce.bucketing=true;  
 set mapreduce.job.reduces=-1;  

--导入数据到分桶表
insert into  table tb_buck_student select sid,name from tb_student;

 

 

总结:

分桶表实则就是将表中的数据分文件管理 , 某个字段的hashcode% 桶数clustered by (name)

• join的时候 相同规则的桶数据进行关联

• 查询数据的时候可以先确定数据的桶 , 加载数据

• 在实际使用时,可以分区和分桶同时使用

分桶抽样查询

SELECT * FROM tb_name TABLESAMPLE (BUCKET x OUT OF y [ON colname])