Hive中静态分区表 & 动态分区表

概念的引入

在Web系的统日志表中,当我们的操作很多时,通常所会采取的办法是:每一次操作都要记录一条日志,而这些日志很多都是按日/月进行分区的;如果不这样做,到时候查起表来回非常非常大。
假设有以下分区:
CLICK_LOG_20180801
CLICK_LOG_20180802
CLICK_LOG_20180803
当我们进行这样进行分区之后,对比原来只有一张表CLICK_LOG,会有以下优点:可以提高我们的查询效率

通过打印的日志观察不使用分区表所带来的问题:

hive>select deptno, count(1) from emp group by deptno;

这里写图片描述

打印的信息中有:
HDFS Read: 8012,表示读取数据量大小为8012(读取方式为全表读取)

我们假设:emp表中有10亿条数据
那么在我们进行全表读取的时候,效率就会很低

但是当我们使用分区表之后,HDFS上数据存储的目录就会成为:

/user/hive/warehouse/emp/day=20180808/
/user/hive/warehouse/emp/day=20180809/

而我们去Hive表中查询数据时,可以这样写:

hive>select ... from xxx where day=20180808

从而抛出Hive中要使用分区表的原因:
这样的操作,可以减少数据的读取量,进而提升效率

报错解决方案

在创建Hive表之后,导入数据的时候,可能会出现报错:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

报错解决方案/乱码解决办法:
注意:改变mysql设置,不能改变已经存在的表。我们需要转换表的编码。

mysql>alter database 数据库名称 character set latin1;
mysql>use 数据库名称;
mysql>alter table PARTITIONS convert to character set latin1;
mysql>alter table PARTITION_KEYS convert to character set latin1;

静态单级分区表的使用

从本地导入数据 & Hive表数据与HDFS数据的区别 & Hive表结构的变化

创建一张表

create table order_partition(
order_number string,
event_time string
)
PARTITIONED BY(event_month string)
row format delimited fields terminated by '\t';

加载数据:

hive>load data local inpath '/opt/data/order_created.txt' overwrite 
     into table order_partition PARTITION(event_month='201405');

数据导入成功,对比hive中查表得到的数据与hdfs中存储的数据:

hive>select * from order_partition;

这里写图片描述

$>hadoop fs -text /user/hive/warehouse/order_partition/event_month=201405/order_created.txt

这里写图片描述

发现
在Hive表中,能查到分区列的数据,但是在HDFS的数据表中查不到分区列的数据
因为:分区列不是表中的一个实际的列,其实质是一个伪列,而HDFS中存储的是真正的数据,因此肯定看不到该列

查看表结构:

hive>desc formatted order_partition;

多了Partition Information的信息:
这里写图片描述

从HDFS导入数据 & 两种方式修改表的分区信息 & 表的分区信息的查询

在HDFS上创建相应的目录,并上传数据:

$>hadoop fs -mkdir /user/hive/warehouse/order_partition/event_month=201406
$>hadoop fs -put /opt/data/order_created.txt /user/hive/warehouse/order_partition/event_month=201406

HDFS中event_month=201406目录下,已经传入数据:
这里写图片描述

但是,出现了一个问题:
虽然,我们在HDFS上添加了event_month=201406的分区数据,但是Hive表中的数据却仍然没有改变
我们通过查询Hive表上的数据,验证发现数据确实没有添加进来,数据仍然是5条

hive>select * from order_partition;

这里写图片描述

开始查找原因:
首先,我们对mysql里的元数据信息进行查询:

mysql>use zhaotao_hive;
mysql>select * from PARTITIONS;
mysql>select * from PARTITION_KEYS;

发现mysql中,分区信息并没有发生改变
这里写图片描述

解决方法
我们需要对表的分区信息进行修改,因此查阅官方文档
网址:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterPartition

语法:

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

修改分区信息:

hive>ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION(event_month='201406'); 

