hive 复杂数据结构

array

drop table test;
create temporary table test(
name string,
course array<string>
);

insert into table test select 'test', array('math');
insert into table test select 'test1', array('math', 'chinese');

select *, course[0], course[1], course[2] from test;

map

drop table test;
create temporary table test(
name string,
course map<string, string>
);

insert into table test select 'test', map('math', '80');
insert into table test select 'test1', map('math', '80', 'chinese', '90');

select *, course['math'], course['chinese'], course['english'] from test;

struct

drop table test;
create temporary table test(
name string,
score struct<name: string, score: int>
);

insert into table test select 'test', named_struct('name', 'math', 'score', 80);

select *, score.name, score.score from test;

array & struct

drop table test;
create temporary table test(
name string, 
score array<struct<name: string, score: int>>
);

insert into table test 
  select 
    'test', 
    array(named_struct('name', 'math', 'score', 80));

insert into table test 
  select 
    'test1', 
    array(named_struct('name', 'math', 'score', 80), 
    named_struct('name', 'chinese', 'score', 90));

select name, score[0], score[1], score[0].name, score[0].score from test;

explode

1. 行转列（对map和array） – 只有要处理的一个字段

select explode(course) from test;  -- use map_test array_test table
select name, explode(course) from test; -- Error 

2. lateral view explode – 除了要处理的字段还有其他字段

drop table test;
create temporary table test(
col1 array<string>,
col2 array<string>
);

insert into table test
select 
array('a', 'b', 'c'), 
array('1', '2', '3');

select a, b 
from test
lateral view explode(col1) table1 as a, 
lateral view explode(col2) table2 as b;

inline

select name, c1, c2 from test lateral view inline(score) t1 as c1, c2;  -- use array&struct table

列转行

select name, collect_list(xx) from test group by name;