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