1. Hive和HBase的区别
HBase是一个用来处理HDFS上文件的NoSQL数据库,建立于HDFS之上。
Hive是封装了MapReduce的操作,让用户可以通过写sql语句的方式,实现MapReduce操作。
Hive基于一个统一的查询分析层,通过SQL语句的方式对HDFS上的数据进行查询、统计和分析。
2. Hive是什么
(1)Hive是一个SQL解析引擎,将SQL语句转译成MR Job,然后再Hadoop平台上运行,达到快速开发的目的。
(2)Hive中的表示纯逻辑表,只有表的定义等,即表的元数据(存储于MySQL中)。本质就是Hadoop的目录/文件,这种设计方式实现了元数据与数据存储分离。
(3)Hive本身不存储数据,它完全依赖HDFS和MapReduce。
(4)Hive中没有定义专门的数据格式,需要由用户指定三个属性:
- 列分割符:空格、\t、\001
- 行分隔符:\n
- 读取文件数据的方法:TextFile(可读性好、但数据不压缩占据空间比较大)、SequenceFile(hadoop提供的一种二进制文件,通过Writable接口以<key,value>形式序列化到文件中)、RCFile(Hive专门推出的,用于面向列的格式)
3. Hive中的SQL与传统SQL区别
可扩展性:
Hive可以自定义一些函数进行扩展
(1)UDF:
- 直接应用于select语句。通常查询的时候,需要对数据字段做一些格式化的处理。例如:大小写转换。
- 特点:一进一出,一对一的关系\
(2)UDAF:
- 直接应用于group by语句。
- 特点:多对一的关系 \
(3)UDTF:
- d
- 特点:一对多的关系
数据检查
读时模式: Hive只有在读的时候才会检查、解析字段和schema。优点是在写的过程中不需要解析数据,从而load data非常迅速。缺点是读的时候很慢。
写时模式:传统的SQL只有在写的时候才会建立索引、压缩、数据一致性、字段检查等等。优点是读的时候会得到优化,读很快。缺点是写的时候很慢。
Hive与传统关系数据特点比较
- hive 和关系数据库存储文件的系统不同,Hive使用的是Hadoop的HDFS(Hadoop的分布式文件系统),关系数据库则是服务器本地的文件系统;
- Hive使用的计算模型是MapReduce,而关系数据库则是自己设计的计算模型
- 关系数据库都是为实时查询的业务进行设计的,而Hive则是为海量数据做数据挖掘设计的,实时性很差
- Hive继承于Hadoop,从而拥有很容易扩展自己的存储能力和计算能力,而关系数据库在这方面比数据库差很多。
4. Hive生态架构
(1)用户接口
CLI: 用户接口——Client终端。启动时,会同时启动一个Hive副本
Client: Hive的客户端,用户连接至Hive Server
GUI: 通过浏览器访问Hive
(2)语句转换
Driver:语句转换——sql->MapReduce,Hive自身不会生成MR,而是通过一个执行计划来去执行MR(XML文件-》MR。
Compiler(解析器): 生成抽象语法树
语法分析器: 验证查询语句
逻辑计划生成器(包括优化器):生成操作符树
查询计划生成器: 转换为MapReduce任务
(3)数据存储
元数据: Hive 的元数据信息通常存储在关系型数据库中,常用MySQL数据库作为元数据库管理。
生成文件: Hive数据以文件形式存储在HDFS的指定目录下面。
查询计划:Hive语句生成查询计划,由MapReduce调用执行。
默认本地数据库:derby(单用户模式)、MySQL(多用户模式、远程服务模式)
5. Hive数据管理
Hive的表的本质就是Hadoop的目录/文件。
Hive默认表存放路径一般都是在你工作目录的Hive目录里面,按表名做文件夹分开。如果有你分区表的话,分区值是子文件夹,可以直接在其他的M/R job里直接应用这部分数据。
Hive支持4个数据模型。
数据表
Table(内部表) :创建表的结构和数据,删除表的时候时会将表的元数据和数据都删掉。
External Table(外部表) :仅创建表的结构,删除外部表时仅删除表的结构,数据不会被删掉。
分区表和分桶表
Partition:可以按照自定义的属性进行分类,类似于Reduce的概念分成一个一个桶。一个表可以有多个分区,表中的要给Partition分区对应于表下的一个目录,所有的Partition的数据都存储再对应的目录中。Partition用于辅助查询,缩小查询范围,加快数据的检索速度和对数据按照一定的规格和条件进行管理。
Bucket :Hive会针对某一个列进行桶的组织,通常对列值做Hash。类似于mysql中的分库,将表内的数据均匀分布到指定大小的各个子表中。采集数据时,可以仅查看子表内的数据来了解数据情况,方便采样。使用Join的时候会自动激活Map端的map-sdie Join,方便了使用Join,从而实现了优化查询。
Type | Name | HDFS Directory |
---|---|---|
Table | mobile_user | /lbs(数仓名字)/mobile_user |
Partition | action(字段)=insight, day = 20130220 | /lbs/mobile_user/action=insight/day = 20130220 |
Bucket | clusted by user into 32 buckets | /lbs/mobile_user/action=insight/day = 20130220/part-00000 |
6. Hive内部表和外部表
Hive用create创建表的时候,选择创建方式:
- create table:默认创建内部表,此表将会创建一个与数据相关联的表。内部表数据由Hive自身管理,若用户删除内部表,则存储数据也会被删除。
- create external table:创建外部表,此表仅创建一个表结构,不与数据相关联。外部表数据由HDFS管理,若删除外部表仅会删除hive表的结构信息,不会删除存储的数据。
7. Hive中的Bucket
- hive中table可以拆分成partition,table和partition可以通过‘CLUSTERED BY ’进一步分bucket,bucket中的数据可以通过‘SORT BY’排序。
- create table bucket_user (id int,name string)clustered by (id) into 4 buckets;
- ‘set hive.enforce.bucketing = true’ 可以自动控制上一轮reduce的数量从而适 配bucket的个数,当然,用户也可以自主设置mapred.reduce.tasks去适配 bucket个数。
- 数据sampling – 提升某些查询操作效率,例如mapside join。
- 查看sampling数据: – hive> select * from student tablesample(bucket 1 out of 2 on id);
- – tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y) – y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了64份,当y=32 时,抽取(64/32=)2个bucket的数据,当y=128时,抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽 取。例如,table总bucket数为32,tablesample(bucket 3 out of 16),表示总共抽取(32/16=)2个bucket的数据 ,分别为第3个bucket和第(3+16=)19个bucket的数据
8. Hive数据类型
9. Hive的优化
Hive的本质实际上就是MapReduce
在hive的执行语句当中的执行查询的顺序,例如一条sql:
select … from … where … group by … having … order by …
执行顺序:
from … where … select … group by … having … order by …
其实总结hive的执行顺序也是总结mapreduce的执行顺序:MR程序的执行顺序:
map阶段
:
1.执行from加载,进行表的查找与加载
2.执行where过滤,进行条件过滤与筛选
3.执行select查询:进行输出项的筛选
4.map端文件合并:map端本地溢出写文件的合并操作,每个map最终形成一个临时文件, 然后按列映射到对应的。
Reduce阶段
:
1.group by:对map端发送过来的数据进行分组并进行计算。
2.select:最后过滤列用于输出结果
3.order by 排件
所以通过上面的例子我们可以看到,在进行select之后我们序后进行结果输出到HDFS文会形成一张表,在这张表当中做分组排序这些操作。
先来看一看Map的优化
-
Map的优化
-
Reduce的优化
select pt, count(1)
from popt_tbaccountcopy_mes
where pt = '2012-07-0' group by pt;
group by pt
:将数据按pt
聚合
where pt = '2012-07-0'
:查询出pt = '2012-07-0'
的数据
count(1)
:每个查询到的数据计数
最终得到的是将pt = '2012-07-0'
的数据进行聚合为key,并将计数求和作为value。
若改为
select count(1)
from popt_tbaccountcopy_mes
where pt = '2012-07-0';
最终也是实现计数求和功能,但只在一个Reducer上运行。
-
分区裁剪(Partition)
Where中的分区条件,会提前生效,不必特意做子查询,直接Join和GroupBy。 -
笛卡尔积
join的时候不加on条件或者无效的on条件,Hive只能使用1个reducer来完成笛卡尔积。 -
Map join
/*+ MAPJOIN(tablelist) */,必须是小表,不要超过1G,或者50万条记录。 -
Union all
先做union all再做join或group by等操作可以有效减少MR过程,尽管是多个Select,最终只有一个 mr。 -
Multi-insert & multi-group by
– 从一份基础表中按照不同的维度,一次组合出不同的数据FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1)] select_statement1 group by key1 INSERT OVERWRITE TABLE tablename2 [PARTITION (partcol2=val2)] select_statement2 group by key2
-
Automatic merge
- 当文件大小比阈值小时,hive会启动一个mr进行合并。
- hive.merge.mapfiles = true 是否和并 Map 输出文件,默认为 True。
- hive.merge.mapredfiles = false 是否合并 Reduce 输出文件,默认为 False。
- hive.merge.size.per.task = 25610001000 合并文件的大小。
-
Multi-Count Distinct
- 必须设置参数:set hive.groupby.skewindata=true;
- select dt, count(distinct uniq_id), count(distinct ip)
- from ods_log where dt = 20170301 group by dt
10. Hive的Join优化
(1)一个和多个MR
- 生成一个MR job
SELECT a.val, b.val, c.val \
FROM a \
JOIN b ON (a.key = b.key1) \
JOIN c ON (a.key = c.key1)
- 生成多个MR job
SELECT a.val, b.val, c.val \
FROM a \
JOIN b ON (a.key = b.key1) \
JOIN c ON (c.key = b.key1)
MR越多耗时时间会越长,优化目标是让MR越少越好。
(2)Join优化——表连接顺序
在对两个表做join时,如果一个表是小表、另一个表是大表的时候,可以提前把这个表放到内存里去,可以提升处理性能。
按照JOIN顺序中的最后一个表应该尽量是大表,因为JOIN前一阶段生成的数据会存在于 Reducer的buffer中,通过stream最后面的表,直接从Reducer的buffer中读取已经缓冲的中间 结果数据(这个中间结果数据可能是JOIN顺序中,前面表连接的结果的Key,数据量相对较小, 内存开销就小),这样,与后面的大表进行连接时,只需要从buffer中读取缓存的Key,与大表 中的指定Key进行连接,速度会更快,也可能避免内存缓冲区溢出。
例如,在执行select x,y from A join B
时,
hive会默认左边为小表,将数据放到内存,右边为大表,将数据做类似流数据。
通过参数也可以指定哪个表被视为大表,那个表被视为小表。
指定小表
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a
JOIN b ON a.key = b.key;
加入参数/*+ MAPJOIN(b) */
,可将b视为小表。
MAPJION
会把小表全部读入内存中,在map阶 段直接拿另外一个表的数据和内存中表数据做 匹配,由于在map是进行了join操作,省去了 reduce运行的效率也会高很多。
指定大表
SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key1);
加入参数/*+ STREAMTABLE(a) */
,可将a视为大表,尝试将b和c视为小表进行缓存。
左连接时,左表中出现的JOIN字段都保留,右表没有连接上的都为空。
SELECT a.val, b.val
FROM a
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'
这种方式是先进行JOIN
在进行WHERE
筛选。这样在JOIN过程中可能会输出大量结果,再对这些结果进行过滤,比较耗时。
ASELECT a.val, b.val
FROM a
LEFT OUTER JOIN b
ON(a.key=b.key AND b.ds='2009-07-07')
可以进行优化对上一种情况进行优化,这种方式是将WHERE
条件放在ON
后 ,在JOIN
的过程中,就对不满足条件的记录进行了预先过滤,JOIN
和ON
内部筛选同时进行。
(3)Join优化——并行执行
同步执行hive的多个阶段,hive在执行过程,将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段,而这些阶段可能并非完全相互依赖的,也就是说可以并行执行,这样可能使得整个job的执行时间缩短。hive执行开启:set hive.exec.parallel=true
11. Hive的优化——数据倾斜
- 操作
- Join
- Group by
- Count Distinct
- 原因
- key分布不均导致
- 人为的建表疏忽
- 业务数据特点
- 症状
- 任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。
- 倾斜度
- 平均记录数超过50W且最大记录数是超过平均记录数的4倍。
- 最长时长比平均时长超过4分钟且最大时长超过平均时长的2倍。
- 万能方法
- hive.groupby.skewindata=true
(1)大小表关联
- 原因
- Hive在进行join时,按照join的key进行分发,而在join左边的表的数据会首先读入内存,如果左边表的key相对 分散,读入内存的数据会比较小,join任务执行会比较快;而如果左边的表key比较集中,而这张表的数据量很大, 那么数据倾斜就会比较严重,而如果这张表是小表,则还是应该把这张表放在join左边。
- 思路
- 将key相对分散,并且数据量小的表放在join的左边,这样可以有效减少内存溢出错误发生的几率。
- 使用map的join让小表先进入内存
- 方法
- small_table join big_table
(2)大大表关联
- 原因
- 日志中有一部分的userid是空或者是0的情况,导致在用user_id进行hash分桶的时候,会将日志中userid为0或者空的数据分到一起,导致了过大的斜率。
- 思路
- 把空值的key变成一个字符串加上随机数,把倾斜的数据分到不同的reduce上,由于null值关联不上,处理后并不影响最终结果。
- 方法
- on case when(x.uid=’-’ or x.uid=‘0’ or x.uid is null) then concat(‘dp_hive_search’,rand()) else x.uid end = f.user_id;
(3)聚合时存在大量特殊值
- 原因
- 做count distinct时,该字段存在大量值为NULL或空的记录。
- 思路
- count distinct时,将值为空的情况单独处理,如果是计算count distinct,可以不用处理,直接过滤,在最后结 果中加1。
- 如果还有其他计算,需要进行group by,可以先将值为空的记录单独处理,再和其他计算结果进行union。
- 方法
其中select cast(count(distinct(user_id))+1 as bigint) as user_cnt from tab_a where user_id is not null and user_id<>""
cast() as datatype
用于数据类型转换