hdfsnanclouderaimpala

How to count NaN items in Impala query?

I have a table that has 'NaN' in a field that is a double. I simply want to count how many items are 'NaN':

Select count(*) from table
where col = 'NaN'

AnalysisException: operands of type DOUBLE and STRING are not comparable: col = 'NaN' 

Select count(*) from table
where col is null

Result = 0 (there are tons of NaN records in this column btw)

Select count(*) from table
where cast(col as string) = 'NaN'

Result = 0

How do I do this where it will actually count the NaN rows?

Solution

  • I would cast the NaNs into string and then do the comparison with 'nan'

    Select count(*) from table
where cast(col as string) = 'nan'