大数据篇--Hive调优

一、表设计层面

1.关闭动态分区：

  动态分区插入数据，会产生大量的小文件，map数据会增加，同时namenode也需要存储更多元数据信息，检索更多的小文件。还有一个更加隐秘的问题，从A表导入数据到B表，AB俩表的分区列一样，如果这时候偷懒，插入B表开动态分区，hadoop会生成假的reduce个数，真实的reduce个数，也就是处理数据reduce节点和分区数一致，其他的reduce都是空跑。如果导入数据极大，redue个数很少，会产生严重的数据倾斜。解决办法：使用distribute by+静态分区。

  静态分区是插入时对分区字段指定值，动态分区是插入时对分区字段不指定值；动态分区在数据量大于0时才会创建分区，静态分区的数据量为0时也会创建分区。动态分区可以通过下面的设置来打开：

set hive.exec.dynamic.partition=true; -- 是开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; -- 这个属性默认值是strict，就是要求分区字段必须有一个是静态的分区值，当前设置为nonstrict,那么可以全部动态分区

2.开启分桶：

分桶：按照用户创建表时指定的分桶字段进行hash散列多个文件。单个分区或者表中的数据量越来越大，当分区不能更细粒的划分数据时，所以会采用分桶技术将数据更细粒度的划分和管理。

  获得更高的查询处理效率：桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大减少JOIN的数据量。

set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;

3.采用合适的存储格式：

  在HiveSQL的create table语句中，可以使用stored as ...指定表的存储格式。Hive表支持的存储格式有TextFile、SequenceFile、RCFile、Avro、ORC、Parquet等。
  存储格式一般需要根据业务进行选择，在我们的实操中，绝大多数表都采用TextFile与Parquet两种存储格式之一。
  TextFile是最简单的存储格式，它是纯文本记录，也是Hive的默认格式。虽然它的磁盘开销比较大，查询效率也低，但它更多地是作为跳板来使用。RCFile、ORC、Parquet等格式的表都不能由文件直接导入数据，必须由TextFile来做中转。
  Parquet和ORC都是Apache旗下的开源列式存储格式。列式存储比起传统的行式存储更适合批量OLAP查询，并且也支持更好的压缩和编码。我们选择Parquet的原因主要是它支持Impala查询引擎，并且我们对update、delete和事务性操作需求很低。
  这里就不展开讲它们的细节，可以参考各自的官网：
https://parquet.apache.org/
https://orc.apache.org/
 

二、参数调优

1.严格模式：

  在hive里面可以通过严格模式防止用户执行那些可能产生意想不到的查询,从而保护hive的集群。在严格模式下，用户在运行如下query的时候会报错：

• 分区表的查询没有使用分区字段来限制即 where 中过滤字段不是分区字段
• 使用了 order by 但没有使用 limit 语句。（如果不使用 limit，会对查询结果进行全局排序，消耗时间长）
• 当用户写代码将表的别名写错的时候会引起笛卡尔积，例如
  select * from origindb.promotion__campaign c JOIN origindb.promotion__campaignex ce ON c.id = c.id limit 1000;

-- 默认是非严格模式（nonstrict）
hive> set hive.mapred.mode;
hive.mapred.mode=nonstrict
-- 设置成严格模式后一定要加limit，否则会报错
hive> set hive.mapred.mode=strict;
hive> select * from emp order by empno desc;
FAILED: SemanticException 1:27 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'empno'

2.Fetch Task功能：

  某些 SELECT 查询可以转换为一个 FETCH 任务，从而最大限度地可以减少交互的延迟。在目前情况下，查询只能是单一数据源，不能有任何的子查询，不能有任何的聚合，去重，Lateral views 以及 Join。Fetch 任务是 Hive 中执行效率比较高的任务之一。直接遍历文件并输出结果，而不是启动 MapReduce 作业进行查询。对于简单的查询，如带有 LIMIT 语句的 SELECT * 查询，这会非常快(单位数秒级)。在这种情况下，Hive 可以通过执行 HDFS 操作来返回结果。

在hive-site.xml中有三个fetch task相关的值：hive.fetch.task.conversion，hive.fetch.task.conversion.threshold，hive.fetch.task.aggr

hive.fetch.task.conversion.threshold：在输入大小为多少以内的时候fetch task生效，从 Hive 0.13.0 版本到 Hive 0.13.1 版本起，默认值为-1（表示没有任何的限制），Hive 0.14.0 版本以及更高版本默认值改为 1073741824 byte(1G)。

