天天看點
傳回

《Hive權威指南》第六章：查詢1.SELECT … FROM 語句2 WHERE語句3 GROUP BY 語句4 JOIN語句5 ORDER BY 和 SORT BY6 含有SORT BY 的DISTRIBUTE BY7 CLUSTER BY8 類型裝換9 抽樣查詢10 UNION ALL

目錄

  • 1.SELECT ... FROM 語句
    • 1.1 使用正規表達式來指定列
    • 1.2 使用列值進行計算
    • 1.3 算數運算符
    • 1.4 使用函數
      • 1.4.1 數學函數
      • 1.4.2 聚合函數
      • 1.4.3 表生成函數
      • 1.4.4 其他内置函數
    • 1.5 LIMIT語句
    • 1.6 列别名
    • 1.7 嵌套SELECT語句
    • 1.8 CASE ... WHEN ... THEN 句式
    • 1.9 什麼情況下Hive可以避免進行MapReduce
  • 2 WHERE語句
    • 2.1 謂詞操作符
    • 2.2 關于浮點數的比較
    • 2.3 LIKE 和 RLIKE
  • 3 GROUP BY 語句
    • HAVING 語句
  • 4 JOIN語句
    • 4.1 INNER JOIN
    • 4.2 JOIN優化
    • 4.3 LEFT OUTER JOIN
    • 4.4 OUTER JOIN
    • 4.5 RIGHT OUTER ON
    • 4.6 FULL OUTER JOIN
    • 4.7 LEFT SEMI-JOIN
    • 4.8 笛卡爾積JOIN
    • 4.9 map-side JOIN
  • 5 ORDER BY 和 SORT BY
  • 6 含有SORT BY 的DISTRIBUTE BY
  • 7 CLUSTER BY
  • 8 類型裝換
    • 類型轉換BINARY值
  • 9 抽樣查詢
    • 9.1 資料塊抽樣
    • 9.2 分桶表的輸入裁剪
  • 10 UNION ALL

1.SELECT … FROM 語句

SELECT是SQL中的射影算子。FROM子句辨別了從哪個表、視圖或嵌套查詢中選擇記錄，SELECT指定了要儲存的列以及輸出函數需要調用的一個或多個列（例如，COUNT(*) 這樣的聚合函數）。

employees表：

