对结构数据类型使用like运算符

jum4pzuy 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(463)

我有一个包含结构数组的表。有没有一种方法可以使用like操作符筛选此列中的记录？

hive> desc location;
location_list           array<struct<city:string,state:string>>

hive> select * from location;
row1 : [{"city":"Hudson","state":"NY"},{"city":"San Jose","state":"CA"},{"city":"Albany","state":"NY"}]
row2 : [{"city":"San Jose","state":"CA"},{"city":"San Diego","state":"CA"}]

我试图运行一个类似这样的查询，只过滤那些状态为“ny”的记录。

hive> select * from location where location_list like '%"NY"%';
FAILED: SemanticException [Error 10014]: Line 1:29 Wrong arguments ''%"NY"%'': No matching method for class org.apache.hadoop.hive.ql.udf.UDFLike with (array<struct<city:string,state:string>>, string). Possible choices: _FUNC_(string, string)

注意：我可以通过对这个struct列进行lateralview&explode来实现这一点。但尽量避免，因为我需要把这张table和另一张不接受侧视图的table连接起来。

Hive

来源：https://stackoverflow.com/questions/50370174/using-like-operator-against-struct-data-type

2条答案

按热度按时间

dw1jzc5e1#

演示 array_contains :

select my_array  
from
( --emulation of your dataset. Just replace this subquery with your table
 select array(named_struct("city","Hudson","state","NY"),named_struct("city","San Jose","state","CA"),named_struct("city","Albany","state","NY")) as my_array
 union all
 select array(named_struct("city","San Jose","state","CA"),named_struct("city","San Diego","state","CA")) as my_array
)s
where array_contains(my_array.state,'NY') 
;

结果：

OK
[{"city":"Hudson","state":"NY"},{"city":"San Jose","state":"CA"},{"city":"Albany","state":"NY"}]
Time taken: 34.055 seconds, Fetched: 1 row(s)

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

kb5ga3dv2#

很好的问题，你可以用以下高效（和漂亮）的方法来做。

select * from location 
where array_contains(location_list.state, 'NY');

在这种情况下， location_list.state 将创建一个字符串数组（本例中的状态），以便您可以使用udf array_contains 用于值检查。这将寻找精确的值，您将无法执行像 like 但你应该能够实现你所期待的

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

我来回答

对结构数据类型使用like运算符

2条答案

相关问题

热门标签

最新问答