You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Tim Robertson <ti...@gmail.com> on 2010/03/29 16:43:58 UTC

select with Array - syntax?

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

Re: select with Array - syntax?

Posted by Tim Robertson <ti...@gmail.com>.

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 <ca...@cloudera.com> 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 <ti...@gmail.com>
> 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
>
>

Re: select with Array - syntax?

Posted by Carl Steinbach <ca...@cloudera.com>.

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 <ti...@gmail.com>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
>