Hive 优化

目录

Hive 优化

1、Explain查看执行计划

2、Fetch抓取

3、本地模式

4、表之间的join操作

1、小表大表join(MapJoin)

2、大表大表join(left join)

3、Group by

4、Count(Distinct) 去重统计

5、笛卡儿积

6、行列过滤

7、设置map及reduce数

参考：

---

Hive 优化

1、Explain查看执行计划

可以使用explain语句来查看HQL的执行计划，可以查看一共有几个阶段，有几个MR任务，有些类似于SQL。

基本语法：

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query;

这里放一段其他博主执行的explain语句

STAGE PLANS:
  Stage: Stage-1 （包含了这个job的大部分处理过程，而且会触发一个 Mapreduce job）
    Map Reduce                                 --发生在job的map处理阶段过程
      Alias -> Map Operator Tree:
        my 
          TableScan                            --TableScan以my表作为输入
            alias: my
            Select Operator
              expressions:
                    expr: id                   --产生只有一个id的输出
                    type: int
              outputColumnNames: id
              Group By Operator
                aggregations:
                      expr: sum(id)            --group by Operator会应用到sum(id)
                bucketGroup: false
                mode: hash
                outputColumnNames: _col0       --产生一个输出字段_col0,它是临时结果字段按规则起的临时字段名
                Reduce Output Operator
                  sort order: 
                  tag: -1
                  value expressions:
                        expr: _col0
                        type: bigint
      Reduce Operator Tree:                    --reduce过程 
        Group By Operator
          aggregations:
                expr: sum(VALUE._col0)         --对_col0字段进行sum操作
          bucketGroup: false
          mode: mergepartial
          outputColumnNames: _col0
          Select Operator
            expressions:
                  expr: _col0
                  type: bigint
            outputColumnNames: _col0
            File Output Operator              --在reducer中科院看到File Output Operator，说明输出结果将是文本格式
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat                                     ---说明输出结果将是文本格式，基于字符串的输出格式


  Stage: Stage-0
    Fetch Operator
      limit: -1                              --job没有limit ，因此是一个没有任何操作的阶段

2、Fetch抓取

Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：select * from employees；在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。

在hive-default.xml.template文件中，hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。即，默认就是最优的了。

--修改属性状态
set hive.fetch.task.conversion=minimal; （仅对当前环境有效）

（在hive-site.xml中配置，永久有效）
<property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
    <description>
      Expects one of [none, minimal, more].
      Some select queries can be converted to single FETCH task minimizing latency.
      Currently the query should be single sourced not having any subquery and should not have
      any aggregations or distincts (which incurs RS), lateral views and joins.
      0. none : disable hive.fetch.task.conversion
      1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
      2. more  : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
    </description>
</property>

3、本地模式

大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时，Hive的输入数据量非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多得多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置hive.exec.mode.local.auto的值为true(默认是false)，来让Hive在适当的时候自动启动这个优化。

当一个job满足如下条件才能真正使用本地模式：
1.job的输入数据大小小于参数值时，则采用local模式：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
2.job中map的最大输入文件数小于参数时，则采用local模式：hive.exec.mode.local.auto.input.files.max(默认4)
3.job的reduce数必须为0或者1

4、表之间的join操作

1、小表大表join(MapJoin)

将key相对分散，并且数据量小的表放在join的左边，可以使用map join让小的维度表先进内存。在map端完成join，并且join的时候，null值默认是会进行过滤的。

实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有区别。

需要开启MapJoin参数设置：

设置自动选择MapJoin
set hive.auto.convert.join=true; (默认就是true)
大表小表的阈值设置
set hive.mapjoin.smalltale.filesize=25000000; (默认大概是25M以下是小表， 具体更改配置可以看具体的集群资源)

2、大表大表join(left join)

空key过滤

有时候join超时是因为某些key对应的数据太多，而相同的key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们可以在SQL语句中进行过滤。例如key对应的字段为空。

insert overwrite table jointable select t.* from 
(select * from nullidtable where id is not null) n left join bigtable o on n.id = o.id;

使用场景：

1、非 inner join

2、不需要字段为null的

空key转换

有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以为key为空的字段赋一个随机的值，使得数据随机均匀地分布到不同的reducer中。需要注意的是，加的随机值不能与之前的值产生join。例如，原本按照id进行join，并且id是0，1，2，3....，我们产生的随机数如果也是0，1，2，3...那么原本join不上的数据，产生了关联，那么结果就是错误的了。

随机分布空null值：

当n.id为null时，用hive+随机数进行代替，否则还用n.id。

