hive常见的几种优化手段
Hive调优的几个入手点：
Hive是基于Hadoop框架的，Hadoop框架又是运行在JVM中的，而JVM最终是要运行在操作系统之上的，所以，Hive的调优可以通过如下几个方面入手：
- 操作系统调优
- Hadoop主要的操作系统是Linux，Linux系统调优包括文件系统的选择、cpu的调度、内存构架和虚拟内存的管理、IO调度和网络子系统的选择等等。
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。- JVM的调优
- JVM调优主要包括堆栈的大小、回收器的选择等等。
- Hadoop参数调优
- Hive查询sql性能调优。
Hive总体调优：
- join连接时的优化
- 当多个表进行查询时，从左到右表的大小顺序应该是从小到大（hive在对每行记录操作时会把其他表先缓存起来，直到扫描最后的表进行计算）。
- 当可以使用left semi join 语法时不要使用inner join，前者效率更高（对于左表中指定的一条记录，一旦在右表中找到立即停止扫描）。
- 在where子句中增加分区过滤器。
- 使用内存表（mapjoin）
- 如果所有表中有一张表足够小，则可置于内存中，这样在和其他表进行连接的时候就能完成匹配，省略掉reduce过程。
- 内存连接查询 mapjoin：
在map端完成join操作，不需要用reduce，基于内存做join，属于优化操作。
在map端把小表加载到内存中，然后读取大表，和内存中的小表完成连接操作。其中使用了分布式缓存技术。
不消耗集群的reduce资源（适用于reduce相对紧缺）,减少了reduce操作，加快程序执行，降低网络负载。
占用部分内存，所以加载到内存中的表不能过大，因为每个计算节点都会加载一次。
- 基础语法
select /*+mapjoin(加载入内存的表别名)*/ 表别名1.列1,表别名1.列2,表别名2.列3...
from (select 列1,列2,列3... from 表1) 表别名1
join (select 列1,列2,列3... from 表2) 表别名2
on 表别名1.列1=表别名2.列1
- 同一种数据的多种处理
- 从一个数据源产生的多个数据聚合，无需每次聚合都需要重新扫描一次。
例如，从employee中取出数据分别插入student和person两张表。
低效的写法： insert overwrite table student select * from employee; insert overwrite table person select * from employee;
高效的写法： from employee insert overwrite table student select * insert overwrite table person select *
- 使用limit子句
- limit子句用于限制返回数据的结果集大小。
- limit子句通常位于所有查询的结尾处。
- limit子句示例：
select t1.*,t2.score,t3.score from Student t1
inner join SC t2 on t1.Sid = t2.Sid and t2.Cid = '01'
inner join SC t3 on t1.Sid = t3.Sid and t3.Cid = '02'
where t2.score > t3.score limit 1;
- 设置多个reduce并开启并发执行
- 某个job任务中可能包含众多的阶段，其中某些阶段没有依赖关系可以并发执行，开启并发执行后job任务可以更快的完成。
- 开启并发执行：set hive.exec.parallel=true
- hive的使用禁忌：
- 当表为分区表时，where字句后没有分区字段和限制时，不允许执行。
- 能使用sort by排序的，不要使用order by，当使用order by语句时，请使用limit字段，因为order by只会产生一个reduce任务。
- 限制笛卡尔积的查询。
Hive排序调优
- 假设我们有一张数据量很大的表，表结构如下
我们希望对里面多个字段分组排序，sql如下：
select t1.ip,t1.logtime,t1.logmessage,t1.logstatus from
(select ip,logtime,logmessage,logstatus,logsize from logfile
order by ip,logtime,logmessage,logstatus,logsize asc) t1
group by t1.ip,t1.logtime,t1.logmessage,t1.logstatus limit 100;
很明显，这条sql的reduce阶段只有一个reduce， 这是因为ORDER BY是全局排序，hive只能通过一个reduce进行排序；
优化方案：我们可以使用distribute by和sort by配合使用，来完成排序，这样可以充分利用hadoop资源， 在多个reduce中局部排序，修改后的sql:
select t1.ip,t1.logtime,t1.logmessage,t1.logstatus from
(select ip,logtime,logmessage,logstatus,logsize from logfile
distribute by ip,logtime,logmessage,logstatus
sort by logsize asc) t1
group by t1.ip,t1.logtime,t1.logmessage,t1.logstatus;
Map数量调优
- 通常情况下，作业会通过input的目录（数据块的分布）产生一个或者多个map任务。
- 主要的决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小。
- map分布实例
- 假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数。
- 假设input目录下有3个文件a,b,c,大小分别为10m，20m，130m，那么hadoop会分隔成4个块（10m,20m,128m,2m）,从而产生4个map数。
- 是不是map数越多越好？
- 如果一个任务有很多小文件（远远小于块大小128m）,则每个小文件也会被当做一个块，用一个map任务来完成。
- 一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。 所以map不是越多越好，而是分块大小越接近128越好。 这种情况可以合并小文件，降低map数量。
- 是不是所有分块大小越接近128越好？
- 比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。 这种情况可以拆分文件，添加map数量。
- 所以，map数量的多少，要根据业务逻辑具体调整，并通过文件大小调节map数量。
-
hive合并小文件，减少map数量的设置参数（根据实际情况调整）
- set mapred.max.split.size;
- set mapred.min.split.size.per.node;
- set mapred.min.split.size.per.rack;
- set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
- hive拆分大文件，增加map数量
- set mapred.reduce.tasks
Reduce数量调优
- 是不是reduce数越多越好？
- 同map一样，启动和初始化reduce也会消耗时间和资源。 有多少个reduce,就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题。
- 同样，在设置reduce个数的时候也需要考虑这两个原则：
- 使大数据量利用合适的reduce数；
- 使单个reduce任务处理合适的数据量。
- 默认reduce数量
- hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
- 计算reducer数的公式：总输入数据量/上述参数，如果reduce的输入（map的输出）总大小不超过1G,那么只会有一个reduce任务
- 调整reduce数量的方法
- set hive.exec.reducers.bytes.per.reducer=500000000（动态计算）
- set mapred.reduce.tasks = 15（可直接设置数量）
- 很多时候我们会发现任务中不管数据量多大，不管有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务，出现这种情况的原因：
- 没有group by的汇总或用了Order by（常见）
- 有笛卡尔积
Sql具体优化示例
- 关于子查询
- 过滤子查询中的数据，减少子查询中的数据量。
- 对于分区表要加分区。
- 子查询只选择需要使用到的字段。
- 低效写法：
select a.user_id from dwd.dwd_d_res_mb_five_imei a
inner join dwd.dwd_d_prd_cm_user_info b on a.user_id=b.user_id
where a.service_type='4G' and b.service_type='4G'and
concat(a.month_id,a.day_id)='20160626‘ and b.day_id='26';
- 高效写法：
select a.user_id from
(select user_id from dwd.dwd_d_res_mb_five_imei a
where concat(a.month_id,a.day_id)='20160626' and a.service_type='4G') a
inner join
(select user_id from dwd.dwd_d_prd_cm_user_info b
where b.day_id='26' and b.service_type='4G') b on a.user_id=b.user_id;
- 合理使用union all
- 子查询中union all部分个数大于2，或者每个union all部分数据量很大，应该拆分多段insert。这样执行时间能提升50%。
- 低效写法：
insert overwite table tablename partition (day_id= ....)
select ..... from (
select ... from A union all
select ... from B union all
select ... from C) R
where ...;
- 高效写法:
insert into table tablename partition (day_id= ....)
select .... from A
WHERE ...;
insert into table tablename partition (day_id= ....)
select .... from B
WHERE ...;
insert into table tablename partition (day_id= ....)
select .... from C
WHERE ...;
- 不要使用count(distinct)，避免数据倾斜
- count(distinct)操作会造成数据倾斜，效率较低，数据量一多，极容易出问题。
- 低效写法：
select a, count(distinct b) as c from tbl group by a;
- 高效写法：
select a, count(1) as c from (select a, b from tbl group by a, b) t group by a;
- hive中没有in/exists (not)，使用LEFT OUTER JOIN或LEFT SEMI JOIN
- LEFT OUTER JOIN写法：
SELECT a.key, a.value FROM a LEFT OUTER JOIN b ON (a.key = b.key) WHERE b.key is not NULL and b.key<>’’;
- LEFT SEMI JOIN更为高效，
LEFT SEMI JOIN 的限制是，JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
- 减少job数
- 在开发过程中，会生成多余Job不够高效比如查询某网站日志中访问过页面a和页面b的用户数量
- 低效的写法是面向明细的，先取出看过页面a的用户，再取出看过页面b的用户，然后取交集，sql如下：
select count(1) from
(select distinct user_id from logs where page_name = 'a') a
inner join
(select distinct user_id from logs where page_name = 'b') b
on a.user_id = b.user_id;
- 这个sql会产生2个求子查询的Job，一个用于关联的Job，还有一个计数的Job，一共有4个Job。
- 高效思路是用group by替代join，更加符合M/R的模式，而且生成了一个完全不带子查询的sql，只需要用一个Job就能跑完：
select count(1) from logs
group by user_id
having (count(case when page_name = 'a' then 1 end) > 0
and count(case when page_name = 'b' then 1 end) > 0)
其它优化注意事项
- 查询sql中避免复杂逻辑，原子化操作，查询sql包含复杂逻辑的，可以拆分成中间表。
- join连接key为空时，空的key都hash到一个reduce上去了。高效做法是把空的key和非空的key做区分，空的key不做join操作。
