hive调优

来源于 https://mp.weixin.qq.com/s/9OqWPOI8CQ3GDYg4Mx_R_g

转载：https://zhuanlan.zhihu.com/p/133455414

优化方法：https://www.cnblogs.com/ittangtang/p/7683028.html

参考：https://www.cnblogs.com/qingyunzong/category/1167552.html

HIve调优主要分为三个部分： 1：表设计层面优化，2：语法和参数层面优化，3：HIVE架构层面的优化

                                         

 1：表优化层面优化（a.分区表优化，b.利用桶表优化，c.选择合适的文件存储格式，d.选择合适的压缩方式）

利用分区表优化

分区表 是在某一个或者几个维度上对数据进行分类存储，一个分区对应一个目录。如果筛选条件里有分区字段，那么 Hive 只需要遍历对应分区目录下的文件即可，不需要遍历全局数据，使得处理的数据量大大减少，从而提高查询效率。

当一个 Hive 表的查询大多数情况下，会根据某一个字段进行筛选时，那么非常适合创建为分区表。

利用桶表优化

指定桶的个数后，存储数据时，根据某一个字段进行哈希后，确定存储在哪个桶里，这样做的目的和分区表类似，也是使得筛选时不用全局遍历所有的数据，只需要遍历所在桶就可以了。

选择合适的文件存储格式

Apache Hive 支持 Apache Hadoop 中使用的几种熟悉的文件格式。

TextFile默认格式，如果建表时不指定默认为此格式。

存储方式：行存储。

每一行都是一条记录，每行都以换行符\n结尾。数据不做压缩时，磁盘会开销比较大，数据解析开销也比较大。

可结合 Gzip、Bzip2 等压缩方式一起使用（系统会自动检查，查询时会自动解压），但对于某些压缩算法 hive 不会对数据进行切分，从而无法对数据进行并行操作。

SequenceFile

一种Hadoop API 提供的二进制文件，使用方便、可分割、个压缩的特点。

支持三种压缩选择：NONE、RECORD、BLOCK。RECORD压缩率低，一般建议使用BLOCK压缩。

RCFile

存储方式：数据按行分块，每块按照列存储 。

  • 首先，将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。

  • 其次，块数据列式存储，有利于数据压缩和快速的列存取。

ORC

存储方式：数据按行分块，每块按照列存储

Hive 提供的新格式，属于 RCFile 的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩快，快速列存取。

Parquet

存储方式：列式存储

Parquet 对于大型查询的类型是高效的。对于扫描特定表格中的特定列查询，Parquet特别有用。Parquet一般使用 Snappy、Gzip 压缩。默认 Snappy。

Parquet 支持 Impala 查询引擎。

表的文件存储格式尽量采用 Parquet 或 ORC，不仅降低存储量，还优化了查询，压缩，表关联等性能；

选择合适的压缩方式

Hive 语句最终是转化为 MapReduce 程序来执行的，而 MapReduce 的性能瓶颈在与 网络IO 和磁盘IO，要解决性能瓶颈，最主要的是 减少数据量，对数据进行压缩是个好方式。压缩虽然是减少了数据量，但是压缩过程要消耗CPU，但是在Hadoop中，往往性能瓶颈不在于CPU，CPU压力并不大，所以压缩充分利用了比较空闲的CPU。

常用压缩算法对

如何选择压缩方式

  1. 压缩比率

  2. 压缩解压速度

  3. 是否支持split

支持分割的文件可以并行的有多个 mapper 程序处理大数据文件，大多数文件不支持可分割是因为这些文件只能从头开始读。

语法和参数层面优化

列裁剪（不需要的列不要select出来）

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

Hive 在读数据的时候，可以只读取查询中所需要用到的列，而忽略其他的列。这样做可以节省读取开销，中间表存储开销和数据整合开销。

set hive.optimize.cp = true; -- 列裁剪，取数只取查询中需要用到的列，默认为真

分区裁剪(查询的时候添加分区)

在查询的过程中只选择需要的分区，可以减少读入的分区数目，减少读入的数据量。

set hive.optimize.pruner=true; // 默认为true

合并小文件(注意小文件产生的原因)

 

小文件是如何产生的？

 

  1. 动态分区插入数据，产生大量的小文件，从而导致map数量剧增

  2. reduce数量越多，小文件也越多，reduce的个数和输出文件个数一致

  3. 数据源本身就是大量的小文件

 

小文件问题的影响

 

  1. 从Mapreduce的角度看，一个文件会启动一个map，所以小文件越多，map也越多，一个map启动一个jvm去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重的影响性能。

  2. 从HDFS角度看，HDFS中文件元信息（位置，大小，分块等）保存在NameNode的内存中，每个对象大约占用150字节，如果小文件过多，会占用大量内存，直接影响NameNode的性能；HDFS读写小文件也会更加耗时，因为每次都需要从NameNode获取元信息，并与对应的DataNode建立连接。

 

如何解决小文件问题

 

  1. 输入合并，在Map前合并小文件  文件归档

  2. 输出合并，在输出结果的时候合并小文件

  3. 控制reduce个数来实现减少小文件个数

  4. combine 
  5. jvm 重用（减少JVM开关时间）

 

Map 输入合并

在执行 MapReduce 程序的时候，一般情况是一个文件需要一个 mapper 来处理。但是如果数据源是大量的小文件，这样岂不是会启动大量的 mapper 任务，这样会浪费大量资源。可以将输入的小文件进行合并，从而减少mapper任务数量。详细分析

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- Map端输入、合并文件之后按照block的大小分割（默认）
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; -- Map端输入，不合并

Map/Reduce输出合并

大量的小文件会给 HDFS 带来压力，影响处理效率。可以通过合并 Map 和 Reduce 的结果文件来消除影响。

set hive.merge.mapfiles=true;  -- 是否合并Map输出文件, 默认值为真
set hive.merge.mapredfiles=true; -- 是否合并Reduce 端输出文件,默认值为假
set hive.merge.size.per.task=25610001000; -- 合并文件的大小,默认值为 256000000

合理控制 map/reduce 任务数量

合理控制 mapper 数量

减少 mapper 数可以通过合并小文件来实现 增加 mapper 数可以通过控制上一个 reduce

