hive：null 和 '' , 'NULL' - Hive

TOP

hive：null 和 '' , 'NULL'

2019-02-12 01:00:12 【大中小】浏览:50次

说下hive中的null
employee表

hive>desc employee;
empid string
deptid string
salary string

查询employee

hive>select * from employee
1 NULL NULL

hive 中null实际在HDFS中默认存储为'\N'
即employee中的数据在HDFS中为
1 \N \N

验证，插入'\N'

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

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

查询employee

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

此时hive中与null有关的函数,如nvl,coalesce,is null等判断是否为null是为true

hive>select nvl(empid,'A'),nvl(deptid,'B'),nvl(salary,'C') from employee
1 B C
2 B C

扩展：

NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。

NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

---------------------

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

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

查询：

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

-------------------------

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

查询

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

注意：1,2同一行的NULL与4行的NULL或null不一样。4行的NULL或null为字符串

---------------------------

此时hive中与null有关的函数,如nvl,coalesce,is null等判断''和null（字符串）或NULL（字符串）是否为null是为false

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

hive>select * from employee where deptid='';
3

hive>select * from employee where deptid='null' and salary ='NULL';
4 null NULL

hive>select * from employee where deptid is null;
1 NULL NULL
2 NULL NULL

可以通过

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

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

ALTER TABLE employee SET SERDEPROPERTIES('serialization.null.format' = '');

查询employee

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

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

验证，将''插入到emloyee

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

查询

hive>select * from employee;
1 \N \N
2 \N \N
3 NULL NULL
4 null NULL
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等语句。