Hive优化----编写SQL时注意事项

痛定思痛。 2023-01-21 15:16 3阅读 0赞

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