hivesql优化（hivesql优化常用的15种方法）

by intanet.cn ca 数据库 on 2024-03-18

本篇文章给大家谈谈hivesql优化，以及hivesql优化常用的15种方法对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

1、Hive Sql优化集(on where过滤)
2、Hive优化
3、数据分析课程笔记 - 19 - HiveSQL 常用优化技巧
4、hive优化方法
5、hive sql 优化的常用手段有哪些

Hive Sql优化集(on where过滤)

分析：这三个sql区别就燃运在于log表和a表的过滤条件是在where字句上还是on字句上。

sql1的结果：

a表和log表的所有数据做连接，只是在a表皮如梁的dt!='2017-09-17'记录上a表上所有字段都为空

sql2的结果：

a表和log表的所有数据先做连接，然后过滤出两个表只在'2017-09-17'分区上的数据

sql3的结果：

a表和log表分别查询出'2017-09-17'分区上数据，两个小数据集做关联，结果和sql2的相同

sql3的执行效率最高，推荐使用；sql2的效率较低，因为需要全表关联；sql1有bug，不建议直接在on中使用过滤条件

但是注意不要将where过滤条件放到on中，除非你很了解SQL执行后的结果；另外不要将on连接橡返条件放到where中，hive并不会像mysq那样做连接优化，这样会导致不可控的情况

[img]

Hive优化

注意:以下SQL不会转为Mapreduce来执行,Explain用于显示执行计划,可以来验证sql是否发生mapreduce

select仅查询本表字段;

where仅对本表字段做条件过滤;

比如下面的语句是会发生mapreduce的;(下面的reduce没有截图)

(1)集群模式:hive默认采用的是集群的方式;

(2)本地模式:首先开启本地模式,测试的时候就可以以本地模式来节省集群资源;

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

注意：hive.exec.mode.local.auto.inputbytes.max默认值为128M表示加载文件的最大值，若大于该配置仍会以集群方式来运行;

通过设置以下参数开启并行模式：默认是不开启并行计算,这是job是线性执行的;

set hive.exec.parallel=true;多个job并行执行提高效率;

注意：hive.exec.parallel.thread.number（一次SQL计算中允许并行执行的job个信弊数的最大值）;

通过设置以下参数开启严格模式：

set hive.mapred.mode=strict;（默认为：nonstrict非严格模式）

开启严格模式后,查询会有限制：

(1)对于分区表，必须添加where对于分区字段的条件过滤,因为hive中的数据量一般都很大,避免全表扫描不添加会执行失败,非分区表正常查询;

(2)order by语句必须包含limit输出限制；还是为了避免全表扫描

(3)限制执行笛卡尔积的查询。

(1)Order By - 对于查询结果做全排序，只允汪坦前许有一个reduce处理（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）;

(2)Sort By - 对于单个reduce的数据进行排序

(3)Distribute By - 分区排序，经常和Sort By结合使用

(4)Cluster By - 相当于 Sort By +Distribute By（Cluster By不能通过asc、desc的方式指定排序规则；可通过 distribute by column sort by column asc|desc 的方式）

Join计算时，将小表（驱动表）放在join的左边

Map Join：在Map端完成Join,join操作对应mapreduce是reduce阶段,因为shuffle,跟reduce阶段比较浪困清费时间,所以才有了map join;

两种实现方式：

(1)SQL方式，在SQL语句中添加MapJoin标记（mapjoin hint）

(2)开启自动mapjoin : 通过修改以下配置启用自动的map join：

set hive.auto.convert.join = true;（该参数为true时，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表使用Map

join）

数据分析课程笔记 - 19 - HiveSQL 常用优化技巧

大家好呀，这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据，运行过程由于通常要经过 MapReduce 的过程，因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的，因此为了减少等待的时间，提高服务器的运行效率，我们需要在 HiveSQL 的语句上进行一些优化。

本节课的主要内容：

引言

1、技巧一：列裁剪和分区裁剪

（1）列裁剪

（2）分区裁剪

2、技巧二：排序技巧——sort by代替order by

3、技巧三：去重技巧——用group by来替换distinct

4、技巧四：聚合技巧——grouping sets、cube、蚂森rollup

（1）grouping sets

（2）cube

（3）rollup

5、技巧五：换个思路解题

6、技巧六：union all时可以开启并发执行

7、技巧七：表连接优化

8、技巧八：遵循严格模式

Hive 作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别敬带注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job 或 I/O 过多、MapReduce 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化，也包含 Hive 配置项和 MR 方面的调整。

列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hive.optimize.cp ，默认是 true 。

分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是 hive.optimize.pruner ，默认是 true 。

HiveSQL中的 order by 与其他 SQL 语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个 reduce 中，在数据量大时可能会长时间计算不完。

如果使用 sort by ，那么就会视情况启动多个 reducer 进行排序，并且保证每个 reducer 内局部有序。为了控制 map 端数据分配到 reduce 的 key，往往还要配合 distribute by 一同使用。如果不加 distribute by 的话，map 端数据就会随机分配给 reducer。

这里需要解释一下， distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。

