一、join优化
Join查找操作的基本原则:应该将条目少的表/子查询放在Join操作符的左边。原因是在Join操作的Reduce阶段,位于Join操作符左边的表的内容会被加载进内存,将条目少的表放在左边,可以有效减少发生内存溢出错误的几率。
Join查找操作中如果存在多个join,且所有参与join的表中其参与join的key都相同,则会将所有的join合并到一个mapred程序中。
案例:
SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key1)在一个mapre程序中执行join
SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key2)在两个mapred程序中执行join
Mapjoin的关键在于join操作中的某个表的数据量很小,案例:
SELECT/*+MAPJOIN(b)*/a.key,a.value
FROMajoinbona.key=b.key
Mapjoin的限制是无法执行aFULL/RIGHTOUTERJOINb,和mapjoin相关的hive参数:hive.join.emit.intervalhive.mapjoin.size.keyhive.mapjoin.cache.numrows
由于join操作是在where操作之前执行,所以当你在执行join时,where条件并不能起到减少join数据的作用;案例:
SELECTa.val,b.valFROMaLEFTOUTERJOINbON(a.key=b.key)
WHEREa.ds='-07-07'ANDb.ds='-07-07'
最好修改为:
SELECTa.val,b.valFROMaLEFTOUTERJOINb
ON(a.key=b.keyANDb.ds='-07-07'ANDa.ds='-07-07')
在join操作的每一个mapred程序中,hive都会把出现在join语句中相对靠后的表的数据stream化,相对靠前的变的数据缓存在内存中。当然,也可以手动指定stream化的表:SELECT/*+STREAMTABLE(a)*/a.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key1)
二、groupby优化
Map端聚合,首先在map端进行初步聚合,最后在reduce端得出最终结果,相关参数:
·hive.map.aggr=true是否在Map端进行聚合,默认为True
·hive.groupby.mapaggr.checkinterval=100000在Map端进行聚合操作的条目数目
数据倾斜聚合优化,设置参数hive.groupby.skewindata=true,当选项设定为true,生成的查询计划会有两个MRJob。第一个MRJob中,Map的输出结果集合会随机分布到Reduce中,每个Reduce做部分聚合操作,并输出结果,这样处理的结果是相同的GroupByKey有可能被分发到不同的Reduce中,从而达到负载均衡的目的;第二个MRJob再根据预处理的数据结果按照GroupByKey分布到Reduce中(这个过程可以保证相同的GroupByKey被分布到同一个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
通过leftouterjoin进行查询,(假设B表中包含另外的一个字段key1
selecta.keyfromaleftouterjoinbona.key=b.keywhereb.key1isnull
通过leftsemijoin实现in
SELECTa.key,a.valFROMaLEFTSEMIJOINbon(a.key=b.key)
Leftsemijoin的限制:join条件中右边的表只能出现在join条件中。
五、排序优化
Orderby实现全局排序,一个reduce实现,效率低
Sortby实现部分有序,单个reduce输出的结果是有序的,效率高,通常和DISTRIBUTEBY关键字一起使用(DISTRIBUTEBY关键字可以指定map到reduce端的分发key)
CLUSTERBYcol1等价于DISTRIBUTEBYcol1SORTBYcol1
六、使用分区
Hive中的每个分区都对应hdfs上的一个目录,分区列也不是表中的一个实际的字段,而是一个或者多个伪列,在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区(只有一个),后面的为副分区
静态分区:静态分区在加载数据和使用时都需要在sql语句中指定
案例:(stat_date='0625',province='hunan')
动态分区:使用动态分区需要设置hive.exec.dynamic.partition参数值为true,默认值为false,在默认情况下,hive会假设主分区时静态分区,副分区使用动态分区;如果想都使用动态分区,需要设置sethive.exec.dynamic.partition.mode=nostrick,默认为strick
案例:(stat_date='0625',province)
七、Distinct使用
Hive支持在groupby时对同一列进行多次distinct操作,却不支持在同一个语句中对多个列进行distinct操作。
八、Hql使用自定义的mapred脚本
注意事项:在使用自定义的mapred脚本时,关键字MAPREDUCE是语句SELECTTRANSFORM(...)的语法转换,并不意味着使用MAP关键字时会强制产生一个新的map过程,使用REDUCE关键字时会产生一个red过程。
自定义的mapred脚本可以是hql语句完成更为复杂的功能,但是性能比hql语句差了一些,应该尽量避免使用,如有可能,使用UDTF函数来替换自定义的mapred脚本
九、UDTF
UDTF将单一输入行转化为多个输出行,并且在使用UDTF时,select语句中不能包含其他的列,UDTF不支持嵌套,也不支持groupby、sortby等语句。如果想避免上述限制,需要使用lateralview语法,案例:
selecta.timestamp,get_json_object(a.appevents,'$.eventid'),get_json_object(a.appenvets,'$.eventname')fromloga;
selecta.timestamp,b.*
fromlogalateralviewjson_tuple(a.appevent,'eventid','eventname')basf1,f2;
其中,get_json_object为UDF函数,json_tuple为UDTF函数。
UDTF函数在某些应用场景下可以大大提高hql语句的性能,如需要多次解析json或者xml数据的应用场景。
十、聚合函数count和sum
Count和sum函数可能是在hql语句中使用的最为频繁的两个聚合函数了,但是在hive中count函数在计算distinctvalue时支持加入条件过滤。