hive中的null处理方式

在处理流水增量表的时候，出现了一个判定的失误。

select a.a1,a.a2
　　from
　　　　(
　　　　select
　　　　　　a.a1
　　　　　 ,if(a.a2<>b.b2,1,0) as diff
　　　　　,a.a2
　　　　from a
　　　　lefter join b
　　　　on a.a1=b.b1
　　　　) c
where c.diff=1;

因为左外关联，可能会出现b表数据不存在 则b.b2 is null , if(a.a2<>b.b2,1,0) as diff,null值的判断只能使用is ,is not

0: jdbc:hive2://localhost:10000/big12> select if(1 <>null,0,1);
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+
1 row selected (0.13 seconds)
0: jdbc:hive2://localhost:10000/big12> select 1<>null;
+-------+--+
|  _c0  |
+-------+--+
| NULL  |
+-------+--+
1 row selected (0.127 seconds)

所以处理方式：

0: jdbc:hive2://localhost:10000/big12> select if(1<>null or null is null,1,0) as diff;
+-------+--+
| diff  |
+-------+--+
| 1     |
+-------+--+
1 row selected (0.121 seconds)
0: jdbc:hive2://localhost:10000/big12> select if(1<>nvl(null,0),1,0) as diff;
+-------+--+
| diff  |
+-------+--+
| 1     |
+-------+--+
1 row selected (0.13 seconds)

其他：

employee表

hive>desc employee;
empid   string
deptid   string
salary   string

查询employee

hive>select * from employee
 NULL NULL

hive 中null实际在HDFS中默认存储为'\N'(但是我们一般为了安全性把null的储存格式调整为'')
即employee中的数据在HDFS中为

1 \N \N
验证，插入'\N'

hive>insert into table employee select '2','\\N','\\N' from employee limit 1;

其中多一个斜杠是转义的作用
查询employee

其中多一个斜杠是转义的作用
查询employee

1 hive>select * from employee
2 1 NULL NULL
3 2 NULL NULL

但是null或NULL和''会被hive当做字符串处理。

1 hive>insert into table employee select '3','','' from employee limit 1;

查询：

1 hive>select * from employee;
2 1 NULL NULL
3 2 NULL NULL
4 3   

1 hive>insert into table employee select '4','null','NULL' from employee limit 1;

查询

1 hive>select * from employee;
2 1 NULL NULL
3 2 NULL NULL
4 3    
5 4 null NULL

注意：1,2同一行的NULL与4行的NULL或null不一样。4行的NULL或null为字符串
此时hive中与null有关的函数,如nvl,coalesce,is null等判断''和null（字符串）或NULL（字符串）是否为null是为false

1 hive> select empid ,nvl(deptid,'E'),nvl(salary,'F') from employee;
2 1 E F
3 2 E F
4 3    
5 4 null NULL

1 hive>select * from employee where deptid='';
2 3    
3 hive>select * from employee where deptid='null' and salary ='NULL';
4 4 null NULL
5 hive>select * from employee where deptid is null;
6 1 NULL NULL
7 2 NULL NULL

可以通过

1 LTER TABLE table_name SET SERDEPROPERTIES('serialization.null.format' = 定义描述符);

修改空值描述符
如果将''定义为NULL

查询employee

1 hive>select * from employee;
2 1 \N \N
3 2 \N \N
4 3 NULL NULL
5 4 null NULL

和前面的select比较发现''变成了NULL，而\N露出了真面目，4行的NULL或null为字符串没变化
验证，将''插入到emloyee

1 hive> insert into table employee select '5','','' from employee limit 1;

查询

1 hive>select * from employee;
2 1 \N \N
3 2 \N \N
4 3 NULL NULL
5 4 null NULL
6 5 NULL NULL

注意：３,５同一行的NULL与4行的NULL或null不一样。4行的NULL或null为字符串
此时HDFS中如此存储

1 \N \N
2 \N \N
3  
4 null NULL
5  

此时

hive> select empid ,nvl(deptid,'E'),nvl(salary,'F') from employee;
1 \N \N
2 \N \N
3 E F
4 null NULL
5 E F

总结hive中null的定义的意义在于：oracle数据导出后原表中的null会变成''，然后导入到hive中也变成了''。但是hive中关于NULL的一些函数如nvl,coalesce和is null却无法使用，因为hive默认\N才代表NULL。在hive中通过
ALTER TABLE SET SERDEPROPERTIES('serialization.null.format' = '');修改''代表NULL，改造存储过程中就不需要改nvl等语句。

参考：

https://www.cnblogs.com/w-j-q/p/15024530.htmlhttps://www.cnblogs.com/w-j-q/p/15024530.html