假如我们要对一张很大的用户信息表按照年龄进行分组，优化前的写法是直接 order by age 。使用 distribute by 和 sort by 结合进行优化的时候， sort by 后面还是 age 这个排序字段， distribute by 后面选择一个没有重复值的均匀字段，比如 user_id 。

这样做的原因是，通常用户的年龄分布是不均匀的，比如20岁以下和50岁以上的人非常少，中间几个年龄段的人又非常多，在 Map 阶段就会造成有些任务很大，有些任务很小。那通过 distribute by 一个均匀字段，就可以让系统均匀地进行“分桶”，对每个桶进行排序闷稿亩，最后再组合，这样就能从整体上提升 MapReduce 的效率。

取出 user_trade 表中全部支付用户：

原有写法的执行时长：

优化写法的执行时长：

考虑对之前的案例进行优化：

注意：在极大的数据量（且很多重复值）时，可以先 group by 去重，再 count() 计数，效率高于直接 count(distinct **) 。

如果我们想知道用户的性别分布、城市分布、等级分布，你会怎么写？

通常写法：

缺点：要分别写三次SQL，需要执行三次，重复工作，且费时。

那该怎么优化呢？

注意：这个聚合结果相当于纵向地堆在一起了（Union all），分类字段用不同列来进行区分，也就是每一行数据都包含 4 列，前三列是分类字段，最后一列是聚合计算的结果。

GROUPING SETS() ：在 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。

如果我们想知道用户的性别分布以及每个性别的城市分布，你会怎么写？

那该怎么优化呢？

注意：第二列为NULL的，就是性别的用户分布，其余有城市的均为每个性别的城市分布。

cube：根据 group by 维度的所有组合进行聚合

注意：跑完数据后，整理很关键！！！

rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。

如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎么办？

那应该如何优化呢？

条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学会思路转换，灵活应用。

来看一个我们之前做过的案例：

有没有别的写法呢？

Hive 中互相没有依赖关系的 job 间是可以并行执行的，最典型的就是

多个子查询union all。在集群资源相对充足的情况下，可以开启并

行执行。参数设置： set hive.exec.parallel=true;

时间对比：

所谓严格模式，就是强制不允许用户执行3种有风险的 HiveSQL 语句，一旦执行会直接报错。

要开启严格模式，需要将参数 hive.mapred.mode 设为 strict 。

好啦，这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句，不断改进，就能掌握最优的写法。

hive优化方法

1、列裁剪和分区裁剪

2、谓词下推

3、sort by 替换order by

4、group by 代替distinct

5、group by 配置调整

map 端预聚合：set hive.map.aggr=true ; set hive.groupby.mapaggr.checkinterval=100000

倾斜均衡配置项：set hive.groupby.skewindate=true

6、join优化

6.1 大小表，小表前置

6.2 多表Join时key相同

6.3 利用mapjoin特性

6.4 分桶表 mapjoin

6.5 倾斜均衡配置项：

设置hive.optimize.skewjoin=true，开启后，在join过程中hive会将计数超过阈值hive.skewjoin.key(默认100000)的倾斜key对应的行临时写进文件中，然后再启动另一个job做map join生成结果。通过hive.skewjoin.mapjoin.map.task参数还可以控制第二个job的mapper数量，默认10000.

6.7 优化sql处理join数据倾斜

6.7.1、空值或无意义的值：若不需要空值数据，就提前写到where语句过滤掉，如果需要保留的话，将空值key用随机方式打散。

6.7.2、单独处理倾斜key

6.7.3、不同数据类型首侍，join的关联字段类型不一样，导致耗时长，所以需要注意做类型转换（join关联字段，一个是int，一个是string，，计算key的者禅吵hash值时默认时int类型做的，这样导致所有真正的string类型的key都分配到一个reducer上了）

6.7.4、mapjoin的小表比较大的时候，，无法直接使用mapjoin，则 select/*+mapjoin(b)*/ from a left join ( select /*+mapjoin (b)*/ from b inner join a )

6.8、mapreduce 优化

6.8.1、调整mapper数

mapper数量与输入文件的split数息息相关。如果想减少mapper数，就适当提高mapred.min.split.size,split数就减少了；如果想增大mapper数，除了降低maperd.min.split.size之外，也可以提高mapred.map.task；一般来讲，如果输入文件是少量大文件，就减少mapper数；如果是大量非小文件，就增大mapper数，如果是小文件，就喝吧小文件

6.8.2 调整reducer数

使用参数mapred.reduce.task可以直接设定reducer数量，如果袭滑不设置的话，hive会自行推测，推测逻辑如下：

参数hive.exec.reducers.bytes.per.reducer.设定每个reducer能够处理的最大的数据量，默认时1G

参数hive.exec.reducers.max设定每个job的最大的reducer数量，默认时999或者1009

得到reducer数：reducers_num=min(total_input_size/reducers.bytes.per.reducer，reducers.max)

reducer数量与输出文件的数量相关，如果reducer数太多，会产生大量小文件，对hdfs造成压力，如果reducer数太少，每个reducer要处理很多数据，容易拖慢运行时间或造成oom

