Hive NVL does not work with Date type of the colum

Question

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?

Answer 1

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

Answer 2

One can also use the originally requested NVL, as in

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