CODEWITHSUNDEEP
hadoophivehiveql
BigD
BigD

Reputation: 888

How to check first non empty values in hive

How to check first non empty values in hive

For ex.

Select ('',5) should result 5

Select (5,'') should result 5

Select ( '',NULL) should result NULL

Select ('','') should result ''

Please help, i know coalesce will be applicable for finding first non null values.

Upvotes: 1

Views: 1223

Answers (1)

leftjoin
leftjoin

Reputation: 38290

Transform NULLs into string 'NULL', empty to NULL and use coalesce, then convert back. Something like this:

create temporary macro empty2null(s string)
case when s='' then null
     when s is null then 'NULL'
     else s
  end;

create temporary macro NULL2empty (s string)
case when s='NULL' then null
     when s is null then ''
     else s
end;

select NULL2empty(coalesce(empty2null(''),empty2null(5)));

The only inconvenience is that you have to wrap every column into empty2null(), except constants like 5, it's just for checking

Tests:

hive> create temporary macro empty2null(s string)
    > case when s='' then null
    >      when s is null then 'NULL'
    >      else s
    >   end;
OK
Time taken: 0.97 seconds
hive>
    > create temporary macro NULL2empty (s string)
    > case when s='NULL' then null
    >      when s is null then ''
    >      else s
    > end;
OK
Time taken: 0.02 seconds
hive>
    > select NULL2empty(coalesce(empty2null(''),empty2null(5)));
OK
5
Time taken: 7.08 seconds, Fetched: 1 row(s)
hive> select NULL2empty(coalesce(empty2null(''),empty2null('')));
OK

Time taken: 3.96 seconds, Fetched: 1 row(s)
hive> select NULL2empty(coalesce(empty2null(''),empty2null(null)));
OK
NULL
Time taken: 0.952 seconds, Fetched: 1 row(s)
hive> select NULL2empty(coalesce(empty2null(5),empty2null('')));
OK
5
Time taken: 0.067 seconds, Fetched: 1 row(s)

Upvotes: 1

Related Questions