Hive中行转列、列转行（UDAF 与 UDTF）

1、行转列
表结构：

create table person_info( name string, constellation string, blood_type string)
row format delimited fields terminated by “\t”;

load data local inpath “person_info.dat” into table person_info;

需求：把星座和血型一样的人归类到一起：
select
t1.base,
concat_ws(’|’, collect_set(t1.name)) name
from
(select
name,
concat(constellation, “,”, blood_type) base
from
person_info) t1
group by
t1.base;

2、列转行
表结构：

create table movie_info( movie string, category array)
row format delimited fields terminated by “\t” collection items terminated by “,”;
注意：“collection items terminated by”：一个字段中各个子元素 item 的分隔符。

load data local inpath “movie_info.tsv” into table movie_info;

需求：将电影分类中的数组数据展开：
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;