IN() Operator

[Andy Kent <An...@forward.co.uk>]

I couldn't find anything on the wiki so thought I would try here. 

Does Hive have an IN() operator similar to in MySQL?

If not then is there an alternative way of testing for inclusion?

Thanks, 
Andy.

Re: IN() Operator

[Amr Awadallah <aa...@cloudera.com>]

Andy,

    Are you trying to do something like:

SELECT
FROM mytable  A
WHERE ....
AND mycol IN (
SELECT ....

)

If so, you can't do sub-queries inside the WHERE clause in Hive, you can 
only do sub-queries within the FROM/JOIN clause.

But, almost any query similar to above can be written using sub-queries 
in the FROM using join instead.

For example:

SELECT ....
FROM mytable A
JOIN ( SELECT .... ) B
ON A.mycol  = B.mycol
WHERE ...

hope that helps.

-- amr

On 2/19/2010 8:58 AM, Andy Kent wrote:
> I couldn't find anything on the wiki so thought I would try here.
>
> Does Hive have an IN() operator similar to in MySQL?
>
> If not then is there an alternative way of testing for inclusion?
>
> Thanks,
> Andy.

Re: IN() Operator

[Vladimir Klimontovich <kl...@gmail.com>]

No, but it's relatively  easy to implement custom UDF (http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF)
 that will implement the same functionality

On Feb 19, 2010, at 7:58 PM, Andy Kent wrote:

> I couldn't find anything on the wiki so thought I would try here. 
> 
> Does Hive have an IN() operator similar to in MySQL?
> 
> If not then is there an alternative way of testing for inclusion?
> 
> Thanks, 
> Andy.

---
Vladimir Klimontovich,
skype: klimontovich
GoogleTalk/Jabber: klimontovich@gmail.com
Cell phone: +7926 890 2349