hive sql 调优相关总结

代码规范调优

1、在代码中经常使用到一些跑出来的中间表,又不想手动创建,使用with xxxtable as ()创建临时中间表。复杂的逻辑也可以尝试建中间表。

2、少用select * 没有必要的数据列不要查询出来,消耗资源,增加io压力。尽量尽早地过滤数据,减少每个阶 段的数据量,对于分区表要加分区,同时只选择需要使用到的字段


3、做一些分组统计时,经常要用到不同维度下面的汇总,可以用group by with cube,效率比union all 汇总快2 3倍。

4、Order by(全局排序,一个reducer) 效率比Sort by(不是全局排序,其在数据进入reducer前完成排序)低,窗口函数使用:row_number()over(distribute BY uid sort by dt desc)row_number()over(distribute BY uid sort by dt desc)


5、尽可能减少数据集,做关联时避免出现过多的笛卡尔数据集。

6、经常反复使用的表可以考虑建中间表。
7、避免使用count(distinct)操作, 解决方法:先使用group by去重,再count计算。尽可能用group by 代替distinct

8、join时小表放在左边,大表放在右边?两张数据量相差特别大的表进行join操作时,如果小表特别小(比如几百几千条记录),使用mapjoin.

9、如果union all的部分个数大于2,或者每个union部分数据量大,应该拆成多个insert into 语句,实际测试过程中,执行时间能提升50%

insert overwite table tablename partition (dt= ....)

select ..... from (

                   select ... from A

                   union all

                   select ... from B

                   union all

                   select ... from C

                               ) R

where ...;

 

可以改写为:

insert into table tablename partition (dt= ....)

select .... from A

WHERE ...;

 

insert into table tablename partition (dt= ....)

select .... from B

WHERE ...;

 

insert into table tablename partition (dt= ....)

select .... from C

WHERE ...; 

set 参数调优:

1、Load local 文件和insert table时

压缩能减少hdfs 的负担和MR 时的磁盘IO,如果压缩不支持分块,MR 时效率较慢和增加网络IO

机器io开销很大,但是机器的cpu开销较小,另外map输出文件也较大,怎么办?

解决办法:通过设置map的中间输出进行压缩就可以了,这个不会影响最终reduce的输出,如下是设置使用gzip来进行压缩

set mapred.output.compress=true;

set hive.exec.compress.output=true;

set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;


SET mapred.output.compression.type=BLOCK; -- 压缩类型


2、跑数时使用一些效率高的队列和较高优先级,当然这个是消耗资源的。

set mapred.job.queue.name=队列名称;

set mapred.job.priority=VERY_HIGH;



3、建表时某些场景还可以可以使用动态分区和分桶。


set hive.enforce.sorting=true;

set hive.enforce.bucketing=true;

set hive.partition.query.strict=true;

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;


set mapred.output.compress=true;

set hive.exec.compress.output=true;

set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;

set mapred.job.queue.name=队列名称;

set mapred.job.priority=VERY_HIGH;


CREATE TABLE if not exists xxxtable (

uid BIGINT,

topsid INT

)

PARTITIONED BY (dt STRING)

clustered by (uid) sorted by (uid) into 128 buckets

STORED AS RCFILE;

insert overwrite table ts_pc_channel_day partition (dt)

select from table


4、Map Join开启


我们可以启用连接自动转换来帮助我们转换,在执行语句之前设置一下即可。它是经过优化的Map Join,无reducer。

set hive.auto.convert.join=true;


set hive.auto.convert.join.noconditionaltask = true;


set hive.auto.convert.join.noconditionaltask.size = 10000;


5、数据倾斜调优(数据量大不怕,就怕就是数据倾斜。)尽量尽早地过滤数据,减少每个阶 段的数据量,对于分区表要加分区,同时只选择需要使用到的字段

set hive.groupby.skewindata=true;--控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce做次预汇总,减少某些key值条数过多某些key条数过小造成的数据倾斜问题


