3-Hive基礎語法

3-Hive基礎語法

來自專欄 數據分析工具<5年數據3年分析>

Hive基礎語法

目 錄

1. 創建表
2. 基礎查詢語法
3. Hive SQL的優化
4. Hive函數

• 匯總統計函數
• 表格生成函數
• 複合類型操作符
• 集合操作函數
• 日期函數
• 條件函數
• 字元串函數
• 窗口函數

---

1. 創建表

CREATE EXTERNAL TABLE t_lxw1234 (id INT,ip STRING COMMENT 『訪問者IP』,avg_view_depth DECIMAL(5,1),bounce_rate DECIMAL(6,5)) COMMENT 『lxw1234』PARTITIONED BY (day STRING)ROW FORMAT DELIMITEDFIELDS TERMINATED BY 『,』STORED AS textfileLOCATION 『hdfs://cdh5/tmp/;

• 關鍵字EXTERNAL：

表示該表為外部表，如果不指定EXTERNAL關鍵字，則表示內部表

• 關鍵字COMMENT

為表和列添加註釋

• 關鍵字PARTITIONED BY

表示該表為分區表，分區欄位為day,類型為string

• 關鍵字ROW FORMAT DELIMITED

指定表的分隔符，通常後面要與以下關鍵字連用：

FIELDS TERMINATED BY 『,』 //指定每行中欄位分隔符為逗號

LINES TERMINATED BY 『
』 //指定行分隔符

COLLECTION ITEMS TERMINATED BY 『,』 //指定集合中元素之間的分隔符

MAP KEYS TERMINATED BY 『:』 //指定數據中Map類型的Key與Value之間的分隔符

舉個例子：

create table score(name string, score map<string,int>)ROW FORMAT DELIMITEDFIELDS TERMINATED BY 『t』COLLECTION ITEMS TERMINATED BY 『,』MAP KEYS TERMINATED BY 『:;

要載入的文本數據為：

biansutao 『數學:80,』語文:89,』英語:95

jobs 『語文:60,』數學:80,』英語:99

• 關鍵字STORED AS

指定表在HDFS上的文件存儲格式，可選的文件存儲格式有：

TEXTFILE //文本，默認值

SEQUENCEFILE // 二進位序列文件

RCFILE //列式存儲格式文件 Hive0.6以後開始支持

ORC //列式存儲格式文件，比RCFILE有更高的壓縮比和讀寫效率，Hive0.11以後開始支持

PARQUET //列出存儲格式文件，Hive0.13以後開始支持

• 關鍵詞LOCATION

指定表在HDFS上的存儲位置。

2.基礎查詢語法

Hive中的SELECT基礎語法和標準SQL語法基本一致，支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查詢等；

[WITH CommonTableExpression (, CommonTableExpression)*] SELECT [ALL | DISTINCT] select_expr, select_expr, ...FROM table_reference[WHERE where_condition][GROUP BY col_list][CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]][LIMIT number]

• ORDER BY和SORT BY

ORDER BY用於全局排序，就是對指定的所有排序鍵進行全局排序，使用ORDER BY的查詢語句，最後會用一個Reduce Task來完成全局排序。

SORT BY用於分區內排序，即每個Reduce任務內排序。

真實業務環境中，我們的需求大多需要使用ORDER BY全局排序來完成。

• DISTRIBUTE BY和CLUSTER BY

distribute by：指定reduce次數，然後按照指定的欄位或表達式對數據進行劃分，輸出到對應的Reduce或者文件中。

cluster by：除了兼具distribute by的功能，還兼具sort by的排序功能。

• 子查詢

子查詢和標準SQL中的子查詢語法和用法基本一致，需要注意的是，Hive中如果是從一個子查詢進行SELECT查詢，那麼子查詢必須設置一個別名。

還有一種將子查詢作為一個表的語法，叫做Common Table Expression（CTE）：

with q1 as (select * from src where key= 5),q2 as (select * from src s2 where key = 4)select * from q1 union all select * from q2;

• join

