string hive 检查逗号分隔的字符串包含一个字符串

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/22627473/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-09 02:14:46  来源:igfitidea点击:

hive check comma separated String contains a string

stringhive

提问by user2978621

I have a column in hive table list_idswhich is a list of ids stored as comma separated string.

我在 hive 表中有一个列,list_ids它是存储为逗号分隔字符串的 id 列表。

how can I write a query for this column to check if it stores a particular id

如何为此列编写查询以检查它是否存储特定的 id

Example:

例子:

 list_ids = "abc,cde,efg"

I want to something like

我想要类似的东西

 select * from table_name where list_ids contains cde; 

回答by libHyman

Use Hive standard functions splitand array_contains

使用 Hive 标准函数splitarray_contains

split(string str, string pat)returns array<string>by splitting str around pat (regular expression)

split(string str, string pat)array<string>通过在 pat 周围拆分 str返回(正则表达式)

array_contains(array<T>, value)returns trueif array contains value

array_contains(array<T>, value)true如果数组包含值则返回

select * from table_name where array_contains(split(list_ids,','),'cde')

select * from table_name where array_contains(split(list_ids,','),'cde')

回答by Neels

Hive supports LIKEoperator. You can do it easily by using:

Hive 支持LIKE运算符。您可以使用以下方法轻松完成:

select * from table_name where list_ids like '%cde%';

select * from table_name where list_ids like '%cde%';

Check out this language manual for more info:

查看此语言手册了解更多信息:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

回答by MukeshKoshyM

Use Hive function explodeyou can achieve this.

使用 Hive 函数爆炸你可以实现这一点。

Example

例子

select *
from table_name
LATERAL VIEW explode(list_ids) exploded_table as list_id_tbl
where list_ids='cde'

select *
from table_name
LATERAL VIEW expand(list_ids) purged_table as list_id_tbl
where list_ids='cde'