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
Even explain function is throwing same exception.
Kindly help here. Is it a bug in Hive distribution?
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:
nt.assess_dt(which is a
datetype column); if it is
nullget the next value in the
COALESCEfunction; 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
Get more about this and other alternative here