Hive中除了支持和傳統資料庫中一樣的內關聯、左關聯、右關聯、全關聯，還支持LEFT SEMI JOIN和CROSS JOIN，但這兩種JOIN類型也可以用前面的代替。

注意：Hive中Join的關聯鍵必須在ON ()中指定，不能在Where中指定，否則就會先做笛卡爾積，再過濾。

LEFT SEMI JOIN

以LEFT SEMI JOIN關鍵字前面的表為主表，返回主表的KEY也在副表中的記錄。

CROSS JOIN

返回兩個表的笛卡爾積結果，不需要指定關聯鍵

3.Hive SQL的優化

• 使用分區剪裁、列剪裁

在SELECT中，只拿需要的列，如果有，盡量使用分區過濾，少用SELECT *。

在分區剪裁中，當使用外關聯時，如果將副表的過濾條件寫在Where後面，那麼就會先全表關聯，之後再過濾，所以正確的寫法是

寫在ON後面：

SELECT a.idFROM lxw1234_a aleft outer join t_lxw1234_partitioned bON (a.id = b.url AND b.day = 『2015-05-10′);

或者直接寫成子查詢：

SELECT a.idFROM lxw1234_a aleft outer join (SELECT url FROM t_lxw1234_partitioned WHERE day = 『2015-05-10′) bON (a.id = b.url)

• 少用COUNT DISTINCT

數據量小的時候無所謂，數據量大的情況下，由於COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個Reduce需要處理的數據量太大，就會導致整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換：

SELECT day,COUNT(DISTINCT id) AS uvFROM lxw1234GROUP BY day

可以轉換成：

SELECT day,COUNT(id) AS uvFROM (SELECT day,id FROM lxw1234 GROUP BY day,id) aGROUP BY day;

4.Hive函數

4.1匯總統計函數

• 個數統計函數:count

語法: count(*), count(expr), count(DISTINCT expr[, expr_.])?

返回值: int?

說明: count(*)統計檢索出的行的個數，包括NULL值的行;count(expr)返回指定欄位的 非空值的個數;count(DISTINCT expr[, expr_.])返回指定欄位的不同的非空值的個數

舉例:

hive> select count(*) from lxw1234;?20?hive> select count(distinct t) from lxw1234;?10

• 集合去重數:collect_set 語法: collect_set (col)

返回值: array

說明: 將 col 欄位進行去重，合併成一個數組。

舉例:

hive> select cookie,ip from lxw1234;cookie1 127.0.0.1cookie1 127.0.0.1cookie1 127.0.0.2cookie1 127.0.0.3hive> select cookie,collect_set(ip) from lxw1234 group by cookie; cookie1 ["127.0.0.1","127.0.0.2","127.0.0.3"]

• 集合不去重函數:collect_list

語法: collect_list (col)

返回值: array

說明: 將 col 欄位合併成一個數組,不去重

舉例:

hive> select cookie,ip from lxw1234;cookie1 127.0.0.1cookie1 127.0.0.1cookie1 127.0.0.2cookie1 127.0.0.3hive>select cookie,collect_list(ip) from lxw1234 group by cookie; cookie1 ["127.0.0.1","127.0.0.1","127.0.0.2","127.0.0.3"]

4.2表格生成函數

• 數組拆分成多行:explode

語法: explode(ARRAY)

返回值: 多行

說明: 將數組中的元素拆分成多行顯示 舉例:

hive> select explode(array(1,2,3)) from lxw1234; 123

1. Map 拆分成多行:explode

語法: explode(Map)

返回值: 多行

說明: 將 Map 中的元素拆分成多行顯示

舉例:

hive> select explode(map(k1,v1,k2,v2)) from lxw1234;k2 v2k1 v1

4.3複合類型操作符

• 獲取 array 中的元素

語法:A[n]

操作類型:所有基礎類型

說明:返回數組 A 中第 n 個索引的元素值。

舉例:

hive> select array(a,b,c)[1] from lxw1234; b

• 獲取 map 中的元素

語法:M[key]

操作類型:所有基礎類型

說明:返回 map 結構 M 中 key 對應的 value。

舉例:

