Hive Tutorial - 25 : Handling null values in Hive | isnull(), isnotnull(), nvl() in hive
This tutorial talks about the hive functions - isnull() & isnotnull() and how to use them.
For this tutorial below table is used. You can create the similar table and load the data using below commands:
1. Table creation:
CREATE TABLE IF NOT EXISTS employee (
id int,
name string,
age int,
gender string,
salary decimal
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
2. Create data file:
1,Santosh,30,M,10000
2,Vaibhav,40,F,20000
3,Rohan,41,M,30000
4,Akshay,20,F,40000
5,Sam,30,M,
7,Ana,,,
4. Queries :
-- select name, isnull(age) from employee;
-- select name, isnotnull(age) from employee;
-- select name from employee where isnull(salary);
-- select name from employee where isnotnull(salary);
-- select name, nvl(age,-99) from employee;
-- select name, nvl(salary,-99999) from employee;