HIVE NULL 值

hive中NULL默认是以 '\N' 表示的
示例：
建表：
hive > create table null_test_1(id INT, name STRING) 
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.275 seconds
插入数据：
$ cat /home/work/data/null_test.txt 
1       2
1        \N
1       3
hive>  load data local inpath '/home/work/data/null_test.txt' into  table null_test_1;
Copying data from file:/home/work/data/null_test.txt
Copying file: file:/home/work/data/null_test.txt
Loading data to table default.null_test_1
OK
Time taken: 0.384 seconds
查看数据：
hive>  select * from null_test_1 ;
OK
1       2
1       NULL
1       3
hive>  select * from null_test_1 where name is not null;
Total MapReduce jobs = 1
......
OK
1       2
1       3
Time taken: 32.729 seconds

可以通过ALTER TABLE table_name SET SERDEPROPERTIES('serialization.null.format' = '');修改空值描述符
示例：
hive>  ALTER TABLE null_test_1 SET SERDEPROPERTIES('serialization.null.format' = '');
OK
Time taken: 0.137 seconds
hive>  select * from null_test_1 where name is not null;
Total MapReduce jobs = 1
......
OK
1       2
1       \N
1       3
Time taken: 21.754 seconds

alter table tbl_num_10 set serdeproperties('serialization.null.format'='NULL');

select * from tbl_num_10 where object_name is null;                            
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201411191750_0023, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201411191750_0023
Kill Command = /home/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job  -kill job_201411191750_0023
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-11-20 11:31:08,818 Stage-1 map = 0%,  reduce = 0%
2014-11-20 11:31:12,851 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.57 sec
2014-11-20 11:31:13,859 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.57 sec
MapReduce Total cumulative CPU time: 2 seconds 570 msec
Ended Job = job_201411191750_0023
MapReduce Jobs Launched: 
Job 0: Map: 1   Cumulative CPU: 2.57 sec   HDFS Read: 627 HDFS Write: 30 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 570 msec
OK
13792 NULL 2013-09-01 14:41:47
Time taken: 10.772 seconds, Fetched: 1 row(s)

hive中分区字段如果为NULL需注意

使用dynamic partition，如果分区字段为NULL，数据会放到一个特殊的分区，这个分区由参数“hive.exec.default.partition.name”控制，默认为“__HIVE_DEFAULT_PARTITION__”。