hive 回顾---字段拆分为 多行+多列

 

一、拆分为多行
 

测试数据：
huangbo	a,1:b,2:c,3
xuzheng	a,4:b,5:c,6
wangbaoqiang	a,7:b,8:c,9

---创建表的语句：
create table testMap(name string,info map<string, int>) row format delimited fields terminated by '\t' collection items terminated by ':' map keys terminated by ',';

load data local inpath '/opt/hive_test.txt' into table testMap;

展示效果：

---实现行转列---使用lateral view exlode（）

case1----数据为map

select m.name, tf.key, tf.value from testMap m lateral view explode(m.info) tf as key,value;

case2----数据为Array
数据源：
1	子宫病损切除术，经宫腔镜|扩张和刮宫术，诊断性
2	胫骨内固定装置去除术|腓骨内固定装置去除术
3	腰椎穿刺术|腰椎穿刺术|腰椎穿刺术

--创建语句
Create table testArray(name string, ssmc array<string> ) row format delimited fields terminated by '\t' collection items terminated by '|';

--展示结果

--拆分为多行：
select name,ss.ssmc from testArray lateral view explode(testArray.ssmc) ss as ssmc;

--拆分结果：

case3--数据为String

--拆分为多行：
select name,ss.ssmc from testStr lateral view explode(split(ssmc,"\\|")) ss as ssmc;

--拆分结果

 

二、拆分为多列

二、拆分为多列

case1---字段数据为Array

select name,ssmc[0] as ssmc1,ssmc[1] as ssmc2,ssmc[2] as ssmc3 from testArray;

case2---字段数据为String

select t.name,t.ssmcs[0] as ssmc1,t.ssmcs[1] as ssmc2,t.ssmcs[2] as ssmc3 from (

select name,split(ssmc,"\\|") as ssmcs from testArray) t