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的字段(字段写在最后，有几个写几个)自行决定导出到哪一个分区中
并自动创建相应的分区，使用上更加方便快捷
工作中用的非常多