针对STRUCT数据类型使用like运算符 [英] Using like operator against STRUCT data type
问题描述
我有一个带有结构数组的表.有没有一种方法可以使用like运算符从该列中过滤记录?
I have a table with array of structs. Is there a way to filter the records from this column using like operator ?
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"状态的记录.
I am trying to run a query something like this, to filter only those records with "NY" state.
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列爆炸.但是要避免这种情况,因为我需要将此表与另一个不接受横向视图的表连接起来.
Note : I could do this by doing a lateralview & explode of this struct column. But trying to avoid it because I need join this table with another where lateral view is not accepted.
推荐答案
很好的问题,您可以通过以下高效(美观)的方式来实现.
Nice question, you can do it in the following efficient ( and beautiful) way.
select * from location
where array_contains(location_list.state, 'NY');
在这种情况下, location_list.state
将创建一个字符串数组(在您的情况下为状态),因此您可以使用UDF array_contains
进行值检查.这将寻找确切的值,您将无法执行类似 like
运算符的匹配,但您应该能够实现所需的结果
In this case, location_list.state
will create an array of strings (states in your case) so you can use the UDF array_contains
for value checking. This will look for exact value, you will not be able to perform a matching like the like
operator but you should be able to achieve what you are looking
这篇关于针对STRUCT数据类型使用like运算符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!