SQL面试题-数据格式转换

数据格式转换

表 temp 两个字段：

需要转换如下结构：

#原数据
hive> select * from temp; 
OK
abc key1:value,key2:value2
def key1:value,key2:value2,key3:value3,key4:value4
xyz Key1:value
Time taken: 0.252 seconds, Fetched: 3 row(s)
#转换后
hive> select users,key,value from temp lateral view explode(str_to_map(profile,",",":")) mp as key,value;
OK
abc key1  value
abc key2  value2
def key1  value
def key2  value2
def key3  value3
def key4  value4
xyz Key1  value
Time taken: 0.124 seconds, Fetched: 7 row(s)      

select users,key,value from temp lateral view explode(str_to_map(profile,",",":")) mp as key,value;