前言
之前由于对Hive底层的对于NULL和’'了解的不透彻,使用的时候出现了一些问题,今天闲来无事就整理一下
简单的测试案例
测试数据:
1,zs,23
2,NULL,24
3,,
4, ww,25
5,zl ,26
6,\N,27
7, \N,\N
8,姓名,28
9, ,年龄
10, , \N
创建Hive表:
create table student(
id int,
name string,
age int
)
row format delimited fields terminated by ','
stored as textfile
location '/user/hive/warehouse/student';
加载数据:
load data local inpath '/data/test.txt' into table default.student;
查看结果:
hive> select * from student;
OK
1 zs 23
2 NULL 24
3 NULL
4 ww 25
5 zl 26
6 NULL 27
7 \N NULL
8 姓名 28
9 NULL
10 NULL
上面究竟做了什么?
首先测试数据使用load加载进去也就是hive数据在HDFS的存储形式,着重讲解几个特殊的行:
- 第2行,name字段底层存储了一个NULL,那么在查询的结果是以NULL显示
- 第3行,name字段底层什么都没有,查询出来就是空白
- 第6行,name字段底层存储了一个\N,那么查询的结果是以NULL显示
- 第7行,name字段底层存储了两个空格加一个\N,那么查询的结果就是两个空格加一个\N
- 第9行,name字段底层存储了一个空格,age字段写为汉字,那么查询的结果name就是一个空格,而age以NULL显示
- 第10行,name字段底层存储了一个tab,age字段底层存储了一个\N,那么查询的结果name就是一个tab,age以NULL显示
总结
在对string类型的字段进行查询过滤空值(这里包括NULL和’’)的时候我们一般以:
select name from student where name is not null and name != '';
在对int类型(包括其他数值类型)的字段进行查询过滤空值(这里包括NULL和’’)的时候我们一般以:
select age from student where age is not null;
看起来string类型比int类型只不过多了一个:!=’’ 的判断而已,但是真是的情况是,就像上面第7、9、10行的name字段一样,我们很多时候根本不知道整个字段究竟有多少空格存在,因此SQL为了应对于此类的问题引入了trim函数
trim函数
语法:
STRING trim(xxx) --将字符串xxx前后的空格全部去除掉,例如trim(name),返回值类型是STRING
注意:
trim函数仅仅处理的空格,并不处理NULL,因此我们一般对string类型处理的时候如下:
select trim(name) from student where name is not null;
现在看起来string空值的问题已经解决了,但是还有一个疑问:为什么int无论什么情况,只要不是int类型,底层都以\N存储,查询的时候都显示NULL呢?整个就不得不说一下Hive的读时模式了
读时模式
传统的数据库是写时模式(schema on write),即数据在写入数据库时对模式进行检查。
Hive对底层存储并没有这样的控制。Hive不会在数据加载时进行验证,而是在查询时进行,也就是读时模式。
那么如果模式和文件内容不匹配将会怎么样呢?
Hive对此做的非常好,因为其可以读取这些数据。如果每行记录汇总字段个数少于对应模式中定义的字段个数的话,那么用户将会看到查询结果中有很多null值。如果某些字段是数值型,但Hive在读取时发现存在非数值型的字符串值的话,那么对于那些字段将会返回null值。除此之外的情况,Hive都极力尝试尽可能将各种错误恢复过来。
我们这里不去议论究竟写时模式和读时模式哪个更好的问题,反正Hive这种读时模式注定会导致HDFS上的会出现一堆的\N数据,那么这样的数据所占的内存和’'比起来究竟怎么样呢?这里我们可以简单用Hive自带的一个函数lengh来进行统计
length函数
语法:
INT length(xxx) -- 计算字符xxx的长度
- 对int类型的age进行长度统计
select length(age) from student;
结果:
2
2
NULL
2
2
2
NULL
2
NULL
NULL
结果稍稍有点意外,明明语法上面写的返回INT,为什么会出现一堆的NULL呢?
对于INT类型处理只要不是数值统统以NULL给你显示
- 对string类型的name进行长度统计
select length(name) from student;
结果:
2
4
0
4
5
NULL
4
2
1
1
由于字符串的特殊性,这里专门做了个表格:
可以看到处理底层以\N存储,会查询出NULL外,其他的都有长度,原因很好理解:
就是length函数统计的就是字符的长度
说到这里简单的普及的一下字符和字节的区别:
字符与字节它们完全不是一个位面的概念,所以两者之间没有“区别”这一说法。在不同编码里,字符和字节的对应关系是不同的。一般来说,半角英文状态下一个字母或数字(称之为字符)占用一个字节,一个汉字用两个字节表示。在不同的编码方式下一个字符占的字节数是不同的,所以两者是不能划等号的。
1个字节(Byte)等于8个bit位,每个bit位是0/1两种状态,也就是说一个字节可以表示256个状态,计算机里用字节来作为最基本的存储单位。
字符,在计算机和电信技术中,一个字符是一个单位的字形、类字形单位或符号的基本信息。
字符是指计算机中使用的字母、数字、字和符号,包括:1、2、3、A、B、C、~!·#¥%……—*()——+等等。
①ASCII码中,一个英文字母(不分大小写)占一个字节的空间,一个中文汉字占两个字节的空间。一个二进制数字序列,在计算机中作为一个数字单元,一般为8位二进制数,换算为十进制。最小值0,最大值255。
②UTF-8编码中,一个英文字符等于一个字节,一个中文(含繁体)等于三个字节。
③Unicode编码中,一个英文等于两个字节,一个中文(含繁体)等于两个字节。
文本符号:英文标点占一个字节,中文标点占两个字节。举例:英文句号“.”占1个字节的大小,中文句号“。”占2个字节的大小。
④UTF-16编码中,一个英文字母字符或一个汉字字符存储都需要2个字节(Unicode扩展区的一些汉字存储需要4个字节)。
⑤UTF-32编码中,世界上任何字符的存储都需要4个字节。
因此,这里一定要注意,对字符串使用length函数时一定要记得使用trim函数去掉字段两边的空值
select length(trim(name)) from student;
结果:
2
4
0
2
2
NULL
2
2
回归正题,我们的重点放在第3行和第6行,‘’的长度为0,而\N长度为NULL,看起来像是0更节省空间,为了进一步确定,我们可以把底层的\N替换成‘’进行测试,如何替换呢?其实Hive的研发人员早就想到了,他们把错误数据全部以\N来存储,肯定有人不乐意,那么好,我给你们接口,你们想以什么存储你们随便,下面且看如何更改
如何将底层NULL的存储格式(我们这里测试将\N修改为‘’)
创建表时更改:
- 方式一:
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
with serdeproperties('serialization.null.format' = '')
经常用Hive的知道,这种方式是很早的一种写法,现在最新的Hive中都是以下面的形式进行更改
- 方式二:
ROW FORMAT DELIMITED NULL DEFINED AS ''
例如:
create table student2(
id int,
name string,
age int
)
row format delimited fields terminated by ','
NULL DEFINED AS '' --只要加这一句就可以
stored as textfile
location '/user/hive/warehouse/student';
修改已存在的表:
alter table student set serdeproperties('serialization.null.format' = '');
将NULL改为’'以后,我们查询一下结果:
1 zs 23
2 NULL 24
3 NULL NULL
4 ww 25
5 zl 26
6 \N 27
7 \N NULL
8 姓名 28
9 NULL
10 NULL
可以看到本来第3行底层本来为\N现在改为‘’后直接查询的出来的就是NULL了,而不再是空
当然了,这里的测试数据太少,里边只有一个字符是以’‘存储的无法统计出修改的后的差别,我在生产环境中统计过,’'占用的空间明显要小于\N,所以在生产环境中可以这样操作
后记
看完本篇博客相信Hive关于空值的问题都已经得到解决