6.1 ? SELECT ... FROM 語句?
?
hive> SELECT name,salary FROM employees;
?? ?--普通查詢
hive>SELECT e.name, e.salary FROM employees e;
??--也支持別名查詢
?
當用戶選擇的列是集合數據類型時，Hive會使用 JSON 語法應用于輸出：
hive> SELECT name,subordinates FROM employees;?
? 顯示 ?John Doe ["Mary Smith","Todd Jones"] ?數組類型的顯示
hive>SELECT na
me,deductions FROM employees;?
? 顯示??John Doe {"Federal Taxes":0.2,"State Taxes":0.05} ?MAP 輸出
hive>SELECT na
me,adress FROM employees; ??
? 顯示 ?John Doe {"street":"1 Michigan Ave.","city":"Chicago","state":"IL"} ?address 列是一個 STRUCT
?
hive> SELECT name,subordinates[0] FROM employees;? 查看數組中的第1個元素,如果不存在元素將返回 NULL
hive>SELECT na
me,deductions["State Taxes"] FROM employees; ?
查詢MAP 元素
hive>SELECT na
me,adress.city FROM employees;
? 查詢STRUCT 中的一個元素，可以用 . 符號
以上三種查詢 在 where 子句中同樣可以使用這些方式；
?
hive>SELECT symbol, `price.*` FROM stocks;?
? 用正則表達戒嚴 選擇我們想要的列，本句是查 symbol 列和所有列名以 price 作為前綴的列；
hive>SELECT upper(name), salary, deductions["Federal Taxes"], round(salary * (1 - deductions["Federal Taxes"])) FROM employees; ?
使用 round() 方法返回一個 Double 類型的最近整數。
?
Hive 中所支持的運算符：
A + B 、A - B、A * B、A / B、A % B [求余]、A & B [按位與]、A | B [按位或]、A ^ B [按位取異或]、~A [按位取反]
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/4b1e2d21fc354196ba0b8b5455b52377.jpg)
【圖片取自《hive編程指南》 81頁】
?
注意：算術運算符接受任意的數值類型，不過，如果數據類型不同，那么兩種類型中值范圍較小的那個數據類型轉換為其他范圍更廣的數據類型。
當進行算術運算時，用戶需要注意數據溢出或數據下溢問題，Hive 遵循的是底層 Java 中數據類型的規則，因為當溢出或下溢發生時計算結果不會自動轉換為更廣泛的數據類型，乘法和除法最有可能會引發這個問題；
?
有時使用函數將數據值按比例從一個范圍縮放到另一個范圍也是很有用的，如 按照 10 次方冪進行除法運算或取 log 值等。
所有數學函數：
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/8d8d20ed5a664610a9e071c0c4aabef4.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/36ab2803617b4473a0470160b488cc9e.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/460dc625a3084e03ab9bf6a9a2639fa2.jpg)
【圖片取自《hive編程指南》 82頁】
?
需要注意的是函數 floor、round、ceil （向上取整）輸入的是 DOUBLE 類型的值，返回的值是 BIGINT 類型的。
?
所有聚合函數：
?
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/a326d549a7ff47db83e7057a52a64186.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/64d4896a12114cba9656905f2eb00b84.jpg)
【圖片取自《hive編程指南》 85頁】
?
hive> SET hive.map.aggr=true;?
? --設置這個屬性為 true 來提高聚合的性能
hive>SELECT count(*), avg(salary) FROM employees;
?? --這個設置會觸發在 map 階段進行的“頂級”聚合過程，（非頂級聚合過程將會在執行一個 GROUP BY 后進行），不過這個設置將需要更多的內存。
?
hive>SELECT count(DISTINCT symbol) FROM stocks;
??多個函數還可以接受像 DISTINCT 這個表達式，來進行排重；v如果 symbol 是分區列時會返回 0.。。。是個bug；
hive>SELECT count(DISTINCT ymd), ?count(DISTINCT volume
) FROM stocks;
??官方不允許這樣查，但實際可以這樣查；
?
表生成函數：與聚合函數相反的一類函數不是所謂的表生成函數，基可以將單列擴展成多列或者多行;
hive> SELECT explode (subordinates) AS sub FROM employees;
???本語句將 employees 表中每行記錄中 subordinates 字段內容轉換成 0 ?個或者多個新的記錄行,如果 subordinates 字段內容為空的話，那么將不會產生新的記錄，如果不為空的話，那么這個數組的每個元素都將產生一行新記錄；AS sub 子句定義了列別名 sub。當全用表生成函數時，Hive 要求使用列別名。【具體在13章中會詳細介紹】
?
表生成函數：
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/fc4a34f8a2d348c2a7a4da66e4168783.jpg)
【圖片取自《hive編程指南》 85頁】
其它內置函數：
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/6460bbe2f7674f649a0bcb4a4858e94c.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/94bc6b4fbe6d4a27b0359e8638eccdfc.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/306abc0721704b5fbd1de20e45d1e67c.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/303d7b6e393a4b41a20ecaac5b30994b.jpg)
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/2fc320a064d2404589a968b5a04460f7.jpg)
其它內置函數：
【圖片取自《hive編程指南》 88-92頁】
?
hive> SELECT upper(name), salary, deductions["Federal Taxes"], ?round(salary *(1 - deductions["Federal Taxes"])) FROM employees?
LIMIT 2
;?
LIMIT 子句用于限制返回的行數；
?
hive> SELECT upper(name), salary, deductions["Federal Taxes"]?
as fed_taxes
, ?round(salary *(1 - deductions["Federal Taxes"]))?
as salary_minus_fed
?FROM employees LIMIT 2; ? ?fed_taxes、
?salary_minus_fed
??給新查出的結果的兩個列起個別名；
?
hive> FROM (upper(name), salary, deductions["Federal Taxes"]?as fed_taxes, ?round(salary *(1 - deductions["Federal Taxes"]))?as salary_minus_fed?FROM employees) e SELECT e.name, ?e.salary_minus_fed_taxes WHERE?
e.salary_minus_fed_taxes >7000;
?
? SELECT 的嵌套查詢
?
CASE ... WHEN ... THEN 句式例子：
hive>SELECT name,salary, CASE
? ? WHEN salary < 5000 THEN 'low'
? ? WHEN?salary >= 5000 AND?salary <7000 THEN 'middle'
? ? WHEN?salary>=7000 AND?salary < 100000 THEN 'high'
ELSE 'very high' END ?AS bracket FROM ?employees;
?
Hive 大多數情況下查詢都會觸發一個 MapReduce 任務，Hive 中本模式的查詢可以不必使用 MP；如： select * from employees;?
SELECT * from employees WHERE country='us' and state='ca' limit 100; ?
對于 WHERE 語句中過濾條件只是分區字段這種情況（無論是否使用了 LIMT語句限制輸出記錄條數）也是無需MapReduce 過程的；
?
hive.exec.mode.local.auto=true;?
?如果這個值為 true Hive 還會嘗試使用本地模式執行其他的操作,否則 Hive 會用 MP 來執行其他所有的查詢；最好將它增加到 $HOME/.hiverc 文件中；
?
6.2 where 語句用于過濾查詢條件 用法與 普通 SQL 一樣；
?
謂詞操作：這些詞的操作同樣可以用于 JION ... ON 和 HAVING 語句中
![Hive[6] HiveQL 查詢](http://img.it610.com/image/product/5edb210cf10c4497adda5021c79eea23.jpg)
【圖片取自《hive編程指南》 88-92頁】
?
LIke 和 RLIKE ：LIKE是一個標準的 SQL 操作，可以讓我們通過字符串的開頭或結尾，以及指定特定的子字符串，或者當子字符串出現在字符串內的任何位置時進行匹配；【RLIKE 子句是 Hive中這個功能的一個擴展，其可以通過 ?JAVA 的正則表達式這個更強大的語言來指定匹配條件。】
?
hive>SELECT name, address.street FROM ?employees WHERE address.street LIKE '%Ave.'
?? ?--查找以Ave 開頭的 雇員姓名；
hive>SELECT name, address.stree FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*';
??--RLIKE 后面字符串含義：字符串中的 . 表示和任意的字符匹配，星號 * 表示重復“左邊的字符串”零次到無數次，表達式（x|y）表示 和 x 或者 y 匹配；【PS：不會正則的可以百度一下去學學】
?
6.3 GROUP BY 語句，它通常會用 聚合函數一起使用，按照一個或者多個列對結果進行分組，然后對每個組執行聚合操作【用法與SQL差不多】
hive>SELECT year(ymd),avg(price_close) FROM stocks WHERE exchange='NASDAQ' AND symbol ='APPLE' GROUP BY year(ymd);?
?--示例
HAVING 語句：
hive>SELECT year(ymd),avg(price_close) FROM stocks?
>WHERE exchange='NASDAQ' AND symbol ='APPLE' GROUP BY year(ymd)?
>HAVING avg(price_close)>50.0;?
? ?--示例
?
6.4 JOIN 語句?
INNER JOIN 內鏈接，只有進行鏈接的兩個表中都存在與連接標準相關匹配的數據才會顯示【用法與SQL差不多】
hive>SELECT a.ymd, ?a.price_close, b.price_close FROM stocks a JOIN STOCKS b ON a.ymd =b.ymd?
? ? ? ?>WHERE a.symbol ='appl' AND b.symbol='ibm';?
?
注意：hive 中不支持的查詢如下【同進也不支持在ON的子句中的謂詞間使用 OR，可以支持 AND 】：
hive>SELECT a.ymd, ?a.price_close, b.price_close FROM stocks a JOIN STOCKS b ON a.ymd
<=
b.ymd?
? ? ? ?>WHERE a.symbol ='appl' AND b.symbol='ibm';?
多張表的鏈接：
hive>SELECT a.ymd, ?a.price_close, b.price_close,c.price_close
? ? ? ?> FROM stocks a JOIN?stocks?b ON a.ym
d
=
b.ymd
?
? ? ? ?> ? ? ? ? ? ? ? ? ? ? ? ? ?JOIN stocks c ON a.ymd = c.ymd
? ? ? ?> ? ? ? ? ? ? ? ? ? ? ? ? ?JOIN stocks c ON a.ymd = c.ymd
? ? ? ?>WHERE a.symbol ='appl' AND b.symbol='ibm' AND c.symbol='ge';
大多數情況下，Hive 會對每個 JOIN 鏈接對象啟動一個 MapReduce 任務，上面例子中會首先啟動一個 MapReduce job 對表 a 和表 b 進行連接操作，然后會再啟動一個 MapReduce job 將第一個 MapReduce job 的輸出和和 c 進行連接操作；【hive 都是從左向右運順序執行的】
?
【提示】
對于3個或者更多表進行 JOIN 鏈接時，如果每個 ON 子句都使用相同的鏈接鍵的話，那么只會產生一個 MapReduce；Hive 同時假定查詢中最后一個表是最大的那個表，在對每行記錄進行鏈接操時，它會嘗將其他表緩存起來，然后掃描最后那個大表進計算，因此我們需要保證連續查詢中的表的大小從左到右是依次增加的；
?
LEFT OUTER JOIN 左外鏈接【與SQL用法類似】
hive> SELECT s.ymd, ?s.symbol, s.price_close, ?d.dividend FROM stocks s LEFT OUTER JION dividends d ON?
>s.ydm AND s.symbol=d.symbol WHERE s.symbol='aapl' ;?
在左外鏈接操作中， JOIN 操作符左邊表中符合 WHERE 子句的所有記錄將會被返回，右邊表中沒有符合 ON 后面的鏈接記錄時，會返回 null;?
【提示】WHERE 語句在連接操作執行后才會執行，因此 WHERE 語句應該只用于過濾那些非null值的列，同時，ON 語句中的分區過濾條件外鏈接（OUTER JOIN）中是無效的，不過在內鏈接中是有效的；
?
RIGHT OUTER JOIN?
右外鏈接：會返回右邊表所有符合 WHERE 語句的記錄，左表中沒有匹配的字段值用 NULL 代替；
FULL OUTER JOIN
?完全鏈接：將會返回所有表中符合 where 語句條件的所有記錄；
LEFT SEMI JOIN
?左半開鏈接：會返回左邊表的記錄，前提是其他記錄對于右邊表滿足ON語句中的判斷條件;【不支持右關開鏈接】
hive>SELECT s.ymd, s.symbol, s.price_close FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol;
?
Hive 不支持的查詢：
hive>SELECT s.ymd, s.symbol, s.price_close FROM stocks s WHERE s.ymd,s.symbol IN(SELECT * FROM dividends d);?
?
JOIN 笛卡爾積：左邊鏈接的行數乘以右邊表的行數等于返回結果集的大小；
hive> SELECT * FROM stocks JOIN dividends;?
如果使用此方法查詢，MapReduce 任何方式都無法法進行優化；
set hive.mapred.mode=strict?
會禁止 笛卡爾積的查詢；
?
map-side JOIN：如果所有表中只有一張表是小表，那么可以在最大的表通過 mapper 的時候將小表完全放到內存中，Hive可以在 map 端執行鏈接過程（稱為 map-side JOIN）; 因為 Hive 可以和內存中的小表進行逐一匹配，從而省略掉常規連接操作所需要的 reduce 過程 ，即使對于很小的數據庥，這個優化也明顯要快于常規的連接操作，不僅減少了 reduce 過程 ，而且有時還可以同時減少 map 過程的執行步驟；
?
hive>set hive.auto.conver.JOIN=true;?
?從0.7版本開始需要設置此屬性才可以生效
hive>set hive.mapjoin.smalltable.filesize=25000000;
??配置能夠使用這個優化的小表的大小；（單位：字節）
右外鏈接、全外鏈接不支持上面的優化；
?
分桶表，對于大表，在特定的情況下也可以使用這個優化，但表中的數據必須是按照ON 語句中的鍵進行分桶的，而且其中一張表的分桶的個數必須是另一張表分桶個數的若干倍，這樣才可以按照分桶數據進行鏈接；hive>set hive.optimize.bucketmapJOIN=true; 也需要設置，默認是關閉的；
?
6.5 ORDER BY 和 SORT BY
??P132
Hive 的 ORDER BY 語句和其他的 SQL 語言的定義都是一樣。會對查詢結果一個全局排序。也就是說會有一個所有的數據都通過一個 reducer 進行處理的過程，如果有大數據集，過程可能會消耗太過漫長的時間來執行；
?
Hive 還有一種排序 SORT BY ，其會在每個 reducer 中對數據進行排序，也就是會執行一個局部排序過程 ，這可以保證每個 reducer 的輸出數據都是有序的（但不是全局有序）這樣可以提高全局排序的效率；
SELECT s.ymd, s.symbol, ?s.price_close FROM stocks s ORDER BY s.ymd ASC , s.symbol DESC;?
?order by 例子 ?
SELECT s.ymd, s.symbol, ?s.price_close FROM stocks s SORT BY s.ymd ASC , s.symbol DESC;?
?sort by 例子 ?
注意：因為 ORDER BY 操作可能會導致運行時間過長，如果屬性 hvie.mapred.mode=strict 的話，那么 hive 要求這樣的語句必須加 LIMIT 語句進行限制，默認情況下 屬性是 nonstrict；
?
6.6 SORT BY 與 DISTRIBUTE BY
distribute by 控制 map 的輸出在 reducer 中是如何劃分的。
假設我們希望具有相同股票交易碼的數據在一起處理，那么我們可以使用 distribute by 來保證具有相同股票交易碼的記錄會分發到同一個 reducer 中進行處理，然后使用 SOTR BY 來按照我們的期望對數據進行排序：
hive>SELECT s.ymd, s.symbol, s.price_close FROM stocks s DISTRIBUTE BY s.symbol SORT BY s.symbol ASC, s.ymd ASC;?
?
DISTRIBUTE BY 和 GROUP BY ?在其控制著 reducer 是如何接受一行行數據進行處理這方面類似類的，而 SORT BY 則控制著 reducer 內的數據是如何進行排序的。需要注意的是?DISTRIBUTE BY 語句一定要寫在 SOTR BY 語句之前；
?
6.7 CLUSTER BY
在上面的例子中 s.symbol 列被用在?DISTRIBUTE BY 語句中，而 s.symbol 和 s.ymd 位用 SOTR BY 語句中，如果這兩個語句中涉及到的完全相同的列，而且采用的是升序的排序方式（也就是默認的排序方式）在這種情況下 cluster by 就等于前面的2個語句 ，相當于簡寫：
hive> SELECT s.ymd, s.symbol, s.price_close FROM ?stock s CLUSTER BY s.symbol;
使用?DISTRIBUTE BY ... SOTR BY 語句或其簡化版的 CLUSTER BY 語句會剝奪 SORT BY 的并行性，然而這樣可以實現輸出文件的數據是全局排序的；
?
6.8 類型轉換
SELECT name, salary FROM employees WHERE cast(salary AS FLOAT )< 10000.0 ;?
? 類型轉換的語法： cast(value AS TYPE) 如果不成功則返回 NULL；
需要注意的是 將浮點數轉換成整數的推薦方式是使用 round() 或者 floor() 函數，而不是類型轉換操作符 cast；
?
binary 類型只支持 BINARY 類型轉換為 STRING 類型；
?
6.9 抽樣查詢：
Hive 可以通過對表進行分桶抽樣來滿足抽樣查詢
hive>SELECT * FOM numbers TABLESAMPLE (BUCKET 3 OUT OF 10 ON rand()) s; ??
分桶語句中的分母表示的是數據將會被散列的桶的個數，而分子表示將會選擇的桶的個數；
?
hive>SELECT * FROM numberflat TABLESAMPLE(0.1 PERCENT) s;?
? ?按照百分比方式進行抽樣，這種是基于行數的；
注意：這種抽樣試不一定適用于所遙 文件格式，另外這種抽樣方式的最小抽樣單元是一個 HDFS 數據塊，如果表的數據大小小于普通的塊大小的 128M 那么會返回 所有行；
基于百分比的抽樣提供了一個變量用于控制基于數據塊的調優的種子信息：
<property>
<name>hive.sample.seednumber</name>
<value>0</value>
</property>
?
6.10 UNION ALL?
可以將2 個或多個表進行合并，每個 union 子查詢必須都需要有相同的列，而且對應的每個字段的字段類型必須是一致的；這個功能便于將一個長的復雜的 WHERE 語句分割成 2 個或多個 union ?子查詢，除非源表建立了索，否則 這個查詢將會對同一份源數據進行多次拷貝分發；
?
FROM (FROM src SELECT src.key, src,value WHERE src.key <100
? ? UNION ALL
? ??FROM src SELECT src.key, src,value WHERE src.key >110
) unioninput
INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unionimput.*
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
