HIVE优化提示-如何写好HQL

标签: hive 优化 hql | 发表时间:2014-08-16 01:24 | 作者:today20080808
出处:http://blog.csdn.net
一、     Hive join优化
1.     尽量将小表放在join的左边,我们这边使用的hive-0.12.0,所以是自动转化的,既把小表自动装入内存,执行map side join(性能好), 这是由参数hive.auto.convert.join=true 和hive.smalltable.filesize=25000000L)参数控制(默认是25M),如果表文件大小在25M左右,可以适当调整此参数,进行map side join,避免reduce side join。 也可以显示声明进行map join:特别适用于小表join大表的时候,SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key
2.     注意带表分区的join, 如:
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.dt='2014-08-07' AND b.dt='2014-08-07'
因为hive是先join再where的,所以如果在b中找不到a表的记录,b表中的所以列都会列出null,包括ds列,这样left outer的查询结果与where子句无关了,解决办法:
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND a.dt='2014-08-07' AND b.dt='2014-08-07'')
3.     怎样写exist/in子句?
Hive不支持where子句中的子查询,SQL常用的exist in子句需要改写。这一改写相对简单。考虑以下SQL查询语句:
SELECT a.key, a.value  FROM a  WHERE a.key in  (SELECT b.key  FROM B);
可以改写为
SELECT a.key, a.value  FROM a LEFT OUTER JOIN b ON (a.key = b.key)  WHERE b.key <> NULL;
一个更高效的实现是利用left semi join改写为:
SELECT a.key, a.val  FROM a LEFT SEMI JOIN b on (a.key = b.key);
4.     Hive join只支持等值连接,不支持非等值连接。
5.     合理的使用map join, 场合:小表A join 大表,
二、     合理设置map与reduce的个数。
1、如何合并小文件,减少map数?
如果一个表中的map数特别多,可能是由于文件个数特别多,而且文件特别小照成的,可以进行如下操作,合并文件,:
   set mapred.max.split.size=100000000; // 100M
  set mapred.min.split.size.per.node=100000000;
   set mapred.min.split.size.per.rack=100000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; //  合并小文件
2、如何适当的增加map数?
如果表A只有一个文件,大小为120M,包含几千万记录,可以考虑用多个map任务完成
set mapred.reduce.tasks=10;
                   create table a_1 as
                   select * from a
                   distribute by rand(123); //将a表的记录,随机的分散到包含10个文件的a_1表中
3、hive如何确定reduce数, reduce的个数基于以下参数设定:
hive.exec.reducers.bytes.per.reducer(每个reduce任务处理的数据量,默认为1000^3=1G)
hive.exec.reducers.max(每个任务最大的reduce数,默认为999)
计算reducer数的公式很简单N=min(参数2,总输入数据量/参数1)
即,如果reduce的输入(map的输出)总大小不超过1G,那么只会有一个reduce任务;所以调整以下参数:
set hive.exec.reducers.bytes.per.reducer=500000000; (500M)
set mapred.reduce.tasks = 15;
三、     如果设计和使用bucket,
Buckets 对指定列计算 hash,根据 hash 值切分数据,目的是为了并行,每一个 Bucket 对应一个文件。 将 user 列分散至 32 个 bucket, 首先对 user 列的值计算 hash,对应 hash 值为 0 的 HDFS 目录为:/wh/pvs/dt=2014-08-01/ctry=US/part-00000; hash 值为 20 的 HDFS 目录为:/wh/pvs/dt=2014-08-01/ctry=US/part-00020
所用场合:对某一列进行分区,比如对用户ID进行分区,例如:
CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)
> PARTITIONED BY (dt STRING)
> CLUSTERED BY (user_id) INTO 96 BUCKETS; // 按照日期分区后,再按照user_id把日志放在96个篮子里。插入数据的时候:
hive> SET hive.enforce.bucketing = true;
hive> FROM raw_logs
> INSERT OVERWRITE TABLE weblog
> PARTITION (dt='2009-02-25')
> SELECT user_id, url, source_ip WHERE dt='2009-02-25'
四、     Count(distinct)
当count distinct 的记录非常多的时候,设置以下两个参数:
hive>  hive.map.aggr = true
hive> set hive.groupby.skewindata=true;
hive>  select count (distinct gid) from cookie_label_summary where i_date=20130924;
五、     Group by
Group By的方法是在reduce做一些操作,这样会导致两个问题:
map端聚合,提前一部分计算:hive.map.aggr = true 同时设置间隔:hive.groupby.mapaggr.checkinterval
均衡处理:hive.groupby.skewindata
这是针对数据倾斜的,设为ture的时候,任务的reduce会把原来一个job拆分成两个,第一个的job中reduce处理处理不同的随即分发过来的key的数据,生成中间结果,再由最后一个综合处理。
六、     Order by, Sort by ,Dristribute by,Cluster By
1、     order by VS Sort by: order by是在全局的排序,只用一个reduce去跑,所以在set hive.mapred.mode=strict 模式下,order by 必须limit,否则报错。Sort by只保证同一个reduce下排序正确。
2、     Distribute by with sort by: Distribute by 是按指定的列把map 输出结果分配到reduce里。所以经常和sort by 来实现对某一字段的相同值分配到同一个reduce排序。
3、     Cluster by 实现了Distribute by+ sort by 的功能
作者:today20080808 发表于2014-8-15 17:24:07 原文链接
阅读:0 评论:0 查看评论

相关 [hive 优化 hql] 推荐:

HIVE优化提示-如何写好HQL

- - 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 优化 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来做.  通常来说前面的任务启动可以稍带一起做的事情就一起做了,以便后续的多个任务重用,与此紧密相连的是模型设计,好的模型特别重要..