Hive2.3.7中对手动插入null的测试

1.create table testNull(id int) row format delimited fields terminated by ‘,’;
2.insert into testNull(id) values(null);
3.插入的null，查询出NULL

hive> select * from testNull;
OK
NULL

4.看看单元格是否存储的字符串，或者空指针null、NULL（就是看空指针null是否能存为null）

hive> select * from testNull where id=null;
OK
Time taken: 0.472 seconds
hive> select * from testNull where id=NULL;
OK
Time taken: 0.092 seconds
hive> select * from testNull where id='NULL';
OK
Time taken: 0.11 seconds
hive> select * from testNull where id='null';
OK
Time taken: 0.093 seconds

5.还是要用is null来查询，说明就是空，没有存储为字符串或者空指针null，null在hive里存储为\N

hive> select * from testNull where id is null;
OK
NULL
Time taken: 0.093 seconds, Fetched: 1 row(s)
hive> select * from testNull where id is NULL;
OK
NULL
Time taken: 0.095 seconds, Fetched: 1 row(s)

6.看看count对手动插入的null是否会计数，即count(col)的时候是否对null不计数（不计数）

hive> insert into testNull(id)  values(null),(null);
hive> select * from testNull;
OK
NULL
NULL
NULL
Time taken: 0.127 seconds, Fetched: 3 row(s)
hive> select count(id) from testNull;
OK
0

7.看看distinct对手动插入的null是否有用，即distinct col的时候是否对null能去重（能去重）

hive> select * from testNull;
OK
NULL
NULL
NULL
Time taken: 0.08 seconds, Fetched: 3 row(s)
hive> select distinct id from testNull;
OK
NULL