Hive（七）分区表和分桶表

作者: 万事万物 | 来源:发表于2021-05-29 05:50 被阅读0次

案例详解__HIVE中内部表、外部表、分区表和分桶表
Hive（七）分区表和分桶表
【Hive】优化
Hive 1.2.1 分区和分捅
Hive使用Beeline的DDL操作
hive基础语法
Hive的分区表和分桶表的区别
Hive（二）分区表、桶
HIVE 表设计优化1 分区表
Hive系列之分区表和桶

分区表

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

分区表基本操作

引入分区表（需要根据日期对日志进行管理, 通过部门信息模拟）
准备三份日志文件
log_2020-01-01.log

张三  男   18
李四  女   20
王二  男   34

log_2020-01-02.log

二狗子 男   28
张大贵人    男   23
小李子 男   23
王疯子 女   78

log_2020-01-03.log

白雪公主    女   18
青蛙王子    男   24
巫师  女   67
大娃  男   8

注意：上面数据虽然看起来乱乱的，但是都是按照\t进行分割的。

创建分区表

hive (default)> create table log_partition(name string,gender string,age int) 
partitioned by (day string) row format delimited fields terminated by '\t';
OK
Time taken: 1.32 seconds

加载数据到分区表
1）分区准备

[admin@hadoop102 partition_table]$ ll
总用量 12
-rw-rw-r--. 1 admin admin 42 5月  27 20:58 log_2020-01-01.log
-rw-rw-r--. 1 admin admin 71 5月  27 21:00 log_2020-01-02.log
-rw-rw-r--. 1 admin admin 67 5月  27 21:01 log_2020-01-03.log

2）加载数据
语法： load data [local] inpath <文件地址> into table <分区表名> partition(<分区字段>=<分区名称>);
说明：
local：可选项，若不设置<文件地址>将需要是HDFS上的地址，若设置了local 表示从本地进行加载·。
<文件地址>：与是否配置 local 有关，配置了local设置本地路径（如Linux地址），不配置就从HDFS上找。
<分区表名>：创建分区表的那个名称
<分区字段>：创建分区表时指定的分区字段（day)，分区字段可以配置多个，表示多级目录。
<分区名称>：可以随便指定，即使与之前面的相同也无所谓。用于存放文件中的数据。

- 加载 log_2020-01-01.log 数据

hive (default)> load data local inpath '/home/admin/partition_table/log_2020-01-01.log' into table  log_partition partition(day='20200101');
Loading data to table default.log_partition partition (day=20200101)
OK
Time taken: 2.009 seconds
hive (default)>

- 加载 log_2020-01-02.log 数据

hive (default)> load data local inpath '/home/admin/partition_table/log_2020-01-02.log' into table  log_partition partition(day='20200102');
Loading data to table default.log_partition partition (day=20200102)
OK
Time taken: 0.564 seconds

- 加载 log_2020-01-03.log 数据

hive (default)> load data local inpath '/home/admin/partition_table/log_2020-01-03.log' into table  log_partition partition(day='20200103');
Loading data to table default.log_partition partition (day=20200103)
OK
Time taken: 0.437 seconds

数据加载结果

查询分区表中数据

查看分区表信息

hive (default)> desc log_partition;
OK
col_name    data_type   comment
name                    string                                      
gender                  string                                      
age                     int                                         
day                     string                                      
         
# Partition Information      
# col_name              data_type               comment             
day                     string                                      
Time taken: 0.141 seconds, Fetched: 8 row(s)

col_name :常规字段
data_type：数据类型
comment：注释。
# Partition Information : 表示分区信息，
# col_name: 分区字段
data_type: 数据类型
comment：注释
day string # 分区字段名

查询单个分区信息

hive (default)> select * from log_partition where day='20200101';
OK
log_partition.name  log_partition.gender    log_partition.age   log_partition.day
张三  男   18  20200101
李四  女   20  20200101
王二  男   34  20200101

也可以查询多个分区信息

