vivo笔试Hive

现有表 vivo 格式如下左图所示：params 为 map 类型数据，请将 params 解析为右图所示格式：

创建表vivo

create table if not exists vivo(
name string,
params map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by '&'
map keys terminated by ':'
stored as textfile
;

加载数据到vivo表

load data local inpath '/myfile/gqds/vivo.txt' overwrite into table vivo;

查询

select 
name,
tmp.key course,
tmp.value score
from vivo
lateral view explode(params) tmp
;

结果行转列

select 
t.name name,
sum(case when t.course='math' then score else 0 end) math,
sum(case when t.course='english' then score else 0 end) english
from (
select
name,
tmp.key course,
tmp.value score
from vivo
lateral view explode(params) tmp
) t
group by name
;