在大數據領域，Hive SQL 是一種常用的查詢語言，用于在 Hadoop上進行數據分析和處理。為了確保代碼的可讀性、維護性和性能，制定一套規范化的 Hive SQL 開發規范至關重要。本文將介紹 Hive SQL 的基礎知識，并提供一些規范化的開發指南，幫助您高效地編寫 Hive SQL 查詢。

本系列分為

Hive SQL 開發指南（一）數據類型及函數

Hive SQL 開發指南（二）使用（DDL、DML，DQL）

Hive SQL 開發指南（三）優化及常見異常

---

一、數據定義命令（DDL）

建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name[(col_name data_type [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], ...)] INTO num_buckets BUCKETS][ROW FORMAT row_format][STORED AS file_format][LOCATION hdfs_path]

?注意事項

1. CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用?IF NOT EXIST 選項來忽略這個異常
2. EXTERNAL 關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION）
3. LIKE 允許用戶復制現有的表結構，但是不復制數據
4. COMMENT可以為表與字段增加描述
5. ROW FORMAT

????DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

????????[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

???| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

?????????用戶在建表的時候可以自定義?SerDe 或者使用自帶的?SerDe。如果沒有指定?ROW FORMAT 或者?ROW FORMAT DELIMITED，將會使用自帶的?SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的?SerDe，Hive 通過?SerDe 確定表的具體的列的數據。

????????6.STORED AS

????????????SEQUENCEFILE

????????????| TEXTFILE

????????????| RCFILE ???

????????????| INPUTFORMAT input_format_classname OUTPUTFORMAT ????????????output_format_classname

???????如果文件數據是純文本，可以使用?STORED AS TEXTFILE。如果數據需要壓縮，使用?STORED AS SEQUENCE 。

使用示例

創建簡單表：

CREATE TABLE pokes (foo INT, bar STRING);

創建外部表：

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,page_url STRING, referrer_url STRING,ip STRING COMMENT 'IP Address of the User',country STRING COMMENT 'country of origination')COMMENT 'This is the staging page view table'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'STORED AS TEXTFILELOCATION '<hdfs_location>';

建分區表?

CREATE TABLE par_table(viewTime INT, userid BIGINT,page_url STRING, referrer_url STRING,ip STRING COMMENT 'IP Address of the User')COMMENT 'This is the page view table'PARTITIONED BY(date STRING, pos STRING)ROW FORMAT DELIMITED ‘\t’FIELDS TERMINATED BY '\n'STORED AS SEQUENCEFILE;

建Bucket表?

CREATE TABLE par_table(viewTime INT, userid BIGINT,page_url STRING, referrer_url STRING,ip STRING COMMENT 'IP Address of the User')COMMENT 'This is the page view table'PARTITIONED BY(date STRING, pos STRING)CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETSROW FORMAT DELIMITED ‘\t’FIELDS TERMINATED BY '\n'STORED AS SEQUENCEFILE;

創建表并創建索引字段ds

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

復制一個空表

CREATE TABLE empty_key_value_storeLIKE key_value_store;

修改表結構

增加分區、刪除分區

增加

alter table 表名 add if not exists partition(dt='2024-02-12');

alter table table1 add if not exists partition(dt='2024-02-12') location '/dws/table1/dt=2024-02-12';

刪除

alter table 表名 drop if exists partition(dt='2024-03-06');

重命名表

ALTER TABLE table_name RENAME TO new_table_name

?修改列的名字、類型、位置、注釋

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

?增加/更新列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) ?

?增加表的元數據信息

ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties::[property_name = property_value…..]

刪表

DROP TABLE [IF EXISTS] table_name

二、?數據操作命令（DML）

LOAD DATA

語法規則

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

?使用說明

1. LOAD DATA命令主要用于裝載已有文件到新的TABLE中，只是拷貝或搬移文件，并不做內容的校驗。
2. 語法規則中，LOCAL表示從本地文件系統LOAD文件，否則就是從HDFS中取文件，OVERWRITE表示覆蓋已有的數據。

使用示例

LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

INSERT

基本模式

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

?多插入模式

FROM from_statementINSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

?將查詢結果寫入HDFS文件系統

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...FROM from_statementINSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

?INSERT INTO

INSERT INTO ?TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

?使用示例

將查詢數據輸出至目錄：

INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';

將查詢結果輸出至本地目錄：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

選擇所有列到本地目錄?：

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;

hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;

hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='<DATE>';

hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

將一個表的統計結果插入另一個表中：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;

hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

JOIN

hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

將多表數據插入到同一表中：

FROM src

INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100

INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200

INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

將文件流直接插入文件：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';