INSERT overwrite TABLE jointable SELECT
n.* 
FROM
    nullidtable n
    FULL JOIN ori o ON
        CASE  
            WHEN n.id IS NULL 
            THEN
                concat('hive', rand())
            ELSE 
                n.id 
        END 
    = o.id;

SMB(sort merge bucket join)

如果对于不是空key的两张大表进行join的情况，我们可以使用分桶表进行处理。

举个简单的例子：

有一张大表TA，还有一张大表TB，此时需要按照id进行join，且id不为空的情况下，我们可以使用分桶表进行优化。将需要join的字段进行去进行分桶，这个时候假设根据id字段进行hash产生四个桶，那么在0号桶和其他桶之间的id则一定join不起来，因为hash值不一样(join TA.id = TB.id 那么hash值不一样id一定不一样)，相当于简单的按照join字段进行了分桶。

且桶的个数不应该超过可用的CPU的核数，保证最大程度的并行。在数据量巨大的情况下，可以节省很大的一部分时间。

3、Group by

默认情况下，Map 阶段同一 Key 数据分发给一个 reduce，当一个 key 数据过大时就倾斜了。并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce 端得出最终结果。

(1)是否在 Map 端进行聚合，默认为 True
hive.map.aggr = true;
(2)在 Map 端进行聚合操作的条目数目
hive.groupby.mapaggr.checkinterval = 100000;
(3)有数据倾斜的时候进行负载均衡（默认是 false）
hive.groupby.skewindata = true;

当 hive.groupby.skewindata 设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 同一 Reduce 中，最后完成最终的聚合操作。

4、Count(Distinct) 去重统计

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY 再Count的方式替换，但是需要注意group by造成数据倾斜问题。

--设置5个reduce个数
set mapreduce.job.reduces = 5;

--执行去重查询
select count(distinct id) from bigtable;

--优化后
select count(id) from (select id from bigtable group by id) a;

5、笛卡儿积

尽量需要避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用一个reducer来完成笛卡尔积，所以我们可以在配置的时候使用严格模式去禁用笛卡尔积。

6、行列过滤

列处理：在select 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用select *。

行处理：在分区裁剪中，当使用外关联时，如果将副表的过滤条件写在where后面，那么就会先全表关联，之后再过滤。

--一般使用以下方式查询，减少查询的数据量
select b.id from TB b
join (select id from TB where id <= 10) o on b.id = o.id;

不过，SQL底层默认优化了，将会进行谓词下推(这里就不作过多的解释，可以通过explain来查看)，简单解释就是两张表都会进行where条件的约束，不过仅限于不太复杂的查询，否则可能导致谓词下推失效。

7、设置map及reduce数

通常情况下，作业会通过input的目录产生一个或者多个map任务。主要的决定因素有：input文件总个数，input的文件大小，集群设置的文件块大小。

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

如果一个接近块大小的文件，但是只有一个或两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做肯定比较费时，所以一般选择增加map数。

复杂文件增加map数：

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

通过设置：

computeSpliteSize(Math.max(minSize, Math.min(maxSize, blocksize)))

调整maxSize最大值，让maxSize最大值低于blocksize就可以增加map的个数。

小文件合并减少map数

系统默认是小文件有合并，可以通过配置

set hive.input.format
hive.merge.mapfiles 在map-only job后合并文件，默认true
hive.merge.mapredfiles 在map-reduce job后合并文件，默认false
hive.merge.size.per.task 合并后每个文件的大小，默认256000000
hive.merge.smallfiles.avgsize 平均文件大小，是决定是否执行合并操作的阈值，默认16000000

reduce数量

设置reduce数据量记住两条原则：

处理大数据量利用合适的 reduce 数；
单个 reduce 任务处理数据量大小要合适；

设置reduce数量的方法有两个，但是需要基于一个公式min(每个任务最大的reduce数，总输入数据量/每个reduce处理的数据量)

-- 每个 Reduce 处理的数据量默认是 256MB
hive.exec.reducers.bytes.per.reducer=256000000
-- 每个任务最大的 reduce 数，默认为 1009
hive.exec.reducers.max=1009 

第二个方法就比较直接，上面计算出来后直接修改mapreduce.job.reduces，若值为-1，则采取上面的方法去设置数量。最好使用第一种方法，通过调控每个reduce处理的数量来操作reduce的数量。

reduce个数也不是越多越好

1、过多的启动和初始化reduce也会消耗时间和资源；

2、另外有多少个reduce就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则会出现小文件过多的问题。

在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适。

 

 

参考：

https://blog.csdn.net/weixin_37746272/article/details/78963699

https://blog.csdn.net/qq_43192537/article/details/110133794