COALESCE with Hive SQL

Question

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?

Hive 0.11 onward, It has now NVL function. Please see https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions — Gyanendra Dwivedi, Aug 31 '15 at 02:52

Ivan Klass · Answer 1 · 2013-02-19T10:06:28.300

48

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

COALESCE(column, 0L)

edited Feb 19 '13 at 10:06

answered Jan 14 '13 at 12:16

Ivan Klass

6,407
3
30
28

score 26 · Accepted Answer · edited Nov 19 '12 at 20:39

26

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

edited Nov 19 '12 at 20:39

Lamak

69,480
12
108
116

answered Nov 19 '12 at 20:34

Parsa

1,137
1
11
15

score 6 · Answer 3 · edited Nov 18 '16 at 11:53

6

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

edited Nov 18 '16 at 11:53

sai_PB

40
1
9

answered Jul 21 '15 at 07:09

kanishka vatsa

2,074
18
8

score 5 · Answer 4 · edited Feb 26 '18 at 11:32

5

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

edited Feb 26 '18 at 11:32

Srikant

406
4
11

answered May 21 '13 at 23:32

Zorayr

23,770
8
136
129

score 4 · Answer 5 · answered Jul 10 '17 at 16:47

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')))

score 3 · Answer 6 · answered Aug 23 '16 at 03:43

3

From Language DDL & UDF of Hive

NVL(value, default value)

Returns default value if value is null else returns value

answered Aug 23 '16 at 03:43

staticor

620
9
19

score 2 · Answer 7 · edited Nov 02 '16 at 18:15

2

nvl(value,defaultvalue) as Columnname

will set the missing values to defaultvalue specified

edited Nov 02 '16 at 18:15

J. Chomel

8,193
15
41
69

answered Nov 02 '16 at 17:49

Srikant

406
4
11

COALESCE with Hive SQL

7 Answers7