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;
    未知