示例:
建表:
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__”。