数据仓库-Hive基础（六） Hive 查询语法

SELECT

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[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]

1. order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2. sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3. distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4. cluster by(字段) 除了具有distribute by的功能外，还会对该字段进行排序.

因此，如果distribute 和sort字段是同一个时，此时， cluster by = distribute by + sort by。

查询语法

全表查询

select * from score;

选择特定列

select s_id ,c_id from score;

列别名

select s_id as myid ,c_id from score;

常用函数

求总行数（count）

select count(1) from score;

求分数的最大值（max）

select max(s_score) from score;

求分数的最小值（min）

select min(s_score) from score;

求分数的总和（sum）

select sum(s_score) from score;

求分数的平均值（avg）

select avg(s_score) from score;

LIMIT语句

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

select * from score limit 3;

WHERE语句

1. 使用WHERE 子句，将不满足条件的行过滤掉。
2. WHERE 子句紧随 FROM 子句。

select * from score where s_score > 60;

比较运算符

Xnip2020-06-15_15-32-04
Xnip2020-06-15_15-32-41

举几个例子

• 查询分数等于80的所有的数据

select * from score where s_score = 80;

• 查询分数在80到100的所有数据

select * from score where s_score between 80 and 100;

• 查询成绩为空的所有数据

select * from score where s_score is null;

• 查询成绩是80和90的数据

select * from score where s_score in(80,90);

LIKE 和 RLIKE

1. 使用LIKE运算选择类似的值
2. 选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。 
_ 代表一个字符。

RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

• 查找以8开头的所有成绩

select * from score where s_score like '8%';

• 查找第二个数值为9的所有成绩数据

select * from score where s_score like '_9%';

• 查找s_id中含1的数据

select * from score where s_id rlike '[1]'; # like '%1%'

逻辑运算符

Xnip2020-06-15_15-36-15

• 查询成绩大于80，并且s_id是01的数据

select * from score where s_score >80 and s_id = '01';

• 查询成绩大于80，或者s_id 是01的数

select * from score where s_score > 80 or s_id = '01';

• 查询s_id 不是 01和02的学生

select * from score where s_id not in ('01','02');

分组

GROUP BY 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

• 计算每个学生的平均分数

select s_id ,avg(s_score) from score group by s_id;

• 计算每个学生最高成绩

select s_id ,max(s_score) from score group by s_id;

HAVING 语句

having与where不同点

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

• 求每个学生的平均分数

select s_id ,avg(s_score) from score group by s_id;

• 求每个学生平均分数大于85的人

select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

JOIN 语句

1. 等值 JOIN

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

select s.s_id,s.s_score,stu.s_name,stu.s_birth from score s join student stu on s.s_id = stu.s_id;

2. 表的别名

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。

合并老师与课程表

select * from techer t join course c on t.t_id = c.t_id;

3. 内连接

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

select * from techer t inner join course c on t.t_id = c.t_id;

4. 左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。 查询老师对应的课 程

select * from techer t left join course c on t.t_id = c.t_id;

5. 右外连接

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

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

6. 多表连接

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

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

select * from teacher t 
left join course c 
on t.t_id = c.t_id 
left join score s 
on s.c_id = c.c_id 
left join student stu 
on s.s_id = stu.s_id;

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

排序

1.全局排序

Order By：全局排序，一个reduce

1. 使用 ORDER BY 子句排序 ASC（ascend）: 升序（默认） DESC（descend）: 降序
2. ORDER BY 子句在SELECT语句的结尾。

查询学生的成绩，并按照分数降序排列

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

查询学生的成绩，并按照分数升序排列

SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score asc;

2. 按照别名排序

按照分数的平均值排序

select s_id ,avg(s_score) avg from score group by s_id order by avg;

3.多个列排序

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

select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

4.每个MapReduce内部排序（Sort By）局部排序

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

设置reduce个数

set mapreduce.job.reduces=3;

查看设置reduce个数

set mapreduce.job.reduces;

查询成绩按照成绩降序排列

select * from score sort by s_score;

将查询结果导入到文件中（按照成绩降序排列）

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

分区排序（DISTRIBUTE BY）

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

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

设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去

set mapreduce.job.reduces=7;

通过distribute by 进行数据的分区

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

CLUSTER BY

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

select * from score cluster by s_id; 

select * from score distribute by s_id sort by s_id;