hive sql 优化

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. 小表要注意放在join的左边,原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。否则会引起磁盘和内存的大量消耗

5. 写SQL要先了解数据本身的特点，如果有join ,group操作的话，要注意是否会有数据倾斜(所谓数据倾斜，说的是由于数据分布不均匀，个别值集中占据大部分数据量，加上hadoop的计算模式，导致计算资源不均匀引起性能下降。)

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

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 ...;

7. 对分区表进行操作需要对分区进行过滤（如：ds=$yday）。 特别是在JOIN操作的时候，分区过滤（如：ds=$yday）需要放到 ON语句 或子查询 里面。

• 不能放到ON后面的WHERE里，这样会扫描所有表，最后才判断分区。也就是说程序会先执行JOIN操作，才会执行最后的WHERE操作。

8. 在JOIN操作中，后面被连续JOIN且同一字段，只会执行一个mapreduce操作。

推荐的: SELECT * FROM a LEFT OUTER JOIN b ON a.t=b.t LEFT OUTER JOIN c ON a.t=c.t;

 

效率低下的: SELECT * FROM a LEFT OUTER JOIN b ON a.t=b.t LEFT OUTER JOIN c ON b.t=c.t;