默认的 mapper 个数计算方式

输入文件总大小：total_size
hdfs 设置的数据块大小：dfs_block_size
default_mapper_num = total_size/dfs_block_size

MapReduce 中提供了如下参数来控制 map 任务个数：

set mapred.map.tasks=10;

从字面上看，貌似是可以直接设置 mapper 个数的样子，但是很遗憾不行，这个参数设置只有在大于default_mapper_num的时候，才会生效。

那如果我们需要减少 mapper 数量，但是文件大小是固定的，那该怎么办呢?

可以通过mapred.min.split.size设置每个任务处理的文件的大小，这个大小只有在大于dfs_block_size的时候才会生效

split_size=max(mapred.min.split.size, dfs_block_size)
split_num=total_size/split_size
compute_map_num = min(split_num,  max(default_mapper_num, mapred.map.tasks))

这样就可以减少mapper数量了。

总结一下控制 mapper 个数的方法：

  • 如果想增加 mapper 个数，可以设置mapred.map.tasks为一个较大的值

  • 如果想减少 mapper 个数，可以设置maperd.min.split.size为一个较大的值

  • 如果输入是大量小文件，想减少 mapper 个数，可以通过设置hive.input.format合并小文件

如何适当的增加map数？ 

        当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
        假设有这样一个任务：
        Select data_desc,
            count(1),
            count(distinct id),
             sum(case when …),
             sum(case when ...),
             sum(…)
       from a group by data_desc
                  如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，
                  这样就可以用多个map任务去完成。
                  set mapred.reduce.tasks=10;
                  create table a_1 as 
                  select * from a 
                  distribute by rand(123); 
                   
                  这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。
                  每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。
    
  看上去，貌似这两种有些矛盾，一个是要合并小文件，一个是要把大文件拆成小文件，这点正是重点需要关注的地方，
  根据实际情况，控制map数量需要遵循两个原则：使大数据量利用合适的map数；使单个map任务处理合适的数据量；

如果想要调整 mapper 个数，在调整之前，需要确定处理的文件大概大小以及文件的存在形式（是大量小文件，还是单个大文件），然后再设置合适的参数。

合理控制reducer数量

如果 reducer 数量过多，一个 reducer 会产生一个结数量果文件，这样就会生成很多小文件，那么如果这些结果文件会作为下一个 job 的输入，则会出现小文件需要进行合并的问题，而且启动和初始化 reducer 需要耗费和资源。

如果 reducer 数量过少，这样一个 reducer 就需要处理大量的数据，并且还有可能会出现数据倾斜的问题，使得整个查询耗时长。默认情况下，hive 分配的 reducer 个数由下列参数决定:

  • 参数1：hive.exec.reducers.bytes.per.reducer(默认1G)

  • 参数2：hive.exec.reducers.max(默认为999)

reducer的计算公式为:

N = min(参数2， 总输入数据量/参数1)

可以通过改变上述两个参数的值来控制reducer的数量。也可以通过

set mapred.map.tasks=10;

直接控制reducer个数，如果设置了该参数，上面两个参数就会忽略。

 

 什么情况下只有一个reduce：
很多时候你会发现任务中不管数据量多大，不管你有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；
其实只有一个reduce任务的情况，除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：
a)    没有group by的汇总，比如把select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 写成 select count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04';
这点非常常见，希望大家尽量改写。
b)    用了Order by
c)   有笛卡尔积
通常这些情况下，除了找办法来变通和避免，我暂时没有什么好的办法，因为这些操作都是全局的，所以hadoop不得不用一个reduce去完成；

Join优化

优先过滤数据（先过滤数据再join）

尽量减少每个阶段的数据量，对于分区表能用上分区字段的尽量使用，同时只选择后面需要使用到的列，最大限度的减少参与 join 的数据量。

小表 join 大表原则(左边的表会被加载到内存中，防止OOM)

小表 join 大表的时应遵守小表 join 大表原则，原因是 join 操作的 reduce 阶段，位于 join 左边的表内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出的几率。join 中执行顺序是从左到右生成 Job，应该保证连续查询中的表的大小从左到右是依次增加的。

 

 Hive 不支持非 等值的连接，因为非等值连接非常难转化到 map/reduce 任务。
另外， Hive 支持多于 2 个表的连接。

写查询时注意以下几点：

     1、只支持等值连接

例如：
SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department) 是正确的，
然而:
SELECT a.* FROM a JOIN b ON (a.id>b.id) 是错误的。
    2、
如果 join 中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而
b.key2 用于第二次 join。

     3、join 时，每次map /reduce的逻辑

reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序 列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。
例如：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。 Reduce 端会缓存 a 表
和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
这里用了 2 次 map/reduce 任务。第一次缓存 a 表，用 b 表序列化；第二次缓存第一次
map/reduce 任务的结果，然后用 c 表序列化。

使用相同的连接键（尽量用同一个id关联表，会合并为一个MR）

在 hive 中，当对 3 个或更多张表进行 join 时，如果 on 条件使用相同字段，那么它们会合并为一个 MapReduce  Job，利用这种特性，可以将相同的 join on 的放入一个 job 来节省执行时间。

    • （1）对3个以上表进行JOIN连接时，如果每个ON子句都使用相同连接键，只会产生一个MapReduce job。
      （2）Hive假定查询中最后一个表是最大的那个表。对每行记录进行连接操作时，它会尝试把其他表缓存起来，扫描最后那个表进行计算。因此，用户需要保证连接查询中的表大小从左到右是依次增加的。
    • ORDER BY和SORT BY
      ORDER BY会对查询结果集进行一个全局排序，也就是说所有数据都通过一个reducer进行处理，对于大数据集，这个过程会消耗很长时间。SORT BY只会在每个reducer中对数据进行排序，也就是执行一个局部排序的过程。这样可以保证每个reducer的输出数据都是有序的（并非全局有序）。这样可以提高后面全局排序的效率。

启用 mapjoin（在Map端进行join，减少了Reduce的步骤）

mapjoin 是将 join 双方比较小的表直接分发到各个 map 进程的内存中，在 map 进程中进行 join 操作，这样就不用进行 reduce 步骤，从而提高了速度。只有 join 操作才能启用 mapjoin。

