hive：如何用Map列分解表

rxztt3cl 于 2021-06-27 发布在 Hive

关注(0)|答案(1)|浏览(322)

我有一张这样的table

+-----+------------------------------+
| id    | mapCol                     |
+-----+------------------------------+
| id1   |     {key1:val1, key2:val2} |
| id2   |     {key1:val3, key2:val4} |
+-----+------------------------------+

所以我可以很容易地执行如下查询 select explode(mapCol) as (key, val) from myTab where id='id1' 我得到了

+--------+-----+
| key    | val |
+--------+-----+
| key1   | val1|
| key2   | val2|
+--------+-----+

我想生成一个这样的表

+-----+------+-----+
|id   | key  | val |
+-----+------+-----+
| id1 | key1 | val1|
| id1 | key2 | val2|
| id2 | key1 | val3|
| id2 | key2 | val4|
+-----+------------+

注意，我想显示 id 以及被分解的行。另外，对于多个id key 可能会重复，因此我希望行反映这一点。基本上， id + key 应该是唯一的。
我该如何为此编写查询？我试过了 select explode(mapCol) as (key, val), id from myTab 但我有 FAILED: SemanticException 1:66 Only a single expression in the SELECT clause is supported with UDTF's

Java Hive user-defined-functions Dictionary hiveql

来源：https://stackoverflow.com/questions/54953074/hive-how-to-explode-table-with-map-column

1条答案

按热度按时间

cnwbcb6i1#

使用侧视图：

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)

用你的table代替 MyTable 子查询。
另请阅读关于侧视图的回答：https://stackoverflow.com/a/51846380/2700344.

赞(0）回复(0）举报 2021-06-27

我来回答

hive：如何用Map列分解表

1条答案

相关问题

热门标签

最新问答