12、HIve数据仓库——行列转换

HIve数据仓库——行列转换

行转列

创建表

create table testArray2(
    name string,
    weight array<string>
)row format delimited 
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';

加载数据

• 数据

志凯	"150","170","180"
上单	"150","180","190"

• 加载数据

load data local inpath '/usr/local/soft/data/testArray2.txt' into table testArray2;

---

• 使用lateral view explode函数

select name,col1 from testArray2 lateral view explode(weight) t1 as col1;

select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t1;

select name,col1,col2 from testArray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;

select name,pos,col1 from testArray2 lateral view posexplode(weight)  t1 as pos,col1;

列转行

创建表

create table testLieToLine(
    name string,
    col1 int
)row format delimited 
fields terminated by '\t';

加载数据

• 数据

志凯	150
志凯	170
志凯	180
上单	150
上单	180
上单	190

• 加载数据

load data local inpath '/usr/local/soft/data/testLieToLine.txt' into table testLieToLine;

---

• 使用函数collect_list( )

select name,collect_list(col1) from testLieToLine group by name;

select  t1.name
        ,collect_list(t1.col1) 
from (
    select  name
            ,col1 
    from testarray2 
    lateral view explode(weight) t1 as col1
) t1 group by t1.name;

---

到底啦！关注靓仔学习更多的大数据知识！ψ(｀∇´)ψ