sql语句大全之hiveSQL优化思路

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之hiveSQL优化思路

HiveSQL执行流程：InputFormat、OutputFormat、SerDe理清这三者之间的关系：SerDe is a short name for "Serializer and Deserializer."；Hive uses SerDe (and !FileFormat) to read and write table rows.

读数据过程：HDFS files –> InputFileFormat(把文件切成不同的文档，每篇文档为一行row) –> <key, value> –> Deserializer(一行切分为各个字段) –> Row object

写数据过程：Row object –> Serializer –> <key, value> –> OutputFileFormat –> HDFS files

主要涉及到数据存储和计算两个过程，设计出合理的数据存储格式对于数据的查询和计算具有很重要的意义。存储的优化思想就是查询数据时能够很快定位到需要的数据，通过索引技术或者缩小检索数据范围来解决；传统数据库领域通过采用索引技术来优化数据的存储达到高效检索访问，在hive数仓技术中也有索引技术，但是，最常用的技术是分区和分桶技术。

表存储思想：根据表字段变更的频率进行水平拆分、垂直拆分，水平拆分采用的技术手段：分区、分桶，其中分区是虚拟列，只是HDFS上的一个文件夹，分桶是实际存在的列，在分区基础上对数据进行更细粒度的划分，无论分区还是分桶技术都是减少检索的数据量，达到有针对性的查询数据；当数据分区之后数据量还是比较大时，可以充分考虑分桶技术，如果两个表的分桶关键字段相关联，且两张关联表分出桶的个数成倍数，则查询时仅仅过滤对应的桶即可。分桶技术在基于大数据技术构建数仓模型实践一篇博客详细介绍。

存储压缩技术推荐使用orc文件格式，因为压缩率比较高(节省大量的存储资源)，且能支持hive的行级更新；

shuffle优化思路：当采用cross join时，会生成一个Reduce进程，Reduce优化思路：1)可以调整大set mapred.reduce.parallel.copies=20;从map端拉取线程数量；2)Reduce内存大小mapreduce.reduce.memory.mb=3072

优化一览表：

hive查询操作优化

-------group by 优化---------

set hive.groupby.skewindata=true 如果是group by过程出现倾斜应该设置为true;

set hive.groupby.mapaggr.checkintenval=1000000; 这个是group的键对应的记录条数超过这个值则会进行优化

-----------join优化-------------------

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

set hive.skewjoin.key=1000000;--这个是join的键对应的记录条数超过这个值则会进行优化

-----------mapjoin-----------------

当表小于25mb的时候，小表自动注入内存

set hive.auto.convert.join=true;

set hive.mapjoin.smalltable.filesize=25mb; 默认值是25mb

----------Hive 表优化--------------

动态分区启用参数

set hive.exec.dynamic.partition=true;

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

分桶启用参数

set hive.enforce.bucketing = true;

set hive.enforce.sorting = true;

----------Hive job优化-----------------------------

----------并行化执行

每个查询被hive转化成多个阶段，有些阶段关联性不大，则可以并行化执行，减少执行时间

set hive.exec.parallel = true;

set hive.exec.parallel.thread.numbe=8;

本地化执行

set hive.exec.model.local.auto = true;

当一个job满足如下条件才能真正使用本地模式:

1、job的输入数据大小必须小于参数：

set hive.exec.mode.local.auto.inputbytes.max=128mb;（默认128MB）

2、job的map数必须小于参数:

set hive.exec.mode.local.auto.tasks.max(默认4)

3、job的reduce数必须为o或者1

------------------------------------------------

job合并输入小文件

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

合并文件数由mapred.max.split.size限制的大小决定

job合并输出小文件

set hive.merge.smallfiles.avgsize=256000000;当输出文件平均大小小于该值，启动新job合并文件

set hive.merge.size.per.task=64000000;合并之后的文件大小

-------------------------------------------

JVM重利用

set mapred.job.reuse.jvm.num.tasks=20;

