Hive中的NULL问题

Hive中有种假NULL，它看起来和NULL一摸一样，但是实际却不是NULL。

空值NULL在底层默认是用'\N'来存储的，可以通过serialization.null.format的设置或创建表时指定结构来修改表的默认的null表示方式。

具体内容请看如下例：

测试表 sunwg00

hive> select * from sunwg00;
OK
NULL    mary
101     tom
Time taken: 0.058 seconds

创建普通表sunwg01,没有指定serialization.null.format
hive> CREATE TABLE sunwg01 (id int,name STRING) STORED AS TEXTFILE;
OK
Time taken: 0.04 seconds
hive> insert overwrite table sunwg01 select * from sunwg00;
Loading data to table sunwg01
2 Rows loaded to sunwg01
OK
Time taken: 17.047 seconds

查看sunwg01在hdfs的文件
[hjl@sunwg src]$ hadoop fs -cat /hjl/sunwg01/attempt_201105020924_0011_m_000000_0
/Nmary
101tom

NULL值被转写成’/N’

创建表sunwg02，指定serialization.null.format

hive> CREATE TABLE sunwg02 (id int,name STRING)
    > ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
> WITH SERDEPROPERTIES (
    >  ‘field.delim’='/t’,
    >  ‘escape.delim’='//’,
    >  ‘serialization.null.format’=”
> ) STORED AS TEXTFILE;
OK
Time taken: 0.046 seconds

hive> insert overwrite table sunwg02 select * from sunwg00;
Loading data to table sunwg02
2 Rows loaded to sunwg02
OK
Time taken: 18.756 seconds

查看sunwg02在hdfs的文件
[hjl@sunwg src]$ hadoop fs -cat /hjl/sunwg02/attempt_201105020924_0013_m_000000_0
        mary
101     tom

NULL值没有被转写成’/N’，而是空字符串。

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

再举个例子如下：

例如如下这个查询：
hive> desc Test;
OK
a       string
Time taken: 0.237 seconds
 
hive> select a from Test;
OK
NULL
Time taken: 46.232 seconds
 
看上去好像Test的a字段保存了一个 NULL， 但是换一个查询会发现它和NULL并不一样：
hive> select a from Test where a is null;
OK
Time taken: 62.56 seconds
 
来看一下实际存储的是什么：
hive> select * from Test;
OK
\N
Time taken: 1.232 seconds
 
hive> select a from Test where a = '\\N';
OK
NULL
Time taken: 72.933 seconds
 
Test的a字段实际存储的是一个'\N'，a = '\\N'是因为Hive中'\'是转义字符，需要对'\'进行一次转义，所以变成'\\N'。
 
这种假NULL产生的原因实际上源于对表的错误操作。在Hive中，空值NULL在底层默认是用'\N'来存储的，看一个例子：
hive> create table Test2 (col1 string);
OK
Time taken: 1.258 seconds
1 Rows loaded to Test2
OK
Time taken: 63.727 seconds
 
hive> insert overwrite table Test2 select NULL from dual;
 
然后看一下底层的数据存储：
$ hadoop fs -cat /group/hive/Test2/attempt_201205041518_256192_m_000000_0
\N
 
可以看到底层数据将NULL存储成了'\N' 。
这样的设计存在一个问题是如果实际想存储'\N'，那么实际查询出来的也是NULL而不是'\N' 。
Hive给出一种并非完美的解决方法就是可以自定义底层用什么字符来表示NULL。
例如我想用字符'a'来表示NULL：
hive> alter table Test2 SET SERDEPROPERTIES('serialization.null.format' = 'a');
OK
Time taken: 0.175 seconds
 
hive> insert overwrite table Test2 select NULL from dual;
1 Rows loaded to Test2
OK
Time taken: 62.66 seconds
 
再看一下底层的存储：
$ hadoop fs -cat /group/hive/Test2/attempt_201205041518_256764_m_000000_0
a
 
这时候底层的存储就变成了'a' ，今后插入到这张表中的'a'查询出来就变成了NULL而不是'a' 。

===============================================================================================

下面来说明一下Hive的使用中不可避免的 NULL和 ‘‘ 的判断识别。

1. 数据类型（int与string的存储）

null默认的存储都是\N。

 string的数据如果为""。存储才是""。

int类型的字段插入数据“”.结果还是\N

[hadoop@nn1 ~]$ hadoop fs -cat /user/hive/warehouse/aaa.db/dual2/*
Warning: $HADOOP_HOME is deprecated.
1aaa
\Nbbb
3\N
3
4
 
2. 查询。

   对于int就是可以使用is NULL。
   对于string类型 is NULL查出来的是\N的数据；对于条件 =''，查询出来的数据是""的。

例：

select b.id,b.name,a.id
from
dual2 b
left outer join 
dual a
on (a.id=b.id)
where b.name ='';
结果：
3               NULL
4               NULL

select b.id,b.name,a.id
from
dual2 b
left outer join 
dual a
on (a.id=b.id)
where b.name is NULL;
结果：
3       NULL    NULL
 
因此，在开发过程中如果需要对空进行判断，一定得知道存储的是哪种数据。