摸鱼大数据——Hive调优4-6

4、fetch抓取

核心点: 在执行SQL, 能不走MR, 尽量不走MR

回想, 在之前执行什么样查询的SQL的时候,没有走MR呢?

1) 全表扫描
2) 查询某个列数据
3) 执行一些简单查询操作
4) 执行limit操作

而这些操作, 没有走MR原因, 就是hive默认以及开启本地抓取的策略方案:

hive.fetch.task.conversion:   设置本地抓取策略
可选:
    more (默认值): 可以保证在执行全表扫描、查询某几个列、简单的limit查询、简单的条件过滤不会变成MapReduce
    minimal: 可以保证在执行全表扫描、查询某几个列、简单的limit查询不会变成MapReduce
    none: 全部的HiveSQL语句都要变成MapReduce

示例:

-- 不会变成MapReduce几类SQL演示
-- 默认是more,底层自动调优
set hive.fetch.task.conversion;  -- 默认值more
select * from log_text; -- 全表扫描
select url from log_text; -- 查询某个列的数据
select * from log_text where city_id=-10; -- 简单的where过滤查询
select * from log_text limit 10; -- 执行limit程序
​
select * from log_text order by url desc limit 100,10; -- 复杂limit会变成MapReduce
​
set hive.fetch.task.conversion=minimal;
set hive.fetch.task.conversion;
select * from log_text; -- 全表扫描
select url from log_text; -- 查询某个列的数据
select * from log_text limit 10; -- 执行limit程序
select * from log_text where city_id=-10; -- 简单的where过滤查询
​
​
set hive.fetch.task.conversion=none;
set hive.fetch.task.conversion; -- 这里也要变成MapReduce
select * from log_text; -- 全表扫描
select url from log_text; -- 查询某个列的数据
select * from log_text limit 10; -- 执行limit程序
select * from log_text where city_id=-10; -- 简单的where过滤查询

5、本地模式

核心点:让MR能走本地模式, 尽量走本地MR(大白话: 小活能自己干就不要麻烦别人)

如何开启:
    set hive.exec.mode.local.auto=true;  默认值为: false
​
开启本地模式后, 在什么情况下执行本地MR:   只有当输入的数据满足以下两个特性后, 才会执行本地MR
    set hive.exec.mode.local.auto.inputbytes.max=51234560;    
        默认为128M。设置本地MR最大处理的数据量
    set hive.exec.mode.local.auto.input.files.max=10; 
        默认值为4。设置本地MR最大处理的文件的数量

示例:

-- 4.开启本地mr(默认false,如果小数据任务进行调优开启,小任务能自己干就自己干)
set hive.exec.mode.local.auto;  -- 默认关闭
set hive.exec.mode.local.auto=false; -- 手动关闭
set hive.exec.mode.local.auto=true; -- 手动开启
​
--设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max;-- 查看
set hive.exec.mode.local.auto.inputbytes.max=134217728;
​
--设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max;-- 查看
set hive.exec.mode.local.auto.input.files.max=4;
​
-- 执行sql语句
-- 没有开启本地执行24秒,开启后1.5秒
select * from log_text order by city_id desc;
​
-- 注意: 有的同学开启本地模式后执行上述sql语句,会报code 2...错误
-- 错误:[08S01][2] Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
-- 如何解决? /export/server/hive/hive-env.sh  修改 export HADOOP_HEAPSIZE=2048
-- 注意: 修改完后需要重启hive服务

6、join的优化操作

思考: 在通过hive执行多表查询的SQL, 最终hive翻译的MR,是如何完成join的工作的呢?

默认的join行为, 基于reduce端的join工作

思考, 请问上述join存在哪些问题?

1、导致reduce的压力剧增，所有的数据全部都汇总到reduce中
2、当有了多个reduce之后，如果某个join的字段出现了大量的数据，那么会导致这些大量的数据都分配给到同一个reduce，从而导致数据倾斜
​
数据倾斜: 实际例子解释，例如有100袋水泥，1袋给别人，99袋给你搬，那么你出现了数据倾斜

那么如何解决reduce端join遇到问题? 可以通过底层map 端 join实现,还可以sql语句join之前提前过滤数据或者转换数据实现

join之前提前过滤数据例子:
​
1- 没有提前过滤的SQL:
语句: select * from a left join b on a.id = b.id where a.age>=18;
过程: 先将a表和b表的所有数据全部加载出来（全表扫描），然后进行join关联操作，最后对join后的数据进行age>=18的过滤操作。这样会导致最开始处理的数据量比较大
​
2- 有提前过滤的SQL
语句: select * from (select * from a where age>=18) as new_a left join b on a.id = b.id;
过程: 在对a表数据进行全表扫描的时候同时对数据按照age>=18进行过滤，过滤出来以后再和b表进行join关联。这样能够让后续处理的数据量变少

• 1) 大表和小表进行join:

  • 解决方案: map join

  • 如何开启这种操作呢?

    set hive.auto.convert.join = true; -- 默认为true  开启mapJoin支持 
    set hive.mapjoin.smalltable.filesize= 25000000;  设置 小表的最大的数据量  23.84m

  • 在执行SQL, 应该将小表放置前面呢, 还是大表放置在前面呢,还是都可以呢 ?

    (hive1旧版本中) : 要求 必须将小表在前大表在后 ,只有这样才可能走Map Join
    (hive2、3新版中): 无所谓, 谁在前, 谁在后, 没有任何的影响, hive会自动进行优化

• 3) 大表和大表join

  • 解决方案:

    1- 将数据过滤提前，也就是在表join之前就先对数据进行过滤，能够减少后续数据处理的量
    2- 如果join字段上，有很多空值（也就是null值），这种情况进行join的时候，容易出现数据倾斜的问题。可以在该字段的后面（一般是放在后面）跟上一些随机值
    3- 使用分桶表