如何在Hive中搜索数组中的项目? [英] How do I search for an item in an array in Hive?

问题描述

使用Hive，我创建了一个包含以下字段的表格:

Using Hive I've created a table with the following fields:

• ID BIGINT，
• MSISDN STRING，
• DAY TINYINT，
• MONTH TINYINT，
• YEAR INT，
• GENDER TINYINT，
• RELATIONSHIPSTATUS TINYINT，
• 教育字符串，
• LIKES_AND_PREFERENCES STRING

通过以下SQL命令填充了数据:

This was filled with data via the following SQL command:

Insert overwrite table temp_output Select a.ID, a.MSISDN, a.DAY, a.MONTH, a.YEAR, a.GENDER, a.RELATIONSHIPSTATUS, b.NAME,  COLLECT_SET(c.NAME) FROM temp_basic_info a JOIN temp_education b ON (a.ID = b.ID) JOIN likes_and_music c ON (c.ID = b.ID) GROUP BY a.ID, a.MSISDN, a.DAY, a.MONTH, a.YEAR, a.Gender, a.RELATIONSHIPSTATUS, b.NAME;

Likes和Preferences是一个数组，但是我没有足够的先见之明来指定它(而是字符串).我该如何选择数组中具有特定项目的记录?

Likes and Preferences is an array, but I was not foresighted enough to specify it as such (it's a string, instead). How would I go about selecting records that have a specific item in the array?

是否简单:

select * from table_result where LIKES_AND_PREFERENCES = "item"

还是会有一些无法预料的问题?

Or will that have some unforeseen issues?

我在上面尝试了该查询，但是它确实缝了输出仅包含数组中项目"的文件.

I tried that query above, and it does seam to output the files with only the "items" in the array, though.

推荐答案

也许您应该尝试这样的事情:

May be you should try something like this:

select * from (
select col1,col2..coln, new_column from table_name lateral view explode(array_column_name) exploded_table as new_column 
) t where t.new_column = '<value of items to be searched>'

希望这对您有帮助... !!!

Hope this helps...!!!

这篇关于如何在Hive中搜索数组中的项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！