select * from log_partition where day='20200101' 
union 
select * from log_partition where day='20200102';

由于该方式查询需要运行ResourceManager，这里就就直接展示结果吧

Total MapReduce CPU Time Spent: 4 seconds 80 msec
OK
_u1.name    _u1.gender  _u1.age _u1.day
二狗子 男   28  20200102
小李子 男   23  20200102
张三  男   18  20200101
张大贵人    男   23  20200102
李四  女   20  20200101
王二  男   34  20200101
王疯子 女   78  20200102
Time taken: 22.96 seconds, Fetched: 4 row(s)

注意：千万不要写成 select * from log_partition where day='20200101' and '20200102'；没有为什么，可能不支持，已躺雷。

查看分区表有多少分区
语法：show partitions <分区表>

hive (default)> show partitions log_partition;
OK
partition
day=20200101
day=20200102
day=20200103
Time taken: 0.068 seconds, Fetched: 3 row(s)

添加分区

创建单个分区
案例：在log_partition分区表中添加 2020104 分区

hive (default)> alter table log_partition add partition(day='2020104');
OK
Time taken: 0.093 seconds

创建单个分区结果查看

-创建多个分区
案例：在log_partition分区表中添加 20200105 和 20200106 分区

hive (default)> alter table log_partition add partition(day='20200105') partition(day='20200106');
OK
Time taken: 0.091 seconds

语法：alter table <分区表> add partition(分区字段=分区值) partition(分区字段=分区值) ...;
总结：添加多个分区，需要指定指定多个partition()，每个partition 使用空格分隔。

删除分区

删除单个分区
语法：alter table <表名> drop partition ()

hive (default)> alter table dept_partition drop partition (day='20200406');

同时删除多个分区（分区之间必须有逗号）
语法：alter table <表名> drop partition (),partition (),partition ()...;

hive (default)> alter table dept_partition drop partition(day='2020041'),partition(day='20200402');

查看分区表结构
语法：desc [fromatted] <table> ;

hive (default)> desc formatted dept_partition;
OK
col_name    data_type   comment
# col_name              data_type               comment             
deptno                  int                                         
dname                   string                                      
loc                     string                                      
         
# Partition Information      
# col_name              data_type               comment             
day                     string          
...
...

分区表二级分区

所谓的二级分区，就是在分区中在嵌套一个分区，简单点描述就是目录嵌套目录。当然不止能创建二级分区，根据业务需求，三级四级也是可以的。对于表来说，分区就是一个字段。以下案例数据过于敏感，并不会展示查询结果。

创建二级分区表

create table zhenai(name string,resume string,hometown string,marriage string,age string,height string) partitioned by (day string,hour string)  
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE
$\color{red}{用于加载 json格式的数据}$
参考网站：https://www.cnblogs.com/lhicp/p/14033516.html

加载数据到分区表中
1.从hdfs中进行加载

hive (default)> load data inpath '/flume/zhenai/20210603/14' into table zhenai partition(day='20210603',hour='14');

2.查询分区数据数据过于铭感，就不展示了

hive (default)> select * from zhenai where day='20210603' and hour='12';

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

方式一：上传数据后修复
1.创建一个分区目录（day=20210603,hour=15）

hive (default)> dfs -mkdir -p /user/hive/warehouse/zhenai/day=20210603/hour=15;

2.将最近的一批数据复制到hour=15目录下

hive (default)> dfs -mv /flume/zhenai/20210603/15/1522706220328.json  /user/hive/warehouse/zhenai/day=20210603/hour=15;

此时查询是无法查询到数据的

&emsp;hive (default)> select * from zhenai where day='20210603'  and hour='15';
OK
zhenai.name   zhenai.resume   zhenai.hometown zhenai.marriage zhenai.age    zhenai.height zhenai.day  zhenai.hour
Time taken: 0.094 seconds

执行修复命令
语法：msck repair table <分区表>

