hive查询和优化

1：Local Model(本地模式):

select user,item from table limit 10;(生成mapreduce)

select * from table limit 10;(不会生存mapreduce)

 

2：列剪裁 ：hive在读数据的时候，可以只读取查询中所需要用到的列

 

3：分区剪裁：在查询的过程中减少不必要的分区

使用explain dependency语法，获取table和partition

 

4：hive的优化机制减少job数：如果join的key相同，不管有多少个表，都会合并为一个mapreduce

(1) select a.val,b.val,c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1) 1个mapreduce

(2) select a.val,b.val,c.val from a join b on (a.key = b.key1) join c in (c.key = b.key2) 2个

 

5：job输入输出优化

(1) insert overwrite table tmp1 select .. from a where 条件1

(2) insert overwrite table tmp1 select .. from a where 条件2

上述合为1个

from a 

          insert overwrite table tmp1 select ... where 条件1

          insert overwrite table tmp2 select ... where 条件2

 

6：避免笛卡儿积

select ... from woa_all_device_info_his A

left outer join(

              select * from woa_all_info_his B

              where  条件

) C

on A.app_id = C.app_id and A.imei = C.imei

 

7: 在join前过滤掉不需要的数据：包括分区过滤和条件过滤

 

8：小表放前大表放后：因为在Reduce阶段，位于join操作符左边的表的内容会被加载进内存，载入条目较少的表，可以有效减少内存溢出。当小表与大表join时，采用mapjoin，即在map端完成。可以避免数据倾斜。

select /*+mapjoin(b)*/ a.key,a.value from a join b on a.key = b.key

 

9: left semi join : 限制条件只能在on子句中设置过滤条件，在where子句，select子句或其他地方过滤不行

select a.key,b.key from a left semi join b on (a.key = b.key)

left semi join 与 join 的区别：b表有重复值的情况下left semi join 产生一条，join会产生多条

 

10：union all优化：局限于非嵌套查询

(1)

select * from (

             select c1,c2,c3 from t1 group by c1,c2,c3

             union all

             select c1,c2,c3 from t2 group by c1,c2,c3

) t3;

(2)

select * from (

             select * from t1

             union all

             select * from t2

) t3 group by c1,c2,c3

 

11: Distinct 与 Group by（去重优化）：使用group by替代distinct

 

12：排序优化

1、order by实现全局排序，一个reduce实现，由于不能并发执行，所以效率低,在hive.mapred.mode=strict模式下，使用order by时必须添加limit限制，能够大幅减少reducer数据规模

2、distribute by,sort by实现部分有序，单个reduce输出的结果是有序，效率高，和distribute by一起使用

3、cluster by col1 = distribute col1 sort by col1

 

13:  数据倾斜：任务进度只有reduce子任务未完成，因为处理的数据量和其他的reduce差异过大

 

reduce处理的数据量不一致，有的大有的小，应该与shuffle排序后的作为reduce的输入数据有关。http://www.cnblogs.com/ggjucheng/archive/2013/01/03/2842860.html

 

14：视图可以允许保存一个查询并像对待表一样对待这个查询进行操作。

(1): 使用视图来降低查询复杂度（当具有嵌套查询时）

from(select * from people join cart on ( cart.people_id = people.id) where firstname='john') select a.lastname where a.id =3

create view shorter join as select * from people join cart on (cart.people_id = people.id) where firstname = 'john'

select lastname from shorter where id = 3

(2): 使用视图来限制基于条件过滤的数据:通过创建视图来限制数据访问可以用来保护信息不被随意查询

(3): 动态分区中的视图和map类型

(4): hive会先解析视图，然后使用解析结果再来解析整个查询语句，（查询语句和视图语句可能会合并一个单一的实际查询，如果视图有个limit 100，使用整个视图有个limit 200，最终只会limit100）

(5):没有固化视图

 

15: hive应该避免对非标准化数据进行连接操作。

16: 同一份数据多种处理

如：

insert overwrite table sales select * from history where action = ‘purchased’ 

insert overwrite table credits select * from history where action = ‘returned’

修改为

from history 

insert overwrite table sales select * where action = ‘purchased’ 

insert overwrite table sales select * where action = ‘returned’ 

 

17: 分桶表数据存储(当不能形成合理的分区时，避免创建太多的分区，可以采用分桶技术)

分桶是将数据集分解成更容易管理的若干部分的技术

同样需要一个分桶字段