三、數據查詢DQL

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[   CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

?使用說明

1)使用ALL和DISTINCT選項區分對重復記錄的處理。默認是ALL，表示查詢所有記錄。DISTINCT表示去掉重復的記錄
2)Where 條件類似我們傳統SQL的where 條件,目前支持 AND,OR ,0.9版本支持between,IN, NOT IN,不支持EXIST ,NOT EXIST
3)ORDER BY與SORT BY的不同
ORDER BY 全局排序，只有一個Reduce任務
SORT BY 只在本機做排序
4)Limit
Limit 可以限制查詢的記錄數
例如：SELECT * FROM t1 LIMIT 5
實現Top k 查詢，下面的查詢語句查詢銷售記錄最大的 5 個銷售代表。
例如：
SET mapred.reduce.tasks = 1?
? ? SELECT * FROM test SORT BY amount DESC LIMIT 5
5)REGEX Column Specification
SELECT 語句可以使用正則表達式做列選擇，下面的語句查詢除了 ds 和 hr 之外的所有列：
SELECT `(ds|hr)?+.+` FROM test

基于Partition的查詢

?使用說明

1)一般 SELECT 查詢會掃描整個表，使用 PARTITIONED BY 子句建表，查詢就可以利用分區剪枝（input pruning）的特性
2)Hive 當前的實現是，只有分區斷言出現在離 FROM 子句最近的那個WHERE 子句中，才會啟用分區剪枝

?JOIN

語法規則

join_table: table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition table_reference: table_factor | join_table table_factor: tbl_name [alias] | table_subquery alias | ( table_references ) join_condition: ON equality_expression ( AND equality_expression )* equality_expression: expression = expression

使用說明

1)Hive 只支持等值連接（equality joins）、外連接（outer joins）和（left semi joins）。Hive 不支持所有非等值的連接，因為非等值連接非常難轉化到 map/reduce 任務
2)LEFT，RIGHT和FULL OUTER關鍵字用于處理join中空記錄的情況
3)LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實現
4)join 時，每次 map/reduce 任務的邏輯是這樣的：reducer 會緩存 join 序列中除了最后一個表的所有表的記錄，再通過最后一個表將結果序列化到文件系統
5)實踐中，應該把最大的那個表寫在最后
6)join 查詢時，需要注意幾個關鍵點
6.1) 只支持等值join

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

6.2) 可以 join 多于 2 個表，例如

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

6.3) 如果join中多個表的 join key 是同一個，則 join 會被轉化為單個 map/reduce 任務
7)LEFT，RIGHT和FULL OUTER
例子

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

7.1) 如果你想限制 join 的輸出，應該在 WHERE 子句中寫過濾條件——或是在 join 子句中寫
7.2) 容易混淆的問題是表分區的情況
SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key) WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘
如果 d 表中找不到對應 c 表的記錄，d 表的所有列都會列出 NULL，包括 ds 列。也就是說，join 會過濾 d 表中不能找到匹配 c 表 join key 的所有記錄。這樣的話，LEFT OUTER 就使得查詢結果與 WHERE 子句無關
解決辦法

SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

8)LEFT SEMI JOIN
LEFT SEMI JOIN 的限制是， JOIN 子句中右邊的表只能在 ON 子句中設置過濾條件，在 WHERE 子句、SELECT 子句或其他地方過濾都不行

SELECT a.key, a.value?FROM a?WHERE a.key in?(SELECT b.key?FROM B);可以被重寫為：SELECT a.key, a.val?FROM a LEFT SEMI JOIN b on (a.key = b.key)

9)UNION ALL
用來合并多個select的查詢結果，需要保證select中字段須一致
select_statement UNION ALL select_statement UNION ALL select_statement ...

四、HIVE SQL使用注意事項

HIVE不支持非等值連接

-- SQL中對兩表內聯可以寫成：
select * from dual a,dual b where a.key = b.key;
-- Hive中應為
select * from dual a join dual b on a.key = b.key; 
-- 而不是傳統的格式：
SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2 WHERE t1.a2 = t2.b2

分號字符

?分號是SQL語句結束標記，在HiveQL中也是，但是在HiveQL中，對分號的識別沒有那么智慧，例如：
select concat(key,concat(';',key)) from dual;
但HiveQL在解析語句時提示：
? ? ? ? FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification
解決的辦法是，使用分號的八進制的ASCII碼進行轉義，那么上述語句應寫成：
select concat(key,concat('\073',key)) from dual;

IS?[NOT]?NULL?

?SQL中null代表空值, 值得警惕的是, 在HiveQL中String類型的字段若是空(empty)字符串, 即長度為0, 那么對它進行IS NULL的判斷結果是False

Hive支持動態設置環境變量

例如，當啟動一個執行環境后，可以調用如下命令:

set mapred.job.queue.nam=queue01;

Hive環境默認是default數據庫，需用use切換數據庫

例如，當啟動一個執行環境后，可以調用如下命令:

use test_databases;

查詢注意事項

inner join中的join key可以作為過濾條件，過濾條件放置位置必須規范，where條件放置過濾條件，on條件放置等值條件，例如下面語句

select * from test a join test b on a.id=b.id and a.id=3;

select * from test a join test b on a.id=b.id where a.id=3;

select * from test a join test b where a.id=b.id and a.id=3;

這三個語句執行結果一致，且執行計劃一致（因為Hive內部幫我們優化了執行計劃），但我們還是應該規范寫法，按照語句二寫SQL

left/right outer join

left/right outer join類的join key不能作為驅動表的過濾條件，要實現過濾的話可以通過on + where組合
select * from test a left join test b on a.id=b.id and a.id=3;

