目录
排序
order by | 与SQL类似,对结果集进行全局排序 |
sort by | SQL中没有,只在每个Reducer内对数据进行排序(分区内排序),只能升序。 |
distribute by | 控制Map的输出数据在Reducer中的划分(分区),必须写在sort by之前。 |
cluster by | 相当于distribute by和sort by的结合体,只能升序,无法手动指定排序方向。 |
1. order by
set hive,mapred.mode=strict; #严格模式
set hive.cli.print.header=true; #打印查询结果的表头信息
select table_o_id,table_p_id,table_item
from table order by table_item desc limit 10; #desc降序
order by注意事项:
(1)order by语句默认ASC(升序)排序,排序字段必须出现在select语句中。
(2)在strict(set hive,mapred.mode=strict,默认是nonstrict)模式下,Hive对order by 进行了限制,要求order by子句后面必须跟随limit子句,以防止单个Reducer处理时间过长。
2. sort by
set mapred.reduce.tasks=2;
set * from table sort by t_name;
注意sort by只有升序通常不会单独使用,而是同distribute by一起使用。
3. distribute by
如果distribute by的指定列的值相同,则它们会被送到同一个Reducer中进行处理。
set mapred.reduce.tasks=2;
select * from table distribute by t_id sort by t_name;
4. cluster by
# 如下两个语句是等价的
select A,B,C from table distribute by A sort by A
select A,B,C from table cluster by A
拓展
distribute by与cluster by的区别?
(1)"distribute by" 主要用于指定MapReduce作业的数据分发方式,控制数据如何分发到不同的reduce节点上。可以避免数据倾斜,提高作业的性能和效率。
(2)"cluster by" 主要用于在创建表时指定数据的排序、分区和分布方式的语句,可以提高查询性能和数据处理效率。
分组聚合
Hive提供多种聚合函数。所谓聚合函数是指对一组值进行计算并返回单个值的函数,通常与select语句的group by子句一起使用。
1. 分组(group by)
select t_id,sum(t_quantity) as total
from table
group by t_id having total<=1000
order by total desc limit 10;
group by注意事项:
(1)除了聚合函数外,select语句所选择的列也必须出现在group by子句中。
(2)group by支持使用case when表达式。
(3)group by配合使用having进行过滤。
2. 基础聚合
- max(col):返回组内呆列中的最大值。
- min(col):返回组内某列中的最小值。
- count(*):返回组内总行数,包括值为NULL的行。
- count(expr):返回组内expr 表达式不是NULL的总行数。
- count(DISTINCT expr):返回组内expr唯一且非NULL的行的数量。sum(col):返回组内某列元素的总和。
- avg(col):返回组内某列元素的平均值。
- collect set(col):返回消除了重复元组的数组。
- collect list(col):返回允许存在重复元素的数组。
3. 高级聚合
3.1 grouping sets
grouping sets可以实现对同一个数据集进行多重group by操作,本质上是多个group by进行union all操作。
3.2 cube与rollup
cube 与 rollup的功能比 grouping sets的功能强。cube 子句可对分组列进行所有可能的组合聚合。rollup子句用于在维度结构中计算聚合。
# 如下两个语句等价
group by a, b, c WITH CUBE
group by a, b, c grouping sets
((a, b, c), (a, b), (a, o), (b, c), (a), b), (c), ())
# 如下两个语句等价
group by a, b, c with rollup
group by a, b, c grouping sets((a, b, c),(a, ), (a) ())
总的来说,Hive 的增强聚合功能方便了部分有规律代码的编写,或者说缩短了代码
的长度,其本质仍然是 group by。
拓展
having 与 where子句的区别?
相同:`HAVING` 和 `WHERE` 子句是用于查询数据时的两个重要关键字,用于过滤数据。
不同:`WHERE` 子句用于对行级别数据进行条件过滤,而 `HAVING` 子句用于对分组后的结果进行条件过滤。
(1)`WHERE` 子句:
`WHERE` 子句用于在查询数据时对行进行过滤,只保留满足条件的行。
`WHERE` 子句在 `SELECT` 语句中使用,通常用于过滤行级别的数据。
`WHERE` 子句在数据进行聚合前进行过滤,对每一行的数据进行条件判断,只返回满足条件的行。
select * from table_name where column_name > 10;
(2)`HAVING` 子句:
`HAVING` 子句用于在查询数据时对分组后的结果进行过滤,只保留满足条件的分组。
`HAVING` 子句在 `GROUP BY` 后使用,在数据分组后对每个分组的数据进行条件判断。
`HAVING` 子句通常在对数据进行聚合操作后使用,用于过滤分组后的数据。
select column_name, count(*) from table_name group by column_name having count(*) > 10;
最后,本篇文章是基于我所学所知进行的知识总结,如有误论,虚心接受指正。参考文献是《Hadoop数据仓库实战》,感兴趣的小伙伴可以对其进行深入阅读。