Hive-day12 Hive行列转换

Hive 行转列

lateral view explode

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"



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

小虎	150
小虎	170
小虎	180
火火	150
火火	180
火火	190

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

key1
key2
key3

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


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

小虎	0	150
小虎	1	170
小虎	2	180
火火	0	150
火火	1	180
火火	2	190

Hive 列转行

// testLieToLine
name col1
小虎	150
小虎	170
小虎	180
火火	150
火火	180
火火	190

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


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

// 结果
小虎	["150","180","190"]
火火	["150","170","180"]

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;