蜂巢:如何使用地图列爆炸表格 [英] Hive: How to explode table with map column
问题描述
我有一张这样的桌子
+-----+------------------------------+
| id | mapCol |
+-----+------------------------------+
| id1 | {key1:val1, key2:val2} |
| id2 | {key1:val3, key2:val4} |
+-----+------------------------------+
这样我就可以轻松地执行
so i can easily perform a query like
从myTab中选择explode(mapCol)作为(key,val),其中id ='id1'
然后我得到
+--------+-----+
| key | val |
+--------+-----+
| key1 | val1|
| key2 | val2|
+--------+-----+
I想要生成这样的表
+-----+------+-----+
|id | key | val |
+-----+------+-----+
| id1 | key1 | val1|
| id1 | key2 | val2|
| id2 | key1 | val3|
| id2 | key2 | val4|
+-----+------------+
请注意,我要显示 id
以及展开的行。另外,对于多个ID,可能会重复键
,因此我希望这些行反映出来。基本上, id
+ 键
应该是唯一的。
note that I want to display the id
alongwith the exploded rows. Also, for multiple id's, the key
may be repeated, hence I want the rows to reflect that. Basically, id
+ key
should be unique.
我将如何为此查询?我试过
How would i write a query for this? I tried
选择explode(mapCol)作为(key,val),来自myTab的ID
但是我得到
失败:SemanticException 1:66仅支持SELECT子句中的单个表达式与UDTF的
推荐答案
使用 侧面视图 :
with MyTable as -------use your table instead of this subquery
(select id, str_to_map(mapStr) mapCol
from
(
select stack(2,
'id1','key1:val1,key2:val2',
'id2','key1:val3,key2:val4'
) as (id, mapStr))s
) -------use your table instead of this subquery
select t.id, s.key, s.val
from MyTable t
lateral view outer explode(mapCol) s as key, val;
结果:
OK
id1 key1 val1
id1 key2 val2
id2 key1 val3
id2 key2 val4
Time taken: 0.072 seconds, Fetched: 4 row(s)
使用表格代替 MyTable
子查询。
还要阅读有关横向视图的答案: https:// stackoverflow。 com / a / 51846380/2700344 。
Read also this answer about lateral view: https://stackoverflow.com/a/51846380/2700344.
这篇关于蜂巢:如何使用地图列爆炸表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!