Hive SQL使用過程中的奇怪現(xiàn)象

hive是基于Hadoop的一個(gè)數(shù)據(jù)倉庫工具，用來進(jìn)行數(shù)據(jù)的ETL，這是一種可以存儲(chǔ)、查詢和分析存儲(chǔ)在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é)果的會(huì)有所不同。

兩個(gè)整數(shù)除

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

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

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

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

區(qū)分大小寫

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

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

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

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

這取決于所使用的SQL引擎，在引用數(shù)據(jù)庫中的表時(shí)需要注意區(qū)分大小寫。如果使用Hive，則在引用表時(shí)無需擔(dān)心大小寫，因?yàn)樗鼈兪冀K將字母轉(zhuǎn)換為小寫字母。但是在MySQL中會(huì)報(bào)1146 - Table 'XX' doesn't exist的錯(cuò)誤。

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

在GROUP BY中使用別名

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

--?使用別名，在Hive中查詢
--?報(bào)錯(cuò)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

--?不使用別名，如果使用下面的語句，則會(huì)正常輸出結(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)行查詢，會(huì)正常輸出結(jié)果。

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

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

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

那么，如果我們將一個(gè)非數(shù)值類型的字符串轉(zhuǎn)為數(shù)值類型，會(huì)出現(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)建視圖時(shí)，其底層是將視圖對應(yīng)的SQL語句存儲(chǔ)到了一張表中的某個(gè)字段中，以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;

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

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

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

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

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é)果上的不同。