SQL 蜂巢中的 ARRAY_CONTAINS 多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25645558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ARRAY_CONTAINS muliple values in hive
提问by dynamo
Is there a convenient way to use the ARRAY_CONTAINS function in hive to search for multiple entries in an array column rather than just one? So rather than:
有没有一种方便的方法可以在 hive 中使用 ARRAY_CONTAINS 函数来搜索数组列中的多个条目而不是一个？所以而不是：
WHERE ARRAY_CONTAINS(array, val1) OR ARRAY_CONTAINS(array, val2)
I would like to write:
我想写：
WHERE ARRAY_CONTAINS(array, val1, val2)
The full problem is that I need to read val1and val2dynamically from the command line arguments when I run the script and I generally don't know how many values will be conditioned on. So you can think of valsbeing a comma separated list (or array) containing values val1, val2, ..., and I want to write
完整的问题是，当我运行脚本时，我需要从命令行参数中动态读取val1和val2动态读取，而且我通常不知道将有多少个值作为条件。所以你可以认为vals是一个逗号分隔的列表（或数组），其中包含值val1, val2, ...，我想写
WHERE ARRAY_CONTAINS(array, vals)
Thanks in advance!
提前致谢！
采纳答案by gobrewers14
There is a UDF herethat will let you take the intersection of two arrays. Assuming your values have the structure
有一个UDF在这里，可以让你把两个数组的交集。假设您的值具有结构
values_array = [val1, val2, ..., valn]
You could then do
然后你可以做
where array_intersection(array, values_array)[0] is not null
If they don't have any elements in common, []will be returned and therefore [][0]will be null
如果它们没有任何共同元素，[]将被返回，因此[][0]将被null
回答by Umer
Create table tmp_cars AS Select make,COLLECT_LIST(TRIM(model))
model_List from default.cars GROUP BY make;
Select array_contains(model_List,CAST('Rainier' as varchar(40)))
FROM Default.tmp_cars t
where make = 'Buick';
Data
数据
[" Rainier"," Rendezvous CX"," Century Custom 4dr"," LeSabre Custom 4dr"," Regal LS 4dr"," Regal GS 4dr"," LeSabre Limited 4dr"," Park Avenue 4dr"," Park Avenue Ultra 4dr"]
["Rainier"、"Rendezvous CX"、"Century Custom 4dr"、"LeSabre Custom 4dr"、"Regal LS 4dr"、"Regal GS 4dr"、"LeSabre Limited 4dr"、"Park Avenue 4dr"、"Park Avenue Ultra 4 博士”]
Return True
返回真
回答by liz
select * from table lateral view explode(array) a as arr where arr in (vals) ;
从表横向视图中选择 *explode(array) a as arr where arr in (vals) ;
