--上传测试数据
drop table test_map_1;
create table test_map_1 as
select 1 as uid, map("key1", "value1","key2", "value2") as map1 union all
select 2 as uid, map("key3", "value3","key4", "value4") as map1;
--查看当前测试表结构是否是map<string,string>类型
hive> desc test_map_1;
col_name data_type comment
uid int
map1 map<string,string>
--查询具体数据
hive> select * from test_map_1;
OK
test_map_1.uid test_map_1.map1
1 {"key1":"value1","key2":"value2"}
2 {"key3":"value3","key4":"value4"}
Time taken: 0.058 seconds, Fetched: 2 row(s)
--转换sql如下,并将结果放入临时表
drop table test_map_1_to_string;
create table test_map_1_to_string as
select
uid,
concat('{"', concat_ws(',', collect_list(concat_ws('":"', k,v) ) ), '"}') as string1
from test_map_1
lateral view outer explode(map1) kv as k,v
group by uid
;
select * from test_map_1_to_string;
--查看原数据类型map<string,string>转为string
hive> desc test_map_1_to_string;
OK
col_name data_type comment
uid int
string1 string
select * from test_map_1_to_string;
OK
test_map_1_to_string.uid test_map_1_to_string.string1
1 {"key1":"value1,key2":"value2"}
2 {"key3":"value3,key4":"value4"}
Time taken: 0.066 seconds, Fetched: 2 row(s)