HQL虚拟视图和行转列函数

行转列函数：explode(col_name)

案例

array 行转列

create table `users`.`t_view`(
pageid string,
adid_list array<int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

# 语法
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
​
​
e.g.
SELECT pageid, adid
FROM t_view LATERAL VIEW explode(adid_list) adTable AS adid;

map

create table if not exists `users`.`t_map`(
uname string,
item_id map<string, int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
​
load data local inpath '/root/data/map.txt' into table `users`.`t_map`;

# 数据
马蓉    皮鞭:2,蜡烛:1,手铐:1
宋喆    红酒:1,花生:1,皮皮虾:10

SELECT uname, itemsname,count
FROM t_map LATERAL VIEW explode(item_id) adTable AS itemsname,count;
;