How to convert array<string> to string in hive

Question

I want to convert an array<string> to string in hive. The array data is as follow:

+-------------------------------------+--+
| NULL                                |
| ["Extension","Terms & Conditions"]  |
| ["Value (generic or item level)"]   |
+-------------------------------------+--+

I want to collect array values to convert to string without [""] so that I could get result like:

+-------------------------------------+--+
| NULL                                |
| Extension,Terms & Conditions        |
| Value (generic or item level)       |
+-------------------------------------+--+

Following query: select concat_ws(',', col_name) as col_name from table_stg; provides the result but is is returning NULL as empty. I tried several reference like:

How can I convert array to string in hive sql?

Hive - How to cast array to string?

But not getting the desired result. Is there any way to get the desired result?

use a case expression. case when size(col_name) = 0 then null else concat_ws(',',col_name) end — Vamsi Prabhala
– Vamsi Prabhala, Commented Apr 17, 2020 at 15:22
Hi @VamsiPrabhala - checking size doesn't worked, but it gave me the idea to solve this. thanks for your comment :) — Sandeep Singh
– Sandeep Singh, Commented Apr 20, 2020 at 7:36

Sandeep Singh · Accepted Answer · 2020-04-20 07:34:32Z

1

With reference to Vamsi comment, I managed to get this and thought to answer as well for the community reference.

select case when col_name is NULL then NULL 
    else concat_ws(',',col_name) end from table_name;

answered Apr 20, 2020 at 7:34

Sandeep Singh

8,0706 gold badges47 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert array<string> to string in hive

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related