Hive SQL使用过程中的奇怪现象

Stella981
• 阅读 1017

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

wx搜一搜:大数据技术与数仓

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Easter79 Easter79
2年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
2年前
HIVE 时间操作函数
日期函数UNIX时间戳转日期函数: from\_unixtime语法:   from\_unixtime(bigint unixtime\, string format\)返回值: string说明: 转化UNIX时间戳(从19700101 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式举例:hive   selec
Stella981 Stella981
2年前
Hive和SparkSQL:基于 Hadoop 的数据仓库工具
Hive前言Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功能,将类SQL语句转换为MapReduce任务执行。!image(https://oscimg.oschina.net/oscnet/3ca2e844a74c7004d3281eeec5
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这