CREATE TABLE employees(
    name            STRING,
    salary          FLOAT,
    subordiantes    ARRAY<STRING>,
    deductions      MAP<STRING, FLOAT>,
    address         STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

當使用者選擇的列是集合(ARRAY<T>)類型時，Hive會使用JSON（Java腳本對象表示法）文法應用于輸出。

同樣，MAP與STRUCT類型的列，輸出也是使用JSON格式來表示，其中格式上有些細微的差别，相信你一眼也能看懂。

接下來，來看看如何引用集合資料類型中的元素。

數組索引是基于0，這個和Java是一樣的。這裡選擇subordinates數組中的第一個元素查詢：

hive> SELECT name, subordinates[0] FROM employees;
John Doe	Mary Smith
Mary Smith	Bill King
Todd Jones	NULL
Bill King	NULL
引用一個不存在的元素會傳回NULL。同時，提取出STRING類型的值不再加引号

為了引用一個MAP元素，使用者還可以使用ARRAY[…]文法，但是使用的是鍵值而不是整數索引：

hive> SELECT name, deductions["State Taxes"] FROM employees
John Doe	0.05
Mary Smith	0.05
Todd Jones	0.03
Bill King	0.03

最後，為了引用STRUCT中的一個元素，使用者可以使用“點”符号，類似于前面提到的“表的别名.列名”這樣的用法：

hive> SELECT name, address.city FROM employees;
John Doe	Chicago
Mary Smith	Chicago
Todd Jones	Oak Park
Bill King	Obscuria

WHERE子句中同樣可以使用這些引用方式

1.1 使用正規表達式來指定列

從stocks中選擇symbol列和所有列名以price作為字首的列：

hive> SELECT symbol, `price.*` FROM stocks;

前提是先設定屬性：

set hive.support.quoted.identifiers=None;

否則會報錯。

《Hive權威指南》第六章：查詢1.SELECT … FROM 語句2 WHERE語句3 GROUP BY 語句4 JOIN語句5 ORDER BY 和 SORT BY6 含有SORT BY 的DISTRIBUTE BY7 CLUSTER BY8 類型裝換9 抽樣查詢10 UNION ALL

當然也可以使用“LIKE”和“RLIKE”關鍵字，後面再讨論。

1.2 使用列值進行計算 

hive> SELECT upper(name), salary, deductions["Federal Taxes"],
    > round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
JOHN DOE	100000.0	0.2		80000
MARY SMITH	80000.0		0.2		64000
TODD JONES	70000.0		0.15	59500
BILL KING	60000.0		0.15	51000

1.3 算數運算符

Hive中支援所有典型的算數運算符。

算數運算符：

運算符 類型 描述
A+B 數值 A和B相加
A-B 數值 A減去B
A*B 數值 A和B相乘
A/B 數值 A除以B。如果能整除，那麼傳回商數
A%B 數值 A除以B的餘數
A&B 數值 A和B按位取與
A|B 數值 A和B按位取或
A^B 數值 A和B按位取亦或
~A 數值 A按位取反

算數運算符接受任意的數值類型。不過，如果資料類型不同，那麼兩種類型中值範圍較小的那個資料類型将轉換為其他範圍更廣的資料類型。（例如INT和BIGINT兩種類型進行運算，INT會将類型轉換提升為BIGINT）

1.4 使用函數

Hive v0.8.0版本中所提供的，而且是用于處理單個列的資料。

1.4.1 數學函數

1.4.2 聚合函數

1.4.3 表生成函數

1.4.4 其他内置函數

1.5 LIMIT語句

典型的查詢會傳回多行資料。LIMIT子句用于限制傳回行數

hive> SELECT upper(name), salary, deductions["Federal Taxes"],
    > round(salary * (1 - deductions["Federal Taxes"])) FROM employees
    > LIMIT 2;
JONE DOE	100000.0	0.2    80000
MARY SMITH	 80000.0	0.2	   64000

1.6 列别名 

hive> SELECT upper(name), salary, deductions["Federal Taxes"] as fed_taxes,
    > round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    > FROM employees LIMIT 2;
JONE DOE	100000.0	0.2    80000
MARY SMITH	 80000.0	0.2	   64000

1.7 嵌套SELECT語句 

hive> FROM(
    >	SELECT upper(name), salary, deductions["Federal Taxes"] as fed_taxes,
    >	round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    >	FROM employees
    > ) e
    > SELECT e.name, e.salary_minus_fed_taxes
    > WHERE e.salary_minus_fed_taxes > 70000;
JOHN DOE	100000.0	0.2	   80000

從這個嵌套查詢語句中可以看到，我們将前面的結果集起了别名，稱之為e，在這個語句外嵌套查詢了name 和salary_minus_fed_taxes兩個字段，同時限制後者的值要大于70000。

1.8 CASE … WHEN … THEN 句式

CASE … WHEN … THEN 語句和if條件語句類似，用于處理單個列的查詢結果。

例如：

hive> SELECT name, salary,
	> CASE 
	>	WHEN salary < 50000.0 THEN 'low'
	>	WHEN salary >= 50000.0 AND salary < 70000.0 THEN 'middle'
	>	WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high'
	>	ELSE 'very high'
	> END AS bracket FROM employees;
John Doe			100000.0    very high
Mary Smith			 80000.0	high
Bill King			 60000.0	middle
Boss Man			200000.0	very high
...

1.9 什麼情況下Hive可以避免進行MapReduce

Hive中對某些情況的查詢可以不必使用MapReduce，也就是所謂的本地模式，例如：

SELECT * FROM employees;

在這種情況下，Hive可以簡單地讀取employees對應的存儲目錄下的檔案，然後輸出格式化後的内容到控制台。

對于WHERE語句中過濾條件知識分區字段這種情況（無論是否使用LIMIT語句限制輸出記錄條數），也是無需MapReduce過程的。

SELECT * FROM employees
WHERE country='US' AND state='CA'
LIMIT 100;

此外，如果屬性

hive.exec.model.local.auto

的值設定為true的話，Hive還會嘗試使用本地模式執行其他的操作：

set hive.exec.mode.local.auto=true;

否則，Hive使用MapReduce來執行其他所有的查詢。

提示：

最好可以将set hive.exec.mode.local.auto=true；這個設定增加到你的 $HOME/.hiverc配置檔案中

2 WHERE語句

WHERE語句使用謂詞表達式，有幾種謂詞表達式可以使用AND和OR相連接配接。當謂詞表達式計算結果為true時，相應的行将被保留并輸出。

注意，不能在WHERE語句中使用列别名。不過可以使使用者子查詢，然後使用子查詢的結果集點上列名取到相應的列（例如:(select col_name as col from test) t1 然後可以通過t1.col 取到該列）。

2.1 謂詞操作符

這些操作符同樣可以用于JOIN … ON 和 HAVING 語句中

操作符 支援的資料類型 描述
A=B 基本資料類型
A<=>B 基本資料類型 如果A和B都為NULL則傳回TRUE，其他的和等号（=）操作符的結果一緻，如果任一為NULL則結果為NULL （Hive v0.9.0版本新增）
A==B 沒有 這個是錯誤文法！SQL使用=，而不是==
A<>B，A!=B 基本資料類型 A或者B為NULL，則傳回NULL；如果A不等于B則傳回TRUE，反之傳回FALSE
A<B 基本資料類型
A<=B 基本資料類型
A>B 基本資料類型
A>=B 基本資料類型
A[NOT] BETWEEN B AND C 基本資料類型 如果A，B或者C任一為NULL，則結果為NULL。如果A的值大于或等于B而且小于或等于C，則結果為TRUE，反之為FALSE。如果使用NOT關鍵字反之。（Hive v0.9.0 版本中新增）
A IS NULL 所有資料類型
A IS NOT NULL 所有資料類型
A [NOT] LIKE B STRING 類型 B是一個簡單的正規表達式，如果A與其比對傳回TRUE，反之傳回FALSE。
A RLIKE B, A REGEXP B STRING 類型 B是一個正規表達式，如果A與其相比對，則傳回TRUE；反之傳回FALSE。

2.2 關于浮點數的比較

例如：

hive> SELECT name, salary, deductions['Federal Taxes']
	> FROM employees WHERE deductions['Federal Taxes'] > 0.2;
John Doe	 100000.0	0.2
Mary Smith	  80000.0	0.2
Boss Man	 200000.0	0.3
Fred Finance 150000.0	0.3

為什麼deductions[‘Federal Taxes’]=0.2的記錄也被輸出了呢？

實際上這并非是僅存于Hive中或Java中的問題。而是所有使用IEEE标準進行浮點數編碼的系統中普遍存在的一問題。

因為deductions這個map值的類型時FLOAT，而比較的數值0.2，Hive預設0.2為DOUBLE類型的，在IEEE編碼标準編碼中0.2的最近似的精确值應該略大于0.2，也就是0.2後面的若幹個0後存在非零的數值。

這裡為了友善了解，簡化表示為：FLOAT類型的0.2 實際為0.2000001，而對于DOUBLE類型是0.200000000001。這是因為一個8個位元組的DOUBL值具有更多的小數位。當表中的FLOAT值通過Hive轉換為DOUBLE值時，其産生的DOUBLE類型的值是0.200000100000，這個值實際要比0.200000000001大。這就是為什麼這個查詢結果像是使用了>= 而不是 > 了。

在Hive中有兩種方法解決這個問題：

  1. 如果是從TEXTFILE文本檔案中讀取資料的話，也就是目前為止我們所嘉定使用的存儲格式，那麼Hive會從資料檔案中讀取字元串“0.2”然後将其轉換為一個數字。我們可以在表模式中定義對應的字段類型為DOUBLE而不是FLOAT。這樣在進行浮點型數值比較的時候會比較準确。
  2. 相信大家會想到，顯式的支出0.2為FLOAT類型的。Java中有一個很好的方式能夠達到這個目的：隻需要在數值末尾加上字母F或f（例如：0.2f）。不幸的是，Hive并不支援這種文法，這裡必須使用cast操作符 
    hive> SELECT name, salary, deductions['Federal Taxes'] FROM employees
	> WHERE deductions['Federal Taxes'] > cast(0.2 AS FLOAT);
Ross Man	     20000.0	0.3
Fred Finance     150000.0	0.3
    注意：cast操作符内部文法：數值 AS FLOAT

實際上，還有第三種解決方案，即：和錢相關的都避免使用浮點數。

警告：對浮點數進行比較時，需要保持極端謹慎的态度。要避免任何從窄類型隐式轉換到更廣泛類型的操作。

2.3 LIKE 和 RLIKE

描述了LIKE和RLIKE謂詞操作符。使用者可能在之前已經見過LIKE的使用了，這裡就不多解釋了。

RLIKE子句是Hive中這個功能的一個擴充，其可以通過Java的正規表達式這個更強大的語言來指定比對條件。

hive> SELECT name, address.street
	> FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*';
Mary Smith	100	Ontario St.
Todd Jones	200	Chicago Ave.

有關正則的使用可以參考網上一些其他的案例。

3 GROUP BY 語句

GROUP BY語句通常會和聚合函數一起使用，按照一個或多個列對結果進行分組，然後對每個組的執行聚合操作。

HAVING 語句

HIVING子句允許使用者通過一個簡單的文法完成原本需要通過子查詢才能對GROUP BY 語句産生的分組進行條件過濾的任務。如下是對平均收盤價要大于$50.0：

hive> SELECT year(ymd), avg(price_close) FROM stocks
	> WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
	> GROUP BY year(ymd)
	> HAVING avg(price_close) > 50.0;

如果沒有哦HAVING子句，那麼這個查詢需要使用一個嵌套的SELECT子查詢：\

hive> SELECT s2.year, s2.avg FROM
	> (SELECT year(ymd) AS year, avg(price_close) AS avg FROM stocks
    > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
    > GROUP BY year(ymd)) s2
    > WHERE s2.avg > 50.0;

4 JOIN語句

Hive支援通常的SQL JOIN語句，但是隻支援等值連接配接。

4.1 INNER JOIN

内連接配接（INNER JOIN）隻有進行連接配接的兩個表中都存與連接配接标準相比對的資料才會被保留下來。

SELECT  a.ymd, a.price_close, b.price_close
FROM stocks a JOIN stocks b
ON a.ymd <= b.ymd
WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';

上面語句在Hive中是非法的，主要原因是通過MapReduce很難實作這中類型的連接配接。（連接配接條件為小于等于）

同時Hive目前還不支援在ON字句中的謂詞間使用OR。

Hive的JOIN都是從左往右的，多個表JOIN的時候，先将左邊的表連接配接起來啟一個MapReduce job，然後将MapReduce job的輸出和後面的那個表進行連接配接操作，以此類推。

大多數情況下，Hive會對每個JOIN連接配接對象啟動一個MapReduce任務

4.2 JOIN優化

當對3個或者更多表進行JOIN連接配接時，如果每個ON子句都使用相同的連接配接鍵的話，那麼隻會産生一個MapReduce job。

Hive同時假定查詢中最後一個表是最大的表。在對每行記錄進行連接配接操作時，它會嘗試将其他表緩存起來，然後掃描最後那個表進行計算。是以，使用者需要保證連續查詢中的表的大小從左到右是依次增大的。

幸運的是Hive提供了一個“标記”機制來顯示地告之查詢優化器哪張表是大表，使用方式如下：

現在Hive将會嘗試将表 stocks作為驅動表，即使其在查詢中不是位于最後。

還有一種類似的非常重要的優化叫做map-side JOIN，可以參考4.9節的内容。

4.3 LEFT OUTER JOIN

左外連接配接通過關鍵字LEFT OUTER進行辨別：

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
	> FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
	> WHERE s.symbol = 'AAPL';
...
1987-05-01	AAPL	80.0	NULL
1987-05-04	AAPL	19.75	NULL
1987-05-05	AAPL	80.25	NULL
1987-05-06	AAPL	80.0	NULL
1987-05-07	AAPL	80.25	NULL
1987-05-11	AAPL	77.0	0.015
1987-05-12	AAPL	75.0	NULL
...

在這種JOIN連接配接操作中，JOIN操作符左邊表中符合WHERE子句的所有記錄将會被傳回。JOIN操作符右邊表中如果沒有符号ON後面連接配接條件的記錄時，那麼從右邊表指定選擇的列的值将會是NULL。

4.4 OUTER JOIN

嵌套SELECT 語句會按照要求執行“下推”過程，在資料進行連接配接操作之前會先進行分區過濾。

提示：

WHERE語句在連接配接操作執行後才會執行，是以WHERE語句應該隻用于那些非NULL值的列值。同時，和Hive的文檔說明中相反的是，ON語句中的分區過濾條件外連接配接（OUTER JOIN）中是無效的，不過在内連接配接（INNER JOIN）中是有效的。

4.5 RIGHT OUTER ON

右外連接配接（RIGHT OUTER JOIN）會傳回右邊表所有符合WHERE語句的記錄。左表中比對不上的字段值用NULL代替。

4.6 FULL OUTER JOIN

完全外連接配接（FULL OUTER JOIN）将會傳回所有表中符合WHERE語句條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那麼就使用NULL值替代。

4.7 LEFT SEMI-JOIN

左半開連接配接（LEFT SEMI-JOIN）會傳回左邊表的記錄，前提是其記錄對于右邊表滿足ON語句中的判定條件。

大多數的SQL方言會通過IN…EXISTS結構來處理這種情況。例如下面的示例：

SELECT s.ymd, s.symbol, s.price_close FROM stocks s
WHERE s.ymd, s.symbol IN
(SELECT d.ymd, d.symbol FROM dividends d);

不過，使用者可以使用如下LEFT SEMI JOIN 文法達到同樣的目的：

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;
注意：SELECT和WHERE語句中不能引用到右表中的表
警告：Hive不支援右半開連接配接（RIGHT SEMI-JOIN）

SEMI-JOIN比通常INNER JOIN要更高效，原因是：對于左表中一條指定的記錄，在右表中一旦找到比對的記錄，Hive就會立即停止掃描。從這點來看，左表中選擇的列是可以預測的。

4.8 笛卡爾積JOIN

笛卡爾積是一種連接配接，表示左邊表的行數乘以右邊表的行數等于笛卡爾積結果集的大小。

上面這條語句，事實上很難找到合适的理由來執行這類連接配接，上述語句會産生 stock表資料條數乘以dividends表中資料條數的結果。它和其他連接配接類型不同，笛卡爾積不是并行執行的，而且使用MapReduce計算架構的話，任何方式都無法進行優化。

這裡大家可以注意，如果使用了錯誤的連接配接（JOIN）文法可能會導緻産生一個執行的時間長、運作緩慢的笛卡爾積查詢。例如，下面這個查詢在很多資料庫中會被優化成内連接配接（INNER JOIN），但是在Hive中沒有此優化：

hive> SELECT * FROM stocks JOIN dividends
	> WHERE stock.symbol = dividends.symbol and stock.symbol='APPL';

在Hive中，這個查詢在應用WHERE語句中的謂詞條件前會先進行笛卡爾積計算。這個過程将會消耗很長的時間。如果設定屬性

hive.mapred.mode

值為strict的話，Hive 會阻止使用者執行笛卡爾積查詢。

提示：當然笛卡爾積有時候也是很有用的

例如，假設一個表存放使用者偏好，另一個表存放新聞文章，同時有一個算法會推測出使用者可能會喜歡讀那些文章。這個時候就需要使用笛卡爾積生成所有使用者和所有網頁的對應關系的集合

4.9 map-side JOIN

如果是以表中隻有一張表是小表，那麼可以在最大的表通過mapper的時候将小表完全放到記憶體中。Hive可以在map端執行連接配接過程（稱為map-side JOIN），這是因為可以和記憶體中的小表進行逐一比對，進而省掉正常連接配接操作所需要的reduce過程。即使對于很小的資料集，這個優化也明顯要快于正常的連接配接操作。其不僅減少了reduce過程，而且有時還可以同時減少map過程的執行步驟。

在Hive v0.7之前的版本中需要增加一個标記來觸發這個優化，但是從Hive v0.7版本開始，Hive廢棄了這種标記方式，使用者可以通過設定屬性

hive.auto.convert.JOIN

的值為true，這樣Hive才會在必要的時候啟動這個優化。預設情況下這個屬性的值是false。

同時，使用者也可以配置能夠使用這個優化的小表的大小。如果是這個屬性的預設值（機關：位元組）：

hive.mapjoin.smalltable.filesize=25000000

如果使用者期望Hive在必要的時候自動啟動這個優化的話，可以将這個屬性設定在***$HOME/.hiverc***檔案中。

Hive對于右外連接配接（RIGHT OUTER JOIN）和全外連接配接（FULL OUTER JOIN）不支援這個優化。

如果所有的資料都是分桶的，那麼對于大表，在特定的情況下同樣可以使用這個優化。（關于分桶，後面我會詳細說明）這裡簡單介紹一下，就是表中的資料必須是按照ON語句中的鍵進行分桶的，而且其中一張分桶的個數必須是另一張表分桶個數的若幹倍。當滿足這些條件時，那麼Hive可以在map階段按照分桶資料進行連接配接。是以這種情況下，不需要先擷取到表中所有的内容，之後才去和另一張表中每個分桶進行比對連接配接。

不過這個優化預設也是沒有開啟的。需要設定參數hive.optimize.bucketmapJOIN為true才可以開啟此優化：

set hive.optimize.bucketmapJOIN=true

如果所涉及的分桶表都具有相同的分桶數，而且資料是按照連接配接鍵或桶的鍵進行排序的，那麼這時Hive可以執行一個更快的分類-合并連接配接（sort-merge JOIN）。同樣地，這個優化需要設定如下屬性才能開啟：

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

set hive.optimize.bucketmapjoin=true;

set hive.optimize.bucketmapjoin.sortedmerge=true;

5 ORDER BY 和 SORT BY

和其他SQL一樣會對查詢的結果進行一個全局排序，也就是說會所有的資料最後會通過一個reducer進行處理，對于大資料集容易出現OOM，同時這個過程也需要花費很長時間。

Hive增加了一個可供選擇的方式，也就是SORT BY，其隻會在每個reducer中對資料進行排序，這可以保證每個reducer的輸出資料都是有序的（但并非全局有序）。這樣可以提高後面進行的全局排序的效率。

同時使用者可以使ASC關鍵字（預設的）表示升序，或者加上DESC關鍵字，表示降序，來對結果集進行控制。

6 含有SORT BY 的DISTRIBUTE BY

DISTRIBUTE BY 控制map的輸出在reducer中是如何劃分的。MapReduce job這個傳輸的所有資料都是按照鍵-值對的方式進行組織的，是以Hive在将使用者的查詢語句轉成MapReduce job時，其必須在内部使用這個功能。

通常使用者不需要擔心這個特性。不過對于使用了Streaming特性（參考後面14章的内容）以及一些狀态為UDAF（使用者自定義聚合函數）的查詢是個例外。

預設情況下，MapReduce計算架構會依據map輸入的鍵計算相應的哈希值，然後按照得到的哈希值将鍵-值對均勻分發到多個reducer中去。不過不幸的是，這也就意味着當我們使用SORT BY時，不同reducer的輸出内容會有明顯的重疊。（也正是因為如此會發生資料傾斜的情況）

假如我們希望具有相同股票交易碼的資料在一起處理。那麼我們可以使用DISTRIBUTE BY來保證具有相同股票交易碼的記錄會分發到同一個reducer中進行處理，然後使用SORT 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内的資料是如何進行排序的。

需要注意的是，Hive要求DISTRIBUTE BY 語句要在 SORT BY 語句之前（即先分區在排序）。

7 CLUSTER BY

在前面的例子中，s.symbol列被用在了DISTRIBUTE BY 語句中，而s.symbol列和s.ymd位于 SORT BY 語句中。如果這2個語句中涉及到的列完全相同，而且采用的是升序排序方式（也就是預設的排序方式），那麼在這種情況下，CLUSTER BY 就等價于前面的2個語句，相當于前面2個句子的一個簡寫。

如下例：

hive> SELECT s.ymd, s.symbol, s.price_close
	> FROM stocks s
	> CLUSTER BY s.symbol;

如果把第6小節中 SORT BY 後面的s.ymd ASC去掉，就可以使用CLUSTER BY替代 DISTRIBUTE BY … SORT BY。

8 類型裝換

文法：cast(value AS TYPE)

例如：

SELECT name,salary FROM employees
WHERE cast(salary AS FLOAT) < 100000.0;

如果上例中的salary是一個不合法的浮點數字字元串的話，Hive會傳回NULL。

将浮點數轉換成整數的推薦方式是使用round()或者floor()函數，而不是使用類型轉換操作符cast。

類型轉換BINARY值

Hive v0.8.0版本中新引入的BINARY類型隻支援BINARY類型轉換成STRING類型。不過如果使用者知道其值是數值的話，那麼可以通過嵌套cast() 的方式對其進行類型轉換，如下面例子，其中b字段類型是BINARY：

使用者同樣可以将STRING類型轉化為BINARY類型。

9 抽樣查詢

對于非常大的資料集，有時使用者需要使用的是一個具有代表性的查詢結果而不是全部結果。Hive可以通過對表進行分桶抽樣來滿足這個需求。

我們可以使用rand()函數進行抽樣，這個函數會傳回一個随機值。前兩個查詢都傳回了不相等的值，而第3個查詢語句無傳回結果：

hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;
2
4
hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;
7
10
hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;

如果我們是按照指定的列而非rand()函數進行分桶的話，那麼同一語句多次執行的傳回值是相同的：

hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;
2
hive> SELECT * from numbers TABLESAMPLE(BUCKET 5 OUT OF 10 ON number) s;
4
hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;
2

分桶語句中的分母表示的是資料将會被散列的桶的個數，而分子表示将會選擇的桶的個數：

hive> SELECT * from numbers TABLESAMPLE(BUCKET 1 OUT OF 2 ON number) s;
2
4
6
8
10
hive> SELECT * from numbers TABLESAMPLE(BUCKET 2 OUT OF 2 ON number) s;
1
3
5
7
9

9.1 資料塊抽樣

Hive提供了另外一種按照抽樣百分比進行抽樣的方式，這種是基于行數的，按照輸入路徑下的資料塊百分比進行的抽樣：

hive> SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s
這總抽樣不一定适用于所有的檔案格式。另外，這種抽樣的最小抽樣單元式一個HDFS資料塊。是以，如果表的資料大小小于普通的塊大小128M的話，那麼将會傳回所有行。

基于百分比的抽樣方式提供了一個變量，用于控制基于資料塊的調優的種子資訊：

<property>
	<name>hive.sample.seednumber</name>
    <value>0</value>
    <description>A number used for percentage sampling. By changing this number, user will change the subsets of data sampled.</description>
</property>

9.2 分桶表的輸入裁剪

從上面看TABLESAMPLE語句，如下查詢和TABLESAMPLE操作相同：

hive> SELECT * FROM numbersflat WHERE number % 2 = 0;
2
4
6
8
10

對于大多數類型的表确實是這樣。抽樣會掃描表中所有的資料，然後在每N行中抽取一行資料。不過如果TIMESAMPLE語句中指定的列和CLUSTERED BY語句中指定列相同，那麼TABLESAMPLE查詢就隻會掃描涉及到的表的哈斯分區下的資料：

hive> CREATE TABLE numbers_bucketed(number int) CLUSTERD BY (number) INTO 3 BUCKETS;
hive> SET hive.enforce.bucketing=true;
hive> INSERT OVERWRITE TABLE numbers_bucketed SELECT number FROM numbers;

hive> dfs -ls /user/hive/warehouse/mydb.db/number_bucketed;
/user/hive/warehouse/mydb.db/number_bucketed/000000_0
/user/hive/warehouse/mydb.db/number_bucketed/000001_0
/user/hive/warehouse/mydb.db/number_bucketed/000002_0

hive> dfs -cat /user/hive/warehouse/mydb.db/numbers_bucketed/000001_0;
1
7
10
4

因為這個表以及聚集了3個資料桶了，是以下面的查詢可以高效的對其中一個資料桶進行抽樣：

hive> SELECT * FROM numbers_bucketed TABLESAMPLE(BUCKET 2 OUT OF 3 ON NUMBER) s;
1
7
10
4

10 UNION ALL

UNION ALL可以将2個或多個表進行合并。每一個union子查詢都必需具有相同的列，而且對應的每個字段的字段類型必須是一緻的。

UNION ALL也可以用于同一個表的資料合并。（從邏輯上講，可以使用一個SELECT和WHERE語句來獲相同的結果。是以這種方法便于将一個複雜的WHERE語句分割成2個或多個UNION子查詢）。不過除非源表建立了索引，否則這個查詢會将對同一分源資料進行多次拷貝分發。

例如：

FROM(
	FROM src SELECT src.key, src.value WHERE src.key < 100
    UNION ALL
    FROM src SELECT src.* WHERE src.key > 110
) unioninput
INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unioninput.*
Hive權威指南 mapreduce hive hadoop 大資料
上一篇: 計算機網絡第六版知識大綱
下一篇: 《HBase權威指南》讀書筆記9:第九章 進階用法行鍵設計輔助索引搜尋內建布隆過濾器

繼續閱讀