6.8.3 合并小文件

输入阶段合并：需要更改hive的输入文件格式，即参数hive.input.format，默认值是org.apache.hadoop.hive.ql.io.hiveiputformat，我们该成org.apache.hadoop.hive.ql.io.combinehiveinputformat.

这样比调整mapper数时，又多出两个参数，分别是mapred.min.split.size.per.node和mapred.min.split.size.per.rack，含义是单节点和单机架上的最小split大小。如果发现又split大小小于这两个默认值100MB，则会进行合并

输出阶段合并：直接将hive.merge.mapfiles和hive.merge.mapredfiles都设置为true即可。

hive.merge.mapfiles表示将map-only任务的输出合并

hive.merge.mapredfiles表示将map-reduce任务的输出合并

另外hive.merge.size.per.task可以指定每个task输出后合并文件大小的期望值

hive.merge.size.smallfiles.avgsize可以指定所有输出文件大小的均值阈值，默认时1G

6.9 启用压缩

压缩job的中间结果数据和输出数据，可以少量CPU时间节省出很多空间。压缩方式一般选择snappy，效率最高，要启用中间压缩，需要设定：

hive.exec.compress.intermediate为true，同时指定压缩方式hive.intermediate.compression.code为org.apache.hadoop.io.compress.snappycode.

另外，参数hive.intermediate.compression.type可以选对块block还是对记录record压缩，block压缩率比较高，输出压缩的配置基本相同：打开hive.exec.compress.output

6.10 jvm重用

在mr job中,默认时每执行一个task就会启动一个jvm，如果task非常小而且碎，那么jvm启动和关闭耗时都会比较长。可以通过调节参数mapred.job.reuse.jvm.num.task来重用。例如将这个参数设置为5，那么久代表同一个mr job中顺序执行的5各task可以重复使用一个jvm，减少启动和关闭开销，但是他对不同mr job的task无效

6.11 采用合适的存储格式

6.12 数据倾斜

数据倾斜原因：1、join 倾斜 2、聚合倾斜

group by 倾斜： group by 字段中某个字段的值过多，导致处理这个值得reduce耗时很久

解决方法：set hive.map.aggr=true; -- 表示开启map端聚合

set hive.groupby.skewindata=true; 注意：只能对单个字段聚合，生成两个map job ，第一个map job中map输出结果随机分布到reduce中，每个reduce做部分聚合操作，并输出结果，这样相同的groub key有可能被分发到不同的reduce中，从而达到负载均衡的目的；第二个map job再根据预处理的数据结果按照group by key分布到reduce中，这个过程可以保证相同的key被分到同一个reduce中，最后完成最终的聚合操作

6.13 优化in、exists语句

使用left semi join 替换in 、exists

6.14 合并 mapreduce操作

multi-group by 是hive的一个非常好的特性，它使得hive中利用中间结果变更非常方便

例如：

from ( select a.status,b.school from status_update a join profilees b on (a.userid=b.suerid)) subq1

insert overwirte table a1 partition(ds='2021-03-07')

select subq1.school,count(1) group by subq1.school

insert overwrite table b1 partition(ds='2021-03-07')

select subq1.status,count(1) group by subq1.status

上述语句使用了multi-group by特性联系group by 2次数据，使用不同的group by key，这一特性可以减少一次mapreduce操作

hive sql 优化的常用手段有哪些

1、join连接时的优化：当三个或多个以上的表进行join操春辩作时，如果每个on使用相同的字段连接时只会产生一个mapreduce。

2、join连接时的优化：当多个表进行查询时，从左到右表的大小顺序应该是从小到大。原因：hive在对每行记录操作时会把其他表先缓存起来，直到扫描最后的表进行计算

3、在where字梁知句中增加分区过滤器。

4、当可以使用left semi join 语法时不要使用inner join，前者效率更高。原因：对于左表中指定的一扒渣缺条记录，一旦在右表中找到立即停止扫描。

关于hivesql优化和hivesql优化常用的15种方法的介绍到此就结束了，不知道你从中找到你需要的信息了吗？如果你还想了解更多这方面的信息，记得收藏关注本站。

oracledecimal的简单介绍 jsonstring（jsonString转成list集合）

hivesql优化（hivesql优化常用的15种方法）

Hive Sql优化集(on where过滤)

Hive优化

数据分析课程笔记 - 19 - HiveSQL 常用优化技巧

hive优化方法

hive sql 优化的常用手段有哪些

最近发表

文章归档

标签列表

hivesql优化（hivesql优化常用的15种方法）

Hive Sql优化集(on where过滤)

Hive优化

数据分析课程笔记 - 19 - HiveSQL 常用优化技巧

hive优化方法

hive sql 优化的常用手段有哪些

相关阅读

k8sredis（k8sredis乱码解决方法）

关于json.org的信息

ajaxjson（ajaxjson自定义成功方法）

redisbigkeys的简单介绍

包含oracle17002的词条

关于oracle17002的信息

最近发表

文章归档

标签列表