[Hive]Union使用指南

sjf0115發表於2017-03-07
Hive

1. union語法

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

UNION將多個SELECT語句的結果集合併為一個獨立的結果集。當前只能支援UNION ALL(bag union)。不消除重複行。每個select語句返回的列的數量和名字必須一樣，否則，一個語法錯誤會被丟擲。

從語法中可以看出UNION有兩個可選的關鍵字：

使用DISTINCT關鍵字與使用UNION 預設值效果一樣，都會刪除重複行
使用ALL關鍵字，不會刪除重複行，結果集包括所有SELECT語句的匹配行（包括重複行）

注意

Hive 1.2.0之前的版本僅支援UNION ALL，其中重複的行不會被刪除。
Hive 1.2.0和更高版本中，UNION的預設行為是從結果中刪除重複的行。

DISTINCT union可以顯式使用UNION DISTINCT，也可以通過使用UNION而不使用以下DISTINCT或ALL關鍵字來隱式生成。

每個select_statement返回的列的數量和名稱必須相同。 否則，將丟擲錯誤。

注意

在Hive 0.12.0和更低版本中，UNION只能在子查詢中使用，例如“SELECT * FROM（select_statement UNION ALL select_statement）unionResult”。
從Hive 0.13.0開始，UNION也可以在頂級查詢中使用：例如“select_statement UNION ALL select_statement UNION ALL ...”。 （見HIVE-6189。）
在Hive 1.2.0之前，僅支援UNION ALL。 
Hive 1.2.0以後版本可以支援支援UNION（或UNION DISTINCT）。 （見HIVE-9039。）

2. UNION在FROM子句內

如果還需要對UNION的結果集進行一些其他的處理，整個語句表示式可以嵌入到FROM子句中，如下所示：

SELECT *
FROM (
  select_statement
  UNION ALL
  select_statement
) unionResultAlias

例如，假設我們有兩個不同的表分別表示哪個使用者釋出了一個視訊，以及哪個使用者釋出了一個評論，那麼下面的查詢將UNION ALL的結果與使用者表join在一起，為所有視訊釋出和評論釋出建立一個註釋流：

SELECT u.id, actions.date
FROM (
    SELECT av.uid AS uid
    FROM action_video av
    WHERE av.date = `2008-06-03`
    UNION ALL
    SELECT ac.uid AS uid
    FROM action_comment ac
    WHERE ac.date = `2008-06-03`
 ) actions JOIN users u ON (u.id = actions.uid)

3. DDL和插入語句的聯合

UNION 可以在檢視，插入和CTAS（建立表作為select）語句中使用。 查詢可以包含多個UNION子句，如上面的語法中所示。

4. Applying Subclauses

如果要對單個SELECT語句應用ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT，請將該子句放在括在SELECT中的括號內：

SELECT key FROM (SELECT key FROM src ORDER BY key LIMIT 10)subq1
UNION
SELECT key FROM (SELECT key FROM src1 ORDER BY key LIMIT 10)subq2

如果要對整個UNION結果應用ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句，請在最後一個之後放置ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT。 以下示例使用ORDER BY和LIMIT子句：

SELECT key FROM src
UNION
SELECT key FROM src1 
ORDER BY key LIMIT 10

5. 模式匹配的列別名

UNION期望在表示式列表的兩側有相同的模式。 因此，以下查詢可能會失敗，並顯示一條錯誤訊息，例如“FAILED：SemanticException 4:47 union的兩邊的模式應該匹配”。

INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" FROM source_table_2

在這種情況下，列別名可使UNION兩側的模式相同：

INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" as category FROM source_table_2

6. 列型別轉換

在2.2.0版本HIVE-14251之前，Hive嘗試在Hive型別組（Hive type group）之間執行隱式轉換。 隨著HIVE-14251的改變，Hive將僅在每個型別組（包括字串組，數字組或日期組，而不是組間）中執行隱式轉換。 為了合併來自不同組的型別，例如字串型別和日期型別，在查詢中需要從字串到日期或從日期到字串的顯式轉換。

SELECT name, id, cast(`2001-01-01` as date) d FROM source_table_1
UNION ALL
SELECT name, id, hiredate as d FROM source_table_2

7. Example

aa資料：

hive> select * from tmp_union_aa;
OK
ios	aa
ios	ab
adr	ac
adr	ad
adr	ad
ios	ab

ab資料：

hive> select * from tmp_union_ab;
OK
ios	ba
ios	bb
adr	ac
adr	bd
adr	bd
ios	ab

union all合併：

hive> select * from
(
   select platform, id from tmp_union_aa
   union all
   select platform, id from tmp_union_ab
) u;

ios	aa
ios	ab
adr	ac
adr	ad
adr	ad
ios	ab
ios	ba
ios	bb
adr	ac
adr	bd
adr	bd
ios	ab

union 合併：

hive>select * from
(
   select platform, id from tmp_union_aa
   union
   select platform, id from tmp_union_ab
) u;

adr	ac
adr	ad
adr	bd
ios	aa
ios	ab
ios	ba
ios	bb

原文：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union


相關文章