hive 循环日期加减 hive 循环遍历

打怪升级之小白的大数据之旅(六十九)

Hive旅程第十站:Hive的优化

上次回顾

上一章介绍了Hive的压缩与存储格式，本章节是Hive的一起其他优化方法

Fetch抓取

• Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算
• 从hive学到了现在，大家有没有这个疑问，为什么使用select * from 表名的时候，hive不走MR程序呢？而使用select count(*) from 表名会执行MR程序？
• SELECT * FROM 表名;在这种情况下，Hive可以简单地读取该表对应的存储目录下的文件，然后输出查询结果到控制台(因为表是通过元数据做映射的，实际表它就是一个文件夹)
• 在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce

以我们的emp员工表查询举例：

• 把hive.fetch.task.conversion设置成none，然后执行查询语句，都会执行mapreduce程序

# 设置抓取为none
set hive.fetch.task.conversion=none;
select *from emp;
select ename from emp;
select ename from emp limit 3;

• 把hive.fetch.task.conversion设置成more，然后执行查询语句，如下查询方式都不会执行mapreduce程序

# 设置抓取为none
set hive.fetch.task.conversion=none;
select *from emp;
select ename from emp;
select ename from emp limit 3;

本地模式

• 在实际开发中，大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的
• 不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多
• 对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短
• 我们可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化
• 具体参数说明如下：

set hive.exec.mode.local.auto=true;  //开启本地mr
//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

还是以员工表emp举例：

开启本地模式，并执行查询语句

set hive.exec.mode.local.auto=true; 
select * from emp cluster by deptno;
-- 用时：Time taken: 1.328 seconds, Fetched: 14 row(s)

关闭本地模式，并执行查询语句

set hive.exec.mode.local.auto=false; 
select * from emp cluster by deptno;
-- 用时：Time taken: 20.09 seconds, Fetched: 14 row(s)

表的优化

小表与大表的Join

• 在以前我们写SQL时，通常有个习惯，就是小表放左边，大表放后面，这是因为将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率
• 新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别
• 具体的优化是因为下面这个配置参数,默认是true，如果我们设置为false,那么我们将小表放左边和放右边，效率就会有明显的不同

set hive.auto.convert.join = true;

大表与大表的Join

空Key过滤

实际开发过程中，数据集并不是都是有数据的，常常会遇到NULL字段，但是根据业务不同，我们在大表间的合并时，对NULL的处理也有所不同，根据业务不同可以分为下面两种情况

• 不需要字段为Null

• 此时我们可以先过滤Null然后再进行Join，这样还可以避免笛卡尔积的发生

• 非 inner join(因为inner join 默认会获取非Null数据)

• 此时我们先Join再过滤Null

案例（测试数据可以后台联系我）

• 有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够
• 此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据
• 我们需要在SQL语句中进行过滤。例如key对应的字段为空

进行示例前，我们先开启历史服务器，便于我们观察

• 配置mapred-site.xml

# 配置历史服务器
<property>
<name>mapreduce.jobhistory.address</name>
<value>hadoop102:10020</value>
</property>
<property>
    <name>mapreduce.jobhistory.webapp.address</name>
    <value>hadoop102:19888</value>
</property>

• 启动历史服务器

sbin/mr-jobhistory-daemon.sh start historyserver

• 查看jobhistory

http://hadoop102:19888/jobhistory

测试数据(这个数据是很久之前模拟用户使用搜索引擎时的记录)

# 总共大概100万条，我只展示一部分
vim /opt/module/hive/dbdata/nullid

\N	20111230000005	57375476989eea12893c0c3811607bcf	奇艺高清	1	1	http://www.qiyi.com/
\N	20111230000005	66c5bb7774e31d0a22278249b26bc83a	凡人修仙传	3	1	http://www.booksky.org/BookDetail.aspx?BookID=1050804&Level=1
\N	20111230000007	b97920521c78de70ac38e3713f524b50	本本联盟	1	1	http://www.bblianmeng.com/
\N	20111230000008	6961d0c97fe93701fc9c0d861d096cd9	华南师范大学图书馆	1	1	http://lib.scnu.edu.cn/
\N	20111230000008	f2f5a21c764aebde1e8afcc2871e086f	在线代理	2	1	http://proxyie.cn/
\N	20111230000009	96994a0480e7e1edcaef67b20d8816b7	伟大导演	1	1	http://movie.douban.com/review/1128960/
\N	20111230000009	698956eb07815439fe5f46e9a4503997	youku	1	1	http://www.youku.com/
\N	20111230000009	599cd26984f72ee68b2b6ebefccf6aed	安徽合肥365房产网	1	1	http://hf.house365.com/
\N	20111230000010	f577230df7b6c532837cd16ab731f874	哈萨克网址大全	1	1	http://www.kz321.com/
\N	20111230000010	285f88780dd0659f5fc8acc7cc4949f2	IQ数码	1	1	http://www.iqshuma.com/
\N	20111230000010	f4ba3f337efb1cc469fcd0b34feff9fb	推荐待机时间长的手机	1	1	http://mobile.zol.com.cn/148/1487938.html

