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