JVM重利用可以是JOB长时间保留slot，直到作业结束，这在对于有较多任务和较多小文件的任务是非常有意义的，减少执行时间。当然这个值不能设置过大，因为

有些作业会有reduce任务，如果reduce任务没有完成，则map任务占用的slot不能释放，其他的作业可能就需要等待。

---------------------------------------------

压缩数据

中间压缩就是处理hive查询的多个job之间的数据，对于中间压缩，最好选择一个节省CPU耗时的压缩方式

set hive.exec.compress.intermediate = true;

set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

set hive.intermediate.compression.type=BLOCK;

hive查询最终的输出也可以压缩

set hive.exec.compress.output=true;

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

set mapred.output.compression.type=BLOCK;

-------------------------------------------------

set mapred.map.tasks=10;

(1)默认map个数

default_num = total_size/block_size;

(2)期望大小

goal_num=mapred.map.tasks;

(3)设置处理的文件大小

split_size=max(mapred.min.split.size,block_size);

split_num=total_size/split_size;

(4)计算的map个数

compute_map_num = min(split_num,max(default_num,goal_num))

--------------------------------------------

Hive Map 优化

经过以上的分析，在设置map个数的时候，可以简单的总结为以下几点：

(1)如果想增加map个数，则设置mapred.map.tasks为一个较大的值。

(2)如果想减少map个数，则设置mapred.min.split.size为一个较大的值。

情况1：输入文件size巨大，但不是小文件

增大mapred.min.split.size的值

情况2:输入文件数量巨大，且都是小文件，就是单个文件的size小于blockSize。这种情况通过增大mapred.min.split.size不可行，需要使用CombineFileInputFormat将多个input path合并成一个

InputSplit送给mapper处理，从而减少mapper的数量。

--------------------------------------

map端聚合

set hive.map.aggr=true;

推测执行

mapred.map.tasks.speculative.execution

--------Hive Shuffle优化-------------------------------

Map端

io.sort.mb

io.sort.spill.percent

min.num.spill.for.combine

io.sort.factor

io.sort.record.percent

Reduce端

mapred.reduce.parallel.copies

mapred.reduce.copy.backoff

io.sort.factor

mapred.job.shuffle.input.buffer.percent

mapred.job.reduce.input.buffer.percent

--------------Hive Reduce优化------------

需要reduce操作的查询

聚合函数

sum,count,distinct...

高级查询

group by,join,distribute by,cluster by ...

order by 比较特殊，只需要一个reduce

推测执行

mapred.reduce.tasks.speculative.execution

hive.mapred.reduce.tasks.speculative.execution

Reduce 优化

set mapred.reduce.tasks=10;直接设置

hive.exec.reducers.max

hive.exec.reducers.bytes.per.reducer 默认:1G

计算公式

numRTasks = min[maxReducers,input.size/perReducer]

maxReducers = hive.exec.reducers.max

perReducer = hive.exec.reducers.bytes.per.reducer

Hive text+gz压缩格式

set hive.exec.compress.output=true;

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

map数据量调整：

set mapred.max.split.size=536870912 ;

set mapred.min.split.size=536870912 ;

set mapred.min.split.size.per.node=536870912 ;

set mapred.min.split.size.per.rack=536870912 ;

数据倾斜平时优化的重点：

1.小表不小不大，怎么用 map join 解决倾斜问题

使用 map join 解决小表(记录数少)关联大表的数据倾斜问题，这个方法使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理。

解决如下：

select * from log a

left outer join users b

on a.user_id = b.user_id;

users 表有 600w+ 的记录，把 users 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。

解决方法：

select /*+mapjoin(x)*/* from log a

left outer join (

select /*+mapjoin(c)*/d.*

from ( select distinct user_id from log ) c

join users d

on c.user_id = d.user_id

) x

on a.user_id = b.user_id;

假如，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。

所以这个方法能解决很多场景下的数据倾斜问题。