select * from test a left join test b on a.id=b.id where a.id=3;

其中第一個語句中沒有把條件放入過濾中，而是直接作為join key值，而語句二放入where中，針對這種情況我們需要特殊情況特殊處理，特別針對partition的時候，如果希望過濾某批數據，應放到where后面處理

hive?mapjoin查詢注意

1．有一個極小的表<1000行
2．需要做不等值join的where操作（a.x < b.y 或者 a.x like b.y等，注：目前版本join下不支持不等值操作，不等值需加到where條件里）
3．默認情況下，hive會優化join，自動轉換為mapjoin，但是某些情況下，mapjoin執行會失敗，主要是由于hive依靠文件大小判斷是否進入mapjoin，然后由于文件行數過大，導致map過程中出現OOM而失敗，比如下面語句：

SELECT T.BD_ID ? ? ? ? ?AS BD_ID,T.BD_NAME ? ? ? ?AS BD_NAME,T.ITEM_CODE ? ? ?AS ITEM_CODE,T.ITEM_DESC ? ? ?AS ITEM_DESC,T.VENDOR_CODE ? ?AS VENDOR_CODE,T.VENDOR_NAME ? ?AS VENDOR_NAME,48 AS DATASOURCE_NUM_IDFROM GKEI.G_SRM_PRICE_ORDER_ALL_DTL TLEFT JOIN TEMP.G_SRM_PRICE_ORDER_TEMP ? ? ? ?TEMPON T.ORGANIZATION_ID = TEMP.ORGANIZATION_IDAND T.ITEM_ID = TEMP.INVENTORY_ITEM_IDWHERE TEMP.INVENTORY_ITEM_ID IS NULL;

其中主表不到40萬，重表 30萬，但是一直跑不出來，經過查看，map執行失敗，這種情況下，我們應該設置參數set hive.auto.convert.join=false;阻止進行map join查詢，讓它走common join

特殊不等值查詢

-- 語句一
SELECT t.statdate,c.cname,count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cnameOR t.area2 =c.cnameOR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'AND platform='pc'
GROUP BY t.statdate,
c.cname;-- 把上面的語句修改為
-- 語句二
SELECT dt,name,count(cid)
FROM(SELECT t.statdate dt,c.cname name,t.cookieid cidFROM tmpdb.city cJOIN ecdata.ext_trackflow t ON t.area1 =c.cnameWHERE t.statdate>='20140818'AND t.statdate<='20140824'AND platform='pc'UNION ALL SELECT t.statdate dt,c.cname name,t.cookieid cidFROM tmpdb.city cJOIN ecdata.ext_trackflow t ON t.area2 =c.cnameWHERE t.statdate>='20140818'AND t.statdate<='20140824'AND platform='pc'UNION ALL SELECT t.statdate dt,c.cname name,t.cookieid cidFROM tmpdb.city cJOIN ecdata.ext_trackflow t ON t.area3 =c.cnameWHERE t.statdate>='20140818'AND t.statdate<='20140824'AND platform='pc') tmp_trackflow
GROUP BY dt,name;-- 而不是修改為
-- 語句三
SELECT t.statdate,c.cname,count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'AND t.statdate<='20140824'AND platform='pc'AND (t.area1= c.cnameOR t.area2 =c.cnameOR t.area3 = c.cname)
GROUP BY t.statdate,c.cname;
-- 因為語句三會做笛卡爾積，很慢

謹防數據傾斜

如：

insert overwrite table temp.temp_css_phone_addr
select distinct a.user_id, b.mobile, a.longitude, a.latitude
fromdw_dmp.dw_user_addr_info a
left outer joindw_dmp.dw_user_phone_info b
on(a.user_id = b.user_id)
whereb.mobile is not NULL and b.mobile <> "" anda.longitude is not NULL and a.latitude is not NULL anda.longitude <> "" and a.latitude <> "" and a.user_id not in (select distinct user_id from temp.temp_css_id_integration_result);

其實這個語句不是那么復雜，可是整個語句跑了10幾個小時也跑不出來，并且還失敗了，為什么呢？

其實這里就這個reduce執行的時間過長，且還把磁盤空間撐爆了，原來是內部有條數據特別多，導致reduce數據傾斜，這種怎么處理呢

1. 設置參數
hive.map.aggr=true
Map 端部分聚合，相當于Combiner
hive.groupby.skewindata=true
有數據傾斜的時候進行負載均衡，當選項設定為 true，生成的查詢計劃會有兩個 MR Job。第一個 MR Job 中，Map 的輸出結果集合會隨機分布到 Reduce 中，每個 Reduce 做部分聚合操作，并輸出結果，這樣處理的結果是相同的 Group By Key 有可能被分發到不同的 Reduce 中，從而達到負載均衡的目的；第二個 MR Job 再根據預處理的數據結果按照 Group By Key 分布到 Reduce 中（這個過程可以保證相同的 Group By Key 被分布到同一個 Reduce 中），最后完成最終的聚合操作
2.在業務邏輯優化效果的不大情況下，有些時候是可以將傾斜的數據單獨拿出來處理。最后union回去（具體參考后文數據傾斜處理）