Hive總結（十二）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;