2.count distinct关键字优化：set mapred.reduce.tasks=3;

select count(1) from (select distinct city from info) tmp;

3. 聚合函数组语句优化手段：

select a,sum(b),count(distinct c),count(distinct d) from test

group by a;

select a,sum(b) as b,count(c) as c,count(d) as d from (

select a,0 as b,c,null as d from test group by a,c

union all

select a,0 as b,null as c,d from test group bu a,d

union all

select a,b,null as c, null as d from test

)

tmp1 group by a;

4.空值产生的数据倾斜

场景：

如日志中，常会有信息丢失的问题，比如日志中的user_id，如果取其中的user_id和用户表中的user_id 关联，会碰到数据倾斜的问题。

解决方法1： user_id为空的不参与关联

select * from log a

join users b

on a.user_id is not null

and a.user_id = b.user_id

union all

select * from log a

where a.user_id is null;

解决方法2 ：赋与空值新的key值

select * from log a left outer join users b on case when a.user_id is null then concat('hive',rand() )

else a.user_id end = b.user_id;

结论：

方法2比方法1效率更好，不但io少了，而且作业数也少了。

解决方法1中 log读取两次，job是2。

解决方法2中 job数是1 。这个优化适合无效 id (比如 -99 ,'', null 等) 产生的倾斜问题。把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上，解决数据倾斜问题。

5.不同数据类型关联产生数据倾斜

场景：用户表中user_id字段为int，log表中user_id字段既有string类型也有int类型。当按照user_id进行两个表的Join操作时，默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。

解决方法：把数字类型转换成字符串类型

select * from users a

left outer join logs b

on a.usr_id = cast(b.user_id as string);

1控制map数量需要遵循两个原则：使大数据量利用合适的map数；使单个map任务处理合适的数据量

1.1作业会通过input的目录产生一个或者多个map任务。

主要的决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小(目前为128M, 可在hive中通过set dfs.block.size;命令查看到，该参数不能自定义修改)；

假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数

假设input目录下有3个文件a,b,c,大小分别为10m，20m，130m，那么hadoop会分隔成4个块（10m,20m,128m,2m）,从而产生4个map数

1.2如果一个任务有很多小文件（远远小于块大小128m）,则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。

1.3比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

实例：合并小文件减少map数

假设一个SQL任务：Select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’;

该任务的inputdir /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04共有194个文件，其中很多是远远小于128m的小文件，总大小9G，正常执行会用194个map任务。

以下方法来在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; 表示执行前进行小文件合并

再执行上面的语句，用了74个map任务

对于这个简单SQL任务，执行时间上可能差不多，但节省了一半的计算资源。

前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），进行合并,最终生成了74个块。

增加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（几百万记录）的数据，效率肯定会好很多。

2.控制hive任务的reduce数，考虑这两个原则：使大数据量利用合适的reduce数；使单个reduce任务处理合适的数据量：

2.1.确定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任务；

如：select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;

/group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04 总大小为9G多，因此这句有10个reduce

2.2调整reduce个数方法一：

调整hive.exec.reducers.bytes.per.reducer参数的值；

set hive.exec.reducers.bytes.per.reducer=500000000; （500M）

select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 这次有20个reduce

2.3.调整reduce个数方法二；

set mapred.reduce.tasks = 15;

select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;这次有15个reduce

2.4.reduce个数并不是越多越好；

同map一样，启动和初始化reduce也会消耗时间和资源；另外，有多少个reduce,就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

2.5.只有一个reduce的情况；

除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：

2.5.1.没有group by的汇总，比如把select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 写成 select count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04';这点非常常见，希望大家尽量改写。

2.5.2用了Order by

2.5.3有笛卡尔积

因为这些操作都是全局的，所以hadoop不得不用一个reduce去完成；

---------------------

作者：andyliuzhii

来源：CSDN

原文：https://blog.csdn.net/andyliuzhii/article/details/79871782

栏目列表