concat_ws()和lateral view explode()
数据:
t1表
uid tags
1 1,2,3
2 2,3
3 1,2
编写sql实现如下结果:
uid tag
1 1
1 2
1 3
2 2
2 3
3 1
3 2
-- 创建表
use bg401;
CREATE TABLE IF NOT EXISTS exptest(
uid int,
tags array<string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
;
-- 加载数据
load data local inpath '/yb/bg401_exptest' into table bg401.exptest;
-- 利用lateral view explode()函数行转列
select uid,tag
from exptest
lateral view explode(tags) atable as tag
-- 利用concat_ws进行拼接
select uid,concat_ws(',',tags)
from exptest;