hive中的空值处理

hive中的空值处理
用Sqoop往MySQL同步数据时,由于hdfs存储的数据中有null值或’’,导致同步数据失败。主要是hive sql中的空值造成的,所以今天就说一下怎么解决这个问题。
一.hive中空值分两种
(1)NULL
hive中null实际在HDFS中默认存储为’\N’,通过查询显示的是’NULL’。
这时如果查询为空值的字段可通过语句:aaa is null 或者 aaa =’\N’ 实现。
此时可用hive中与null有关的函数,如nvl,coalesce,is null等判断是否为null是为true。产生NULL值,一般都是由hive外链接引起的。

(2)’'
‘’ 表示的是字段不为null且为空字符串,此时用 aaa is null 是无法查询这种值的,必须通过 aaa ==’’
或者 length(aaa)=0 查询,产生’‘值,一般都是源数据为空。
在hive sql里面已经加为null判断了。再看数据文件,发现其中的值不是通常的NULL,或者是\N,而是’’。
所以得到原因,用判断null的nvl函数并不能排除’'的情况,需要用IF(aaa == ‘’,‘未知’,aaa) AS aaa 来判断。

二.hive中空值NULL的处理方法
1.在hive中使用COALESCE进行空值处理
COALESCE是一个函数, (expression_1, expression_2, …,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。

比如我们要登记用户的电话,数据库中包含他的person_tel,home_tel,office_tel,我们只要取一个非空的就可以,则我们可以写查询语句

select COALESCE(person_tel,home_tel,office_tel) as contact_number from Contact;

select coalesce(a,b,c);
参数说明: 如果anull,则选择b;如果bnull,则选择c;
如果a!=null,则选择a;如果a b c 都为null ,则返回为null

使用实例:
这个参数使用的场合为:假如某个字段默认是null,你想其返回的不是null,而是比如0或其他值,可以使用这个函数

SELECT COALESCE(field_name,0) as value from table;

2.在hive中使用nvl进行空值处理
a.NVL函数
NVL函数的格式如下:NVL(expr1,expr2)
含义是:如果oracle第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。

例如:
SQL> select ename,NVL(comm, -1) from emp;
 
ENAME NVL(COMM,-1)
------- ----
SMITH -1
ALLEN 300
WARD 500
JONES -1
MARTIN 1400
BLAKE -1
FORD -1
MILLER -1
其中显示-1的本来的值全部都是空值的

b.NVL2函数
NVL2函数的格式如下:NVL2(expr1,expr2, expr3)
含义是:如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第三个参数的值。

例如:
SQL> select ename,NVL2(comm,-1,1) from emp;

ENAME NVL2(COMM,-1,1)
------- -----
SMITH 1
ALLEN -1
WARD -1
JONES 1
MARTIN -1
BLAKE 1
CLARK 1
SCOTT 1
上面的例子中。凡是结果是1的原来都不为空,而结果是-1的原来的值就是空。

备注: 适用于数字型、字符型和日期型,但是 expr1和expr2的数据类型必须为同类型

3.在hive中灵活运用 is null 和 is not null 进行null值过滤
Hive中有种假的NULL,它看起来和NULL一摸一样,但是实际却不是NULL。空值NULL在底层默认是用’\N’来存储的,而我们习惯性对NULL 值的过滤,一般是is null 和 is not null。所以此时可以通过对修改hive 表的信息,来保证控制都显示为指定值 ,方便进行过滤。解决方法如下:
(1)如下:
alter table t_dw_ct_author set serdeproperties (‘serialization.null.format’ = ‘NULL’);
则出现:底层数据保存的是’NULL’,通过查询显示的是’NULL’
这时如果查询为空值的字段可通过 语句:a is null 或者 a=‘NULL’
或者
alter table t_dw_ct_author set serdeproperties(‘serialization.null.format’ = ‘\N’);
则出现:底层数据保存的是’\N’,通过查询显示的是’NULL’
这时如果查询为空值的字段可通过 语句:a is null 或者 a=’\N’
(2)’’ 与 length(xx)=0
‘’ 表示的是字段不为null且为空字符串,此时用 a is null 是无法查询这种值的,必须通过 a=’’ 或者 length(a)=0 查询

三.hive中空值’'的处理方法
1.IF(aaa == ‘’,‘未知’,aaa) 的用法
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull

**实例:**if(条件表达式,结果1,结果2)相当于java中的三目运算符,只是if后面的表达式类型可以不一样。

hive> select if(aaa == '','未知',aaa) fromlxw_dual;
    未知
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值