工作中使用Hive SQL的幾個知識點(建議收藏)

hive是基于Hadoop的一個數(shù)據(jù)倉庫工具，用來進(jìn)行數(shù)據(jù)的ETL，這是一種可以存儲、查詢和分析存儲在Hadoop中的大規(guī)模數(shù)據(jù)的機(jī)制。hive能將結(jié)構(gòu)化的數(shù)據(jù)文件映射為一張數(shù)據(jù)庫表，并提供SQL查詢功能。Hive SQL是一種類SQL語言，與關(guān)系型數(shù)據(jù)庫所支持的SQL語法存在微小的差異。本文對比MySQL和Hive所支持的SQL語法，發(fā)現(xiàn)相同的SQL語句在Hive和MySQL中輸出結(jié)果的會有所不同。

兩個整數(shù)除

除法是SQL引擎難以解釋的算術(shù)運算。如果將兩個整數(shù)相加，相減或相乘，則始終會得到一個整數(shù)。值得注意的是，如果將兩個整數(shù)相除，不同的SQL查詢引擎輸出的結(jié)果不盡相同。在Hive和MySQL中，運算兩個整數(shù)相除，輸出的結(jié)果都是decimal類型。

--?Hive中查詢
select?10/3???????--?輸出：3.3333333333333335
--?在MySQL中查詢
select?10/3???????--?輸出：3.3333

如果使用下面的方式，則會返回整形類型

--?Hive中查詢
select?10?div?3???????--?輸出：3
--?在MySQL中查詢
select?10?div?3???????--?輸出：3

區(qū)分大小寫

當(dāng)我們比較兩個字符串時，在不同的SQL引擎會產(chǎn)生不同的結(jié)果。需要注意的是，在字符串比較中，Apache Hive是區(qū)分大小寫，看下面的例子。

--?Hive中查詢
select?'Bigdata'?=?'bigdata'???--?輸出false
--?在MySQL中查詢
select?'Bigdata'?=?'bigdata'??--?輸出1

可以看出：相同的SQL語句，如果使用MySQL，則同一查詢將返回1，因為在進(jìn)行字符串比較時MySQL不區(qū)分大小寫。這意味著只要它們具有相同的字母，MySQL便會將兩個字符串解釋為相同的字符串。

我們再來看一下另外一個現(xiàn)象，當(dāng)我們把表名寫成大寫的，會出現(xiàn)什么現(xiàn)象呢？

這取決于所使用的SQL引擎，在引用數(shù)據(jù)庫中的表時需要注意區(qū)分大小寫。如果使用Hive，則在引用表時無需擔(dān)心大小寫，因為它們始終將字母轉(zhuǎn)換為小寫字母。但是在MySQL中會報1146 - Table 'XX' doesn't exist的錯誤。

--?假設(shè)Hive、MySQL中有一張test表
--?在Hive中查詢
select?*?from?Test???--?正常輸出結(jié)果
--?在MySQL中查詢
select?*?from?Test???--?報錯：1146 - Table 'Test' doesn't exist

在GROUP BY中使用別名

假設(shè)有如下查詢：

--?使用別名，在Hive中查詢
--?報錯Error?while?compiling?statement:?FAILED:?SemanticException?[Error?10004]:?line?7:9?Invalid?table?alias?or?column?reference?'inventory_status':?(possible?column?names?are:?userid,?visitdate,?visitcount)
SELECT?CASE
???????????WHEN?visicount?>?5?THEN?"more?than?5"
???????????ELSE?"less?than?5"
???????END?AS?inventory_status,
???????count(*)?AS?cnt
FROM?test
GROUP?BY?inventory_status

--?不使用別名，如果使用下面的語句，則會正常輸出結(jié)果
SELECT?CASE
???????????WHEN?visitcount?>?5?THEN?"more?than?5"
???????????ELSE?"less?than?5"
???????END?AS?inventory_status,
???????count(*)?AS?cnt
FROM?test
GROUP?BY?CASE
???????????WHEN?visitcount?>?5?THEN?"more?than?5"
???????????ELSE?"less?than?5"
???????END

相同的查詢語句在MySQL中進(jìn)行查詢，會正常輸出結(jié)果。

非數(shù)值類型的字符串轉(zhuǎn)為數(shù)值類型

使用SQL，我們可以使用CAST命令轉(zhuǎn)換表中列的數(shù)據(jù)類型。如果要將字符串列轉(zhuǎn)換為整數(shù)，可以執(zhí)行以下操作。

SELECT?CAST(column_name?AS?INT)?FROM?table_name