<property>
  <name>hive.fetch.task.conversion.threshold</name>
  <value>1073741824</value>
  <description>
    Input threshold for applying hive.fetch.task.conversion. If target table is native, input length
    is calculated by summation of file lengths. If it's not native, storage handler for the table
    can optionally implement org.apache.hadoop.hive.ql.metadata.InputEstimator interface.
  </description>
</property>

hive.fetch.task.aggr：对于没有 group by 的聚合查询，比如 select count(*) from src，这种最终都会在一个 reduce 中执行，像这种查询，可以把这个置为 true 将其转换为 fetch task，这可能会节约一些时间。

<property>
  <name>hive.fetch.task.aggr</name>
  <value>true</value>
  <description>
    Aggregation queries with no group-by clause (for example, select count(*) from src) execute
    final aggregations in single reduce task. If this is set true, Hive delegates final aggregation
    stage to fetch task, possibly decreasing the query time.
  </description>
</property>

hive.fetch.task.conversion：
(1) 直接在命令行中使用set命令进行设置：

hive> set hive.fetch.task.conversion=more;

(2) 使用hiveconf进行设置：

bin/hive --hiveconf hive.fetch.task.conversion=more

(3) 上面的两种方法都可以开启了 Fetch Task，但是都是临时起作用的；如果你想一直启用这个功能，可以在 ${HIVE_HOME}/conf/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>

  可支持的选项有 none,minimal 和 more，从 Hive 0.10.0 版本到 Hive 0.13.1 版本起，默认值为 minimal，Hive 0.14.0 版本以及更高版本默认值改为 more：

• none：禁用fetch task优化（在Hive 0.14.0版本中引入）
• minimal：只在select *、使用分区列过滤、带有limit的语句上进行优化
• more：在minimal的基础上更加强大了，select不仅仅可以是*，还可以单独选择几列，并且filter也不再局限于分区字段，同时支持虚拟列（别名）

3.reduce个数控制：

  为什么要设置rereduce的个数？

• reduce数量很大就可能生成很多小文件。
• reduce数量很少就可能导致作业耗费时间长，最终有可能任务跑不出来。
  
  参考官网：https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

hive> set hive.exec.reducers.bytes.per.reducer;
hive.exec.reducers.bytes.per.reducer=256000000
hive> set hive.exec.reducers.max;
hive.exec.reducers.max=1009; -- 默认是它自己根据相应公式算的，具体可翻阅源码
hive> set mapred.reduce.tasks = 3; -- 设置reduce数量

在Spark中相当于是要设置partition的数量：
  Partition数量太少：太少的影响显而易见，就是资源不能充分利用，例如local模式下，有16core，但是Partition数量仅为8的话，有一半的core没利用到。
  Partition数量太多：太多，资源利用没什么问题，但是导致task过多，task的序列化和传输的时间开销增大。
  那么多少的partition数是合适的呢，这里我们参考spark doc给出的建议，Typically you want 2-4 partitions for each CPU in your cluster。（Spark 官网建议的 Task 的设置原则是：设置 Task 数目为num-executors * executor-cores的2~3倍较为合适。）
  也可以参考：如何管理Spark的分区

4.map join：

参考官网：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties中的hive.auto.convert.join参数

Map Join：
优点： 没有shuffle/Reduce过程，效率提高
缺点 ：由于小表都加载到内存当中，读内存的要求提高了
原理执行流程可参考：
Hive Map Join 原理
http://lxw1234.com/archives/2015/06/313.htm

  Hive0.11之前，默认的join方式是reduce端join，即Common Join=Shuffle join=Reduce join(hive.auto.convert.join默认为false)，其原理是map的输出数据通过hash进行partition，然后shuffle至对应的reduce端执行join。如果join key分布不均匀，则会造成一定的数据倾斜，比较明显的现象就是某一个reduce会一直运行在99%，在join运行完毕后，可以通过job的counter看到，reduce处理的数据量相差很大。
  join中还有一个方式是map join，即在map端进行join，其原理是broadcast join，即把比较小的表直接放到内存中去，然后再对比较大的表进行Map操作，join就会在map操作的时候，每当扫描一个大的table中的数据，就要去查看小表的数据，哪条与之相符，继而进行连接。这种方式比较适合表中有一个小表的情况，hive是rbo的方法来执行操作的，所以需要把小表放在前面，不过也可以手动指定hint，比如/*+ mapjoin(a)*/。

  Hive 0.11之后，在表的大小符合设置时（hive.auto.convert.join.noconditionaltask=true，hive.auto.convert.join.noconditionaltask.size=10000，hive.mapjoin.smalltable.filesize=25000000，即25M），默认会把join转换为map join（让hive.ignore.mapjoin.hint为true，hive.auto.convert.join为true），不过hive0.11的map join bug比较多，可以通过默认关闭map join convert，在需要时再设置hint：hive.auto.convert.join=false，hive.ignore.mapjoin.hint=false

  如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即在Reduce阶段完成join，整个过程包含Map、Shuffle、Reduce阶段。

