Hive SQL 幾乎是每一位互聯網分析師的必備技能，相信每一位面試過大廠的童鞋都有被面試官問到 Hive 優化問題的經歷。所以掌握扎實的 HQL 基礎尤為重要，既能幫分析師在日常工作中“如魚得水”提高效率，也能在跳槽時獲得一份更好的工作 offer。

本篇為 Hive 入門篇，主要為 Hive SQL 基礎語法介紹，文章爭取做到言簡意賅，讓大家 30 分鐘入門 HQL。

文中視角多處 HQL 對比 關系型數據庫 SQL，適合有一定 SQL 基礎的童鞋。(未掌握基礎 SQL 童鞋請移步「w3c school - SQL」快速上手 SQL)

---------- Hive 優化篇敬請期待

一、Hive 介紹

簡單來說，Hive 是基于 Hadoop 的一個數據倉庫工具。

Hive 的計算基于 Hadoop 實現的一個特別的計算模型 MapReduce，它可以將計算任務分割成多個處理單元，然后分散到一群家用或服務器級別的硬件機器上，降低成本并提高水平擴展性。

Hive 的數據存儲在 Hadoop 一個分布式文件系統上，即 HDFS。

需明確的是，Hive 作為數倉應用工具，對比 RDBMS(關系型數據庫) 有3個“不能”：不能像 RDBMS 一般實時響應，Hive 查詢延時大；

不能像 RDBMS 做事務型查詢，Hive 沒有事務機制；

不能像 RDBMS 做行級別的變更操作(包括插入、更新、刪除)。

另外，Hive 相比 RDBMS 是一個更“寬松”的世界，比如：Hive 沒有定長的 varchar 這種類型，字符串都是 string；

Hive 是讀時模式，它在保存表數據時不會對數據進行校驗，而是在讀數據時校驗不符合格式的數據設置為NULL。

二、Hive 查詢語句

Hive select 常規語法與 Mysql 等 RDBMS SQL 幾乎無異，下面附注語法格式，具體不做詳細講解。本節重點介紹 Hive 中出現的一些比較特殊且日常中我有用到的一些技巧給到大家參考。

2.1 附注 select 語法及語序，

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list]

[ORDER BY order_condition]

[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]

[LIMIT number]

2.2 多維度聚合分析 grouping sets/cube/roolup，

以一個示例來說明 3者作用及區別。request 表為后端請求表，現需分別統計3種不同維度的聚合：總共多少請求？不同系統、設備多少請求？不同城市多少請求。

不使用多維聚合方法，

SELECT NULL, NULL, NULL, COUNT(*)

FROM requests

UNION ALL

SELECT os, device, NULL, COUNT(*)

FROM requests GROUP BY os, device

UNION ALL

SELECT null, null, city, COUNT(*)

FROM requests GROUP BY city;

使用 grouping sets，

SELECT os, device, city ,COUNT(*)

FROM requests

GROUP BY os, device, city GROUPING SETS((os, device), (city), ());

cube 會枚舉指定列的所有可能組合作為 grouping sets，而 roolup 會以按層級聚合的方式產生 grouping sets。如，

GROUP BY CUBE(a, b, c)

--等價于以下語句。

GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())

GROUP BY ROLLUP(a, b, c)

--等價于以下語句。

GROUPING SETS((a,b,c),(a,b),(a), ())

2.3 正則方法指定 select 字段列

說是指定，其實是排除，如：`(num|uid)?+.+` 排除 num 和 uid 字段列。

另外，where 使用正則可以如此：where A Rlike B、where A Regexp B。

2.4 Lateral View(一行變多行)

Lateral View 和表生成函數(例如Split、Explode等函數)結合使用，它能夠將一行數據拆成多行數據，并對拆分后的數據進行聚合。

假設您有一張表pageAds，它有兩列數據，第一列是pageid string，第二列是adid_list，即用逗號分隔的廣告ID集合。

現需要統計所有廣告在所有頁面的出現次數，則先用 Lateral View + explode 做處理，即可正常分組聚合統計。

SELECT pageid, adid

FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

2.5 窗口函數

Hive 的窗口函數非常豐富，這在很多 RDBMS 中是少見的。(至少在 mysql 的較早期版本一直沒有支持窗口函數，一個分組排序得用上非常復雜的 SQL 自定義變量)

其中最常用的窗口函數當屬 row_number() over(partition by col order col_2)，它可以實現按指定字段的分組排序。

