How do I search for an item in an array in Hive?

Question

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,
• EDUCATION STRING,
• LIKES_AND_PREFERENCES STRING

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 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?

Is it as simple as:

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.

Answer 1

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...!!!