Hive是将符合SQL语法的字符串解析生成可以在Hadoop上执行的MapReduce的工具。

使用Hive尽量按照分布式计算的一些特点来设计sql，和传统关系型数据库有区别，

所以需要去掉原有关系型数据库下开发的一些固有思维。

基本原则：

1：尽量尽早地过滤数据，减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段

select ... from A
join B
on A.key = B.key
where A.userid>10
     and B.userid<10
        and A.dt='20120417'
        and B.dt='20120417';

应该改写为：

select .... from (select .... from A
                  where dt='201200417'
                                    and userid>10
                              ) a
join ( select .... from B
       where dt='201200417'
                     and userid < 10   
     ) b
on a.key = b.key;

2：尽量原子化操作，尽量避免一个SQL包含复杂逻辑

可以使用中间表来完成复杂的逻辑

drop table if exists tmp_table_1;
create table if not exists tmp_table_1 as
select ......;
 
drop table if exists tmp_table_2;
create table if not exists tmp_table_2 as
select ......;
 
drop table if exists result_table;
create table if not exists result_table as
select ......;
 
drop table if exists tmp_table_1;
drop table if exists tmp_table_2;

3：单个SQL所起的JOB个数尽量控制在5个以下

 

4：慎重使用mapjoin,一般行数小于2000行，大小小于1M(扩容后可以适当放大)的表才能使用,小表要注意放在join的左边（目前TCL里面很多都小表放在join的右边）。

否则会引起磁盘和内存的大量消耗

 

5：写SQL要先了解数据本身的特点，如果有join ,group操作的话，要注意是否会有数据倾斜

如果出现数据倾斜，应当做如下处理：

set hive.exec.reducers.max=200;

set mapred.reduce.tasks= 200;---增大Reduce个数

set hive.groupby.mapaggr.checkinterval=100000 ;--这个是group的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置

set hive.groupby.skewindata=true; --如果是group by过程出现倾斜 应该设置为true

set hive.skewjoin.key=100000; --这个是join的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置

set hive.optimize.skewjoin=true;--如果是join 过程出现倾斜 应该设置为true

 

6：如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%

insert overwite table tablename partition (dt= ....)
select ..... from (
                   select ... from A
                   union all
                   select ... from B
                   union all
                   select ... from C
                               ) R
where ...;

 

可以改写为：

insert into table tablename partition (dt= ....)
select .... from A
WHERE ...;
 
insert into table tablename partition (dt= ....)
select .... from B
WHERE ...;
 
insert into table tablename partition (dt= ....)
select .... from C
WHERE ...;


join、distinct的情况，reduce过程较慢的情况：


Reduce过程比较慢的现象又可以分为两类： 

情形一：map已经达到100%,而reduce阶段一直是99%,属于数据倾斜 

情形二：使用了count(distinct)或者group by的操作，现象是reduce有进度但是进度缓慢，31%-32%-34%...一个附带的提示是使用reduce个数很可能是1 

下面，笔者将以亲身经历的两次优化过程为例，给大家说明下笔者优化的思路及过程： 

情形二（distinct）： 

示例sql: 

select count(distinct A),count(B),count(distinct C) from T;


T的数据量峰值时候大概在2亿左右，文件大小约10G+,优化的思路： 

1. 问题产生的RC是需要在大数据集下做多个字段的distinct，而且还是多个字段，所以最先需要设置set hive.groupby.skewindata=true 

打散去重字段,但是目前hive不支持在一个sql中去重几个不同字段需要拆分后join； 

2. 因为T是外部装载进入hive的，文件比较散乱，考虑先使用合并小文件，确保Map过程中不会因为资源不足而持续append状态； 

3. 适当减少每个reduce处理的数据量，默认1G 

综上，调整的参数如下： 

set mapreduce.job.queuename=root.mapreduce.hourlyetl; 
set mapred.min.split.size=128000000; 
set mapred.min.split.size.per.node=128000000; 
set mapred.min.split.size.per.rack=128000000; 
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; 
set hive.exec.reducers.bytes.per.reducer=100000000; 
set hive.groupby.skewindata=true;

代码调整为 

select t1.a,t2.c 
from 
( 
select count(distinct A) as a from T 
)t1 join 
( 
select count(distinct C) as c from T 
)t2 
;


Job执行时间从13min缩减到5min(同一数据量下的T)