Handling null values on hive

Question

I have a column in hive with type double, but some of the rows are NULL when I do:

select columnA from table;

now, if I run the following, I get 0 for both queries:

select count(*) from table where columnA = "NULL";
select count(*) from table where columnA = NULL;

how can I count the rows in my table which are NULL?

Answer 1

The correct query is:

select count(*) from table where columnA is null;

Answer 2

In Hive, count(*) counts all rows and count(columnA) will only count rows where columnA is non-NULL. If you would like to do multiple columns you could write the query as:

select count(*)-count(columnA), count(*)-count(columnB) from table;

and get the number of null values in each column. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF