0458-Hive数据类型校验问题分析

温馨提示:如果使用电脑查看图片不清晰,可以使用手机打开文章单击文中的图片放大查看高清原图。

Fayson的github: https://github.com/fayson/cdhproject

提示:代码块部分可以左右滑动查看噢

1

文章编写目的

使用Hive时大家都会遇到数据类型校验的问题,相比传统关系型数据库会严格要求数据的Schema,数据的列数、每一列的字段类型都有严格的规定,因此数据的存储必须按照定义的Schema格式来存储。而Hive数据库对数据格式及具体的内容并不关心,只有在数据被读出时才会与定义的Schema进行转换。那这个时候就会出现数据类型转换的问题,本篇文章Fayson主要分析下如何查找表中类型转换错误的数据以及Hive对空值和NULL的处理。

  • 测试环境

1.RedHat7.2

2.CM和CDH版本为5.15.0

2

测试数据准备

1. 建表语句如下:

create table test_null (id int, age string) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

(可左右滑动)

2. 测试数据如下:

[root@cdh2 ~]# vim test1.dat
1,23
2,24c
3,32d
4,30
5,NULL

(可左右滑动)

3. 将测试数据加载到test_cast表中,查看表中的数据

Fayson在前面定义的表结构为id和age两个字段均为int类型,在Load的示例数据中age列有非数值类型的数据,查看表数据时会看到如上截图类型转换失败显示为NULL。

3

查找异常类型数据

Hive本身没有机制来校验数据的有效性,如果我们想检索出表中类型转换异常的数据,则可以通过nvl和cast两个函数来结合判断数据是否转换失败了。如下为实现方式:

1. 创建一个测试表及准备测试数据,SQL如下:

select id,nvl(cast(age as int), "error") age from test_cast;

2.将类型异常的数据插入到新的表中,SQL如下:

create table  test_exception as
 select * from (select id,nvl(cast(age as int), 'error') age from test_cast) as b where b.age='error';

(可左右滑动)

同样也可以只是用cast来进行查找,SQL如下:

create table test_exception as 
select * from (select id,nvl(cast(age as int), age) age from test_cast) as b where b.age is null;

(可左右滑动)

查看检索出来类型异常的数据

3.查看写入到HDFS的类型转换异常的数据

通过如上方式我们可以检索出test_cast表中age列类型转换异常的数据,通过每条数据的ID查找对应的原始数据找到问题原因。在上述过程中还出现了另一个问题Hive中NULL和空值是如何处理的?如下Fayson再介绍下Hive中对着两个值的处理。

4

Hive中NULL和空值处理

通过上述的处理过程,我们可以看到Hive对于类型转换异常的数据查询出来显示为NULL,但我们将这些数据写入到新的表后数据文件中显示的为\N。那在我们的数据中如果存’NULL’类型的字符串呢?Hive中默认将NULL存为\N,NULL类型的字符串如何检索?

1.创建一个测试表及准备测试数据,SQL如下:

create table test_null (id int, age string) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

测试数据如下:

[root@cdh2 ~]# vim test1.dat
1,23
2,24c
3,32d
4,30
5,NULL
6,\N

2.将数据Load到test_null表中显示如下:

如上图所5、6两条数据均显示的为NULL,通过数据我们无法真实的区别那条数据的age真正的为空。

3.通过指定查询条件可以检索出空值和NULL类型字符串数据

使用is null可以检索出存储为\N的数据(即id为6的这条数据)

使用=’NULL’可以检索出为NULL字符串的数据(即id为5的这条数据)

4.在Hive中是通过serialization.null.format参数来保存和标识NULL,通过将表的该参数修改为NULL表示为空值

alter table test_null set serdeproperties ('serialization.null.format' = 'NULL');

(可左右滑动)

向表中插入一条age为NULL的数据

insert into test_null values(7,NULL);

查看此时表中的数据显示

查看HDFS插入的数据显示NULL

5

总结

1.Hive在对表进行Put和Load数据操作时,Hive是不支持数据类型的校验,在使用insert into table select…方式向表中插入数据时,对于类型异常的数据会在表中插入一个\N空的值(\N为Hive中默认NULL标识)

2.可以使用serialization.null.format来指定Hive中保存和标识NULL,可以设置为默认的\N,也可以为NULL或''

3.如果表中存在大量的NULL值,则在Hive的数据文件中会产生大量的\N数据,浪费存储空间,那我们可以将serialization.null.format设置为''

alter table test_null set serdeproperties('serialization.null.format' = '');

(可左右滑动)

插入为NULL的数据后,HDFS的数据文件存储如下

可以通过建表语句中指定Hive保存和标识NULL,也可以通过alter修改已存在的表,建表指定方式如下:

create table test_null_1 (id int, age string) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
NULL DEFINED AS''
STORED AS TEXTFILE;

提示:代码块部分可以左右滑动查看噢

为天地立心,为生民立命,为往圣继绝学,为万世开太平。 温馨提示:如果使用电脑查看图片不清晰,可以使用手机打开文章单击文中的图片放大查看高清原图。

原文发布于微信公众号 - Hadoop实操(gh_c4c535955d0f)

原文发表时间:2018-11-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏别先生

|ERROR|ERROR: missing data for column "createtime" (seg3 slice1 192.168.66.23:40001 pid=33370)之mys

引发这个错误,并不是这个字段引起的错误,一般是这个字段临近的字段存在空格或者换行符引发的错误。

8020
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 3.索引的基本知识

索引优化是查询优化中最重要的一部分,索引是一种用于排序和搜索的结构,在查找数据时索引可以减少对I/O的需要;当计划中的某些元素需要或是可以利用经过排序的数据时,...

13430
来自专栏微服务生态

用尽洪荒之力整理的Mysql数据库32条军规

2、控制单表数据量 int型不超过1000w,含char则不超过500w; 合理分表; 限制单库表数量在300以内;

10230
来自专栏web编程技术分享

【手把手】JavaWeb 入门级项目实战 -- 文章发布系统 (第十一节)1.根据ID查询文章数据2.评论功能后台业务实现

89540
来自专栏SpringBoot 核心技术

第五章:使用QueryDSL与SpringDataJPA实现查询返回自定义对象

77440
来自专栏Jerry的SAP技术分享

使用ABAP正则表达式解析HTML标签

需求就是我用ABAP的某个函数从数据库读取一个字符串出来,该字符串的内容是一个网页。

13220
来自专栏CSDN技术头条

【干货】从配置到实例:MyBatis 基础入门

本质而言,ORM(Object-Relation Mapping),是一种编程技术,能够实现面向对象编程语言与关系型数据库之间的数据转换(映射)。

12720
来自专栏Java帮帮-微信公众号-技术文章全总结

Web-第二十四天 Oracle学习【悟空教程】

ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S...

30720
来自专栏乐沙弥的世界

PL/SQL --> 游标

映射在结果集中某一行数据的具体位置,类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行,然后根据业务需求

9420
来自专栏安恒网络空间安全讲武堂

Sqli_labs65关通关详解(下)

less-31 逻辑跟30关一样,只不过 $id = '"' .$id. '"'; $sql="SELECT * FROM users WHERE id=($i...

48280

扫码关注云+社区

领取腾讯云代金券