set hive.auto.convert.join = true; -- 是否根据输入小表的大小，自动将reduce端的common join 转化为map join，将小表刷入内存中。
set hive.mapjoin.smalltable.filesize = 2500000; -- 刷入内存表的大小(字节)
set hive.mapjoin.maxsize=1000000;  -- Map Join所处理的最大的行数。超过此行数，Map Join进程会异常退出

尽量原子操作(join的时候尽量不要join里面再套join,如果需要可以先建一个tmp表的方式，先算出来再join,也可以并行跑sql)

尽量避免一个SQL包含复杂的逻辑，可以使用中间表来完成复杂的逻辑。

桶表 mapjoin

当两个分桶表 join 时，如果 join on的是分桶字段，小表的分桶数是大表的倍数时，可以启用 mapjoin 来提高效率。

set hive.optimize.bucketmapjoin = true; -- 启用桶表 map join

Group By 优化

默认情况下，Map阶段同一个Key的数据会分发到一个Reduce上，当一个Key的数据过大时会产生 数据倾斜。进行group by操作时可以从以下两个方面进行优化：

1. Map端部分聚合

事实上并不是所有的聚合操作都需要在 Reduce 部分进行，很多聚合操作都可以先在 Map 端进行部分聚合，然后在 Reduce 端的得出最终结果。

set hive.map.aggr=true; -- 开启Map端聚合参数设置

set hive.grouby.mapaggr.checkinterval=100000; -- 在Map端进行聚合操作的条目数目

2. 有数据倾斜时进行负载均衡

set hive.groupby.skewindata = true; -- 有数据倾斜的时候进行负载均衡（默认是false）

当选项设定为 true 时，生成的查询计划有两个 MapReduce 任务。在第一个 MapReduce 任务中，map 的输出结果会随机分布到 reduce 中，每个 reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的group by key有可能分发到不同的 reduce 中，从而达到负载均衡的目的；第二个 MapReduce 任务再根据预处理的数据结果按照group by key分布到各个 reduce 中，最后完成最终的聚合操作。

Order By 优化

order by只能是在一个reduce进程中进行，所以如果对一个大数据集进行order by，会导致一个reduce进程中处理的数据相当大，造成查询执行缓慢。

  • 在最终结果上进行order by，不要在中间的大数据集上进行排序。如果最终结果较少，可以在一个reduce上进行排序时，那么就在最后的结果集上进行order by。

  • 如果是去排序后的前N条数据，可以使用distribute by和sort by在各个reduce上进行排序后前N条，然后再对各个reduce的结果集合合并后在一个reduce中全局排序，再取前N条，因为参与全局排序的order by的数据量最多是reduce个数 * N，所以执行效率很高。

COUNT DISTINCT优化

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

-- 优化前（只有一个reduce，先去重再count负担比较大）：
select count(distinct id) from tablename;

-- 优化后（启动两个job，一个job负责子查询(可以有多个reduce)，另一个job负责count(1))：
select count(1) from (select distinct id from tablename) tmp;

一次读取多次插入

有些场景是从一张表读取数据后，要多次利用，这时可以使用multi insert语法：

from sale_detail
  insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  select shop_name, customer_id, total_price where .....
  insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
  select shop_name, customer_id, total_price where .....;

说明：

  • 一般情况下，单个SQL中最多可以写128路输出，超过128路，则报语法错误。

  • 在一个multi insert中：

    • 对于分区表，同一个目标分区不允许出现多次。

    • 对于未分区表，该表不能出现多次。

  • 对于同一张分区表的不同分区，不能同时有insert overwrite和insert into操作，否则报错返回。

启用压缩

map 输出压缩

set mapreduce.map.output.compress=true;
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

中间数据压缩

中间数据压缩就是对 hive 查询的多个 job 之间的数据进行压缩。最好是选择一个节省CPU耗时的压缩方式。可以采用snappy压缩算法，该算法的压缩和解压效率都非常高。

set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;

结果数据压缩

最终的结果数据（Reducer输出数据）也是可以进行压缩的，可以选择一个压缩效果比较好的，可以减少数据的大小和数据的磁盘读写时间；注：常用的gzip，snappy压缩算法是不支持并行处理的，如果数据源是gzip/snappy压缩文件大文件，这样只会有有个mapper来处理这个文件，会严重影响查询效率。所以如果结果数据需要作为其他查询任务的数据源，可以选择支持splitable的LZO算法，这样既能对结果文件进行压缩，还可以并行的处理，这样就可以大大的提高job执行的速度了。关于如何给Hadoop集群安装LZO压缩库可以查看这篇文章。

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

Hadoop集群支持一下算法：

  • org.apache.hadoop.io.compress.DefaultCodec

  • org.apache.hadoop.io.compress.GzipCodec

  • org.apache.hadoop.io.compress.BZip2Codec

  • org.apache.hadoop.io.compress.DeflateCodec

  • org.apache.hadoop.io.compress.SnappyCodec

  • org.apache.hadoop.io.compress.Lz4Codec

  • com.hadoop.compression.lzo.LzoCodec

  • com.hadoop.compression.lzo.LzopCodec

Hive架构层面优化

启用直接抓取

Hive 从 HDFS 中读取数据，有两种方式：启用 MapReduce 读取、直接抓取。

直接抓取数据比 MapReduce 方式读取数据要快的多，但是只有少数操作可以使用直接抓取方式。

可以通过hive.fetch.task.conversion参数来配置在什么情况下采用直接抓取方式：

  • minimal：只有 select * 、在分区字段上 where 过滤、有 limit 这三种场景下才启用直接抓取方式。

  • more：在 select、where 筛选、limit 时，都启用直接抓取方式。

set hive.fetch.task.conversion=more; -- 启用fetch more模式

本地化执行

Hive 在集群上查询时，默认是在集群上多台机器上运行，需要多个机器进行协调运行，这种方式很好的解决了大数据量的查询问题。但是在Hive查询处理的数据量比较小的时候，其实没有必要启动分布式模式去执行，因为以分布式方式执行设计到跨网络传输、多节点协调等，并且消耗资源。对于小数据集，可以通过本地模式，在单台机器上处理所有任务，执行时间明显被缩短。

