DQL语法是平时使用最多的语法,首先来看下DQL语法树。
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
JOIN table_other ON expr
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
不管是写select语句环视看select语句,from关键字之后的表时最重要的,其后面的表可能是一张真实的物理存在的表,也可能是虚拟的表(视图view)
DQL高阶查询cluster、distribute、sort、union。
CLUSTER BY 分桶查询。
--根据学生编号进行分桶查询
select * from student cluster by num;
--其中桶的个数就是reducetask的个数,
--如果用户没有设置,不指定reduce task个数。则hive根据表输入数据量自己评估
--日志显示:Number of reduce tasks not specified. Estimated from input data size: 1
select * from student cluster by num;
--手动设置reduce task个数
--日志显示:Number of reduce tasks not specified. Defaulting to jobconf value of: 2
set mapreduce.job.reduces =2;
select * from student cluster by num;
----分桶查询的结果真的根据reduce tasks个数分为了两个部分,并且每个部分中还根据了字段进行了排序。
--总结:cluster by xx 分且排序的功能
分为几个部分 取决于reducetask个数
排序只能是正序 用户无法改变
--需求:把student表数据根据num分为两个部分,每个部分中根据年龄age倒序排序。
set mapreduce.job.reduces =2;
select * from student cluster by num order by age desc;
select * from student cluster by num sort by age desc;
--FAILED: SemanticException 1:50 Cannot have both CLUSTER BY and SORT BY clauses
DISTRIBUTE BY+SORT BY:相当于相当于把cluster by的功能一分为二。其中
distribute by只负责分,sort by只负责分之后的每个部分排序,并且分和排序的字段可以不一样。
union联合查询,用于将来自多个select的语句结果合并为一个结果集。
--语法规则
select_statement UNION [ DISTINCT|ALL ] select_statement UNION [ALL | DISTINCT] select_statement ...;
--使用DISTINCT关键字与使用UNION默认值效果一样,都会删除重复行。
select num,name from student_local
UNION
select num,name from student_hdfs;
--和上面一样
select num,name from student_local
UNION DISTINCT
select num,name from student_hdfs;
--使用ALL关键字会保留重复行。
select num,name from student_local
UNION ALL
select num,name from student_hdfs limit 2;
--如果要将ORDER BY,SORT BY,CLUSTER BY,DISTRIBUTE BY或LIMIT应用于单个SELECT
--请将子句放在括住SELECT的括号内
SELECT num,name FROM (select num,name from student_local LIMIT 2) subq1
UNION
SELECT num,name FROM (select num,name from student_hdfs LIMIT 3) subq2;
--如果要将ORDER BY,SORT BY,CLUSTER BY,DISTRIBUTE BY或LIMIT子句应用于整个UNION结果
--请将ORDER BY,SORT BY,CLUSTER BY,DISTRIBUTE BY或LIMIT放在最后一个之后。
select num,name from student_local
UNION
select num,name from student_hdfs
order by num desc;