数仓Hive基础调优

引言

此篇文章主要针对hive在执行hql时候，所面临的执行效率慢、数据倾斜、jobs任务过多情况进行分析调优；由于本人也是初次了解hive调优方面知识，在网上也查阅了很多资料，写下此篇文章也是为了巩固自己的知识点及能够与各位同学相互交流学习。以下本人会针对配置参数、HQL语句两个方面进行相关调优说明，另外在优化过程中，需要结合业务逻辑去优化会达到事半功倍的效果，后续会陆续更新此方面知识。

一、配置参数调优 

1、本地模式：hive.exec.mode.local.auto
在我们开发/测试阶段，我们通常会导入一笔少量数据到hive中进行开发测试。如果我们配置的是非本地模式（即hive.exec.mode.local.auto=false），则执行语句过程中会在yarn启动一个job作业，进行资源分配等步骤，消耗大量时间，造成开发效率缓慢。所以我们可以考虑配置本地模式（即hive.exec.mode.local.auto=true）进行开发测试，本地模式只会在本地运行jobs作业，通过一个reducer完成，极大的提高了开发效率。但注意在实际生产过程中数据量极大的情况下，需要改回非本地模式。其中本地模式有三个配置参数：

hive.exec.mode.local.auto	决定hive是否自动地根据文件输入大小在本地模式下运行。默认为false
hive.exec.mode.local.auto.inputbytes.max	如果 hive.exec.mode.local.auto 为 true，当输入文件大小小于此阈值时可以自动在本地模式运行，默认是 128兆。
hive.exec.mode.local.auto.tasks.max hive.exec.mode.local.auto.input.files.max（Hive1.2）	如果 hive.exec.mode.local.auto 为 true，当Hive Tasks(Hadoop jobs，map文件输入数)小于此阈值时可以自动在本地模式上运行。默认4

 

 

 

 1.1 默认配置：

SET hive.exec.mode.local.auto=true; -- 默认 false

SET hive.exec.mode.local.auto.inputbytes.max=50000000; 

SET hive.exec.mode.local.auto.input.files.max=5; -- 默认 4

一个作业只要满足下面的条件，会启用本地模式

• 输入文件的大小小于hive.exec.mode.local.auto.inputbytes.max配置的大小
• map任务的数量小于hive.exec.mode.local.auto.input.files.max配置的大小
• reduce任务的数量是1或者0

1.2 数据验证

•  数据准备：
  shop_order.txt
  001,耐克,2019-06-01
  002,安踏,2019-06-01
  003,李宁,2019-06-02
  001,阿迪达斯,2019-06-03
  001,李宁,2019-06-05
  002,耐克,2019-06-05
  003,耐克,2019-06-05
  004,乔丹,2019-06-05
• hive建表
  create table shop_order(
      zvip string,
      shopname string,
      orderdate string
  ) row format delimited terminated by ',' ;
• 导入数据
  hive -e "load data local inpath 'shop_order.txt' into table shop_order"
• hql执行优化示例

  ---

  示例1：
  在hive中非本地模式下，执行hql：select count(zvip) from shop_order;
  结果：
  
  结果分析：
  在结果截图中我们可以看到，在非本地模式下执行count()函数，通过yarn调度分配资源，在集群环境下执行，消耗了28seconds。
  示例2：
  设置本地模式，此处我在当前hive会话中设置，如果需要一直有效可以在hive-site.xml中进行配置。
  set hive.exec.mode.local.auto=true;
  结果：
  
  结果分析：
  如上图结果截图，本地模式下并没有通过yarn资源调度，仅在本地上执行操作，执行仅要1.65seconds即可。
  示例3：
  我重新导入了4份文件到shop_order表中，再进行count()执行。
  结果：
  
  结果分析：
  如果map输入文件数大于hive.exec.mode.local.auto.tasks.max/hive.exec.mode.local.auto.input.files.max大小，将重新启动非本地模式。

2、FETCH模式：fetch task为执行hive时，避免执行MapReduce，相关参数：
 

hive.fetch.task.conversion	分别有三个值[none，minimal，more]，1.2版本默认为more。 一些select查询可以转换成简单的fetch task来减少延迟。但在这些查询中只能是单一的来源表，不能有多个子查询和聚合或者distinct、lateral view和join操作。 none：不使用hive.fetch.task.conversion minimal：只有SELECT *，在分区列做过滤（FILTER），LIMIT有效。 more：在SELECT，FILTER，LIMIT有效，支持虚拟列。
hive.fetch.task.conversion.threshold	在输入大小为多少以内时候，fetch task生效。默认1G
hive.fetch.task.aggr	对于没有group by的聚合查询。比如select count(*) from table，这种最终都会在一个reduce中执行。设置为true可能节约一些时间。

 

 

 

 

 

 

数据准备：
分别将shop_order.txt加载到shop_order2表的三个分区
partition by 20190610,20190611,20190612
shop_order.txt
001,耐克,2019-06-01
002,安踏,2019-06-01
003,李宁,2019-06-02
001,阿迪达斯,2019-06-03
001,李宁,2019-06-05
002,耐克,2019-06-05
003,耐克,2019-06-05
004,乔丹,2019-06-05
建表：
create table shop_order2(zvip string,shopname string,orderdate string) partitioned by (etl_date string) row format delimited fields terminated by ',';
加载数据：
hive -e "load data local inpath 'shop_order.txt' into table young.shop_order2 partition(etl_date='20190610')";
在hive命令中通过命令将[20190610]分区数据copy到[20190611]、[20190612]分区中：
hive>insert into shop_order2 partition(etl_date='20190611') select zvip,shopname,orderdate from shop_order2 where etl_date='20190610';
hive>insert into shop_order2 partition(etl_date='20190612') select zvip,shopname,orderdate from shop_order2 where etl_date='20190610';
示例1：
当hive.fetch.task.conversion=none时，执行MR程序：
结果：


示例2：
当hive.fetch.task.conversion=minimal时，
执行SELECT * ，不执行MR程序；

执行 select zvip a,shopname,etl_date from shop_order2 ;仍然可以直接查询，此处不理解。。

示例3：
当hive.fetch.task.conversion=more时，
执行SELECT * ，不执行MR程序；

示例1：当hive.fetch.task.aggr默认为false时候，执行count()函数，会执行MR程序
select count(1) from shop_order2;
结果：

示例2：设置hive.fetch.task.aggr=true
set hive.fetch.task.aggr=true;
select count(1) from shop_order2;
结果：

结果分析：
仍然走MR程序，此处不是很理解。考虑到是不是跟本地模式有关，我将本地模式关闭之后执行效果仍然不起作用。

 

3、并行执行
     当一个sql有多个jobs时候，并且多个jobs没有依赖关系，可以增加并行度让顺序执行变成并行执行（此处一般为union all）

hive.exec.parallel	是否并行执行，默认为false
hive.exec.parallel.thread.number	同一个sql最多有多少个线程可以被并行执行，默认8

 

 

3.1 配置方式：

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

并行执行可以增加集群资源的利用率，如果集群的资源使用率已经很高了，那么并行执行的效果不会很明显。 

示例1：
hive.exec.parallel默认为false
select zvip,count(1) from shop_order2 where etl_Date='20190610' group by zvip union all select  zvip,count(1) from shop_order3 where etl_Date='20190613' group by zvip;
结果：


结果分析：
通过上图截图结果，执行了3个jobs，其中step-1和step-3是group by语句，step-2是union all语句，从执行过程中可以看到执行步骤是顺序执行的。先执行了step-1过程，再执行step-3，再执行step-2。总共花费时间为45.9seconds。

示例2：
hive.exec.parallel=tr