set hive.exec.mode.local.auto=true; -- 打开hive自动判断是否启动本地模式的开关
set hive.exec.mode.local.auto.input.files.max=4; -- map任务数最大值
set hive.exec.mode.local.auto.inputbytes.max=134217728; -- map输入文件最大大小

JVM重用

Hive 语句最终会转换为一系列的 MapReduce 任务，每一个MapReduce 任务是由一系列的Map Task 和 Reduce Task 组成的，默认情况下，MapReduce 中一个 Map Task 或者 Reduce Task 就会启动一个 JVM 进程，一个 Task 执行完毕后，JVM进程就会退出。这样如果任务花费时间很短，又要多次启动 JVM 的情况下，JVM的启动时间会变成一个比较大的消耗，这时，可以通过重用 JVM 来解决。

set mapred.job.reuse.jvm.num.tasks=5;

JVM也是有缺点的，开启JVM重用会一直占用使用到的 task 的插槽，以便进行重用，直到任务完成后才会释放。如果某个不平衡的job中有几个 reduce task 执行的时间要比其他的 reduce task 消耗的时间要多得多的话，那么保留的插槽就会一直空闲却无法被其他的 job 使用，直到所有的 task 都结束了才会释放。

并行执行

有的查询语句，hive会将其转化为一个或多个阶段，包括：MapReduce 阶段、抽样阶段、合并阶段、limit 阶段等。默认情况下，一次只执行一个阶段。但是，如果某些阶段不是互相依赖，是可以并行执行的。多阶段并行是比较耗系统资源的。

set hive.exec.parallel=true;  -- 可以开启并发执行。
set hive.exec.parallel.thread.number=16;  -- 同一个sql允许最大并行度，默认为8。

推测执行

在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。

set mapreduce.map.speculative=true;
set mapreduce.reduce.speculative=true;

建议：

如果用户对于运行时的偏差非常敏感的话，那么可以将这些功能关闭掉。如果用户因为输入数据量很大而需要执行长时间的map或者Reduce task的话，那么启动推测执行造成的浪费是非常巨大大。

 

 

 

1.配置方面的优化

 

hive在解析sql的时候会默认认为最后一张表是大表,会把其他的表尝试缓存起来,然后扫描最后一个表进行计算,但是用户并不会经常性的将大表放到最后,所以我们可以在sql里面加一个配置,自动去检测并告诉查询优化器哪个是大表

 

如select /*+streamtable(s)*/a.id from log a left join user b on a.uid=b.uid

 

2.map-side join 

 

 set hive.auto.convert.join=true;

 

如果所有的表中有一张表足够的小,完全将小表缓存在内存中,在最大的表进行mapper的时候,可以和内存中的小表进行逐一匹配,从而省略掉常规连接所需要的reduce的过程

 

用户可以配置和优化小表的大小(单位是字节)

 

hive.mapjoin.smalltable.filesize=25000000

 

注意:hive 对于右外连接（right outer join）和全外连接（full outer join）不支持这个优化

 

3.本地模式

 

set hive.exec.mode.local.auto=true;(默认为false)

 

当一个job满足如下条件才能真正使用本地模式：
1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)

 

3.job的reduce数必须为0或者1

 

一般的简单的查询语句比如:
select * from a;这种语句是不会启东mapreduce的。
同样一些简单的查询语句,虽然会启动mr但是也可以使用本地模式

 

select a.name,a.age from user a where age=12;

 

也可以在hive_home/conf/hive-site.xml中配置

 

4.并行执行

 

set hive.exec.parallel=true;

 

set hive.exec.parallel.thread.number=10;

 

hive在执行的时候,会 将一个查询转换成一个或者多个阶段,这样的阶段可以是多个MapReduce阶段、抽样阶段、

 

合并阶段、limit 阶段或者hive执行过程中的其他阶段。默认情况下,hive一次只会执行一个阶段。不过,有些特定的

 

job可能包含众多的阶段,而这些阶段可能并非完全互依赖,可以并行执行,这样可以使整个的执行缩短

 

也可以在hive_home/conf/hive-site.xml中配置

 

5.严格模式

 

set hive.mapred.mode=strict;严格模式

 

set hive.mapred.mode=nonstrict;非严格模式

 

hive提供的严格模式,可以防止用户执行那些可能产生意想不到的不好的影响的查询。

 

第一种:对于分区,除非where的语句中含有分区字段的过滤条件来限制数据范围,否则不允许执行。

 

第二种:对于使用order by语句的查询,要求必须使用limit的语句。因为order by为了执行排序过程会将

 

          所有的结果数据分发到同一个reduce中进行处理,强制用户增加limit 语句可以防止reducer的额外

 

          执行时间。

 

第三种:限制笛卡尔积的查询.必须写 on 语句进行关联

 

6.设置mapper和reducer的个数

 

set hive.exec.reducers.max=(集群总reduce的槽位个数*1.5)/(执行中的查询的平均个数)

 

hive的默认的reducer的个数是3

 

也可以在hive_home/conf/hive-site.xml中配置

 

7.JVM重用

 

set mapred.job.reuse.jvm.num.tasks=10 

 

也可以在hadoop的mapred-site.xml中配置

 

Hadoop的默认配置通常是使用派生JVM来执行map和reduce的任务.这时JVM的启动过程可能会

 

造成相当大的开销,尤其是执行的job包含成百上千的task任务的情况。JVM重用可以使得JVM实例

 

在同一个job中重复使用N次,有个缺点,开启JVM重用会一直占用task的插槽,直到任务结束才会释放

 

8.hive动态分区 

 

参考地址 https://blog.csdn.net/oracle8090/article/details/72627135

 

9.set hive.map.aggr=true;

 

相当于 map端的 combiner操作

 

 

 

10.join优化（当操作的时候数据有大部分为null就会出现倾斜）

 

set hive.optimize.skewjoin=true;

 

如果是join过程中出现数据倾斜应该设置为true,变成自动优化

 

set hive.skewjoin,key=10000;

 

当join的建对应的记录条数超过这个值则会进行优化

 

 

 

11. group by 优化

 

 

 

hive.groupy.skewindata=true;
如果是group by 过程中出现出具倾斜应该设置为true
set hive.groupby.mapaggr.checkinterval=100000;
当group的键对应的记录条数超过这个值则会进行优化

 

