Reputation: 1167
Since there is no IFNULL
, ISNULL
, or NVL
function supported on Hive, I'm having trouble converting NULL to 0. I tried COALESCE(*column name*, 0)
but received the below error message:
Argument type mismatch 0: The expressions after COALESCE should all have the same type: "bigint" is expected but "int" is found
How to resolve this?
Upvotes: 35
Views: 164158
Reputation: 24962
From [Hive Language Manual][1]:
COALESCE (T v1, T v2, ...)
Will return the first value that is not NULL, or NULL if all values's are NULL
Upvotes: 5
Reputation: 708
If customer primary contact medium is email, if email is null then phonenumber, and if phonenumber is also null then address. It would be written using COALESCE as
coalesce(email,phonenumber,address)
while the same in hive can be achieved by chaining together nvl as
nvl(email,nvl(phonenumber,nvl(address,'n/a')))
Upvotes: 4
Reputation: 2334
Since 0.11 hive has a NVL function
nvl(T value, T default_value)
which says Returns default value if value is null else returns value
Upvotes: 6
Reputation: 436
nvl(value,defaultvalue) as Columnname
will set the missing values to defaultvalue specified
Upvotes: 2
Reputation: 618
From Language DDL & UDF of Hive
NVL(value, default value)
Returns default value if value is null else returns value
Upvotes: 3
Reputation: 6627
Hive supports bigint literal since 0.8 version. So, additional "L" is enough:
COALESCE(column, 0L)
Upvotes: 49
Reputation: 1167
As Lamak pointed out in the comment, COALESCE(column, CAST(0 AS BIGINT))
resolves the error.
Upvotes: 26