hive> select map(k1,v1)[k1] from lxw1234; v1

• 獲取 struct 中的元素

語法:S.x

操作類型:所有類型

說明:返回 struct 結構 S 中名為 x 的元素。

舉例:

hive> select named_struct(a,1,b,aaa,c,FALSE).c from lxw1234; false

4.4集合操作函數

• map 類型大小:size

語法: size(Map<K.V>)?

返回值: int?

說明: 返回 map 類型的 size?

舉例:

hive> select size(map(k1,v1,k2,v2)) from lxw1234; 2

• array 類型大小:size

語法: size(Array<T>)

返回值: int?

說明: 返回 array 類型的 size?

舉例:

hive> select size(array(1,2,3,4,5)) from lxw1234; 5

• 判斷元素數組是否包含元素:array_contains

語法: array_contains(Array<T>, value)

返回值: boolean

說明: 返回 Array<T>中是否包含元素 value

舉例:

hive> select array_contains(array(1,2,3,4,5),3) from lxw1234; true

• 獲取 map 中所有 value 集合

語法: map_values(Map<K.V>)

返回值: array<V>

說明: 返回 Map<K.V>中所有 value 的集合

舉例:

hive> select map_values(map(k1,v1,k2,v2)) from lxw1234; ["v2","v1"]

• 獲取map中所有key集合

語法: map_keys(Map<K.V>)

返回值: array<K>

說明: 返回 Map<K.V>中所有 key 的集合

舉例:

hive> select map_keys(map(k1,v1,k2,v2)) from lxw1234; ["k2","k1"]

• 數組排序

語法: sort_array(Array<T>)

返回值: array<t>

說明: 對 Array<T>進行升序排序

舉例:

hive> select sort_array(array(5,7,3,6,9)) from lxw1234; [3,5,6,7,9]

4.5日期函數

• 日期時間轉日期函數: to_date

語法: to_date(string timestamp)?

返回值: string?

說明: 返回日期時間欄位中的日期部分。

舉例:

hive> select to_date(2011-12-08 10:03:01) from lxw1234;2011-12-08

• 日期比較函數: datediff

語法: datediff(string enddate, string startdate)

返回值: int

說明: 返回結束日期減去開始日期的天數。

舉例:

hive> select datediff(2012-12-08,2012-05-09) from lxw1234; 213

• 日期增加函數: date_add

語法: date_add(string startdate, int days)

返回值: string

說明: 返回開始日期 startdate 增加 days 天后的日期。

舉例:

hive> select date_add(2012-12-08,10) from lxw1234; 2012-12-18

• 日期減少函數: date_sub

語法: date_sub (string startdate, int days)

返回值: string

說明: 返回開始日期 startdate 減少 days 天后的日期。 舉例:

hive> select date_sub(2012-12-08,10) from lxw1234; 2012-11-28

4.6條件函數

• 條件判斷函數:CASE

語法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END?

返回值: T?

說明:如果 a 等於 b，那麼返回 c;如果 a 等於 d，那麼返回 e;否則返回 f?

舉例:

hive> Select case 100 when 50 then tom when 100 then mary else tim end from lxw1234;?mary?

4.7字元串函數

• URL 解析函數:parse_url

語法: parse_url(string urlString, string partToExtract [, string keyToExtract])?

返回值: string?

說明:返回 URL 中指定的部分。partToExtract 的有效值為:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.?

舉例:

hive> select parse_url(http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1, HOST) from lxw1234;?facebook.com?hive> select parse_url(http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1, QUERY, k1) from lxw1234;?v1

• json 解析函數:get_json_object

語法: get_json_object(string json_string, string path)

返回值: string

說明:解析 json 的字元串 json_string,返回 path 指定的內容。如果輸入的 json 字元串無 效，那麼返回 NULL。

舉例:

hive> select get_json_object({"store":> {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],> "bicycle":{"price":19.95,"color":"red"}> },> "email":"amy@only_for_json_udf_test.net",> "owner":"amy">}> ,$.owner) from lxw1234; amy

4.8窗口函數

• SUM

