五种关键字是有严格顺序的
一个SQL语句往往会产生多个临时视图，那么这些关键字的执行顺序就非常重要了，因为你必须了解这个关键字是在对应视图形成前的字段进行操作还是对形成的临时视图进行操作，这个问题在使用了别名的视图尤其重要。按照如下顺序进行执行。
where → group by → having → order by → limit
首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数)
然后通过Group By关键字后面指定的分组条件将筛选得到的视图进行分组
接着根据Having关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉
然后按照Order By语句对视图进行排序
最后Limit 规定返回的结果集大小，这样最终的结果就产生了。
PS:
凡是在group by后面出现的字段，必须同时在select后面出现；
凡是在select后面出现的、同时未在聚合函数中出现的字段，必须同时出现在group by后面.
having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。
where和having的区别:
where是先过滤再分组(对原始数据过滤),where限定聚合函数
hive> select count(*),age from tea where id>18 group by age;
having是先分组再过滤(对每个组进行过滤,having后只能跟select中已有的列)
hive> select age,count(*) c from tea group by age having c>2;
group by后面没有的列,select后面也绝不能有(聚合函数除外)
hive> select ip,sum(load) as c from logs group by ip sort by c desc limit 5;
distinct关键字返回唯一不同的值(返回age和id均不相同的记录)
hive> select distinct age,id from tea;
hive只支持Union All,不支持Union
hive的Union All相对sql有所不同,要求列的数量相同,并且对应的列名也相同,但不要求类的类型相同(可能是存在隐式转换吧)
select name,age from tea where id<80
union all
select name,age from stu where age>18;
Order By特性：
对数据进行全局排序，只有一个reducer task，效率低下。
与mysql中 order by区别在于：在 strict 模式下，必须指定 limit，否则执行会报错
使用命令set hive.mapred.mode; 查询当前模式
使用命令set hive.mapred.mode=strick; 设置当前模式
hive> select * from logs where date='2015-01-02' order by te;
FAILED: SemanticException 1:52 In strict mode,
if ORDER BY is specified, LIMIT must also be specified.
Error encountered near token 'te'
在order by 状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无法输出结果的情况，如果进行 limit n ，那只有 n * map number 条记录而已。只有一个reduce也可以处理过来。
对于分区表，还必须显示指定分区字段查询
hive> select * from logs order by te limit 5;
FAILED: SemanticException [Error 10041]:
No partition predicate found for Alias "logs" Table "logs"
Sort BY特性：
可以有多个Reduce Task（以DISTRIBUTE BY后字段的个数为准）。也可以手工指定：set mapred.reduce.tasks=4;
每个Reduce Task 内部数据有序，但全局无序
set mapred.reduce.tasks = 2;
insert overwrite local directory '/root/hive/b'
select * from logs sort by te;
上述查询语句，将结果保存在本地磁盘 /root/hive/b ，此目录下产生2个结果文件：000000_0 + 000001_0 。每个文件中依据te字段排序。
sort by不是全局排序，其在数据进入reducer前完成排序.
因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序。
sort by 不受 hive.mapred.mode 是否为strict ,nostrict 的影响
sort by 的数据只能保证在同一reduce中的数据可以按指定字段排序。
使用sort by 你可以指定执行的reduce 个数 （set mapred.reduce.tasks=<number>）,对输出的数据再执行归并排序，即可以得到全部结果。
注意：可以用limit子句大大减少数据量。使用limit n后，传输到reduce端（单机）的数据记录数就减少到n* （map个数）。否则由于数据过大可能出不了结果。
Distribute by特性：
按照指定的字段对数据进行划分到不同的输出 reduce 文件中
distribute by相当于MR 中的paritioner，默认是基于hash 实现的
distribute by通常与Sort by连用
set mapred.reduce.tasks = 2;
insert overwrite local directory '/root/hive/b'
select * from logs distribute by date sort by te;
Cluster By特性：
如果 Sort By 和 Distribute By 中所有的列相同，可以缩写为Cluster By以便同时指定两者所使用的列。
注意被cluster by指定的列只能是降序，不能指定asc和desc。一般用于桶表
set mapred.reduce.tasks = 2;
insert overwrite local directory '/root/hive/b'
select * from logs cluster by date;
网友评论