那么，如果我們將一個非數(shù)值類型的字符串轉(zhuǎn)為數(shù)值類型，會出現(xiàn)什么樣的結(jié)果呢？

--?在Hive中查詢
select?cast("bigdata"?as?int)?--?返回null
--?在MySQL中查詢
select?cast("bigdata"?as?signed?int)??--?返回0

Hive中的視圖與SQL查詢語句

當(dāng)我們在Hive中創(chuàng)建視圖時，其底層是將視圖對應(yīng)的SQL語句存儲到了一張表中的某個字段中，以Hive為例，其元數(shù)據(jù)中存在下面的一張表：

CREATE?TABLE?`TBLS`?(
??`TBL_ID`?bigint(20)?NOT?NULL,
??`CREATE_TIME`?int(11)?NOT?NULL,
??`DB_ID`?bigint(20)?DEFAULT?NULL,
??`LAST_ACCESS_TIME`?int(11)?NOT?NULL,
??`OWNER`?varchar(767)?CHARACTER?SET?latin1?COLLATE?latin1_bin?DEFAULT?NULL,
??`OWNER_TYPE`?varchar(10)?CHARACTER?SET?latin1?COLLATE?latin1_bin?DEFAULT?NULL,
??`RETENTION`?int(11)?NOT?NULL,
??`SD_ID`?bigint(20)?DEFAULT?NULL,
??`TBL_NAME`?varchar(128)?CHARACTER?SET?latin1?COLLATE?latin1_bin?DEFAULT?NULL,
??`TBL_TYPE`?varchar(128)?CHARACTER?SET?latin1?COLLATE?latin1_bin?DEFAULT?NULL,
??`VIEW_EXPANDED_TEXT`?mediumtext?CHARACTER?SET?utf8,
??`VIEW_ORIGINAL_TEXT`?mediumtext?CHARACTER?SET?utf8,
??`LINK_TARGET_ID`?bigint(20)?DEFAULT?NULL,
??PRIMARY?KEY?(`TBL_ID`),
??UNIQUE?KEY?`UNIQUETABLE`?(`TBL_NAME`,`DB_ID`),
??KEY?`TBLS_N50`?(`SD_ID`),
??KEY?`TBLS_N49`?(`DB_ID`),
??KEY?`TBLS_N51`?(`LINK_TARGET_ID`),
??CONSTRAINT?`TBLS_FK1`?FOREIGN?KEY?(`SD_ID`)?REFERENCES?`SDS`?(`SD_ID`),
??CONSTRAINT?`TBLS_FK2`?FOREIGN?KEY?(`DB_ID`)?REFERENCES?`DBS`?(`DB_ID`),
??CONSTRAINT?`TBLS_FK3`?FOREIGN?KEY?(`LINK_TARGET_ID`)?REFERENCES?`TBLS`?(`TBL_ID`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1;

上面的這張表存儲了Hive中表和視圖的元數(shù)據(jù)信息，如果創(chuàng)建一張視圖，則VIEW_EXPANDED_TEXT字段與VIEW_ORIGINAL_TEXT字段存儲了視圖對應(yīng)的SQL語句。

當(dāng)我們使用下面的SQL語句創(chuàng)建視圖或者直接執(zhí)行時，可能會出現(xiàn)不一樣的結(jié)果：

create?view?as?select?*?from?test?where?name?like?"%大數(shù)據(jù)"

如果是直接執(zhí)行SQL語句，則會按照條件篩選出想要的結(jié)果。但是，如果是創(chuàng)建視圖，則可能不會出現(xiàn)想要的結(jié)果。上面提到，視圖對應(yīng)的SQL語句是作為一個字段存儲到Hive的元數(shù)據(jù)中的，對應(yīng)其中的一張表。如上面的SQL語句，**like "%大數(shù)據(jù)"**中包含中文，該中文字符會出現(xiàn)亂碼現(xiàn)象，即存儲到表中時會變成下面的形式：

create?view?as?select?*?from?test?where?name?like?"???"

解決上面的問題很簡單，只需要修改元數(shù)據(jù)中該字段的編碼即可：

ALTER?TABLE?`TBLS`?MODIFY?COLUMN?VIEW_EXPANDED_TEXT?mediumtext?CHARACTER?SET?utf8；
ALTER?TABLE?`TBLS`?MODIFY?COLUMN?VIEW_ORIGINAL_TEXT?mediumtext?CHARACTER?SET?utf8；

總結(jié)

本文分享了Hive使用過程中存在的一些問題，并給出了相對應(yīng)的示例，我們在使用的過程中可以留意一下這些問題，對比相同的SQL語句在MySQL和Apache Hive上的結(jié)果上的不同。