set hive.optimize.skewjoin=true; --如果是join 过程出现倾斜 应该设置为true

通过hive.map.aggr = true(默认为true)在Map端做combiner,假如map各条数据基本上不一样, 聚合没什么意义,做combiner反而画蛇添足,hive里也考虑的比较周到通过参数hive.groupby.mapaggr.checkinterval = 100000 (默认)hive.map.aggr.hash.min.reduction=0.5(默认),预先取100000条数据聚合,如果聚合后的条数/100000>0.5,则不再聚合

例子:查询差不多一年的用户,和使用过的app

--table_xxxxxx每天50G左右(大概10分钟内跑完)
set hive.groupby.skewindata=true;
set mapred.reduce.tasks=500;
SELECT uid,app FROM  table_xxxxxx WHERE dt>=20161001 and dt<20161101 and app in ('yylove_appid','yylove-android') and uid>0 group by uid,app


6、注意小文件的问题,我通过以下方法来在map执行前合并小文件,减少map数.在hive里有两种比较常见的处理办法:

第一是使用Combinefileinputformat,将多个小文件打包作为一个整体的inputsplit,减少map任务数

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

set mapreduce.input.fileinputformat.split.minsize=134217728;

set mapreduce.input.fileinputformat.split.maxsize=134217728;

set mapreduce.input.fileinputformat.split.minsize.per.node=134217728;

set mapreduce.input.fileinputformat.split.minsize.per.rack=134217728;



第二是设置hive参数,将额外启动一个MR Job打包小文件

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


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



增加map数:

set mapred.reduce.tasks=100;

如果表a只有一个文件,大小为120M,但包含几千万的记录,如果用1个map去完成这个任务,肯定是比较耗时的,这种情况下,我们要考虑将这一个文件合理的拆分成多个, 这样就可以用多个map任务去完成。



7、

并发执行:

hive.exec.parallel=true ,默认为false

hive.exec.parallel.thread.number=8



*************************************************************************



hive.optimize.cp=true:列裁剪

hive.optimize.prunner:分区裁剪

hive.limit.optimize.enable=true:优化LIMIT n语句

hive.limit.row.max.size=1000000:

hive.limit.optimize.limit.file=10:最大文件数


1. 本地模式(小任务):

需要满足以下条件:

  1.job的输入数据大小必须小于参数:hive.exec.mode.local.auto.inputbytes.max(默认128MB)

  2.job的map数必须小于参数:hive.exec.mode.local.auto.tasks.max(默认4)

  3.job的reduce数必须为0或者1

hive.exec.mode.local.auto.inputbytes.max=134217728

hive.exec.mode.local.auto.tasks.max=4

hive.exec.mode.local.auto=true

hive.mapred.local.mem:本地模式启动的JVM内存大小


2. 并发执行:

hive.exec.parallel=true ,默认为false

hive.exec.parallel.thread.number=8


3.Strict Mode:

hive.mapred.mode=true,严格模式不允许执行以下查询:

分区表上没有指定了分区

没有limit限制的order by语句

笛卡尔积:JOIN时没有ON语句


4.动态分区:

hive.exec.dynamic.partition.mode=strict:该模式下必须指定一个静态分区

hive.exec.max.dynamic.partitions=1000

hive.exec.max.dynamic.partitions.pernode=100:在每一个mapper/reducer节点允许创建的最大分区数

DATANODE:dfs.datanode.max.xceivers=8192:允许DATANODE打开多少个文件


5.推测执行:

mapred.map.tasks.speculative.execution=true

mapred.reduce.tasks.speculative.execution=true

hive.mapred.reduce.tasks.speculative.execution=true;


6.Single MapReduce MultiGROUP BY

hive.multigroupby.singlemar=true:当多个GROUP BY语句有相同的分组列,则会优化为一个MR任务


7. hive.exec.rowoffset:是否提供虚拟列


8. 分组

两个聚集函数不能有不同的DISTINCT列,以下表达式是错误的:

