Hi Tim, I think what you're looking for is the array_contains() UDF. There is a JIRA ticket open for this feature (http://issues.apache.org/jira/browse/HIVE-1179), and one of my coworkers has posted a patch, but unfortunately it has not yet been committed to trunk.
With this UDF your query would like this: SELECT * FROM clb_occ WHERE array_contains(1909, occurrence_ids); Carl On Mon, Mar 29, 2010 at 7:43 AM, Tim Robertson <[email protected]>wrote: > Hi guys, > > I have a table: > > CREATE TABLE clb_occ(classification_id STRING, taxon_concept_id > STRING, taxon_name_id STRING, kingdom_concept_id STRING, > phylum_concept_id STRING, class_concept_id STRING, order_concept_id > STRING, family_concept_id STRING, genus_concept_id STRING, > species_concept_id STRING, nub_concept_id STRING, occurrence_ids > ARRAY<STRING>); > > Can I issue a select for rows where the occurrence_ids contain a value? > > e.g. something like > select * from clb_occ where occurrence_ids contains(1909); > > What is the syntax please if this is possible? > > Thanks > Tim >
