将 Hive 空值统一为 \N

Hive在使用过程中不可避免需要对NULL、’’（空字符串）进行判断和识别。Hive默认情况下底层存储空值跟其他传统数据库有所不同。

1、hive默认存储空值的规则
（1） 不同数据类型对空值的存储规则
Int与String类型，NULL底层默认存储为\N，查询时显示为NULL。String类型的数据如果为”“，存储则是”“，另外往Int类型的字段插入数据””时，结果还是\N。

（2） 不同数据类型空值的查询
对于Int类型可以使用is null来判断空值，对于string类型，条件is null 查出来的是\N的数据，而条件 =”，查询出来的是”“的数据。

2、修改hive底层存储和标识空值的方式
建表时可以通过NULL DEFINED AS定义空值的编码方式，对于已经创建的表，可以通过alter table table_name SET SERDEPROPERTIES(‘serialization.null.format’ = ’ \N’)语句修改hive底层存储空值的方式。

假设hdfs上有以下5行数据的文件：
1|NULL|100
2|null|200
3|\N|300
4||400
5|five|500

现使用以下建表语句创建指向该文件的外部表：

CREATE EXTERNAL TABLE null_test(
c1 int,
c2 string,
c3 int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/dianshang/asiainfo/null_test';
 
 

 
 
1
2
3
4
5
6
7
8

（1） 默认情况下hive认为底层数据为\N的数据才是空值，查询结果用NULL表示，通过is null条件查询出来的是第三条记录：

（2） 如果希望hive底层用空字符’’存储空值，可以在建表时通过NULL DEFINED AS指定或者修改表的属性值serialization.null.format。
建表时指定空值的编码方式：

CREATE EXTERNAL TABLE null_test(
c1 int,
c2 string,
c3 int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
NULL DEFINED AS ''
LOCATION '/dianshang/asiainfo/null_test';
 
 

 
 
1
2
3
4
5
6
7
8
9

修改表的属性serialization.null.format改变空值的编码方式：
alter table null_test SET SERDEPROPERTIES(‘serialization.null.format’ = ”);
此时通过is null查询条件查出的结果是第4条数据：

同样可以修改hive底层存储空值的编码方式为字符串”null”，此时通过is null查询条件查出的结果是第2条数据：

3、使用sqoop把hive表数据导到oracle时因空值报错问题。
如果hive底层空值存储是默认的\N，导入oracle时会报以下错误：
Caused by: java.lang.RuntimeException: Can’t parse input data: ‘\N’，这时可以通过以下方法解决：
（1） 在sqoop导出语句增加–input-null-string “\\N” –input-null-non-string “\\N” 参数，把\N转化成空字符’’。
（2） 建表时通过NULL DEFINED AS ”声明hive底层使用空字符存储和标识空值。
（3） 通过alter table table_name SET SERDEPROPERTIES(‘serialization.null.format’ = ”) 语句修改hive底层存储和标识空值为空字符’’，但是修改后需要重跑数据。

综上所述，需要与外部进行数据交互的表，建表时建议使用NULL DEFINED AS ”声明hive底层使用空字符’’存储和标识空值，减少一些不必要的错误。在查询语句中，判断字段c为空值的条件建议写成 c is null or length(c)=0 或者c is null or c=’’。