12.合并小文件

 

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

 

13. 数据量太大分开处理

 

set mapred.reduce.tasks=10; 
select * from a 
distribute by rand(123);

 

 

set hive.optimize.skewjoin.compiletime=true;

set hive.groupby.skewindata=true;

 

hive 配置信息 https://blog.csdn.net/chaoping315/article/details/8500407

 

1.介绍

首先，我们来看看Hadoop的计算框架特性，在此特性下会衍生哪些问题？

  • 数据量大不是问题，数据倾斜是个问题。
  • jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是比较长的。
  • sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题。
  • count(distinct ),在数据量大的情况下，效率较低，如果是多count(distinct )效率更低，因为count(distinct)是按group by 字段分组，按distinct字段排序，一般这种分布方式是很倾斜的。举个例子：比如男uv,女uv，像淘宝一天30亿的pv，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。

面对这些问题，我们能有哪些有效的优化手段呢？下面列出一些在工作有效可行的优化手段：

  • 好的模型设计事半功倍。
  • 解决数据倾斜问题。
  • 减少job数。
  • 设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。
  • 了解数据分布，自己动手解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化有时不能适应特定业务背景，开发人员了解业务，了解数据，可以通过业务逻辑精确有效的解决数据倾斜问题。
  • 数据量较大的情况下，慎用count(distinct)，count(distinct)容易产生倾斜问题。
  • 对小文件进行合并，是行至有效的提高调度效率的方法，假如所有的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的正向影响。
  • 优化时把握整体，单个作业最优不如整体最优。

而接下来，我们心中应该会有一些疑问，影响性能的根源是什么？

2.性能低下的根源

hive性能优化时，把HiveQL当做M/R程序来读，即从M/R的运行角度来考虑优化性能，从更底层思考如何优化运算性能，而不仅仅局限于逻辑代码的替换层面。

RAC（Real Application Cluster）真正应用集群就像一辆机动灵活的小货车，响应快；Hadoop就像吞吐量巨大的轮船，启动开销大，如果每次只做小数量的输入输出，利用率将会很低。所以用好Hadoop的首要任务是增大每次任务所搭载的数据量。

Hadoop的核心能力是parition和sort，因而这也是优化的根本。

观察Hadoop处理数据的过程，有几个显著的特征：

  • 数据的大规模并不是负载重点，造成运行压力过大是因为运行数据的倾斜。
  • jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联对此汇总，产生几十个jobs，将会需要30分钟以上的时间且大部分时间被用于作业分配，初始化和数据输出。M/R作业初始化的时间是比较耗时间资源的一个部分。
  • 在使用SUM，COUNT，MAX，MIN等UDAF函数时，不怕数据倾斜问题，Hadoop在Map端的汇总合并优化过，使数据倾斜不成问题。
  • COUNT(DISTINCT)在数据量大的情况下，效率较低，如果多COUNT(DISTINCT)效率更低，因为COUNT(DISTINCT)是按GROUP BY字段分组，按DISTINCT字段排序，一般这种分布式方式是很倾斜的；比如：男UV，女UV，淘宝一天30亿的PV，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。
  • 数据倾斜是导致效率大幅降低的主要原因，可以采用多一次 Map/Reduce 的方法， 避免倾斜。

最后得出的结论是：避实就虚，用 job 数的增加，输入量的增加，占用更多存储空间，充分利用空闲 CPU 等各种方法，分解数据倾斜造成的负担。

3.配置角度优化

我们知道了性能低下的根源，同样，我们也可以从Hive的配置解读去优化。Hive系统内部已针对不同的查询预设定了优化方法，用户可以通过调整配置进行控制， 以下举例介绍部分优化的策略以及优化控制选项。

3.1 列裁剪

Hive 在读数据的时候，可以只读取查询中所需要用到的列，而忽略其它列。例如，若有以下查询：

SELECT a,b FROM q WHERE e<10;

在实施此项查询中，Q 表有 5 列（a，b，c，d，e），Hive 只读取查询逻辑中真实需要 的 3 列 a、b、e，而忽略列 c，d；这样做节省了读取开销，中间表存储开销和数据整合开销。

裁剪所对应的参数项为：hive.optimize.cp=true（默认值为真）

3.2 分区裁剪

可以在查询的过程中减少不必要的分区。例如，若有以下查询：

SELECT FROM (SELECTT a1,COUNT(1) FROM T GROUP BY a1) subq WHERE subq.prtn=100; #（多余分区）SELECT FROM T1 JOIN (SELECT * FROM T2) subq ON (T1.a1=subq.a2) WHERE subq.prtn=100;

查询语句若将“subq.prtn=100”条件放入子查询中更为高效，可以减少读入的分区 数目。Hive 自动执行这种裁剪优化。

分区参数为：hive.optimize.pruner=true（默认值为真）

3.3 JOIN操作

在编写带有 join 操作的代码语句时，应该将条目少的表/子查询放在 Join 操作符的左边。因为在 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，载入条目较少的表 可以有效减少 OOM（out of memory）即内存溢出。所以对于同一个 key 来说，对应的 value 值小的放前，大的放后，这便是“小表放前”原则。若一条语句中有多个 Join，依据 Join 的条件相同与否，有不同的处理方法。

3.3.1 JOIN原则

在使用写有 Join 操作的查询语句时有一条原则：应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生 OOM 错误的几率。对于一条语句中有多个 Join 的情况，如果 Join 的条件相同，比如查询：

INSERT OVERWRITE TABLE pv_users

SELECT pv.pageid, u.age FROM page_view p

JOIN user u ON (pv.userid = u.userid)

JOIN newuser x ON (u.userid = x.userid);
  • 如果 Join 的 key 相同，不管有多少个表，都会则会合并为一个 Map-Reduce
  • 一个 Map-Reduce 任务，而不是 ‘n’ 个
  • 在做 OUTER JOIN 的时候也是一样

如果 Join 的条件不相同，比如：

INSERT OVERWRITE TABLE pv_users

SELECT pv.pageid, u.age FROM page_view p

JOIN user u ON (pv.userid = u.userid)

JOIN newuser x on (u.age = x.age);

Map-Reduce 的任务数目和 Join 操作的数目是对应的，上述查询和以下查询是等价的：

