Hive SQL使用过程中的奇怪现象|避坑指南
hive是基于Hadoop的一个数据仓库工具，用来进行数据的ETL，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能。Hive SQL是一种类SQL语言，与关系型数据库所支持的SQL语法存在微小的差异。本文对比MySQL和Hive所支持的SQL语法，发现相同的SQL语句在Hive和MySQL中输出结果的会有所不同。
两个整数除
除法是SQL引擎难以解释的算术运算。如果将两个整数相加，相减或相乘，则始终会得到一个整数。值得注意的是，如果将两个整数相除，不同的SQL查询引擎输出的结果不尽相同。在Hive和MySQL中，运算两个整数相除，输出的结果都是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
区分大小写
当我们比较两个字符串时，在不同的SQL引擎会产生不同的结果。需要注意的是，在字符串比较中，Apache Hive是区分大小写，看下面的例子。
-- Hive中查询
select 'Bigdata' = 'bigdata' -- 输出false
-- 在MySQL中查询
select 'Bigdata' = 'bigdata' -- 输出1
可以看出：相同的SQL语句，如果使用MySQL，则同一查询将返回1，因为在进行字符串比较时MySQL不区分大小写。这意味着只要它们具有相同的字母，MySQL便会将两个字符串解释为相同的字符串。
我们再来看一下另外一个现象，当我们把表名写成大写的，会出现什么现象呢？
这取决于所使用的SQL引擎，在引用数据库中的表时需要注意区分大小写。如果使用Hive，则在引用表时无需担心大小写，因为它们始终将字母转换为小写字母。但是在MySQL中会报1146 - Table 'XX' doesn't exist的错误。
-- 假设Hive、MySQL中有一张test表
-- 在Hive中查询
select * from Test -- 正常输出结果
-- 在MySQL中查询
select * from Test -- 报错：1146 - Table 'Test' doesn't exist
在GROUP BY中使用别名
假设有如下查询：
-- 使用别名，在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
-- 不使用别名，如果使用下面的语句，则会正常输出结果
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中进行查询，会正常输出结果。
非数值类型的字符串转为数值类型
使用SQL，我们可以使用CAST命令转换表中列的数据类型。如果要将字符串列转换为整数，可以执行以下操作。
SELECT CAST(column_name AS INT) FROM table_name
那么，如果我们将一个非数值类型的字符串转为数值类型，会出现什么样的结果呢？
-- 在Hive中查询
select cast("bigdata" as int) -- 返回null
-- 在MySQL中查询
select cast("bigdata" as signed int) -- 返回0
Hive中的视图与SQL查询语句
当我们在Hive中创建视图时，其底层是将视图对应的SQL语句存储到了一张表中的某个字段中，以Hive为例，其元数据中存在下面的一张表：
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中表和视图的元数据信息，如果创建一张视图，则VIEW_EXPANDED_TEXT字段与 VIEW_ORIGINAL_TEXT字段存储了视图对应的SQL语句。
当我们使用下面的SQL语句创建视图或者直接执行时，可能会出现不一样的结果：
create view as select * from test where name like "%大数据"
如果是直接执行SQL语句，则会按照条件筛选出想要的结果。但是，如果是创建视图，则可能不会出现想要的结果。上面提到，视图对应的SQL语句是作为一个字段存储到Hive的元数据中的，对应其中的一张表。如上面的SQL语句，**like "%大数据"**中包含中文，该中文字符会出现乱码现象，即存储到表中时会变成下面的形式：
create view as select * from test where name like "???"
解决上面的问题很简单，只需要修改元数据中该字段的编码即可：
ALTER TABLE `TBLS` MODIFY COLUMN VIEW_EXPANDED_TEXT mediumtext CHARACTER SET utf8；
ALTER TABLE `TBLS` MODIFY COLUMN VIEW_ORIGINAL_TEXT mediumtext CHARACTER SET utf8；
总结
本文分享了Hive使用过程中存在的一些问题，并给出了相对应的示例，我们在使用的过程中可以留意一下这些问题，对比相同的SQL语句在MySQL和Apache Hive上的结果上的不同。
- END -
历史好文推荐