HIVE 优化浅谈

标签: dev | 发表时间:2019-09-06 00:00 | 作者:
出处:http://itindex.net/relian


作者:邓力,entobit技术总监,八年大数据从业经历,由一代HADOOP入坑,深耕云计算应用领域,由从事亚马逊EMR和阿里云EMR应用开发逐步转入大数据架构领域,对大数据生态及框架应用有深刻理解。


引言

随着商务/运营同学执行的HQL越来越多,整体HIVE执行效率变低,本文从HIVE切入,分析HQL面临的问题和待优化部分,结合其他大数据框架来解决实际问题。以下内容没有针对业务代码提供优化建议.

常见的HQL

select型

设置hive.fetch.task.conversion=none会以集群模式运行,无论是否有limit。在数据量小时建议使用hive.fetch.task.conversion=more,此时select配合limit以单机执行获取样本数据,执行更快

常见的select配合order by/group by等基本操作不在此赘述

注:
select查询可以通过split.maxsize和split.minsize控制并发MAPPER数量

insert型

分为两种

  • insert into

  • insert overwrite

配合分区可以达到重写分区或者在分区追加数据的目的。还可以配合动态分区模式插入对应分区

开启动态分区:

   // 开启动态分区模式   sethive.exec.dynamic.partition=true;   // 开启动态分区非严格模式(多分区时首分区支持动态分区必要条件,首分区为静态分区可以不设置)   sethive.exec.dynamic.partition.mode=nonstrict;   // 单节点上限   sethive.exec.max.dynamic.partitions.pernode=100;   // 集群上限   sethive.exec.max.dynamic.partitions=1000;

开启之后可以利用SQL达到动态插入的目的:

   // 根据分区day动态插入数据   insertintotabledefault.testpartition(day)selectid,dayfromorginal
CTAS

全称CREATE TABLE AS SELECT语句,语法和MYSQL类似,可以指定存储/压缩包等

   // 采用parquet存储已准备配合SparkSQL使用   createtabledefault.parquet_teststoredasparquetasselect*fromdefault.test       
// 同时可以指定压缩格式 createtabledefault.parquet_teststoredasparquet TBLPROPERTIES ('orc.compress'='SNAPPY') asselect*fromdefault.test
// 指定OSS作为存储(推荐) createtabledefault.parquet_teststoredasparquet location'oss:xxx:yyy/parquet/test'

JOIN优化

上面提到了常见的不同HQL类型,实际在执行的HQL中更可能变慢的是JOIN部分,以下会根据不同的场景来分析

大表x小表

这里可以利用mapjoin,SparkSQL中也有mapjoin或者使用广播变量能达到同样效果,此处描述HQL

   // 开启mapjoin并设定map表大小   sethive.auto.convert.join.noconditionaltask =true;   sethive.auto.convert.join.noconditionaltask.size =10000000;   // 大表 join 小表   select*frombig_tablejoinsmall_tableonbig_table.id=small_table.id

原理:将小表加载进入节点容器内存中,大表可以直接读取节点容器内存中的数据进行匹配过滤

大表x大表

小表可以放进内存,大表则不行。尽量避免大表x大表的执行需求。如果确认有此需求,可以参考以下方法

  1.尝试将大右表自我join成为一张宽表

   // 利用右表的唯一属性自我join   selectid,casewhentype='food'then0else1astype_tag,casewhen   sale_type='city'thensaleselsenullassale_amountfromgroupbyid

   2.尝试先将大表按照主键分桶后join

   createtablenew_leftasselect*fromleft_table clusterbyid   createtablenew_rightasselect*fromright_table clusterbyid   select*fromnew_leftjoinnew_rightonnew_left.id=new_right.id

   3.根据数据大小量级合理增加reduce数量,reduce不宜设置过大

   // hadoop1代   setmapred.reduce.tasks=200;   // hadoop2代   setmapreduce.job.reduces=200;

   4.利用ORC bloomfilter, 大幅度提高join效率
      注:parquet bloomfilter在开发中

   // 建立orc表   create tabledefault.right_orc storedasorcfileTBLPROPERTIES   ('orc.compress'='SNAPPY',   'orc.create.index'='true',   'orc.bloom.filter.columns'='id')   asselect*fromright_table   // 使用新表join   select*fromleft_orcjoinright_orconleft_orc.id=righ_orc.id

5.调整内存限制
join时容易造成节点OOM,导致任务失败,可以尝试以下方法:

map阶段OOM,适当增加map阶段内存 set mapreduce.map.memory.mb=3096

reduce阶段OOM,适当增加reduce阶段内存 set mapreduce.reduce.memory.mb=4096

注: 默认执行引擎为mr,如果是TEZ,参考tez优化部分

6.善用explain/analyze

使用explain和analyze分析HQL语句和表,试图从中找出实际数据中可以优化的部分,这里和数据强关联,需要根据实际数据考量

7.数据预处理。

将部分join放入离线计算任务,减少业务join的时间

更多思考

  1. 文件压缩后仍然很大:可以使用GZIP压缩代替SNAPPY,但是性能比SNAPPY差很多

  2. HQL队列拥挤:可以参考队列抢占式资源调度策略,对小任务支持更好

  3. HIVE作为数据仓库/交互式查询的优秀手段之一,是否有更好的计算框架可以替代:EMR SparkSQL可以替代大部分HIVE应用场景,并且3.22版本relational cache带来了极强的性能优化。推荐使用

