How to get the list of all columns having NULL values only in hive?

Question

I am working on a huge data set having more than 10k rows and more than 600 columns in Hive. There are multiple rows columns which having NULL value for all the rows. How can I get the list of all the columns having only NULL values?

Answer 1

Use count(col) to count all NOT NULL rows for some column.

Columns with all NULLs will have 0 counts:

select
count(col1) as col1_cnt,
count(col2) as col2_cnt,
...
count(colN) as colN_cnt

from table