INSERT OVERWRITE TABLE tmptable

SELECT * FROM page_view p JOIN user u

ON (pv.userid = u.userid);

INSERT OVERWRITE TABLE pv_users

SELECT x.pageid, x.age FROM tmptable x

JOIN newuser y ON (x.age = y.age);

3.4 MAP JOIN操作

Join 操作在 Map 阶段完成，不再需要Reduce，前提条件是需要的数据在 Map 的过程中可以访问到。比如查询：

INSERT OVERWRITE TABLE pv_users

SELECT /*+ MAPJOIN(pv) */ pv.pageid, u.age

FROM page_view pv

JOIN user u ON (pv.userid = u.userid);

可以在 Map 阶段完成 Join.

相关的参数为：

hive.join.emit.interval = 1000

hive.mapjoin.size.key = 10000

hive.mapjoin.cache.numrows = 10000

3.5 GROUP BY操作

进行GROUP BY操作时需要注意一下几点：

  • Map端部分聚合

事实上并不是所有的聚合操作都需要在reduce部分进行，很多聚合操作都可以先在Map端进行部分聚合，然后reduce端得出最终结果。

这里需要修改的参数为：

hive.map.aggr=true（用于设定是否在 map 端进行聚合，默认值为真） hive.groupby.mapaggr.checkinterval=100000（用于设定 map 端进行聚合操作的条目数）

  • 有数据倾斜时进行负载均衡

此处需要设定 hive.groupby.skewindata，当选项设定为 true 是，生成的查询计划有两 个 MapReduce 任务。在第一个 MapReduce 中，map 的输出结果集合会随机分布到 reduce 中， 每个 reduce 做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可 能分发到不同的 reduce 中，从而达到负载均衡的目的；第二个 MapReduce 任务再根据预处 理的数据结果按照 Group By Key 分布到 reduce 中（这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中），最后完成最终的聚合操作。

3.6 合并小文件

我们知道文件数目小，容易在文件存储端造成瓶颈，给 HDFS 带来压力，影响处理效率。对此，可以通过合并Map和Reduce的结果文件来消除这样的影响。

用于设置合并属性的参数有：

  • 是否合并Map输出文件：hive.merge.mapfiles=true（默认值为真）
  • 是否合并Reduce 端输出文件：hive.merge.mapredfiles=false（默认值为假）
  • 合并文件的大小：hive.merge.size.per.task=25610001000（默认值为 256000000）

4.程序角度优化

4.1 熟练使用SQL提高查询

熟练地使用 SQL，能写出高效率的查询语句。

场景：有一张 user 表，为卖家每天收到表，user_id，ds（日期）为 key，属性有主营类目，指标有交易金额，交易笔数。每天要取前10天的总收入，总笔数，和最近一天的主营类目。

解决方法 1

如下所示：常用方法

INSERT OVERWRITE TABLE t1

SELECT user_id,substr(MAX(CONCAT(ds,cat),9) AS main_cat) FROM users

WHERE ds=20120329 // 20120329 为日期列的值，实际代码中可以用函数表示出当天日期 GROUP BY user_id;

INSERT OVERWRITE TABLE t2

SELECT user_id,sum(qty) AS qty,SUM(amt) AS amt FROM users

WHERE ds BETWEEN 20120301 AND 20120329

GROUP BY user_id

SELECT t1.user_id,t1.main_cat,t2.qty,t2.amt FROM t1

JOIN t2 ON t1.user_id=t2.user_id

下面给出方法1的思路，实现步骤如下：

第一步：利用分析函数，取每个 user_id 最近一天的主营类目，存入临时表 t1。

第二步：汇总 10 天的总交易金额，交易笔数，存入临时表 t2。

第三步：关联 t1，t2，得到最终的结果。

解决方法 2

如下所示：优化方法

SELECT user_id,substr(MAX(CONCAT(ds,cat)),9) AS main_cat,SUM(qty),SUM(amt) FROM users

WHERE ds BETWEEN 20120301 AND 20120329

GROUP BY user_id

在工作中我们总结出：方案 2 的开销等于方案 1 的第二步的开销，性能提升，由原有的 25 分钟完成，缩短为 10 分钟以内完成。节省了两个临时表的读写是一个关键原因，这种方式也适用于 Oracle 中的数据查找工作。

SQL 具有普适性，很多 SQL 通用的优化方案在 Hadoop 分布式计算方式中也可以达到效果。

4.2 无效ID在关联时的数据倾斜问题

问题：日志中常会出现信息丢失，比如每日约为 20 亿的全网日志，其中的 user_id 为主 键，在日志收集过程中会丢失，出现主键为 null 的情况，如果取其中的 user_id 和 bmw_users 关联，就会碰到数据倾斜的问题。原因是 Hive 中，主键为 null 值的项会被当做相同的 Key 而分配进同一个计算 Map。

解决方法 1：user_id 为空的不参与关联，子查询过滤 null

SELECT * FROM log a

JOIN bmw_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 如下所示：函数过滤 null

SELECT * FROM log a LEFT OUTER

JOIN bmw_users b ON

CASE WHEN a.user_id IS NULL THEN CONCAT(‘dp_hive’,RAND()) ELSE a.user_id END =b.user_id;

调优结果：原先由于数据倾斜导致运行时长超过 1 小时，解决方法 1 运行每日平均时长 25 分钟，解决方法 2 运行的每日平均时长在 20 分钟左右。优化效果很明显。

我们在工作中总结出：解决方法2比解决方法1效果更好，不但IO少了，而且作业数也少了。解决方法1中log读取两次，job 数为2。解决方法2中 job 数是1。这个优化适合无效 id（比如-99、 ‘’，null 等）产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的 数据分到不同的Reduce上，从而解决数据倾斜问题。因为空值不参与关联，即使分到不同 的 Reduce 上，也不会影响最终的结果。附上 Hadoop 通用关联的实现方法是：关联通过二次排序实现的，关联的列为 partion key，关联的列和表的 tag 组成排序的 group key，根据 pariton key分配Reduce。同一Reduce内根据group key排序。

4.3 不同数据类型关联产生的倾斜问题

问题：不同数据类型 id 的关联会产生数据倾斜问题。

