2021年大数据Hive（四）：Hive查询语法
全網最詳細的Hive文章系列，強烈建議收藏加關注！
后面更新文章都會列出歷史文章目錄，幫助大家回顧知識重點。
目錄
系列歷史文章
前言
hive查詢語法
一、SELECT語句
1、語句結構
2、全表查詢
3、選擇特定列查詢
4、列別名
5、常用函數
6、LIMIT語句
7、WHERE語句
二、運算符
1、比較運算符
2、邏輯運算符
三、分組
1、GROUP BY語句
2、HAVING語句
???????四、JOIN語句
???????1、內連接（INNER JOIN）
???????2、左外連接（LEFT OUTER JOIN）
??????????????3、右外連接（RIGHT OUTER JOIN）
???????4、滿外連接（FULL OUTER JOIN）
????????????5、多表連接
五、排序
1、Order By-全局排序
2、???????Sort By-每個MapReduce內部局部排序
???????3、Distribute By-分區排序
???????4、Cluster By
系列歷史文章
2021年大數據Hive（十二）：Hive綜合案例！！！???????
2021年大數據Hive（十一）：Hive調優???????
2021年大數據Hive（十）：Hive的數據存儲格式
2021年大數據Hive（九）：Hive的數據壓縮???????
2021年大數據Hive（八）：Hive自定義函數
2021年大數據Hive（七）：Hive的開窗函數
2021年大數據Hive（六）：Hive的表生成函數
2021年大數據Hive（五）：Hive的內置函數（數學、字符串、日期、條件、轉換、行轉列）
2021年大數據Hive（四）：Hive查詢語法
2021年大數據Hive（三）：手把手教你如何吃透Hive數據庫和表操作（學會秒變數倉大佬）
2021年大數據Hive（二）：Hive的三種安裝模式和MySQL搭配使用
2021年大數據Hive（一）：Hive基本概念
前言
?2021大數據領域優質創作博客，帶你從入門到精通，該博客每天更新，逐漸完善大數據各個知識體系的文章，幫助大家更高效學習。
有對大數據感興趣的可以關注微信公眾號：三幫大數據
hive查詢語法
一、SELECT語句
1、語句結構
基本語法:
SELECT?[ALL?|?DISTINCT]select_expr,?select_expr,?...FROM?table_reference[WHERE?where_condition][GROUP?BY col_list][HAVING?where_condition][ORDER?BY col_list][CLUSTER?BY col_list|?[DISTRIBUTE BY?col_list]?[SORT BY?col_list]][LIMIT?number]1、ORDER BY用于全局排序，就是對指定的所有排序鍵進行全局排序，使用ORDER BY的查詢語句，最后會用一個Reduce Task來完成全局排序。解釋:
2、sort by用于分區內排序，即每個Reduce任務內排序。，則sort by只保證每個reducer的輸出有序，不保證全局有序。
3、distribute by(字段)根據指定的字段將數據分到不同的reducer，且分發算法是hash散列。
4、cluster by(字段) 除了具有Distribute by的功能外，還兼具sort by的排序功能。。
因此，如果distribute by和sort by字段是同一個時，此時，cluster by = distribute by + sort by
2、全表查詢
select?*?from?score;3、選擇特定列查詢
select?sid ,cid from?score;4、列別名
select?sid as?myid ,cid from?score;???????5、常用函數
1）求總行數（count）select?count(1)?from?score;2）求分數的最大值（max）select?max(sscore)?from?score;3）求分數的最小值（min）select?min(sscore)?from?score;4）求分數的總和（sum）select?sum(sscore)?from?score;5）求分數的平均值（avg）select?avg(sscore)?from?score;???????6、LIMIT語句
典型的查詢會返回多行數據。LIMIT子句用于限制返回的行數。
select?*?from?score limit?3;???????7、WHERE語句
1）使用WHERE 子句，將不滿足條件的行過濾掉。
2）WHERE 子句緊隨 FROM 子句。
3）案例實操
查詢出分數大于60的數據
select?*?from?score where?sscore >?60;二、運算符
???????1、比較運算符
1、操作符
| 操作符 | 支持的數據類型 | 描述 |
| A=B | 基本數據類型 | 如果A等于B則返回TRUE，反之返回FALSE |
| A<=>B | 基本數據類型 | 如果A和B都為NULL，則返回TRUE，其他的和等號（=）操作符的結果一致，如果任一為NULL則結果為NULL |
| A<>B, A!=B | 基本數據類型 | A或者B為NULL則返回NULL；如果A不等于B，則返回TRUE，反之返回FALSE |
| A | 基本數據類型 | A或者B為NULL，則返回NULL；如果A小于B，則返回TRUE，反之返回FALSE |
| A<=B | 基本數據類型 | A或者B為NULL，則返回NULL；如果A小于等于B，則返回TRUE，反之返回FALSE |
| A>B | 基本數據類型 | A或者B為NULL，則返回NULL；如果A大于B，則返回TRUE，反之返回FALSE |
| A>=B | 基本數據類型 | A或者B為NULL，則返回NULL；如果A大于等于B，則返回TRUE，反之返回FALSE |
| A [NOT] BETWEEN B AND C | 基本數據類型 | 如果A，B或者C任一為NULL，則結果為NULL。如果A的值大于等于B而且小于或等于C，則結果為TRUE，反之為FALSE。如果使用NOT關鍵字則可達到相反的效果。 |
| A IS NULL | 所有數據類型 | 如果A等于NULL，則返回TRUE，反之返回FALSE |
| A IS NOT NULL | 所有數據類型 | 如果A不等于NULL，則返回TRUE，反之返回FALSE |
| IN(數值1, 數值2) | 所有數據類型 | 使用 IN運算顯示列表中的值 |
| A [NOT] LIKE B | STRING 類型 | B是一個SQL下的簡單正則表達式，如果A與其匹配的話，則返回TRUE；反之返回FALSE。B的表達式說明如下：‘x%’表示A必須以字母‘x’開頭，‘%x’表示A必須以字母’x’結尾，而‘%x%’表示A包含有字母’x’,可以位于開頭，結尾或者字符串中間。如果使用NOT關鍵字則可達到相反的效果。 |
| A RLIKE B, A REGEXP B | STRING 類型 | B是一個正則表達式，如果A與其匹配，則返回TRUE；反之返回FALSE。匹配使用的是JDK中的正則表達式接口實現的，因為正則也依據其中的規則。例如，正則表達式必須和整個字符串A相匹配，而不是只需與其字符串匹配。 |
2、案例實操
（1）查詢分數等于80的所有的數據select?*?from?score where?sscore =?80;（2）查詢分數在80到100的所有數據select?*?from?score where?sscore between?80?and?100;（3）查詢成績為空的所有數據select?*?from?score where?sscore is?null;（4）查詢成績是80或 90的數據select?*?from?score where?sscore in(80,90);3、LIKE和RLIKE
1）使用LIKE運算選擇類似的值
2）選擇條件可以包含字符或數字:
% 代表零個或多個字符(任意個字符)。
_ 代表一個字符。
3）RLIKE子句是Hive中這個功能的一個擴展，其可以通過Java的正則表達式這個更強大的語言來指定匹配條件。
4）案例實操
（1）查找以8開頭的所有成績select?*?from?score where?sscore like?'8%';（2）查找第二個數值為9的所有成績數據select?*?from?score where?sscore like?'_9%';（3）查找id中含1的所有成績信息select?*?from?score where?sid rlike '[1]';???????2、邏輯運算符
| 操作符 | 含義 |
| AND | 邏輯并 |
| OR | 邏輯或 |
| NOT | 邏輯否 |
案例實操
（1）查詢成績大于80，并且sid是01的數據select?*?from?score where?sscore >80?and?sid =?'01';（2）查詢成績大于80，或者sid ?是01的數select?*?from?score where?sscore >?80?or?sid =?'01';（3）查詢sid ?不是 01和02的學生select?*?from?score where?sid not?in?('01','02');三、分組
1、GROUP BY語句
GROUP BY語句通常會和聚合函數一起使用，按照一個或者多個列隊結果進行分組，然后對每個組執行聚合操作。注意使用group ?by分組之后，select后面的字段只能是分組字段和聚合函數。
案例實操：
1）計算每個學生的平均分數
select?sid ,avg(sscore)?from?score group?by?sid;2）計算每個學生最高成績
select?sid ,max(sscore)?from?score group?by?sid;???????2、HAVING語句
1、having與where不同點
（1）where針對表中的列發揮作用，查詢數據；having針對查詢結果中的列發揮作用，篩選數據。
（2）where后面不能寫分組函數，而having后面可以使用分組函數。
（3）having只用于group by分組統計語句。
2、案例實操：???????
-- 求每個學生的平均分數select?sid ,avg(sscore)?from?score group?by?sid;-- 求每個學生平均分數大于85的人select?sid ,avg(sscore)?avgscore from?score group?by?sid having?avgscore >?85;???????四、JOIN語句
Hive的join操作只支持等值連接
???????1、內連接（INNER JOIN）
內連接：只有進行連接的兩個表中都存在與連接條件相匹配的數據才會被保留下來。
select * from teacher t, course c where t.tid = c.tid;?#隱式內連接select?*?from?teacher t inner?join?course c on?t.tid =?c.tid;?#顯式內連接select?*?from?teacher t join?course c on?t.tid =?c.tid;???????2、左外連接（LEFT OUTER JOIN）
左外連接：JOIN操作符左邊表中符合WHERE子句的所有記錄將會被返回。
查詢老師對應的課程
select?*?from?teacher t left?join?course c on?t.tid =?c.tid;??????????????3、右外連接（RIGHT OUTER JOIN）
右外連接：JOIN操作符右邊表中符合WHERE子句的所有記錄將會被返回。
select?*?from?teacher t right?join?course c on?t.tid =?c.tid;???????4、滿外連接（FULL OUTER JOIN）
滿外連接：將會返回所有表中符合WHERE語句條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那么就使用NULL值替代。
SELECT?*?FROM?teacher t FULL JOIN?course c ON?t.tid =?c.tid ;????????????5、多表連接
注意：連接 n個表，至少需要n-1個連接條件。例如：連接三個表，至少需要兩個連接條件。
多表連接查詢，查詢老師對應的課程，以及對應的分數，對應的學生
select?*?from?teacher tleft?join?course con?t.tid =?c.tidleft?join?score son?s.cid =?c.cidleft?join?student stuon?s.sid =?stu.sid;大多數情況下，Hive會對每對JOIN連接對象啟動一個MapReduce任務。本例中會首先啟動一個MapReduce job對表teacher和表course進行連接操作，然后會再啟動一個MapReduce job將第一個MapReduce job的輸出和表score;進行連接操作。
五、排序
1、Order By-全局排序
Order By：全局排序，一個reduce
1、使用 ORDER BY 子句排序
ASC（ascend）: 升序（默認）
DESC（descend）: 降序
2、ORDER BY 子句在SELECT語句的結尾。
3、案例實操
(1)查詢學生的成績，并按照分數降序排列
SELECT?*?FROM?student s LEFT?JOIN?score sco ON?s.sid =?sco.sid ORDER?BY?sco.sscore DESC;(2)按照分數的平均值排序
select?sid ,avg(sscore)?avg?from?score group?by?sid order?by?avg;(3)按照學生id和平均成績進行排序
select?sid ,avg(sscore)?avg?from?score group?by?sid order?by?sid,avg;2、???????Sort By-每個MapReduce內部局部排序
Sort By：每個MapReduce內部進行排序，對全局結果集來說不是排序。
1)設置reduce個數set?mapreduce.job.reduces=3;2)查看設置reduce個數set?mapreduce.job.reduces;3）查詢成績按照成績降序排列select?*?from?score sort by?sscore;4)將查詢結果導入到文件中（按照成績降序排列）insert?overwrite local?directory '/export/data/exporthive/sort'?select?*?from?score sort by?sscore;???????3、Distribute By-分區排序
Distribute By：類似MR中partition，進行分區，結合sort by使用。
注意，Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前。
對于distribute by進行測試，一定要分配多reduce進行處理，否則無法看到distribute by的效果。
案例實操：
先按照學生id進行分區，再按照學生成績進行排序。
1)設置reduce的個數，將我們對應的sid劃分到對應的reduce當中去set?mapreduce.job.reduces=7;2)通過distribute by進行數據的分區insert?overwrite local?directory '/export/data/exporthive/distribute'?select?*?from?score distribute by?sid sort by?sscore;?????????4、Cluster By
當distribute by和sort by字段相同時，可以使用cluster by方式。
cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是升序排序，不能指定排序規則為ASC或者DESC。
以下兩種寫法等價:
select?*?from?score cluster?by?sid;?select?*?from?score distribute by?sid sort by?sid;- 📢博客主頁：https://lansonli.blog.csdn.net
- 📢歡迎點贊 👍 收藏 ?留言 📝 如有錯誤敬請指正！
- 📢本文由 Lansonli 原創，首發于 CSDN博客🙉
- 📢大數據系列文章會每天更新，停下休息的時候不要忘了別人還在奔跑，希望大家抓緊時間學習，全力奔赴更美好的生活?
總結
以上是生活随笔為你收集整理的2021年大数据Hive（四）：Hive查询语法的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 2021年大数据Hive（三）：手把手教
- 下一篇： 2021年大数据Hive（五）：Hive