Hive空值处理

浅浅的花香味﹌ 2022-06-03 06:21 420阅读 0赞

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） 默认情况下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';

修改表的属性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=’’。