Thanks Carl, I exploded to a temp table as a workaround, but glad to see it will be added.
Cheers, Tim On Mon, Mar 29, 2010 at 5:00 PM, Carl Steinbach <[email protected]> wrote: > 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 > >
