Hive基础三（查询中常用的语法）

相关链接：
Hive基础一（数据库，表，分区表，视图，导入导出数据）
Hive基础二（join原理和机制，join的几种类型，数据倾斜简单处理）
Hive基础三（查询中常用的语法）

Hive中的SELECT基础语法和标准SQL语法基本一致，支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等。

一，ORDER BY和SORT BY

ORDER BY根据key进行全局排序，由一个Reduce Task来完成。
SORT BY用于分区内排序，即有多个reduce，在每个Reduce任务内排序。
看下面的例子，原表数据为：

hive> select * from lxw1234_com;
OK
5
3
6
2
9
8
1

使用ORDER BY：

hive> select * from lxw1234_com order by id;
1
2
3
5
6
8
9

使用SORT BY：

hive> set mapred.reduce.tasks=2;
hive> select * from lxw1234_com sort by id;
2
5
6
9
1
3
8

设定了2个reduce，从结果可以看出，每个reduce内做了排序。如果reduce数为1，那么ORDER BY和SORT BY的结果是一样的。真实业务环境中，我们的需求大多需要使用ORDER BY全局排序来完成，默认是升序，降序是字段后加desc。

二，DISTRIBUTE BY和CLUSTER BY

distribute by：按照指定的字段或表达式对数据进行划分，输出到对应的Reduce或者文件中，reduce数量大于1才能有效果。
cluster by：除了兼具distribute by的功能，还兼具sort by的排序功能。
使用distribute by：

hive> set mapred.reduce.tasks=2;
hive>INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
distribute BY id;

执行后在本地的/tmp/lxw1234目录中生成了000000_0和000001_0两个文件，是按照奇偶划分的（如果是按照length(id)划分，则长度相同的会划分在一个reduce）：

$cat 000000_0
8
2
6
$cat 000001_0 
1
9
3
5

使用cluster by：

hive> set mapred.reduce.tasks=2;
hive> INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
CLUSTER BY id;
$cat 000000_0
2
6
8
$cat 000001_0
1
3
5
9

注意：使用cluster by之后，每个文件中的id都进行了排序，而distribute by没有。

三，group by的实现原理

以下面的语句为例，以省市为关键字进行分组：

select province,city,count(*) from table1 group by province,city;

思路：既然以省和市作为group by的字段，那就把他们的组合作为map的输出key，输出value置为1，经过shuffle之后，相同的省市组合会被合并，然后进入reduce，拆开省市组合作为两个字段，累加value作为count。
从Hive0.7.0开始HAVING被添加到Hive作为GROUP BY结果集的条件过滤。

四，distinct的实现原理

以下面的语句为例，以省为关键字进行分组，计算去重后的市的数量：

select province, count(distinct city) from table1 group by province;

思路：当只有一个distinct字段时，只需要将Group By字段和Distinct字段组合为map的输出key，value随意，经过shuffle之后，相同key合并，此时便完成了distinct操作（因为合并之后，相同的省后边的市肯定不同），然后先进入combine，把key拆开（省作为新的key，市作为新的value），再经过一次shuffle，相同的省合并，不同的市组成value-list，最后进入reduce即可。
如果是多个distinct呢？参看：
https://tech.meituan.com/hive-sql-to-mapreduce.html

五，UNION ALL和子查询

对两个查询结果进行并集操作，包括重复行，不进行排序，两个查询结果的字段必须一致。
下面这个例子还用了一种将子查询作为一个表的语法，叫做Common Table Expression（CTE）：

with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2;

子查询和标准SQL中的子查询语法和用法基本一致，需要注意的是，Hive中如果是从一个子查询进行SELECT查询，那么子查询必须设置一个别名：

SELECT col FROM (
SELECT a+b AS col
FROM t1
) t2

另外，从Hive0.13开始，在WHERE子句中也支持子查询，比如：

SELECT * FROM A
WHERE A.a IN (SELECT foo FROM B);
-----
SELECT A FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

原文来自：http://lxw1234.com/archives/2015/07/365.htm