注意,結果和ORDER BY相關,默認為升序

hive>SELECT cookieid, createtime, pv,SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默認為從起點到當前行SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --從起點到當前行，結果同pv1 SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分組內所有行SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --當前行+往前3行SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --當前行+往前3行+往後1行SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---當前行+往後所有行 FROM lxw1234;cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6 -----------------------------------------------------------------------------cookie1 2015-04-10 1 1 1 26 1 6 26cookie1 2015-04-11 5 6 6 26 6 13 25cookie1 2015-04-12 7 13 13 26 13 16 20cookie1 2015-04-13 3 16 16 26 16 18 13cookie1 2015-04-14 2 18 18 26 17 21 10cookie1 2015-04-15 4 22 22 26 16 20 8cookie1 2015-04-16 4 26 26 26 13 13 4

如果不指定ROWS BETWEEN,默認為從起點到當前行;

如果不指定ORDER BY，則將分組內所有值累加;

關鍵是理解ROWS BETWEEN含義,也叫做WINDOW子句：

PRECEDING：往前

FOLLOWING：往後

CURRENT ROW：當前行

UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING：表示到後面的終點

–其他AVG，MIN，MAX，和SUM用法一樣。

• NTILE

NTILE(n)，用於將分組數據按照順序切分成n片，返回當前切片值，如果切片不均勻，默認增加第一個切片的分布

–比如，統計一個cookie，pv數最多的前1/3的天

hive>SELECT cookieid, createtime, pv,NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn FROM lxw1234;cookieid day pv rn----------------------------------cookie1 2015-04-12 7 1cookie1 2015-04-11 5 1cookie1 2015-04-15 4 1cookie1 2015-04-16 4 2cookie1 2015-04-13 3 2cookie1 2015-04-14 2 3cookie1 2015-04-10 1 3

--rn = 1 的記錄，就是我們想要的結果

• ROW_NUMBER

ROW_NUMBER() 從1開始，按照順序，生成分組內記錄的序列

–比如，按照pv降序排列，生成分組內每天的pv名次

ROW_NUMBER() 的應用場景非常多，再比如，獲取分組內排序第一的記錄;獲取一個session中的第一條refer等。

hive>SELECT cookieid, createtime, pv,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM lxw1234;cookieid day pv rn------------------------------------------- cookie1 2015-04-12 7 1cookie1 2015-04-11 5 2cookie1 2015-04-15 4 3cookie2 2015-04-15 9 1cookie2 2015-04-16 7 2cookie2 2015-04-13 6 3

• RANK 和 DENSE_RANK

—RANK() 生成數據項在分組中的排名，排名相等會在名次中留下空位

—DENSE_RANK() 生成數據項在分組中的排名，排名相等會在名次中不會留下空位

hive>SELECT cookieid, createtime, pv,RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 FROM lxw1234 WHERE cookieid = cookie1;cookieid day pv rn1 rn2 rn3 -------------------------------------------------- cookie1 2015-04-12 7 1 1 1cookie1 2015-04-11 5 2 2 2cookie1 2015-04-15 4 3 3 3cookie1 2015-04-16 4 3 3 4cookie1 2015-04-13 3 5 4 5cookie1 2015-04-14 2 6 5 6cookie1 2015-04-10 1 7 6 7

rn1: 15號和16號並列第3, 13號排第5

rn2: 15號和16號並列第3, 13號排第4

rn3: 如果相等，則按記錄值排序，生成唯一的次序，如果所有記錄值都相等，或許會隨機排吧。

• LAG

LAG(col,n,DEFAULT) 用於統計窗口內往上第n行值

第一個參數為列名，第二個參數為往上第n行（可選，默認為1），第三個參數為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL）

