Hive查询
Hive查询 排序和聚集 通过Hive提供的order by子句可以让最终的输出结果整体有序。但是因为Hive是基于Hadoop之上的,要生成这种整体有序的结果,就必须强迫Hadoop只利用一个Reduce来完成处理。这种方式的副作用就是回降低效率。 如果你不需要最终结果整体有序
Hive查询
排序和聚集
通过Hive提供的order by子句可以让最终的输出结果整体有序。但是因为Hive是基于Hadoop之上的,要生成这种整体有序的结果,就必须强迫Hadoop只利用一个Reduce来完成处理。这种方式的副作用就是回降低效率。
如果你不需要最终结果整体有序,你就可以使用sort by子句来进行排序。这种排序操作只保证每个Reduce的输出是有序的。如果你希望某些特定行被同一个Reduce处理,则你可以使用distribute子句来完成。比如:
表student(classNo,stuNo,score)数据如下:
C01 N0101 82
C01 N0102 59
C02 N0201 81
C01 N0103 65
C03 N0302 92
C02 N0202 82
C02 N0203 79
C03 N0301 56
C03 N0306 72
我们希望按照成绩由低到高输出每个班级的成绩信息。执行以下语句:
Select classNo,stuNo,score from student distribute byclassNo sort by score;
输出结果为:
C02 N0203 79
C02 N0201 81
C02 N0202 82
C03 N0301 56
C03 N0306 72
C03 N0302 92
C01 N0102 59
C01 N0103 65
C01 N0101 82
我们可以看到每一个班级里所有的学生成绩是有序的。因为同一个classNo的记录会被分发到一个单独的reduce处理,而同时sort by保证了每一个reduce的输出是有序的。
注意:
为了测试上例中的distribute by的效果,你应该首先设置足够多的reduce。比如上例中有3个不同的classNo,则我们需要设置reduce个数至少为3或更多。如果设置的reduce个数少于3,将会导致多个不同的classNo被分发到同一个reduce,从而不能产生你所期望的输出。设置命令如下:
set mapred.reduce.tasks = 3;
MapReduce脚本
如果我们需要在查询语句中调用外部脚本,比如Python,则我们可以使用transform,map,reduce等子句。
比如,我们希望过滤掉所有不及格的学生记录,只输出及格学生的成绩信息。
新建一个Python脚本文件score_pass.py,内容如下:
#! /usr/bin/env python
import sys
for line in sys.stdin:
(classNo,stuNo,score)= line.strip().split('\t')
ifint(score) >= 60:
print"%s\t%s\t%s" %(classNo,stuNo,score)
执行以下语句
add file /home/user/score_pass.py;
select transform(classNo,stuNo,score) using'score_pass.py' as classNo,stuNo,score from student;
输出结果为:
C01 N0101 82
C02 N0201 81
C01 N0103 65
C03 N0302 92
C02 N0202 82
C02 N0203 79
C03 N0306 72
注意:
1) 以上Python脚本中,分隔符只能是制表符(\t)。同样输出的分隔符也必须为制表符。这个是有hive自身决定的,不能更改,不要尝试使用其他分隔符,否则会报错。同时需要调用strip函数,以去除掉行尾的换行符。(或者直接使用不带参数的line.split()代替。
2) 使用脚本前,先使用add file语句注册脚本文件,以便hive将其分发到Hadoop集群。
3) Transfom传递数据到Python脚本,as语句指定输出的列。
连接(join)
直接编程使用Hadoop的MapReduce是一件比较费时的事情。Hive则大大简化了这个操作。
内连接(inner join)
和SQL的内连相似。执行以下语句查询每个学生的编号和教师名:
Select a.stuNo,b.teacherName from student a join teacherb on a.classNo = b.classNo;
输出结果如下:
N0203 Sun
N0202 Sun
N0201 Sun
N0306 Wang
N0301 Wang
N0302 Wang
N0103 Zhang
N0102 Zhang
N0101 Zhang
注意:
数据文件内容请参照上一篇文章。
不要使用select xx from aa bb where aa.f=bb.f这样的语法,hive不支持这种写法。
如果需要查看hive的执行计划,你可以在语句前加上explain,比如:
explain Select a.stuNo,b.teacherName from student a jointeacher b on a.classNo = b.classNo;
外连接(outer join)
和传统SQL类似,Hive提供了left outer join,right outer join,full out join。
半连接(semi join)
Hive不提供in子查询。此时你可以用leftsemi join实现同样的功能。
执行以下语句:
Select * from teacher left semi join student onstudent.classNo = teacher.classNo;
输出结果如下:
C02 Sun
C03 Wang
C01 Zhang
可以看出,C04 Dong没有出现在查询结果中,因为C04在表student中不存在。
注意:
右表(student)中的字段只能出现在on子句中,不能出现在其他地方,比如不能出现在select子句中。
Map连接(map join)
当一个表非常小,足以直接装载到内存中去时,可以使用map连接以提高效率,比如:
Select /*+mapjoin(teacher) */ a.stuNo,b.teacherNamefrom student a join teacher b on a.classNo = b.classNo;
以上红色标记部分采用了C的注释风格。
当连接时用到不等值判断时,也比较适合Map连接。具体原因需要深入了解Hive和MapReduce的工作原理。
子查询(sub query)
运行以下语句将返回所有班级平均分的最高记录。
Select max(avgScore) as maScore
from
(Select classNo,avg(score) as avgScore from student group byclassNo) a;
输出结果:
80.66666666666667
以上语句中红色部分为一个子查询,且别名为a。返回的子查询结果和一个表类似,可以被继续查询。
视图(view)
和传统数据库中的视图类似,Hive的视图只是一个定义,视图数据并不会存储到文件系统中。同样,视图是只读的。
运行以下两个命令:
Create view avg_score as
Select classNo,avg(score) as avgScore from student groupby classNo;
Select max(avgScore) as maScore
From avg_score;
可以看到输出结果和上例中的结果是一样的。