总结



HIVE本身确实是数据仓库和交互式查询的优秀框架,但随着数据的增多,join的复杂度和性能问题,需要花时间和精力解决性能优化的问题。除了基于HIVE本身优化,还可以接入计算性能更好的框架,SparkSQL relational cache对使用者透明,开发不需要关心底层优化逻辑,可以将更多精力放入业务设计开发中。后续将针对SparkSQL部分提供个人经验供参考。

欢迎对EMR及相关技术感兴趣的同学进钉钉群一起讨论 :)


相关 [hive 优化] 推荐:

hive 优化 tips

- - CSDN博客推荐文章
一、     Hive join优化. 也可以显示声明进行map join:特别适用于小表join大表的时候,SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key. 2.     注意带表分区的join, 如:.

hive优化(2)

- - 开源软件 - ITeye博客
Hive是将符合SQL语法的字符串解析生成可以在Hadoop上执行的MapReduce的工具. 使用Hive尽量按照分布式计算的一些特点来设计sql,和传统关系型数据库有区别,. 所以需要去掉原有关系型数据库下开发的一些固有思维. 1:尽量尽早地过滤数据,减少每个阶段的数据量,对于分区表要加分区,同时只选择需要使用到的字段.

hive优化

- - 开源软件 - ITeye博客
hive.optimize.cp=true:列裁剪. hive.optimize.prunner:分区裁剪. hive.limit.optimize.enable=true:优化LIMIT n语句. hive.limit.optimize.limit.file=10:最大文件数.   1.job的输入数据大小必须小于参数:hive.exec.mode.local.auto.inputbytes.max(默认128MB).

Hive优化

- - 互联网 - ITeye博客
     使用Hive有一段时间了,目前发现需要进行优化的较多出现在出现join、distinct的情况下,而且一般都是reduce过程较慢.      Reduce过程比较慢的现象又可以分为两类:. 情形一:map已经达到100%,而reduce阶段一直是99%,属于数据倾斜. 情形二:使用了count(distinct)或者group by的操作,现象是reduce有进度但是进度缓慢,31%-32%-34%...一个附带的提示是使用reduce个数很可能是1.

hive优化

- - 互联网 - ITeye博客
1:尽量尽早地过滤数据,减少每个阶段的数据量,对于分区表要加分区,同时只选择需要使用到的字段. 2:尽量原子化操作,尽量避免一个SQL包含复杂逻辑. 可以使用中间表来完成复杂的逻辑. 3:单个SQL所起的JOB个数尽量控制在5个以下. 4:慎重使用mapjoin,一般行数小于2000行,大小小于1M(扩容后可以适当放大)的表才能使用,小表要注意放在join的左边(目前TCL里面很多都小表放在join的右边).

Hive优化总结

- - 淘剑笑的博客
优化时,把hive sql 当做map reduce 程序来读,会有意想不到的惊喜. 理解hadoop 的核心能力,是hive 优化的根本. 这是这一年来,项目组所有成员宝贵的经验总结. 长期观察hadoop处理数据的过程,有几个显著的特征 :. 1.不怕数据多,就怕数据倾斜. 2.对jobs数比较多的作业运行效率相对比较低,比如即使有几百行的表,如果多次关联多次汇总,产生十几个jobs,没半小时是跑不完的.

HIVE 优化浅谈

- - IT瘾-dev
作者:邓力,entobit技术总监,八年大数据从业经历,由一代HADOOP入坑,深耕云计算应用领域,由从事亚马逊EMR和阿里云EMR应用开发逐步转入大数据架构领域,对大数据生态及框架应用有深刻理解. 随着商务/运营同学执行的HQL越来越多,整体HIVE执行效率变低,本文从HIVE切入,分析HQL面临的问题和待优化部分,结合其他大数据框架来解决实际问题.

Hive Join 优化 翻译

- - 数据库 - ITeye博客
翻译自  https://cwiki.apache.org/confluence/display/Hive/LanguageManual+JoinOptimization#LanguageManualJoinOptimization-AutoConversiontoSMBMapJoin. Join Optimization ----Join 调优.

hive优化要点总结

- - CSDN博客云计算推荐文章
1、让服务器尽可能的多做事情,榨干服务器资源,以最高系统吞吐量为目标. 再好的硬件没有充分利用起来,都是白扯淡. (1)  启动一次job尽可能的多做事情,一个job能完成的事情,不要两个job来做.  通常来说前面的任务启动可以稍带一起做的事情就一起做了,以便后续的多个任务重用,与此紧密相连的是模型设计,好的模型特别重要..

Hive作业优化总结

- - 开源软件 - ITeye博客
一、Hadoop 计算框架的特性. 4、设置合理reducer个数. 5、合并MapReduce操作. 一、Hadoop 计算框架的特性. •由于数据分布不均匀,造成数据大量的集中到一点,造成数据热点. 2、Hadoop框架的特性. •不怕数据大,怕数据倾斜. •jobs数比较多的作业运行效率相对比较低,如子查询比较多.