hive>SELECT cookieid, createtime, url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LAG(createtime,1,1970-01-01 00:00:00) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time FROM lxw1234;cookieid createtime url rn last_1_time last_2_time-------------------------------------------------------------------------------------------cookie1 2015-04-10 10:00:00 url1 1 1970-01-01 00:00:00 NULLcookie1 2015-04-10 10:00:02 url2 2 2015-04-10 10:00:00 NULLcookie1 2015-04-10 10:03:04 1url3 3 2015-04-10 10:00:02 2015-04-10 10:00:00cookie1 2015-04-10 10:10:00 url4 4 2015-04-10 10:03:04 2015-04-10 10:00:02cookie2 2015-04-10 10:00:00 url11 1 1970-01-01 00:00:00 NULLcookie2 2015-04-10 10:00:02 url22 2 2015-04-10 10:00:00 NULLcookie2 2015-04-10 10:03:04 1url33 3 2015-04-10 10:00:02 2015-04-10 10:00:00cookie2 2015-04-10 10:10:00 url44 4 2015-04-10 10:03:04 2015-04-10 10:00:02

• LEAD

LEAD(col,n,DEFAULT) 用於統計窗口內往下第n行值

第一個參數為列名，第二個參數為往下第n行（可選，默認為1），第三個參數為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL）

hive>SELECT cookieid, createtime, url,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,LEAD(createtime,1,1970-01-01 00:00:00) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time FROM lxw1234;cookieid createtime url rn next_1_time next_2_time -------------------------------------------------------------------------------------------cookie1 2015-04-10 10:00:00 url1 1 2015-04-10 10:00:02 2015-04-10 10:03:04cookie1 2015-04-10 10:00:02 url2 2 2015-04-10 10:03:04 2015-04-10 10:10:00cookie1 2015-04-10 10:50:05 url6 3 2015-04-10 11:00:00 NULLcookie1 2015-04-10 11:00:00 url7 4 1970-01-01 00:00:00 NULLcookie2 2015-04-10 10:00:00 url11 1 2015-04-10 10:00:02 2015-04-10 10:03:04cookie2 2015-04-10 10:00:02 url22 2 2015-04-10 10:03:04 2015-04-10 10:10:00cookie2 2015-04-10 10:50:05 url66 3 2015-04-10 11:00:00 NULLcookie2 2015-04-10 11:00:00 url77 4 1970-01-01 00:00:00 NULL

• GROUPING SETS

在一個GROUP BY查詢中，根據不同的維度組合進行聚合，等價於將不同維度的GROUP BY結果集進行UNION ALL，GROUPING__ID，表示結果屬於哪一個分組集合

hive> SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM lxw1234 GROUP BY month,day GROUPING SETS (month,day,(month,day)) ORDER BY GROUPING__ID; month day uv GROUPING__ID------------------------------------------------2015-03 NULL 5 12015-04 NULL 6 1NULL 2015-03-10 4 2NULL 2015-03-12 1 22015-03 2015-03-10 4 32015-03 2015-03-12 1 3

等價於

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY dayUNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

• CUBE

根據GROUP BY的維度的所有組合進行聚合

hive> SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM lxw1234 GROUP BY month,day WITH CUBE ORDER BY GROUPING__ID;month day uv GROUPING__ID--------------------------------------------NULL NULL 7 02015-03 NULL 5 12015-04 NULL 6 1NULL 2015-04-12 2 2NULL 2015-04-13 3 2NULL 2015-03-10 4 2NULL 2015-03-12 1 22015-03 2015-03-10 4 32015-03 2015-03-12 1 32015-04 2015-04-12 2 32015-04 2015-04-13 3 3

等價於

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234UNION ALL SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY dayUNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

• ROLLUP

是CUBE的子集，以最左側的維度為主，從該維度進行層級聚合。

比如，以month維度進行層級聚合：

hive> SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM lxw1234 GROUP BY month,dayWITH ROLLUP ORDER BY GROUPING__ID;month day uv GROUPING__ID---------------------------------------------------NULL NULL 7 02015-03 NULL 5 12015-04 NULL 6 12015-03 2015-03-10 4 32015-03 2015-03-12 1 32015-04 2015-04-12 2 32015-04 2015-04-13 3 32015-04 2015-04-15 2 32015-04 2015-04-16 2 3

可以實現這樣的上鑽過程：月天的UV->月的UV->總UV

推薦閱讀：