• 创建空id表

// 创建空id表
create table nullidtable(id bigint, t bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

• 加载空id数据到空id表中

load data local inpath '/opt/module/hive/dbdata/nullid' into table nullidtable;

• 测试不过滤空id

insert overwrite table jointable select n.* from nullidtable n
left join bigtable o on n.id = o.id;
/*
用时: 
Time taken: 42.038 seconds
Time taken: 37.284 seconds
*/

• 测试过滤空id

insert overwrite table jointable select n.* from (select * from nullidtable where id is not null ) n  left join bigtable o on n.id = o.id;

/*
用时: 
Time taken: 31.725 seconds
Time taken: 28.876 seconds
*/

空key转换

• 有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中
• 此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分布到不同的reducer上

不随机分布空null值

-- 设置5个reduce个数
set mapreduce.job.reduces = 5;
-- JOIN两张表
insert overwrite table jointable
select n.* from nullidtable n left join bigtable b on n.id = b.id;

通过历史服务器，我们可以发现出现了数据倾斜，第一条的执行时间要远远大于其他

使用随机分布空null值

-- 设置5个reduce个数
set mapreduce.job.reduces = 5;
-- JOIN两张表
insert overwrite table jointable
select n.* from nullidtable n full join bigtable o on 
nvl(n.id,rand()) = o.id;

下图可以发现，我们刚才的数据倾斜基本上得到了控制（数据倾斜不可能完全消除）

Group By

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了,如下图

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

我们可以通过开启Map端聚合来减少负载均衡

（1）是否在Map端进行聚合，默认为True
set hive.map.aggr = true
（2）在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000
（3）有数据倾斜的时候进行负载均衡（默认是false）
set hive.groupby.skewindata = true

开启Map端聚合来减少负载均衡原理：

• 当选项设定为 true，生成的查询计划会有两个MR Job
• 第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果
• 这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；
• 第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作

Count(Distinct) 去重统计

• 数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成
• 这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成
• 一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换,但是需要注意group by造成的数据倾斜问题

笛卡尔积

笛卡尔积在Hadoop介绍过了，我们尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积

行列过滤

• 列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
• 行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤
• 在HQL中，当我们使用了where，底层会优先执行过滤条件，通常称这个操作为谓词下推

• 意思就是当sql语句不是很长的时候，它会将HQL语句中的where过滤条件先执行再进行关联操作，第二种相当于手动进行谓词下推操作，子查询进行了过滤之后，在进行联表操作

分区和分桶

使用了分区表和分桶表可以大大提高我们的查询效率，具体的创建和使用方法我在单独的那一章介绍过了：

并行执行&JVM重用

JVM重用

• JVM重用我在Hadoop优化也讲过了，就不再重复了
  并行执行
• 默认情况下，Hive一次只会执行一个阶段
• 不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的
• 这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成
• 在hive中,通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加

set hive.exec.parallel=true;  //打开任务并行执行，默认为false
set hive.exec.parallel.thread.number=16;//同一个sql允许最大并行度，默认为8。

当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来

严格模式

分区表不使用分区过滤

• 将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。
• 换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。
• 没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表

set hive.strict.checks.no.partition.filter=true;

使用order by没有limit过滤

• 将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语
• 因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理
• 强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间

set hive.strict.checks.orderby.no.limit = true;

笛卡尔积判定

• 将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。

hive.strict.checks.cartesian.product

执行计划

实际开发中，我们要善用执行计划来查看我们的HQL语句，这样可以大大减少因为语法问题导致时间的浪费

语法格式

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query

（1）查看下面这条语句的执行计划
hive (default)> explain select * from emp;
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;
（2）查看详细执行计划
hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;

总结

Hive的所有知识点到这里就结束了，下一章是一综合案例，综合案例完毕我会总结一下整个Hive的内容