问题描述
使用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中搜索数组中的项目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!