• Map阶段：读取源表的数据，Map输出时候以Join on条件中的列为key，如果Join有多个关联键，则以这些关联键的组合作为key；Map输出的value为join之后所关心的(select或者where中需要用到的)列；同时在value中还会包含表的Tag信息，用于标明此value对应哪个表；按照key进行排序。
• Shuffle阶段：根据key的值进行hash，并将key/value按照hash值推送至不同的reduce中，这样确保两个表中相同的key位于同一个reduce中。
• Reduce阶段：根据key的值完成join操作，期间通过Tag来识别不同表中的数据。
  

扩展：小表不小不大，怎么用 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 = x.user_id;

  假如，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

5.skewjoin方案：

参考：
https://www.cda.cn/discuss/post/details/5ef4b5dae76c715bf35703e4
https://www.cnblogs.com/aukle/p/3233704.html

  在Hive的数据处理过程中，由于join造成的倾斜，常见情况是不能做map join的两个表(能做map join的话基本上可以避免倾斜)，其中一个是行为表，另一个应该是属性表。比如我们有三个表，一个用户属性表users，一个商品属性表items，还有一个用户对商品的操作行为表日志表logs。假设现在需要将行为表关联用户表：select * from logs l join users u on l.user_id = u.user_id;
  其中logs表里面会有一个特殊用户user_id = 0，代表未登录用户，假如这种用户占了相当的比例，那么个别reduce会收到比其他reduce多得多的数据，因为它要接收所有user_id = 0的记录进行处理，使得其处理效果会非常差，其他reduce都跑完很久了它还在运行。
  hive给出的解决方案叫skew join，其原理把这种user_id = 0的特殊值先不在reduce端计算掉，而是先写入hdfs，然后启动一轮map join专门做这个特殊值的计算，期望能提高计算这部分值的处理速度。当然你要告诉hive这个join是个skew join，即：set hive.optimize.skewjoin = true;
  还有要告诉hive如何判断特殊值，根据set hive.skewjoin.key = skew_key_threshold （default = 100000）设置的数量hive可以知道，比如默认值是100000，那么超过100000条记录的值就是特殊值。所以使用这个参数控制倾斜的阈值，如果超过这个值，新的值会发送给那些还没有达到的reduce， 一般可以设置成你（处理的总记录数/reduce个数）的2-4倍都可以接受。倾斜是经常会存在的，一般select 的层数超过2层，翻译成执行计划多于3个以上的mapreduce job 都很容易产生倾斜，建议每次运行比较复杂的sql 之前都可以设一下这个参数. 如果你不知道设置多少，可以就按官方默认的1个reduce 只处理1G 的算法，那么 skew_key_threshold = 1G/平均行长. 或者默认直接设成250000000 (差不多算平均行长4个字节)。
  其他相关参数：hive.skewjoin.mapjoin.map.tasks = <用于处理skew join的map join 的最大数量> (defaul : 10000)，hive.skewjoin.mapjoin.min.split=33554432（通过指定最小split的大小，执行细粒度的控制）
  skew join的流程可以用下图描述：

6.group by导致的数据倾斜：

原因：group by 维度过小，某值的数量过多。处理某值的reduce灰常耗时。

set hive.map.aggr=true;：Map 端部分聚合，相当于Combiner
set hive.groupby.skewindata=true;：有数据倾斜的时候进行负载均衡，当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

7.调整切片数（Map任务数）：

  Hive底层自动对小文件做了优化，用了CombineTextInputFormat，将多个小文件切片合成一个切片。合成完之后的切片大小，如果大于mapred.max.split.size 的大小，就会生成一个新的切片。mapred.max.split.size 默认是128MB，set mapred.max.split.size=134217728（128MB），对于切片数（MapTask）数量的调整，要根据实际业务来定，比如一个100MB的文件，假设有1千万条数据，此时可以调成10个MapTask，则每个MapTask处理1百万条数据。

  这个调优相当于是解决小文件的问题，可参考我的另一篇文章第四章中的第二小节：大数据篇–小文件

  上图是InputFormat的派生子类关系图：

