Hive 作为大数据畛域罕用的数据仓库组件，在设计和开发阶段须要留神效率。影响 Hive 效率的不仅仅是数据量过大；数据歪斜、数据冗余、job 或 I / O 过多、MapReduce 调配不合理等因素都对 Hive 的效率有影响。对 Hive 的调优既蕴含对 HiveQL 语句自身的优化，也蕴含 Hive 配置项和 MR 方面的调
整。

从以下三个方面开展：
架构优化
参数优化
SQL 优化

1. 架构方面

执行引擎方面针对公司内平台的资源，抉择更适合的更快的引擎，比方 MR、TEZ、Spark 等，

如果抉择是 TEZ 引擎，能够在优化器时候开启向量化的优化器，另外能够抉择老本优化器 CBO，配置别离如下：

set hive.vectorized.execution.enabled = true; -
- 默认 false
set hive.vectorized.execution.reduce.enabled = true; -
- 默认 false
SET hive.cbo.enable=true; -- 从 v0.14.0 默认
true
SET hive.compute.query.using.stats=true; -- 默认 false
SET hive.stats.fetch.column.stats=true; -- 默认 false
SET hive.stats.fetch.partition.stats=true; -- 默认 true 

在表的设计上优化，比方抉择分区表，分桶表，以及表的存储格局，为了缩小数据传输，能够应用压缩的形式，上面给几个参数（更多参数能够查看官网）

-- 两头后果压缩
SET
hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec ;
-- 输入后果压缩
SET hive.exec.compress.output=true;
SET mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodc

2. 参数优化

第二局部是参数优化，其实下面架构局部，有局部也是通过参数来管制的，这一部分的参数管制次要有上面几个方面

本地模式、严格模式、JVM 重用、并行执行、揣测执行、合并小文件、Fetch 模式

2.1 本地模式

当数据量较小的时候，启动分布式解决数据会比较慢，启动工夫较长，不如本地模式快，用上面的参数来调整

SET hive.exec.mode.local.auto=true; -- 默认 false 
小
SET hive.exec.mode.local.auto.inputbytes.max=50000000; -- 输出文件的大小小于 hive.exec.mode.local.auto.inputbytes.max 配置的大
SET hive.exec.mode.local.auto.input.files.max=5; -- 默认 4  map 工作的数量小于 hive.exec.mode.local.auto.input.files.max 配置的
大小

2.2 严格模式

这其实是个开关，满足上面三个语句时候，就会失败，如果不开启就失常执行，开启后就让这些语句主动失败

hive.mapred.mode=nostrict
 -- 查问分区表时不限定分区列的语句；-- 两表 join 产生了笛卡尔积的语句；-- 用 order by 来排序，但没有指定 limit 的语句 

2.3 Jvm 重用

在 mr 外面，是以过程为单位的，一个过程就是一个 Jvm, 其实像短作业，这些过程可能重用就会很快，然而它的毛病是会等工作执行结束后 task 插槽，这个在数据歪斜时候较为显著。开启这个应用上面的参数

SET mapreduce.job.jvm.numtasks=5;

2.4 并行执行

Hive 的查问会转为 stage，这些 stage 并不是相互依赖的，能够并行执行这些 stage，应用上面的参数

SET hive.exec.parallel=true; -- 默认 false
SET hive.exec.parallel.thread.number=16; -- 默认 8 

2.5 揣测执行

这个参数的作用是，应用空间资源来换取失去最终后果的工夫，比方因为网络，资源不均等起因，某些工作运行特地慢，会启动备份过程解决同一份数据，并最终选用最先胜利的计算结果作为最终后果。

set mapreduce.map.speculative=true
set mapreduce.reduce.speculative=true
set hive.mapred.reduce.tasks.speculative.execution=true

2.6 合并小文件

在 map 执行后面，先合并小文件来缩小 map 数

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

在工作完结后，合并小文件

# 在 map-only 工作完结时合并小文件，默认 true
SET hive.merge.mapfiles = true;
# 在 map-reduce 工作完结时合并小文件，默认 false
SET hive.merge.mapredfiles = true;
# 合并文件的大小，默认 256M
SET hive.merge.size.per.task = 268435456;
# 当输入文件的均匀大小小于该值时，启动一个独立的 map-reduce 工作进行文件 merge
SET hive.merge.smallfiles.avgsize = 16777216;

