CODEWITHSUNDEEP
sqlhive
Parsa
Parsa

Reputation: 1167

COALESCE with Hive SQL

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

Answers (7)

Zorayr
Zorayr

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

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions

Upvotes: 5

Amit_Hora
Amit_Hora

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

kanishka vatsa
kanishka vatsa

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

Srikant
Srikant

Reputation: 436

nvl(value,defaultvalue) as Columnname

will set the missing values to defaultvalue specified

Upvotes: 2

staticor
staticor

Reputation: 618

From Language DDL & UDF of Hive

NVL(value, default value)

Returns default value if value is null else returns value

Upvotes: 3

Ivan Klass
Ivan Klass

Reputation: 6627

Hive supports bigint literal since 0.8 version. So, additional "L" is enough:

COALESCE(column, 0L)

Upvotes: 49

Parsa
Parsa

Reputation: 1167

As Lamak pointed out in the comment, COALESCE(column, CAST(0 AS BIGINT)) resolves the error.

Upvotes: 26

Related Questions