Hive NVL 不适用于列的 Date 类型 - NullpointerException [英] Hive NVL does not work with Date type of the column - NullpointerException
问题描述
我在 HDFS 上使用 MapR Hive 发行版并面临以下问题.如果表的列类型是日期"类型,则 NVL 功能不起作用.这同样适用于其他数据类型.
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.
它只是抛出
NullpointerException:Null
即使是解释函数也会抛出同样的异常.
Even explain function is throwing same exception.
请在这里提供帮助.是 Hive 发行版中的错误吗?
Kindly help here. Is it a bug in Hive distribution?
推荐答案
我自己解决了这个问题:
I solved the problem myself with a workaround:
对于日期类型,您可以使用 hive COALESCE
函数如下:
For Date type, you could use hive COALESCE
function as below:
COALESCE(nt.assess_dt, cast('9999-01-01' as date))
以上答案可以解释为:
- 找到
nt.assess_dt
(这是一个date
类型的列);如果它是null
在COALESCE
函数中获取下一个值;在上面的例子中这是一个非空值,因此将被返回.
- Find
nt.assess_dt
(which is adate
type column); if it isnull
get the next value in theCOALESCE
function; which is a non-null value in above example and hence will be returned back.
请注意,它与 NVL 略有不同,其中 COALESCE
返回的值需要是相同的类型.因此,在上面的示例中,COALESCE
不能返回空白的 ''
.
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.
因此,我使用了一个非常大的日期值 9999-01-01
来表示 null
值并区分真正的日期值.如果您的日期列确实可以将这么大的值作为有效值,您应该考虑其他一些有效日期值来表示 null
日期.
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
这篇关于Hive NVL 不适用于列的 Date 类型 - NullpointerException的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!