Hive系列之-4、Hive的查询语法

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

大数据系列文章目录

SELECT语句

语句结构

基本语法:

SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING where_condition]
[ORDER BY col_list]
[CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

解释:

1、 order by 用于全局排序，就是对指定的所有排序键进行全局排序，使用ORDER BY的查询语句，最后会用一个Reduce Task来完成全局排序。

2、 sort by 用于分区内排序，即每个Reduce任务内排序。，则sort by只保证每个reducer的输出
有序，不保证全局有序。

3、 distribute by (字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、 cluster by (字段) 除了具有Distribute by的功能外，还兼具sort by的排序功能。。
因此，如果distribute by和sort by字段是同一个时，此时，cluster by = distribute by + sort by

全表查询

select * from score;

选择特定列查询

select sid ,cid from score;

列别名

select sid as myid ,cid from score;

常用函数

1）求总行数（count）
 select count(1) from score;
2）求分数的最大值（max）
 select max(sscore) from score;
3）求分数的最小值（min）
 select min(sscore) from score;
4）求分数的总和（sum）
 select sum(sscore) from score;
5）求分数的平均值（avg）
 select avg(sscore) from score;

LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

WHERE语句

1）使用WHERE 子句，将不满足条件的行过滤掉。
2）WHERE 子句紧随 FROM 子句。
3）案例实操
查询出分数大于60的数据

select * from score where sscore > 60;

运算符

比较运算符

1、操作符



2、案例实操

（1）查询分数等于80的所有的数据
select * from score where sscore = 80;
（2）查询分数在80到100的所有数据
select * from score where sscore between 80 and 100;
（3）查询成绩为空的所有数据
select * from score where sscore is null;
（4）查询成绩是80或 90的数据
select * from score where sscore in(80,90);

3、LIKE和RLIKE
1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4）案例实操

（1）查找以8开头的所有成绩
select * from score where sscore like '8%';
（2）查找第二个数值为9的所有成绩数据
select * from score where sscore like '_9%';
（3）查找id中含1的所有成绩信息
select * from score where sid rlike '[1]';		

逻辑运算符

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

案例实操

（1）查询成绩大于80，并且sid是01的数据
select * from score where sscore >80 and sid = '01';
（2）查询成绩大于80，或者sid  是01的数
select * from score where sscore > 80 or sid = '01';
（3）查询sid  不是 01和02的学生
select * from score where sid not in ('01','02');

分组

GROUP BY语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。注意使用group by分组之后，select后面的字段只能是分组字段和聚合函数。

案例实操：
（1）计算每个学生的平均分数

select sid ,avg(sscore) from score group by sid;

（2）计算每个学生最高成绩

select sid ,max(sscore) from score group by sid;

HAVING语句

1、having与where不同点

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。

2、案例实操：

-- 求每个学生的平均分数
select sid ,avg(sscore) from score group by sid;
-- 求每个学生平均分数大于85的人
select sid ,avg(sscore) avgscore from score group by sid having avgscore > 85;

JOIN语句

Hive的join操作只支持等值连接

内连接（INNER JOIN）

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select * from teacher t, course c where t.tid = c.tid; #隐式内连接
select * from teacher t inner join course c on t.tid = c.tid; #显式内连接
select * from teacher t join course c on t.tid = c.tid;

左外连接（LEFT OUTER JOIN）

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

查询老师对应的课程

select * from teacher t left join course c on t.tid = c.tid;

右外连接（RIGHT OUTER JOIN）

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select * from teacher t right join course c on t.tid = c.tid;

满外连接（FULL OUTER JOIN）

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

SELECT * FROM teacher t FULL JOIN course c ON t.tid = c.tid ;

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

select * from teacher t 
left join course c 
on t.tid = c.tid
left join score s 
on s.cid = c.cid
left join student stu 
on s.sid = stu.sid;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表teacher和表course进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。

排序

Order By-全局排序
Order By：全局排序，一个reduce

1、使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

2、ORDER BY 子句在SELECT语句的结尾。

3、案例实操
（1）查询学生的成绩，并按照分数降序排列

SELECT * FROM student s LEFT JOIN score sco ON s.sid = sco.sid ORDER BY sco.sscore DESC;

(2)按照分数的平均值排序

select sid ,avg(sscore) avg from score group by sid order by avg;

(3)按照学生id和平均成绩进行排序

select sid ,avg(sscore) avg from score group by sid order by sid,avg;

Sort By-每个MapReduce内部局部排序

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。

1)设置reduce个数
 set mapreduce.job.reduces=3;
2)查看设置reduce个数
 set mapreduce.job.reduces;
3）查询成绩按照成绩降序排列
 select * from score sort by sscore;
4)将查询结果导入到文件中（按照成绩降序排列）
 insert overwrite local directory '/export/data/exporthive/sort' select * from score sort by sscore;

Distribute By-分区排序
Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操：

先按照学生id进行分区，再按照学生成绩进行排序。

1)设置reduce的个数，将我们对应的sid划分到对应的reduce当中去
set mapreduce.job.reduces=7;
2)通过distribute by进行数据的分区
insert overwrite local directory '/export/data/exporthive/distribute' select * from score distribute by sid sort by sscore;  

Cluster By

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
以下两种写法等价:

select * from score cluster by sid; 
select * from score distribute by sid sort by sid;

结束

Hive的查询语法目前就介绍这么多，后面还有Hive的数据压缩和Hive的数据存储格式及Hive的调优，敬请期待。