Hive 多数组合并 CONCAT_WS

多列的情况

先上结果

select split(concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC")), ",")

hive> select split(concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC")), ",");
OK
["AAA","bbb","CCC","AAA","bbb","GGG","CCC"]
Time taken: 0.291 seconds, Fetched: 1 row(s)

拆分concat_ws 可以拆分数组

select concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC"))

hive> select concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC"));
OK
AAA,bbb,CCC,AAA,bbb,GGG,CCC
Time taken: 0.456 seconds, Fetched: 1 row(s)

然后在用split切分再变回数组

select split(concat_ws(',',array("AAA", "bbb"), array('CCC',"AAA", "bbb"), array("GGG","CCC")), ",")

多行合并

select
l1,split(concat_ws(',',collect_set(concat_ws(',',l2))),',')
from
(
select 666 l1,array("AAA", "bbb") l2
union
select 666 l1,array('CCC',"AAA", "bbb") l2
union
select 666 l1,array("AAABB", "bbbFF","AAAEE", "bbbFF") l2
union
select 666 l1,array("AATTA", "bbTb") l2
) res
group by l1

结果和上面效果一样