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;
select name, explode( course) from test;
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;
列转行
select name, collect_list( xx) from test group by name;