2.7 Fetch 模式

最初一种 fetch 模式，则是在某些状况下尽量不跑 mr，比方查问几个字段，全局查找，字段查，limit 查等状况

hive.fetch.task.conversion=more

3.sql 优化

这一部分较简单，可能波及到数据歪斜问题，至于数据歪斜问题始终是大数据处理的不可比防止的一个问题，解决形式也较多

3.1 sql 优化

sql 优化是开发人员最容易管制的局部，往往是教训使之，大概总结一下又上面的形式

列，分区拆解，sort by 代替 order by, group by 代替 count(distinct)，group by 的预聚合（通过参数来管制），歪斜配置项，map join，独自过滤空值，适当调整 map 和 reduces 数，这些在工作中简直都会碰到，尽可能去优化他们呢是你要做的

3.2 歪斜平衡配置项

这个配置与 group by 的歪斜平衡配置项殊途同归，通过 hive.optimize.skewjoin 来配置，默认 false。如果开启了，在 join 过程中 Hive 会将计数超过阈值 hive.skewjoin.key（默认 100000）的歪斜 key 对应的行长期写进文件中，而后再启动另一个 job 做 map join 生成后果。通过 hive.skewjoin.mapjoin.map.tasks 参数还能够管制第二个 job 的 mapper 数量，默认 1000

3.3 独自解决歪斜 key

如果歪斜的 key 有理论的意义，一般来讲歪斜的 key 都很少，此时能够将它们独自抽取进去，对应的行独自存入长期表中，而后打上一个较小的随机数前缀（比方 0~9），最初再进行聚合。不要一个 Select 语句中，写太多的 Join。肯定要理解业务，理解数据。(A0-A9)分成多条语句，分步执行；(A0-A4; A5-A9)；先执行大表与小表的关联；

4. 两个 SQL

4.1 找出全副夺得 3 连贯的队伍

team,year
活塞,1990
公牛,1991
公牛,1992

--
 -- 1 排名
select team, year, 
row_number() over (partition by team order by year) as rank
  from t1;

-- 2 获取分组 id
select team, year, 
row_number() over (partition by team order by year) as rank,
(year -row_number() over (partition by team order by year)) as groupid
  from t1;

-- 3 分组求解
select team, count(1) years
  from (select team, 
        (year -row_number() over (partition by team order by year)) as groupid
          from t1
       ) tmp
group by team, groupid
having count(1) >= 3;

4.2 找出每个 id 在在一天之内所有的波峰与波谷值

波峰：
这一时刻的值 > 前一时刻的值
这一时刻的值 > 后一时刻的值
波谷：
这一时刻的值 < 前一时刻的值
这一时刻的值 < 后一时刻的值
id time price 前一时刻的值（lag）后一时刻的值(lead)
sh66688, 9:35, 29.48 null 28.72
sh66688, 9:40, 28.72 29.48 27.74
sh66688, 9:45, 27.74
sh66688, 9:50, 26.75
sh66688, 9:55, 27.13
sh66688, 10:00, 26.30
sh66688, 10:05, 27.09
sh66688, 10:10, 26.46
sh66688, 10:15, 26.11
sh66688, 10:20, 26.88
sh66688, 10:25, 27.49
sh66688, 10:30, 26.70
sh66688, 10:35, 27.57
sh66688, 10:40, 28.26
sh66688, 10:45, 28.03

-- 思路：要害是找到波峰波谷的特色
-- 波峰的特色: 大于前一个时间段、后一个时间段的值
-- 波谷的特色: 小于前一个时间段、后一个时间段的值
-- 找到这个特色 SQL 就好写了

select id, time, price,
       case when price > beforeprice and price > afterprice then "波峰"
            when price < beforeprice and price < afterprice then "波谷" end as feature
  from (select id, time, price,
               lag(price) over (partition by id order by time) beforeprice,
               lead(price) over (partition by id order by time) afterprice
          from t2
        )tmp
 where (price > beforeprice and price > afterprice) or
       (price < beforeprice and price < afterprice);

吴邪，小三爷，混迹于后盾，大数据，人工智能畛域的小菜鸟。
更多请关注