INSERT OVERWRITE TABLE pv_gender_agg SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)

FROM pv_users GROUP BY pv_users.gender;

SELECT语句中只能有GROUP BY的列或者聚集函数。


9.

hive.map.aggr=true;在map中会做部分聚集操作,效率更高但需要更多的内存。

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


10.

hive.groupby.skewindata=true:数据倾斜时负载均衡,当选项设定为true,生成的查询计划会有两个MRJob。第一个MRJob 中,

Map的输出结果集合会随机分布到Reduce中,每个Reduce做部分聚合操作,并输出结果,这样处理的结果是相同的GroupBy Key

有可能被分发到不同的Reduce中,从而达到负载均衡的目的;第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到

Reduce中(这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中),最后完成最终的聚合操作。


11.Multi-Group-By Inserts:

FROM test

INSERT OVERWRITE TABLE count1

SELECT count(DISTINCT test.dqcode)

GROUP BY test.zipcode

INSERT OVERWRITE TABLE count2

SELECT count(DISTINCT test.dqcode)

GROUP BY test.sfcode;


12.排序

ORDER BY colName ASC/DESC

hive.mapred.mode=strict时需要跟limit子句

hive.mapred.mode=nonstrict时使用单个reduce完成排序

SORT BY colName ASC/DESC :每个reduce内排序

DISTRIBUTE BY(子查询情况下使用 ):控制特定行应该到哪个reducer,并不保证reduce内数据的顺序

CLUSTER BY :当SORT BY 、DISTRIBUTE BY使用相同的列时。



13.合并小文件

hive.merg.mapfiles=true:合并map输出

hive.merge.mapredfiles=false:合并reduce输出

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

hive.mergejob.maponly=true:如果支持CombineHiveInputFormat则生成只有Map的任务执行merge

hive.merge.smallfiles.avgsize=16000000:文件的平均大小小于该值时,会启动一个MR任务执行merge。



14.map/reduce数目

减少map数目:

  set mapred.max.split.size

  set mapred.min.split.size

  set mapred.min.split.size.per.node

  set mapred.min.split.size.per.rack

  set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

增加map数目:

当input的文件都很大,任务逻辑复杂,map执行非常慢的时候,可以考虑增加Map数,来使得每个map处理的数据量减少,从而提高任务的执行效率。

假设有这样一个任务:

  select data_desc, count(1), count(distinct id),sum(case when …),sum(case when ...),sum(…) from a group by data_desc

如果表a只有一个文件,大小为120M,但包含几千万的记录,如果用1个map去完成这个任务,肯定是比较耗时的,这种情况下,我们要考虑将这一个文件合理的拆分成多个,这样就可以用多个map任务去完成。

  set mapred.reduce.tasks=10;

  create table a_1 as select * from a distribute by rand(123);

这样会将a表的记录,随机的分散到包含10个文件的a_1表中,再用a_1代替上面sql中的a表,则会用10个map任务去完成。每个map任务处理大于12M(几百万记录)的数据,效率肯定会好很多。


reduce数目设置:

 参数1:hive.exec.reducers.bytes.per.reducer=1G:每个reduce任务处理的数据量

 参数2:hive.exec.reducers.max=999(0.95*TaskTracker数):每个任务最大的reduce数目

 reducer数=min(参数2,总输入数据量/参数1)

 set mapred.reduce.tasks:每个任务默认的reduce数目。典型为0.99*reduce槽数,hive将其设置为-1,自动确定reduce数目。



15.使用索引:

hive.optimize.index.filter:自动使用索引

hive.optimize.index.groupby:使用聚合索引优化GROUP BY操作


*********************************************************************************************

判断某些字符串中是否存在列表中,如‘’123‘’ 是否存在 ‘123’,‘456’当中array_contains(split(b.gifttype,','),cast(a.goodstype as string))


参考:hive的查询注意事项以及优化总结 .

编辑于 2016-11-08