Hive NVL不适用于列的日期类型 - NullpointerException [英] Hive NVL does not work with Date type of the column - NullpointerException
问题描述
我在HDFS上使用MapR Hive分发并面临以下问题。
如果表的列类型是'日期'类型,那么 NVL 功能不起作用。
它只是抛出
NullpointerException:null
甚至explain函数也抛出相同的异常。
请在这里帮忙。它是Hive发行版中的一个错误吗?
我使用一种解决方法自己解决了问题:
对于Date类型,您可以使用hive COALESCE
函数如下:
COALESCE(nt.assess_dt,cast('9999-01-01'as date))
以上答案可以解释为:
nt.assess_dt
(它是 date
type列);如果它是 null
获取 COALESCE
函数中的下一个值;这是上面例子中的一个非空值,因此将返回。
请注意,它与NVL有点不同,其中由 由于这个原因,我使用了一个非常大的日期值 详细了解此替代方法和其他方法 here 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 The above answer can be explain as: Please note that it is a little different than NVL, where the value returned by the Due to this, I have used a very large date value Get more about this and other alternative here 这篇关于Hive NVL不适用于列的日期类型 - NullpointerException的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! COALESCE
返回的值需要具有相同的类型。因此,在上例中, COALESCE
不能返回空白'<<$ code>。
9999-01-01
来表示一个 null
值并区分真正的日期值。如果您的日期列确实可以将这个大值作为有效值,那么您应该考虑其他有效的日期值来表示 null
日期。
NullpointerException:Null
COALESCE
function as below:COALESCE(nt.assess_dt, cast('9999-01-01' as date))
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.COALESCE
needs to be of same type. Hence a blank ''
can not be returned by COALESCE
in the above example. 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.