8.本地模式：

  Hive也可以不将任务提交到集群进行运算，而是直接在一台节点上处理。因为消除了提交到集群的overhead，所以比较适合数据量很小，且逻辑不复杂的任务。设置hive.exec.mode.local.auto为true可以开启本地模式。但任务的输入数据总量必须小于hive.exec.mode.local.auto.inputbytes.max（默认值128MB），且mapper数必须小于hive.exec.mode.local.auto.tasks.max（默认值4），reducer数必须为0或1，才会真正用本地模式执行。
 

三、语法层面调优

1.order by和sort by：

参考hive官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy

Order by只会产生一个reducer，全局排序。
Sort by只能保证每个reduce内部是有序的，并不能保证全局有序。

hive> set mapred.reduce.tasks = 3;
hive> insert overwrite local directory '/home/hadoop/hive_tmp/sort' select * from emp sort by empno desc;

2.cluster by和distribute by：

distribute by：按照指定的字段把数据分散到不同的reduce里面去。
cluster by：如果sort by和distribute by中所有的列相同，可以缩写为cluster by以便同时指定两者所用的列。

hive> insert overwrite local directory '/home/hadoop/hive_tmp/distribute' select * from emp distribute by length(ename) sort by empno;

3.执行计划Explain：

  我们都知道，hive在执行的时候会把所对应的SQL语句都会转换成mapreduce代码执行，但是具体的MR执行信息我们怎样才能看出来呢？这里就用到了explain的关键字，他可详细的表示出在执行所对应的语句所对应的MR代码。 explain会把查询语句转化成stage组成的序列，主要由三方面组成：

• 查询的抽象语法树
• plane中各个stage的依赖情况
• 每个阶段的具体描述：描述具体来说就是显示出对应的操作算子和与之操作的对应的数据，例如查询算子，filter算子，fetch算子等等。

参考官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain

查看下面这条语句的执行计划：
hive (default)> explain select * from emp;

hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;

4.where条件优化：

优化前：select m.cid,u.id from order m join customer u on m.cid =u.id where m.dt='20180808';
优化后(where条件在map端执行而不是在reduce端执行）：select m.cid,u.id from (select * from order where dt='20180818') m join customer u on m.cid=u.id;

注意：hive在做join时小表写在前面。

5.union优化：

  尽量不要使用union（union会去掉重复的记录）而是使用 union all 然后再用 group by 去重。

6.count distinct优化：

  数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 Job 很难完成，一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换：

SELECT day,
COUNT(DISTINCT id) AS uv
FROM users;

可以转换成：

SELECT day,
COUNT(id) AS uv
FROM (SELECT day,id FROM users GROUP BY day,id) a;

注意：count 操作是全局计数，在底层转换 MRJob 时用于计数的分区（reduce Task）只有一个。

-- 优化前
Select a,sum(b),count(distinct c),count(distinct d) from test group by a
-- 优化后的语句
Select a ,sum(b),count（c）,count(d) from (
				Select a,b,null c,null d from test
				Union all
				Select a,0 b,c,null d from test group by a,c
				Union all
				Select a,0,null c,d from test group by a,d
)

四、数据倾斜

1.表现：

  任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce 子任务未完成。因为其处理的数据量和其他 reduce 差异过大。单一 reduce 的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。

2.一些常见的：

关键词	情形	后果	解决
大小表Join	其中一个表较小，但是 key 集中	分发到某一个或几个 Reduce 上的数据远高于平均值	参考上面第二大节的第4小节
大表Join大表	两个表都较大，不能支持 map join，其中一个表中数据量某一类值特别多	分配到该值的 reducer，耗时较长	参考上面第二大节的第5小节
group by	group by 维度过小，某值的数量过多	处理某值的 reduce 非常耗时	参考上面第二大节的第6小节
count(distinct)	数据量大的情况下	用一个 Reduce Task 来完成，就会导致整个 Job 很难完成	参考上面第三大节的第6小节

3.空值产生的数据倾斜：

场景：如日志中，常会有信息丢失的问题，比如日志中的 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 读取两次，jobs 是2。解决方法2 job 数是1 。这个优化适合无效 id (比如 -99 , ’’, null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的 reduce上 ,解决数据倾斜问题。

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

场景：用户表中 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)

参考：https://my.oschina.net/osenlin/blog/1603056