Grokbase Groups Hive user March 2010
FAQ
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

Search Discussions

  • Carl Steinbach at Mar 29, 2010 at 3:01 pm
    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 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
  • Tim Robertson at Mar 29, 2010 at 3:03 pm
    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 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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedMar 29, '10 at 2:44p
activeMar 29, '10 at 3:03p
posts3
users2
websitehive.apache.org

2 users in discussion

Tim Robertson: 2 posts Carl Steinbach: 1 post

People

Translate

site design / logo © 2022 Grokbase