Hive 多数组合并 CONCAT_WS
目录
多列的情况
先上结果
拆分concat_ws 可以拆分数组
然后在用split切分再变回数组
多行合并
多列的情况
先上结果
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
结果和上面效果一样