一张表 s8 的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。s8 的日志中有 32 为字符串商品 id，也有数值商品 id，日志中类型是 string 的，但商品中的 数值 id 是 bigint 的。猜想问题的原因是把 s8 的商品 id 转成数值 id 做 hash 来分配 Reduce， 所以字符串 id 的 s8 日志，都到一个 Reduce 上了，解决的方法验证了这个猜测。

解决方法：把数据类型转换成字符串类型

SELECT * FROM s8_log a LEFT OUTERJOIN r_auction_auctions b ON a.auction_id=CASE(b.auction_id AS STRING)

调优结果显示：数据表处理由 1 小时 30 分钟经代码调整后可以在 20 分钟内完成。

4.4 利用Hive对UNION ALL优化的特性

多表 union all 会优化成一个 job。

问题：比如推广效果表要和商品表关联，效果表中的 auction_id 列既有 32 为字符串商 品 id，也有数字 id，和商品表关联得到商品的信息。

解决方法：Hive SQL 性能会比较好

SELECT * FROM effect a

JOIN

(SELECT auction_id AS auction_id FROM auctions

UNION All

SELECT auction_string_id AS auction_id FROM auctions) b

ON a.auction_id=b.auction_id

比分别过滤数字 id，字符串 id 然后分别和商品表关联性能要好。

这样写的好处：1 个 MapReduce 作业，商品表只读一次，推广效果表只读取一次。把 这个 SQL 换成 Map/Reduce 代码的话，Map 的时候，把 a 表的记录打上标签 a，商品表记录 每读取一条，打上标签 b，变成两个对，<(b,数字 id),value>，<(b,字符串 id),value>。

所以商品表的 HDFS 读取只会是一次。

4.5 解决Hive对UNION ALL优化的短板

Hive 对 union all 的优化的特性：对 union all 优化只局限于非嵌套查询。

  • 消灭子查询内的 group by

示例 1：子查询内有 group by

SELECT * FROM

(SELECT * FROM t1 GROUP BY c1,c2,c3 UNION ALL SELECT * FROM t2 GROUP BY c1,c2,c3)t3

GROUP BY c1,c2,c3

从业务逻辑上说，子查询内的 GROUP BY 怎么都看显得多余（功能上的多余，除非有 COUNT(DISTINCT)），如果不是因为 Hive Bug 或者性能上的考量（曾经出现如果不执行子查询 GROUP BY，数据得不到正确的结果的 Hive Bug）。所以这个 Hive 按经验转换成如下所示：

SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)t3 GROUP BY c1,c2,c3

调优结果：经过测试，并未出现 union all 的 Hive Bug，数据是一致的。MapReduce 的 作业数由 3 减少到 1。

t1 相当于一个目录，t2 相当于一个目录，对 Map/Reduce 程序来说，t1，t2 可以作为 Map/Reduce 作业的 mutli inputs。这可以通过一个 Map/Reduce 来解决这个问题。Hadoop 的 计算框架，不怕数据多，就怕作业数多。

但如果换成是其他计算平台如 Oracle，那就不一定了，因为把大的输入拆成两个输入， 分别排序汇总后 merge（假如两个子排序是并行的话），是有可能性能更优的（比如希尔排 序比冒泡排序的性能更优）。

  • 消灭子查询内的 COUNT(DISTINCT)，MAX，MIN。
SELECT * FROM

(SELECT * FROM t1

UNION ALL SELECT c1,c2,c3 COUNT(DISTINCT c4) FROM t2 GROUP BY c1,c2,c3) t3

GROUP BY c1,c2,c3;

由于子查询里头有 COUNT(DISTINCT)操作，直接去 GROUP BY 将达不到业务目标。这时采用 临时表消灭 COUNT(DISTINCT)作业不但能解决倾斜问题，还能有效减少 jobs。

INSERT t4 SELECT c1,c2,c3,c4 FROM t2 GROUP BY c1,c2,c3;

SELECT c1,c2,c3,SUM(income),SUM(uv) FROM

(SELECT c1,c2,c3,income,0 AS uv FROM t1

UNION ALL

SELECT c1,c2,c3,0 AS income,1 AS uv FROM t2) t3

GROUP BY c1,c2,c3;

job 数是 2，减少一半，而且两次 Map/Reduce 比 COUNT(DISTINCT)效率更高。

调优结果：千万级别的类目表，member 表，与 10 亿级得商品表关联。原先 1963s 的任务经过调整，1152s 即完成。

  • 消灭子查询内的 JOIN
SELECT * FROM

(SELECT * FROM t1 UNION ALL SELECT * FROM t4 UNION ALL SELECT * FROM t2 JOIN t3 ON t2.id=t3.id) x

GROUP BY c1,c2;

上面代码运行会有 5 个 jobs。加入先 JOIN 生存临时表的话 t5，然后 UNION ALL，会变成 2 个 jobs。

INSERT OVERWRITE TABLE t5

SELECT * FROM t2 JOIN t3 ON t2.id=t3.id;

SELECT * FROM (t1 UNION ALL t4 UNION ALL t5);

调优结果显示：针对千万级别的广告位表，由原先 5 个 Job 共 15 分钟，分解为 2 个 job 一个 8-10 分钟，一个3分钟。

4.6 GROUP BY替代COUNT(DISTINCT)达到优化效果

计算 uv 的时候，经常会用到 COUNT(DISTINCT)，但在数据比较倾斜的时候 COUNT(DISTINCT) 会比较慢。这时可以尝试用 GROUP BY 改写代码计算 uv。

  • 原有代码

INSERT OVERWRITE TABLE s_dw_tanx_adzone_uv PARTITION (ds=20120329)SELECT 20120329 AS thedate,adzoneid,COUNT(DISTINCT acookie) AS uv FROM s_ods_log_tanx_pv t WHERE t.ds=20120329 GROUP BY adzoneid

关于COUNT(DISTINCT)的数据倾斜问题不能一概而论，要依情况而定，下面是我测试的一组数据：

测试数据：169857条

统计每日IP

CREATE TABLE ip_2014_12_29 AS SELECT COUNT(DISTINCT ip) AS IP FROM logdfs WHERE logdate=’2014_12_29′;

耗时：24.805 seconds

统计每日IP（改造）

