大数据知识点全讲解之Hive（中）
- Hive查询语法
- 基础语法
- 常用函数
- Limit语句
- Where语句
- 分组
- Join语句
- 排序
Hive查询语法
select [ALL | DISTINCT] select_expr, select_expre, ...
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]- order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间
- sort by 不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reduce的输出有序，不保证全局有序
- distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列
- 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语句
- 使用where子句，将不满足条件的行过滤掉
- where子句紧随from子句
查询分数大于60的数据
select * from score where s_score > 60;查询分数在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);查询分数是以8开头的所有数据
select * from score where s_score like '8%';查询学号不是01和02的学生
select * from score where s_id not in ('01','02');分组
GROUP BY语句
Group By语句通常会和聚合函数一起使用，按照一个或者多个队列结果进行分组，然后对每个组执行聚合操作
计算每个学生的最高成绩
select s_id, max(s_score) from score group by s_id;HAVING语句
having与where不同点：
- where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据
- where后面不能写分组函数，而having后面可以使用分组函数
- having只用于group by分组统计语句
求每个平均分数大于85的学生编号：
select s_id, avg(s_score) avgscore from score group by s_id having avgscore>85;Join语句
等值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表的别名
好处：
- 使用别名可以简化查询
- 使用表名前缀可以提高执行效率
select * from teacher t join on course c on t.t_id=c.t_id内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
select * from teacher t inner join course c on t.t_id = c.t_id左外连接
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回
select * from teacher t left join course c on t.t_id = c.t_id右外连接
JOIN操作符右边表中符合WHERE子句的所有记录将会被返回
select * from teacher t right join course c on t.t_id = c.t_id多表连接
连接N个表，至少需要N-1个连接条件
实例：查询老师对应的课程，以及对应的分数，对应的学生
select * from tracher 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对表teacher和表course进行连接操作，然后会再启动一个MapReduce Job将第一个MapReduce Job的输出和表score进行连接操作
排序
全局排序
使用ORDER BY子句排序，在SELECT语句的结尾，默认ASC升序，DESC降序
select * from student s left join score sc on s.s_id = sc.s_id order by sc.s_score desc;按照别名排序
select s_id, avg(s_score) avg from score group by s_id order by avg;多个列排序
select s_id, avg(s_score) avg from from score order by s_id, avg每个MapReduce内部(SortBy)局部排序
每个MapReduce内部进行排序，对全局结果集来说不是排序
1.设置reduce个数
set mapreduce.job.reduce=3;2.查询成绩按照成绩降序排列
select * from score sort by s_score3.将查询结果导入到文件中
insert overwrite local directory '/.../...' select * from score sort by s_score分区排序(DISTRIBUTE BY)
类似MR中的partition，进行分区，结合sort by使用
注意，HIVE要求DISTRIBUTE BY语句要写在SORT BY语句之前
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果
insert overwrite local directory '/.../...' select * from score distribute by s_id sort by s_scoreCLUSTER 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
