温馨提示：如果使用电脑查看图片不清晰，可以使用手机打开文章单击文中的图片放大查看高清原图。
Fayson的github：
提示：代码块部分可以左右滑动查看噢
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实操，第一时间，分享更多Hadoop干货，欢迎转发和分享。
原创文章，欢迎转载，转载请注明：转载自微信公众号Hadoop实操
