英文:
Hive : Filter array of maps
问题
我有一个Hive表列如下：
col1 array<map<string,string>>
该列包含以下值：
[
{'a':1,'b':2},
{'a':1,'c':3},
.
.
.
]
现在内部映射中的键可以每个项目都不同。我想筛选数组，只获取包含键b的项目。是否可以使用array_contains来实现这样的操作？我尝试过，但是出现了错误：
select col1 from mytab where size(col1) > 0 and array_contains(col1, 'b');
FAILED: SemanticException [Error 10016]: Line 1:196 Argument type mismatch ''b'':
"map<string,string>" expected at function ARRAY_CONTAINS, but "string" is found
这是显而易见的，但我还是尝试了一下。
更新
我尝试了以下操作：
select res from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res is not NULL and length(res) > 0;
对于这个操作，我得到了以下结果：
{}
{a=1, b=2}
{a=1, b=2}
{}
这是很好的，但是如何避免空的结果，即{}？
更新 2
我成功地移除了空结果，如下：
select res from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res['b'] is not NULL and length(res['b']) > 0 res <> '{}';
最后一个问题是，如何将res从字符串转换为正确的映射？我想使用res中的键来添加一些筛选条件。我尝试过：
select str_to_map(res) as st from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res is not NULL and length(res) > 0 res <> '{}';
但我得到了：
{"{a=1, b=2}":null}
我应该如何获得正确的映射？我只想能够使用映射中的键进行筛选。目前，该映射被选中为字符串：
select res['b'] from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res['b'] is not NULL and length(res['b']) > 0 res['b'] <> '{}' and res['b']=1;
但是我得到了错误：
FAILED: SemanticException [Error 10033]: Line 1:237 [] not valid on non-collection types ''b'':
string
英文:
I have a hive table column as
col1 array<map<string,string>>
this column contains values as
[
{'a':1,'b':2},
{'a':1,'c':3},
.
.
.
]
now the keys in the inner map can vary per item. I want to filter the array to only get items where key b is present. Can such a thing be achieved using array_contains ? I tried but i got error
select col1 from mytab where size(col1) > 0 and array_contains(col1, 'b');
FAILED: SemanticException [Error 10016]: Line 1:196 Argument type mismatch ''b'': "map<string,string>" expected at function ARRAY_CONTAINS, but "string" is found
which is obvious but i tried anyway.
Update
i tried
select res from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res is not NULL and length(res) > 0;
for this i get
{}
{a=1, b=2}
{a=1, b=2}
{}
which is good but how can i avoid the empty results i.e. {} ?
Update 2
I was able to remove the empty results as
select res from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res['b'] is not NULL and length(res['b']) > 0 res <> '{}';
Last question is, how to convert res from string to a proper map ? i want to have some where condition using keys in res. I tried
select str_to_map(res) as st from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res is not NULL and length(res) > 0 res <> '{}';
but i get
{"{a=1, b=2}":null}
how can i get a proper map ? i just want to be able to filter using the keys of the map. right now this map is being selected as a string
select res['b'] from mytab LATERAL VIEW EXPLODE (col1) c as res where
size(col1) > 0 and res['b'] is not NULL and length(res) > 0 res['b'] <> '{}' and res['b']=1;
but i get
FAILED: SemanticException [Error 10033]: Line 1:237 [] not valid on non-collection types ''b'': string
专注分享java语言的经验与见解，让所有开发者获益！
评论