Hive（七）分区表和分桶表
分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。 ，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过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
参考网站：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）
2.将最近的一批数据复制到hour=15目录下hive (default)> dfs -mkdir -p /user/hive/warehouse/zhenai/day=20210603/hour=15;hive (default)> dfs -mv /flume/zhenai/20210603/15/1522706220328.json /user/hive/warehouse/zhenai/day=20210603/hour=15;此时查询是无法查询到数据的 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)
2 . 导入数据hive (default)> dfs -mkdir -p /user/hive/warehouse/zhenai/day=20210603/hour=16;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'; # 指定切割符
分桶抽样
作者:万事万物
原文链接:https://www.jianshu.com/p/dbcaeedeef45