CREATE TABLE ip_2014_12_29 AS SELECT COUNT(1) AS IP FROM (SELECT DISTINCT ip from logdfs WHERE logdate=’2014_12_29′) tmp;

耗时：46.833 seconds

测试结果表名：明显改造后的语句比之前耗时，这是因为改造后的语句有2个SELECT，多了一个job，这样在数据量小的时候，数据不会存在倾斜问题。

5.优化总结

优化时，把hive sql当做mapreduce程序来读，会有意想不到的惊喜。理解hadoop的核心能力，是hive优化的根本。这是这一年来，项目组所有成员宝贵的经验总结。

  • 长期观察hadoop处理数据的过程，有几个显著的特征:
  1. 不怕数据多，就怕数据倾斜。
  2. 对jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，没半小时是跑不完的。map reduce作业初始化的时间是比较长的。
  3. 对sum，count来说，不存在数据倾斜问题。
  4. 对count(distinct ),效率较低，数据量一多，准出问题，如果是多count(distinct )效率更低。
  • 优化可以从几个方面着手：
  1. 好的模型设计事半功倍。
  2. 解决数据倾斜问题。
  3. 减少job数。
  4. 设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。
  5. 自己动手写sql解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化总是漠视业务，习惯性提供通用的解决方法。Etl开发人员更了解业务，更了解数据，所以通过业务逻辑解决倾斜的方法往往更精确，更有效。
  6. 对count(distinct)采取漠视的方法，尤其数据大的时候很容易产生倾斜问题，不抱侥幸心理。自己动手，丰衣足食。
  7. 对小文件进行合并，是行至有效的提高调度效率的方法，假如我们的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的影响。

优化时把握整体，单个作业最优不如整体最优。

6.优化的常用手段

主要由三个属性来决定：

  • hive.exec.reducers.bytes.per.reducer ＃这个参数控制一个job会有多少个reducer来处理，依据的是输入文件的总大小。默认1GB。
  • hive.exec.reducers.max ＃这个参数控制最大的reducer的数量， 如果 input / bytes per reduce > max 则会启动这个参数所指定的reduce个数。 这个并不会影响mapre.reduce.tasks参数的设置。默认的max是999。
  • mapred.reduce.tasks ＃这个参数如果指定了，hive就不会用它的estimation函数来自动计算reduce的个数，而是用这个参数来启动reducer。默认是-1。

6.1 参数设置的影响

如果reduce太少：如果数据量很大，会导致这个reduce异常的慢，从而导致这个任务不能结束，也有可能会OOM 2、如果reduce太多： 产生的小文件太多，合并起来代价太高，namenode的内存占用也会增大。如果我们不指定mapred.reduce.tasks， hive会自动计算需要多少个reducer。

   

数据倾斜解决方法 

2.6.业务场景
1.空值产生的数据倾斜

 

场景说明：在日志中，常会有信息丢失的问题，比如日志中的 user_id，如果取其中的user_id 和用户表中的 user_id 相关联，就会碰到数据倾斜的问题。

 

解决方案 1：user_id 为空的不参与关联
select * from log a join user b on a.user_id is not null and a.user_id = b.user_id 
union all 
select * from log c where c.user_id is null;

 

解决方案 2：赋予空值新的 key 值
select * from log a left outer join user 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 是 1。这个优化适合无效 id（比如-99，’’，null）产生的数据倾斜，把空值的 key 变成一个字符串加上一个随机数，就能把造成数据倾斜的数据分到不同的 reduce 上解决数据倾斜的问题。

 

改变之处：使本身为 null 的所有记录不会拥挤在同一个 reduceTask 了，会由于有替代的随机字符串值，而分散到了多个 reduceTask 中了，由于 null 值关联不上，处理后并不影响最终结果。

 

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

 

场景说明：用户表中 user_id 字段为 int，log 表中 user_id 为既有 string 也有 int 的类型，当按照两个表的 user_id 进行 join 操作的时候，默认的 hash 操作会按照 int 类型的 id 进行分配，这样就会导致所有的 string 类型的 id 就被分到同一个 reducer 当中。

 

解决方案：把数字类型 id 转换成 string 类型的 id
select * from user a left outer join log b on b.user_id = cast(a.user_id as string)

 

3.大小表关联查询产生数据倾斜

 

注意：使用map join解决小表关联大表造成的数据倾斜问题。这个方法使用的频率很高。
map join 概念：将其中做连接的小表（全量数据）分发到所有 MapTask 端进行 Join，从
而避免了 reduceTask，前提要求是内存足以装下该全量数据

 

 

 

以大表 a 和小表 b 为例，所有的 maptask 节点都装载小表 b 的所有数据，然后大表 a 的一个数据块数据比如说是 a1 去跟 b 全量数据做链接，就省去了 reduce 做汇总的过程。所以相对来说，在内存允许的条件下使用 map join 比直接使用 MapReduce 效率还高些，当然这只限于做 join 查询的时候。

 

在 hive 中，直接提供了能够在 HQL 语句指定该次查询使用 map join，map join 的用法是在查询/子查询的SELECT关键字后面添加/*+ MAPJOIN(tablelist) */提示优化器转化为map join（早期的 Hive 版本的优化器是不能自动优化 map join 的）。其中 tablelist 可以是一个表，或以逗号连接的表的列表。tablelist 中的表将会读入内存，通常应该是将小表写在这里。

 

MapJoin 具体用法：
select /* +mapjoin(a) */ a.id aid, name, age from a join b on a.id = b.id;
select /* +mapjoin(movies) */ a.title, b.rating from movies a join ratings b on a.movieid = b.movieid;

 

在 hive0.11 版本以后会自动开启 map join 优化，由两个参数控制：
set hive.auto.convert.join=true;//设置 MapJoin 优化自动开启
set hive.mapjoin.smalltable.filesize=25000000//设置小表不超过多大时开启 mapjoin 优化

 

如果是大大表关联呢？那就大事化小，小事化了。把大表切分成小表，然后分别 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不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题

优化方法：https://blog.csdn.net/yuanziok/article/details/105726524

 

posted on 2019-10-24 22:25  大鹏的鸿鹄之志  阅读(1097)  评论(1)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 大鹏的鸿鹄之志
Powered by .NET 8.0 on Kubernetes