How to get first not null element from ARRAY<string> in hive 2.2.0

2
votes

I have a hive SQL as below which returns the following output, now I want to get the first not null value from array i.e. "11".

SQ::

select cod_cust, split(reverse(concat_ws('',collect_list(case when acc_status='Dormant' then "1" else "0" end))),'0') status from account_status group by cod_cust;

Output::

cod_cust status

1023 ["","11","1","111","",""]

2209 ["","11","1","111","",""]

1
hivehiveql

1 Answers

2
votes

Without using split function the output would be look like:

cod_cust    status
1023        01101011100
2209        01101011100

So the task is to find the first subsequence of 1, which can be solved by regexp_extract. Also you can use IF instead of CASE:

SELECT cod_cust, 
       regexp_extract(reverse(concat_ws('', collect_list(if((acc_status='Dormant'), '1', '0'))), '1+', 0) first_element
status 
FROM account_status 
GROUP BY cod_cust;