乐趣区

关于hive: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 上的后果上的不同。

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

退出移动版