Hive查询

jopen 7年前

排序和聚集

通过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;

可以看到输出结果和上例中的结果是一样的。