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?
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
nvl(value,defaultvalue) as Columnname
will set the missing values to defaultvalue specified
Hive supports bigint literal since 0.8 version. So, additional "L" is enough:
COALESCE(column, 0L)
From Language DDL & UDF of Hive
NVL(value, default value)
Returns default value if value is null else returns value
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
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')))
Source: Stackoverflow.com