hive (default)>  msck repair table zhenai;
OK
Partitions not in metastore:  zhenai:day=20210603/hour=15
Repair: Added partition to metastore zhenai:day=20210603/hour=15
Time taken: 0.085 seconds, Fetched: 2 row(s)

再次查询就有数据了

hive (default)> select * from zhenai where hour='15';
OK
zhenai.name   zhenai.resume   zhenai.hometown zhenai.marriage zhenai.age    zhenai.height zhenai.day  zhenai.hour
# 表数据在这展示，数据敏感，不做展示

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

创建一个分区目录 (day=20210603,hour=16)

hive (default)> dfs -mkdir -p /user/hive/warehouse/zhenai/day=20210603/hour=16;

2 . 导入数据

hive (default)> dfs -cp /flume/zhenai/20210603/15/1622706220328.json   /user/hive/warehouse/zhenai/day=20210603/hour=

添加分区

hive (default)> alter table zhenai add partition (day='20210603',hour='16');

数据查询

hive (default)> select * from zhenai where day='20210603' and hour='16';
OK
zhenai.name   zhenai.resume   zhenai.hometown zhenai.marriage zhenai.age    zhenai.height zhenai.day  zhenai.hour
# 表数据在这展示，数据敏感，不做展示

方式三：创建文件夹后load数据到分区

创建一个分区目录 (day=20210603,hour=17)

hive (default)> dfs -mkdir -p /user/hive/warehouse/zhenai/day=20210603/hour=17;

上传数据

hive (default)> load data inpath '/flume/zhenai/20210603/16/1622707379156.json' into table zhenai partition(day='20210603',hour='17');

查询数据

hive (default)> select * from zhenai where day='20210603' and hour='17';
OK
zhenai.name   zhenai.resume   zhenai.hometown zhenai.marriage zhenai.age    zhenai.height zhenai.day  zhenai.hour
# 表数据在这展示，数据敏感，不做展示

动态分区调整

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

开启动态分区参数设置

开启动态分区功能（默认true，开启）

hive.exec.dynamic.partition=true

设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

hive.exec.dynamic.partition.mode=nonstrict

在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000

hive.exec.max.dynamic.partitions=1000

在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

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

整个MR Job中，最大可以创建多少个HDFS文件。默认100000

hive.exec.max.created.files=100000

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

hive.error.on.empty.partition=false

分桶表

概述

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

分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。

语法

create table <表名>(id int, name string)
clustered by(id) # 指定按什么字段进行分桶
into 4 buckets # 共分为几个桶
row format delimited fields terminated by '\t'; # 指定切割符

分桶抽样

案例详解__HIVE中内部表、外部表、分区表和分桶表
目录一、Hive建表语法二、内部表外部表三、分区表四、分桶表 Hive在建表时可指定内部表、外部表、分区表和分桶表...
Hive（七）分区表和分桶表
分区表分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。，把一个大的数...
【Hive】优化
分区表的设计和优化普通表结构问题使用分区表可以优化上述查询问题分桶表的设计和优化 Join问题分桶表设计 ...
Hive 1.2.1 分区和分捅
1. 借鉴 Hive学习笔记——Hive中的分桶Hive分区和分桶（0925）HIVE表索引，分区和分桶的区别 2...
Hive使用Beeline的DDL操作
beeline进入交互命令行内部表外部表分区表分桶表分桶表&分区表导入数据的时候需要指定分区倾斜表 ...
hive基础语法
目录 Hive安装和启动 Hive表操作-分区表 Hive表操作-复杂类型操作 Hive 查询语句 Zepplin...
Hive的分区表和分桶表的区别
本文链接：https://blog.csdn.net/JENREY/article/details/8058849...
Hive（二）分区表、桶
分区表 Hive中没有复杂的分区类型（List,Range,Hash）、各种复合分区，分区列不是表中的实际字段而是...
HIVE 表设计优化1 分区表
1. 分区表和分桶表 1. 分区表分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所...
Hive系列之分区表和桶
为提升hive数据的查询和写入性能， hive提供了分区表机制。hive每个表格可以指定多个分区key, 这些分区...