Skip to content
Advertisement

Hive NVL does not work with Date type of the column – NullpointerException

I am using MapR Hive distribution over HDFS and facing below issue. If for a table the column type is ‘Date’ type, then the NVL function does not work. The same is working for other datatype.

It simply throws 

NullpointerException:Null

Even explain function is throwing same exception.

Kindly help here. Is it a bug in Hive distribution?

Advertisement

Answer

I solved the problem myself with a workaround:

For Date type, you could use hive COALESCE function as below:

COALESCE(nt.assess_dt, cast('9999-01-01' as date))

The above answer can be explain as:

  • Find nt.assess_dt (which is a date type column); if it is null get the next value in the COALESCE function; which is a non-null value in above example and hence will be returned back.

Please note that it is a little different than NVL, where the value returned by the COALESCE needs to be of same type. Hence a blank '' can not be returned by COALESCE in the above example.

Due to this, I have used a very large date value 9999-01-01 to represent a null value and distinguish between a genuine date value. In case your date column can have indeed this large value as a valid value, you should think of some other valid date value to represent a null date.

Get more about this and other alternative here

Advertisement