Hive总结（十二）Hive查询进阶--以及调用外部脚本

ゝ一纸荒年。 2022-08-07 11:34 185阅读 0赞

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

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

Hive总结（十二）Hive查询进阶--以及调用外部脚本

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 hive(二) -- hive部署

相关 Hive 查询优化总结

相关 Hive总结（十二）Hive查询进阶--以及调用外部脚本

相关 Hive(十二)--Hive函数大全

相关 Hive进阶之Hive的数据查询

相关 Hive进阶之Hive数据导入

相关 [Hadoop] Hive 外部表

相关 HIVE 走近大数据之Hive进阶---慕课网---总结笔记--后续持续更新

相关 Hive查询

相关 hive进阶学习