HiveSQL常用（下篇：使用技巧与优化）

--disTinct去重时，如果存在NULL，结果会异常，Hive不会将null值归为一个值处理，此时需要给NULL进行转换
SELEct disTinct nvl(column1,''),nvl(column2,0) from t;

--count(*)、count(1)对所有行进行统计，包括null行，count(column_Name)只对该列中非null的进行统计

--Hive中要避免使用COunt(disTinct)，它无法进行聚合操作，只在一个reduce上完成，容易出现性能瓶颈甚至oom内存溢出，使用group by来替代
--count disTinct
SELEct col1,count(disTinct id) as did
from t
group by col1;
--使用group by优化替代
SELEct col1,count(id) as did
from(SELEct col1,id from t group by col1,id) as temp
group by col1;

--subquerys子查询:hive只支持from和where后的子查询--如果子查询中包含null值，不能使用Not in(not in会报错，in不会)--不推荐使用in/not in，可使用exists/not exists替代,支持子查询中的多值匹配
--not exists和left join可以有等价写法
--not exists
SELEct a,b
from t1
where not exists(SELEct 1 from t2 where t1.a=t2.a and t1.b=t2.b);

--等价not exists的left join写法
SELEct t1.a,t2.b
from t1
left join t2
on (t1.a=t2.a and t1.b=t2.b)
where t2.a is null;

--left semi join 替代 in和exists,效率更高
--LEFT SEMI JOIN（左半连接）是IN/EXISTS子句查询的一种更高效的实现
--LEFT SEMI JOIN 的限制是：JOIN 子句中右边的表只能在ON 子句中设置过滤条件，在WHERE 子句、SELECT 子句或其他地方过滤都不行
--LEFT SEMI JOIN 只会显示出左边表的字段，left semi join会掉右表中重复的记录，不会因为右表重复key join出多条
--in/exists
SELECT a.key, a.value
FROM a
WHERE a.key in
(SELECT b.key
FROM B);
--left semi join替代in/exists
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)

--ORDER BY 全局排序，默认了reducer个数为1，只有一个Reduce任务，效率低下，如果对大数据集进行order by排序可能会造成性能瓶颈，造成reduce的时间非常长
--如果在Strict模式下使用order by语句，那么必须要在语句中加上limit关键字，因为执行order by只启动单个reduce，如果排序的结果集过大，那么执行时间会很久的原因
set hive.mapred.mode=nonStrict; (default value / 默认值)
set hive.mapred.mode=Strict;
--order by会引发全局排序，数据量较小order by即可（Hive中尽量不要使用order by，除非非常确定结果集非常小）
--实际场景中一般先使用sort by再使用order by效率更高一些，使用diStribute和sort进行分组排序，sort by+order by，sort by过程可以设置reducer个数（n），order by过程用n个reduce的输出文件进行一次全排序，得到最终结果
--sort by&diStribute by
--sort by只能保证在单个reduce内有序
SELEct * from baidu_click diStribute by product_line sort by click desc;
SELEct * from t diStribute by id sort by id;
--diStribute指定map输出结果是如何分配的，上句中相同的id会被分配到同一个reduce上去处理，然后再通过sort by对各个reduce上的id进行排序（被diStribute by设定的字段为KEY，数据会被HASH分发到不同的reducer机器上，然后sort by会对同一个reducer机器上的每组数据进行局部排序）
--cluster by(diStribute by + sort by替代方案)
--当diStribute by和sort by的字段完全一致时，等价于cluster by,但cluster by排序只能是升序排序，不能指定排序规则为ASC或者DESC
--cluster by 和 diStribute by 是很相似的,最大的不同是, cluster by 里含有一个分桶的方法
SELEct * from emp cluster by deptno;
SELEct * from emp diStribute by deptno sort by deptno; 

--常见两种高效的排序实现
--可先通过一个group by的子查询来取一个小的结果集，然后再对这个结果集进行全局排序
SELEct * from (
SELEct id,count(id) as cnt
from t
group by id) as temp
order by temp.cnt;

--高效实现top排序
--先取出各个结果集的top n，再取出全局的top n
SELEct a.id,salary
from (SELEct id,salary from t1 diStribute by sort by salary desc limit 10) as temp
order by temp.salary limit 10;