其它更豐富的窗口函數，我這不贅述，篇幅太大，完全可以重開一篇新的文章。建議參考阿里云 MaxCompute 的這份「窗口函數」的文檔，寫得非常詳細，強烈推薦！

2.6 代碼復用CTE復用：with t1 as();

阿里云 MaxCompute 支持創建 SQL Script 腳本：允許使用 @var:= 方式創建變量，實現復用。

with t1 as(

select user_id

from user

where ...

)

@var:= select

shop_id

from shop

where ...;

select *

from user_shop

where user_id in(select * from t1)

and shop_id in(select * from @var);

三、Hive 定義語句(DDL)

3.1 Hive 建表語句格式，

方法一：獨立聲明

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

[(col_name data_type [DEFAULT value] [COMMENT col_comment], ...)]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

[CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS]

[STORED BY StorageHandler] -- 僅限外部表

[WITH SERDEPROPERTIES (Options)] -- 僅限外部表

[LOCATION OSSLocation]; -- 僅限外部表

[LIFECYCLE days]

[AS select_statement]

方法二：從已有表直接復制

CREATE TABLE [IF NOT EXISTS] table_name

LIKE existing_table_name

下面對當中關鍵的聲明語句做解釋：[EXTERNAL]：聲明為外部表，往往在該表需要被多個工具共享時聲明，外部表刪表不會刪數據，只會刪元數據。

col_name datatype：data_type 一定要嚴謹定義，避免 bigint、double 等等統統用 string 的偷懶做法，否則不知某天數據就出錯了。(團隊內曾有同事犯過此錯誤)

[if not exists]：創建時不指定，若存在同名表則返回出錯。指定此選項，若存在同名表忽略后續，不存在則創建。

[DEFAULT value]：指定列的默認值，當INSERT操作不指定該列時，該列寫入默認值。

[PARTITIONED BY]：指定表的分區字段，當利用分區字段對表進行分區時，新增分區、更新分區內數據和讀取分區數據均不需做全表掃描，可以提高處理效率。

[LIFECYCLE]：是表的生命周期，分區表則每個分區的生命周期與表生命周期相同

[AS select_statement]：意味著可直接跟 select 語句插入數據

簡單示例：創建表sale_detail來保存銷售記錄，該表使用銷售時間 sale_date 和銷售區域 region 作為分區列。

create table if not exists sale_detail

(

shop_name string,

customer_id string,

total_price double

)

partitioned by (sale_date string, region string);

創建成功的表可以通過 desc 查看定義信息，

desc ;

desc extended ; --查看外部表信息。

如果需要不記得完整的表名，可以通過 show tables 在 db(數據庫)范圍內查找，

use db_name;

show tables ('tb.*'); --- tb.* 為正則表達式

3.2 Hive 刪表語句格式，

DROP TABLE [IF EXISTS] table_name; --- 刪除表

ALTER TABLE table_name DROP [IF EXISTS] PARTITION (partition_col1 = partition_col_value1, ...); --- 刪除某分區

3.3 Hive 變更表定義語句格式，

ALTER TABLE table_name RENAME TO table_name_new; --- 重命名表

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION (partition_col1 = partition_col_value1 ...); --- 增加分區

ALTER TABLE table_name ADD COLUMNS (col_name1 type1 comment 'XXX'); --- 增加列，同時定義類型與注釋

ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name column_type COMMENT column_comment; --- 修改列名和注釋

ALTER TABLE table_name SET lifecycle days; --- 修改生命周期

四、Hive 操作語句

Hive insert語句格式，

INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1...]

select_statement

FROM from_statement;

下面對當中關鍵的聲明語句做解釋：into|overwrite：into-直接向表或表的分區中追加數據；先清空表中的原有數據，再向表或分區中插入數據。

[PARTITION (partcol1=val1...]：不允許使用函數等表達式，只能是常量。

關于 PARTITION 這里展開說明指定分區插入和動態分區插入，輸出到指定分區：在INSERT語句中直接指定分區值，將數據插入指定的分區。

輸出到動態分區：在INSERT語句中不直接指定分區值，只指定分區列名。分區列的值在SELECT子句中提供，系統自動根據分區字段的值將數據插入到相應分區。

以上為 Hive 入門篇，希望對分析師的你有幫助。

若著有幫助，各位鐵子多點贊、收藏！！！

下一期，鐘家福：30分鐘掌握 Hive SQL 優化(解決數據傾斜)?zhuanlan.zhihu.com

敬請期待。

總結

以上是生活随笔為你收集整理的hive hql文档_30分钟入门 Hive SQL（HQL 入门篇）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。