CODEWITHSUNDEEP
sqlhivenullhiveql
Manjeet Suman
Manjeet Suman

Reputation: 95

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

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?

Upvotes: 3

Views: 1896

Answers (1)

leftjoin
leftjoin

Reputation: 38335

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

Upvotes: 3

Related Questions