'What is the replacement of NULLIF in Hive?

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.

sqlhivehiveqlhadoop2


Solution 1:[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.

Solution 2:[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 ...

Solution 3:[3]

NULLIF is available in Hive as of 2.3.0.

You can see it in their list of conditional functions.

Solution 4:[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..

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 GoodDok
Solution 3 Eyal
Solution 4 Afif Pratama

Related Questions