Hive-cleaningNulls/Handling null

cleaning null values from a hive table
-------------------------------------------
[cloudera@quickstart ~]$ cat nulldata.txt
1,,20
20,30,
,50,60

syntax
-----
null check
---------
colname is null

not null check
---------------
colname is not null


hive>create table nulldata(a int,b int,c int)
     row format delimited fields terminated by ',';

hive>load data  local inpath 'nulldata.txt' into table nulldata;

hive>select *from nulldata;

hive>
insert overwrite table nulldata
select if(a is null,0,a),if(b is null,0,b),if(c is null,0,c) from nulldata;

or

insert overwrite table nulldata
select nvl(a,0),nvl(b,0),nvl(c,0) from nulldata;

Note:
nvl()---It is a method used to check null.

hive> select name from empdata where name like '%A'.

Task:
in gender col 'm' to be transformed to male
and f to be transformed to female.

dno
11-->Marketing
12-->HR
13-->finance

hive> select if(gender='m','male','female'),if(dno=11,'marketing',if(dno=12,'hr',if(dno=13,'finance',dno))) from empdata;