HIVE数据库查询优化

1.join的优化

join语句在使用时，先进行join操作，再进行where语句，因此加更多where语句限制并不能加快hive数据库的查询速度。

MAPJOIN注意事项：新版本中应该先使用set hive.auto.convert.join = true; 再添加/*+ MAPJOIN(b) */

由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用；案例：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

最好修改为：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

stream化

在join操作的每一个mapred程序中，hive都会把出现在join语句中相对靠后的表的数据stream化，相对靠前的变的数据缓存在内存中。当然，也可以手动指定stream化的表：SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

注意事项：如果是partition字段，则必须放到where条件中

partition的例子：查询所有学生的信息并将每个学生的各科成绩降序排列
select t.*,row_number() over(partition by t.uname order by t.uscore desc) rm from tb_sc t;

2、合并小文件

文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

· hive.merge.mapfiles = true是否和并 Map 输出文件，默认为 True

· hive.merge.mapredfiles = false是否合并 Reduce 输出文件，默认为 False

· hive.merge.size.per.task = 256*1000*1000合并文件的大小

3、Hive实现(not) in

通过left outer join进行查询,（假设B表中包含另外的一个字段 key1 

select a.key from a left outer join b on a.key=b.key where b.key1 is null

通过left semi join 实现 in

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

Left semi join 的限制：join条件中右边的表只能出现在join条件中。

4、排序优化

Order by 实现全局排序，一个reduce实现，效率低

Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字 可以指定map 到 reduce端的分发key）

CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1  根据某两个字段排序

    5、行转列

later view可用作行转列的用法，

 目前Lateral View不支持有上而下的优化。如果使用Where子句，查询可能将不被编译。解决方法见：

此时，在查询之前执行et hive.optimize.ppd=false;

例子：pageAds。它有两个列：pageid，adid_list:

string pageid	Array<int> adid_list
"front_page"	[1, 2, 3]
"contact_page"	[3, 4, 5]

SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

将输出如下结果：

string pageid	int adid
"front_page"	1
"front_page"	2
"front_page"	3
"contact_page"	3
"contact_page"	4

5.count/group可以被使用：

SELECT adid, count(1) FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid GROUP BY adid;