Hive 查询优化总结

jopen 12年前

一、join优化

Join查找操作的基本原则:应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,将条目少的表放在左边,可以有效减少发生内存溢出错误的几率。

Join查找操作中如果存在多个join,且所有参与join的表中其参与joinkey都相同,则会将所有的join合并到一个mapred程序中。

案例:

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)  在一个mapre程序中执行join

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)   在两个mapred程序中执行join

Map join的关键在于join操作中的某个表的数据量很小,案例:

SELECT /*+ MAPJOIN(b) */ a.key, a.value

  FROM a join b on a.key = b.key 

Mapjoin 的限制是无法执行a FULL/RIGHT OUTER JOIN b,和map join相关的hive参数:hive.join.emit.interval  hive.mapjoin.size.key  hive.mapjoin.cache.numrows

由于join操作是在where操作之前执行,所以当你在执行join时,where条件并不能起到减少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'

最好修改为:

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

join操作的每一个mapred程序中,hive都会把出现在join语句中相对靠后的表的数据stream化,相对靠前的变的数据缓存在内存中。当然,也可以手动指定stream化的表: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)

二、group by 优化

Map端聚合,首先在map端进行初步聚合,最后在reduce端得出最终结果,相关参数:

· hive.map.aggr = true是否在 Map 端进行聚合,默认为 True

· hive.groupby.mapaggr.checkinterval = 100000在 Map 端进行聚合操作的条目数目

数据倾斜聚合优化,设置参数hive.groupby.skewindata = true当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MR Job 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作

三、合并小文件

文件数目过多,会给 HDFS 带来压力,并且会影响处理效率,可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响:

· hive.merge.mapfiles = true是否和并 Map 输出文件,默认为 True

· hive.merge.mapredfiles = false是否合并 Reduce 输出文件,默认为 False

· hive.merge.size.per.task = 256*1000*1000合并文件的大小

四、Hive实现(not) in

通过left outer join进行查询,(假设B表中包含另外的一个字段 key1 

select a.key from a left outer join b on a.key=b.key where b.key1 is null

通过left semi join 实现 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制:join条件中右边的表只能出现在join条件中。

五、排序优化

Order by 实现全局排序,一个reduce实现,效率低

Sort by 实现部分有序,单个reduce输出的结果是有序的,效率高,通常和DISTRIBUTE BY关键字一起使用(DISTRIBUTE BY关键字 可以指定map 到 reduce端的分发key

CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1

六、使用分区

Hive中的每个分区都对应hdfs上的一个目录,分区列也不是表中的一个实际的字段,而是一个或者多个伪列,在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区(只有一个),后面的为副分区

静态分区:静态分区在加载数据和使用时都需要在sql语句中指定

          案例:(stat_date='20120625',province='hunan')

动态分区:使用动态分区需要设置hive.exec.dynamic.partition参数值为true,默认值为false,在默认情况下,hive会假设主分区时静态分区,副分区使用动态分区;如果想都使用动态分区,需要设置set hive.exec.dynamic.partition.mode=nostrick,默认为strick

          案例:(stat_date='20120625',province)

七、Distinct 使用

Hive支持在group by时对同一列进行多次distinct操作,却不支持在同一个语句中对多个列进行distinct操作。

八、Hql使用自定义的mapred脚本

注意事项:在使用自定义的mapred脚本时,关键字MAP REDUCE 是语句SELECT TRANSFORM ( ... )的语法转换,并不意味着使用MAP关键字时会强制产生一个新的map过程,使用REDUCE关键字时会产生一个red过程。

自定义的mapred脚本可以是hql语句完成更为复杂的功能,但是性能比hql语句差了一些,应该尽量避免使用,如有可能,使用UDTF函数来替换自定义的mapred脚本

九、UDTF

UDTF将单一输入行转化为多个输出行,并且在使用UDTF时,select语句中不能包含其他的列,UDTF不支持嵌套,也不支持group by sort by等语句。如果想避免上述限制,需要使用lateral view语法,案例:

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

select a.timestamp, b.*

from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

其中,get_json_objectUDF函数,json_tupleUDTF函数。

UDTF函数在某些应用场景下可以大大提高hql语句的性能,如需要多次解析json或者xml数据的应用场景。

十、聚合函数countsum

Count和sum函数可能是在hql语句中使用的最为频繁的两个聚合函数了,但是在hive中count函数在计算distinct value时支持加入条件过滤。