What is the replacement of NULLIF in Hive?

Question

I would like to know what is the replacement of NULLIF in Hive? I am using COALESCE but its not serving my requirement. My query statement is something like :

COALESCE(A,B,C) AS D

COALESCE will return first NOT NULL value. But my A/B/C contain blank values so COALESCE is not assigning that value to D as it is considering blank as NOT NULL. But I want the correct value to be get assign to D.

In SQL I could have use COALESCE(NULLIF(A,'')......) so it will check for blank as well. I tried CASE but it's not working.

Trigraph

Answer 1

Just use case:

select (case when A is null or A = '' then . . . end)

This is standard SQL, so it will work in Hive and elsewhere.

For your particular problem:

select (case when A is not null and A <> '' then A
             when B is not null and B <> '' then B
             else C
        end)

You can actually shorten this to:

select (case when A <> '' then A
             when B <> '' then B
             else C
        end)

because NULL values fail comparisons. I would use this version but often people learning SQL prefer the more explicit version with the not null comparison.

Abattoir

Thanks a lot Gordon for your very quick help...:) – Trigraph

Sometimes, specially for fixed-width type data; using trim(A) <> '' can be greatly helpful. – Lynnelle

Answer 2

Another HiveQL specific option is here:

create temporary macro nullify(s string) if(s = '', null, s);
--
select coalesce(nullify(A), nullify(B), nullify(C)) as D ...

Cosmic

I like this macro solution very much, it is very helpful if your version of Hive is less than 2.3.0 – Carlton

Answer 3

NULLIF is available in Hive as of 2.3.0.

You can see it in their list of conditional functions.

Bustamante

Answer 4

Just use case syntax like following below :

select
     coalesce(
              case when A = '' then NULL else A end
              ,case when B = '' then NULL else B end
              ,case when C = '' then NULL else C end
             ) as D
 from myTable

Hope can solved your problem. Thank you..

Polky

Why do you need the coalesce? – Rube