成功解决,再度查询数据

hive>select * from order_partition;
mysql>select * from PARTITIONS;
mysql>select * from PARTITION_KEYS;

Hive表中的数据:
这里写图片描述

MySQL中的元数据信息:
这里写图片描述

通过上图,我们发现数据都进来了,从HDFS中导入分区数据成功

另一种修改表的分区信息方式(不建议这样操作,暴力执行)
网址:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-RecoverPartitions(MSCKREPAIRTABLE)

语法:

MSCK REPAIR TABLE table_name;
$>hadoop fs -mkdir /user/hive/warehouse/order_partition/event_month=201407
$>hadoop fs -put /opt/data/order_created.txt /user/hive/warehouse/order_partition/event_month=201407           
hive>MSCK REPAIR TABLE order_partition; 

执行完这句话之后,Hive表中的数据直接进来了,mysql的元数据信息也增加了
这里写图片描述
这里写图片描述

不建议这样使用,太暴力了
举个例子:
metastore存了一年了,5分钟1个分区,1天就是288个分区
假设有100条业务线
一旦这样一执行,可能会造成的情况,就是整个数据库出现风险
因此,生产上建议使用alter

查询分区信息

hive>show partitions order_partition;

这里写图片描述
(可以通过查阅源码,去验证show partitions从mysql的哪张表里来读取分区数据信息 )

【注意】
如果没有mysql上的元数据信息,HDFS上的数据是和hive中执行的sql无法关联起来的
因此,每次执行一个SQL语句,都必须要知晓底层的metastore到底干了些什么事情,这对学习Hive会有很大的帮助

静态多级分区表的使用

创建表

create table order_mulit_partition(
order_number string,
event_time string
)
PARTITIONED BY(event_month string, step string)
row format delimited fields terminated by '\t';

从本地向静态多级分区表导入数据

hive>load data local inpath '/opt/data/order_created.txt' overwrite 
     into table order_mulit_partition PARTITION(event_month='201405', step='1');

这里写图片描述

注意:在多分区使用的时候注意: 每个分区字段要写明,包括顺序等等

动态分区表的使用

在Hive中,使用动态分区表,这种方式在工作中常用

需求

按照不同部门作为分区导数据到目标表

使用静态分区表来完成

创建静态分区表:

create table emp_static_partition(
empno int, 
ename string, 
job string, 
mgr int, 
hiredate string, 
sal double, 
comm double)
PARTITIONED BY(deptno int)
row format delimited fields terminated by '\t';

插入数据:

hive>insert into table emp_static_partition partition(deptno=10)
     select empno , ename , job , mgr , hiredate , sal , comm from emp where deptno=10;

查询数据:

hive>select * from emp_static_partition;

这里写图片描述

使用动态分区表来完成

创建动态分区表:

create table emp_dynamic_partition(
empno int, 
ename string, 
job string, 
mgr int, 
hiredate string, 
sal double, 
comm double)
PARTITIONED BY(deptno int)
row format delimited fields terminated by '\t';

【注意】动态分区表与静态分区表的创建,在语法上是没有任何区别的

插入数据:

hive>insert into table emp_dynamic_partition partition(deptno)
     select empno , ename , job , mgr , hiredate , sal , comm, deptno from emp;

【注意】分区的字段名称,写在最后,有几个就写几个 与静态分区相比,不需要where了

需要设置属性的值:

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

这里写图片描述
【注意】不设置,会报错

查询数据:

hive>select * from emp_dynamic_partition;

这里写图片描述
分区列为deptno,实现了动态分区

动态分区表的总结

动态分区无需手工指定数据导入的具体分区
而是由select的字段(字段写在最后,有几个写几个)自行决定导出到哪一个分区中
并自动创建相应的分区,使用上更加方便快捷
工作中用的非常多

版权声明:本文为lemonZhaoTao原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lemonZhaoTao/article/details/78245454