Hive中Map数据类型转String类型，其中具体内容不变

最新推荐文章于 2024-12-12 22:12:57 发布

转载最新推荐文章于 2024-12-12 22:12:57 发布 · 4.4k 阅读

CC 4.0 BY-SA版权

原文链接：https://blog.csdn.net/JnYoung/article/details/106102412?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.essearch_pc_relevant&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%

文章标签：

#hive

Hive 专栏收录该内容

32 篇文章

订阅专栏

这篇博客介绍了如何在Hive中将含有Map数据类型的表转换为String类型。通过示例展示了创建测试数据，查看表结构，查询数据的过程，并提供了SQL转换语句，将map<string,string>转换为字符串格式。转换后的数据不再保留原始的Map结构，而是以JSON格式的字符串表示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--上传测试数据
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)

如果大括号不需要，可以去掉最外层concat()函数；
如果不需要双引号"",则可以将concat_ws('":"', k,v)改为concat_ws(':', k,v)