深入解析hive中的NULL(空值)和''(空字符串)

一、hive里面的 null(空值) 和 ''(空字符串) 是两个不同的东西

1、如果查某个字段【 a 是否为空】应该这样写:

a is null

a is not null

2、如果查某个字段 【a 是否为''】应该这样写:

a = ''

a <>''

在hive的数据中,null值(空值)较为常见,比如字段没有值,就是所谓的null值(空值),而 ''(空字符串)多为人为给定的,不多见。

二、【表】NULL值的处理

HIVE表中默认将NULL存为\N,可查看表的源文件(hadoop fs -cat或者hadoop fs -text),文件中存储大量\N,  
这样造成浪费大量空间。而且用java、python直接进入路径操作源数据时,解析也要注意。

另外,hive表的源文件中,默认列分隔符为\001(SOH),行分隔符为\n(目前只支持\n,别的不能用,所以定义时不需要显示声明)。元素间分隔符\002,map中key和value的分隔符为\003

1、举例,如源文件中一条记录为:

10000042SOH77SOH435SOH16SOH22SOH1156120000SOH\NSOH\NSOH\NSOH\NSOH\NSOH\NSOH\NSOHyoukuSOH85133.0SOH111

可以看出存储NULL的\N 浪费了大量空间。

但hive的NULL有时候是必须的:
1)、hive中insert语句必须列数匹配,不支持不写入,没有值的列必须使用null占位。

2)、hive表的数据文件中按分隔符区分各个列。空列会保存NULL(\n)来保留列位置。但外部表加载某些数据时如果列不够,如表13列,文件数据只有2列,则在表查询时表中的末尾剩余列无数据对应,自动显示为NULL。

所以,NULL转化为空字符串,可以节省磁盘空间,实现方法有几种:
    1)建表时直接指定(两种方式):
        a、用语句

ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
    with serdeproperties('serialization.null.format' = '')

     实现,注意两者必须一起使用,如

   CREATE TABLE hive_tb (id int,name STRING)
   PARTITIONED BY ( `day` string,`type` tinyint COMMENT '0 as bid, 1 as win, 2 as ck', `hour` tinyint)
   ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
   WITH SERDEPROPERTIES (
        ‘field.delim’='/t’,
        ‘escape.delim’='//’,
        ‘serialization.null.format'=''
   ) STORED AS TEXTFILE;

         b、或者通过ROW FORMAT DELIMITED NULL DEFINED AS '' 如

   CREATE TABLE hive_tb (id int,name STRING)
   PARTITIONED BY ( `day` string,`type` tinyint COMMENT '0 as bid, 1 as win, 2 as ck', `hour` tinyint)
   ROW FORMAT DELIMITED 
        NULL DEFINED AS '' 
   STORED AS TEXTFILE;

          2)修改已存在的表:

    alter table hive_tb set serdeproperties('serialization.null.format' = '');
节省空间的验证结果如下:
    hadoop fs -du /hivedata/warehouse/pmp.db/hive_tb/day=2016-05-14/type=1/hour=00/0*
    1137
    hadoop fs -du /hivedata/warehouse/pmp.db/hive_tb/day=2016-05-14/type=1/hour=01/0*
    319753
    -----------------------------------
    hadoop fs -du /hivedata/warehouse/pmp.db/hive_tb/day=2016-05-14/type=1/hour=00/0*
    885
    hadoop fs -du /hivedata/warehouse/pmp.db/hive_tb/day=2016-05-14/type=1/hour=01/0*
    249529

三、【字段】null值处理——NVL、Coalesce函数

1、NVL函数

NVL函数的格式如下:NVL(expr1,expr2)
含义是:如果第一个参数为null(空值)那么显示第二个参数的值,如果第一个参数的值不为null(空值),则显示第一个参数本来的值。

select nvl(null,3);    -- 结果:3

select nvl('',3);      -- 结果:''   (注意:在hive中展示位1,但实际为'',可用select nvl('',3)='';验证)

2、Coalesce函数

Coalese函数格式如下:Coalesce(expr1, expr2, expr3….. exprn)

Coalese函数的作用和NVL函数有点相似,其优势是有更多的选项
表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型

作用是:返回表达式中第一个非空表达式。

SELECT COALESCE(NULL,null,3,4,5);   -- 结果为:3

SELECT COALESCE(NULL,null,'',3,4,5);   -- 结果为:''

SELECT COALESCE(NULL,null,null,NULL);  -- 结果为:null

如果所有自变量均为 NULL,则 COALESCE 返回 NULL

©️2020 CSDN 皮肤主题: 像素格子 设计师:CSDN官方博客 返回首页