You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Prakrati Agrawal <Pr...@mu-sigma.com> on 2012/08/14 11:12:15 UTC
NOT IN clause in Hive
Dear all,
I am trying to execute this query : SELECT distinct(buyerid) from transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from transaction)
This gives me the following error:
FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT' 'distinct' '(' in expression specification
How can I rectify the query to get it working?
Thanks and Regards,
Prakrati
________________________________
This email message may contain proprietary, private and confidential information. The information transmitted is intended only for the person(s) or entities to which it is addressed. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may be illegal. If you received this in error, please contact the sender and delete the message from your system.
Mu Sigma takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.
Re: NOT IN clause in Hive
Posted by Philip Tromans <ph...@gmail.com>.
https://cwiki.apache.org/Hive/languagemanual-joins.html
On 14 August 2012 10:29, Prakrati Agrawal <Pr...@mu-sigma.com> wrote:
> Dear Phil,
>
> Can you be a liitle more specific about using the left outer join?
>
> Thanks and Regards,
> Prakrati
>
> -----Original Message-----
> From: Philip Tromans [mailto:philip.j.tromans@gmail.com]
> Sent: Tuesday, August 14, 2012 2:55 PM
> To: user@hive.apache.org
> Subject: Re: NOT IN clause in Hive
>
> Hive doesn't support IN. You'll need to rewrite your query as a left outer join, and check whether the RHS is null.
>
> Phil.
>
> On 14 August 2012 10:20, Bertrand Dechoux <de...@gmail.com> wrote:
>> According to the error message, you are not using the correct synthax :
>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
>> #LanguageManualSelect-ALLandDISTINCTClauses
>>
>> Bertrand
>>
>>
>> On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal
>> <Pr...@mu-sigma.com> wrote:
>>>
>>> Dear all,
>>>
>>>
>>>
>>> I am trying to execute this query : SELECT distinct(buyerid) from
>>> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from
>>> transaction)
>>>
>>> This gives me the following error:
>>>
>>>
>>>
>>> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT'
>>> 'distinct' '(' in expression specification
>>>
>>>
>>>
>>> How can I rectify the query to get it working?
>>>
>>>
>>>
>>> Thanks and Regards,
>>>
>>> Prakrati
>>>
>>>
>>> ________________________________
>>> This email message may contain proprietary, private and confidential
>>> information. The information transmitted is intended only for the
>>> person(s) or entities to which it is addressed. Any review,
>>> retransmission, dissemination or other use of, or taking of any
>>> action in reliance upon, this information by persons or entities
>>> other than the intended recipient is prohibited and may be illegal.
>>> If you received this in error, please contact the sender and delete the message from your system.
>>>
>>> Mu Sigma takes all reasonable steps to ensure that its electronic
>>> communications are free from viruses. However, given Internet
>>> accessibility, the Company cannot accept liability for any virus
>>> introduced by this e-mail or any attachment and you are advised to
>>> use up-to-date virus checking software.
>>
>>
>>
>>
>> --
>> Bertrand Dechoux
>
> This email message may contain proprietary, private and confidential information. The information transmitted is intended only for the person(s) or entities to which it is addressed. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may be illegal. If you received this in error, please contact the sender and delete the message from your system.
>
> Mu Sigma takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.
Re: NOT IN clause in Hive
Posted by hj g <ho...@gmail.com>.
maybe like this:
select buyerid from (select buyerid ,sellerid from transation left out
join transation on buyerid=sellerid group by buyerid ,sellerid)
where sellerid is null
2012/8/14 Prakrati Agrawal <Pr...@mu-sigma.com>
> Dear Phil,
>
> Can you be a liitle more specific about using the left outer join?
>
> Thanks and Regards,
> Prakrati
>
> -----Original Message-----
> From: Philip Tromans [mailto:philip.j.tromans@gmail.com]
> Sent: Tuesday, August 14, 2012 2:55 PM
> To: user@hive.apache.org
> Subject: Re: NOT IN clause in Hive
>
> Hive doesn't support IN. You'll need to rewrite your query as a left outer
> join, and check whether the RHS is null.
>
> Phil.
>
> On 14 August 2012 10:20, Bertrand Dechoux <de...@gmail.com> wrote:
> > According to the error message, you are not using the correct synthax :
> > https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
> > #LanguageManualSelect-ALLandDISTINCTClauses
> >
> > Bertrand
> >
> >
> > On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal
> > <Pr...@mu-sigma.com> wrote:
> >>
> >> Dear all,
> >>
> >>
> >>
> >> I am trying to execute this query : SELECT distinct(buyerid) from
> >> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from
> >> transaction)
> >>
> >> This gives me the following error:
> >>
> >>
> >>
> >> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT'
> >> 'distinct' '(' in expression specification
> >>
> >>
> >>
> >> How can I rectify the query to get it working?
> >>
> >>
> >>
> >> Thanks and Regards,
> >>
> >> Prakrati
> >>
> >>
> >> ________________________________
> >> This email message may contain proprietary, private and confidential
> >> information. The information transmitted is intended only for the
> >> person(s) or entities to which it is addressed. Any review,
> >> retransmission, dissemination or other use of, or taking of any
> >> action in reliance upon, this information by persons or entities
> >> other than the intended recipient is prohibited and may be illegal.
> >> If you received this in error, please contact the sender and delete the
> message from your system.
> >>
> >> Mu Sigma takes all reasonable steps to ensure that its electronic
> >> communications are free from viruses. However, given Internet
> >> accessibility, the Company cannot accept liability for any virus
> >> introduced by this e-mail or any attachment and you are advised to
> >> use up-to-date virus checking software.
> >
> >
> >
> >
> > --
> > Bertrand Dechoux
>
> This email message may contain proprietary, private and confidential
> information. The information transmitted is intended only for the person(s)
> or entities to which it is addressed. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited and may be illegal. If you received this in error, please
> contact the sender and delete the message from your system.
>
> Mu Sigma takes all reasonable steps to ensure that its electronic
> communications are free from viruses. However, given Internet
> accessibility, the Company cannot accept liability for any virus introduced
> by this e-mail or any attachment and you are advised to use up-to-date
> virus checking software.
>
Re: NOT IN clause in Hive
Posted by hj g <ho...@gmail.com>.
maybe like this:
select * from (select buyerid ,sellerid from transation left out join
transation on buyerid=sellerid group by buyerid ,sellerid) where sellerid
is not null
2012/8/14 Prakrati Agrawal <Pr...@mu-sigma.com>
> Dear Phil,
>
> Can you be a liitle more specific about using the left outer join?
>
> Thanks and Regards,
> Prakrati
>
> -----Original Message-----
> From: Philip Tromans [mailto:philip.j.tromans@gmail.com]
> Sent: Tuesday, August 14, 2012 2:55 PM
> To: user@hive.apache.org
> Subject: Re: NOT IN clause in Hive
>
> Hive doesn't support IN. You'll need to rewrite your query as a left outer
> join, and check whether the RHS is null.
>
> Phil.
>
> On 14 August 2012 10:20, Bertrand Dechoux <de...@gmail.com> wrote:
> > According to the error message, you are not using the correct synthax :
> > https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
> > #LanguageManualSelect-ALLandDISTINCTClauses
> >
> > Bertrand
> >
> >
> > On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal
> > <Pr...@mu-sigma.com> wrote:
> >>
> >> Dear all,
> >>
> >>
> >>
> >> I am trying to execute this query : SELECT distinct(buyerid) from
> >> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from
> >> transaction)
> >>
> >> This gives me the following error:
> >>
> >>
> >>
> >> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT'
> >> 'distinct' '(' in expression specification
> >>
> >>
> >>
> >> How can I rectify the query to get it working?
> >>
> >>
> >>
> >> Thanks and Regards,
> >>
> >> Prakrati
> >>
> >>
> >> ________________________________
> >> This email message may contain proprietary, private and confidential
> >> information. The information transmitted is intended only for the
> >> person(s) or entities to which it is addressed. Any review,
> >> retransmission, dissemination or other use of, or taking of any
> >> action in reliance upon, this information by persons or entities
> >> other than the intended recipient is prohibited and may be illegal.
> >> If you received this in error, please contact the sender and delete the
> message from your system.
> >>
> >> Mu Sigma takes all reasonable steps to ensure that its electronic
> >> communications are free from viruses. However, given Internet
> >> accessibility, the Company cannot accept liability for any virus
> >> introduced by this e-mail or any attachment and you are advised to
> >> use up-to-date virus checking software.
> >
> >
> >
> >
> > --
> > Bertrand Dechoux
>
> This email message may contain proprietary, private and confidential
> information. The information transmitted is intended only for the person(s)
> or entities to which it is addressed. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited and may be illegal. If you received this in error, please
> contact the sender and delete the message from your system.
>
> Mu Sigma takes all reasonable steps to ensure that its electronic
> communications are free from viruses. However, given Internet
> accessibility, the Company cannot accept liability for any virus introduced
> by this e-mail or any attachment and you are advised to use up-to-date
> virus checking software.
>
RE: NOT IN clause in Hive
Posted by Prakrati Agrawal <Pr...@mu-sigma.com>.
Dear Phil,
Can you be a liitle more specific about using the left outer join?
Thanks and Regards,
Prakrati
-----Original Message-----
From: Philip Tromans [mailto:philip.j.tromans@gmail.com]
Sent: Tuesday, August 14, 2012 2:55 PM
To: user@hive.apache.org
Subject: Re: NOT IN clause in Hive
Hive doesn't support IN. You'll need to rewrite your query as a left outer join, and check whether the RHS is null.
Phil.
On 14 August 2012 10:20, Bertrand Dechoux <de...@gmail.com> wrote:
> According to the error message, you are not using the correct synthax :
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
> #LanguageManualSelect-ALLandDISTINCTClauses
>
> Bertrand
>
>
> On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal
> <Pr...@mu-sigma.com> wrote:
>>
>> Dear all,
>>
>>
>>
>> I am trying to execute this query : SELECT distinct(buyerid) from
>> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from
>> transaction)
>>
>> This gives me the following error:
>>
>>
>>
>> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT'
>> 'distinct' '(' in expression specification
>>
>>
>>
>> How can I rectify the query to get it working?
>>
>>
>>
>> Thanks and Regards,
>>
>> Prakrati
>>
>>
>> ________________________________
>> This email message may contain proprietary, private and confidential
>> information. The information transmitted is intended only for the
>> person(s) or entities to which it is addressed. Any review,
>> retransmission, dissemination or other use of, or taking of any
>> action in reliance upon, this information by persons or entities
>> other than the intended recipient is prohibited and may be illegal.
>> If you received this in error, please contact the sender and delete the message from your system.
>>
>> Mu Sigma takes all reasonable steps to ensure that its electronic
>> communications are free from viruses. However, given Internet
>> accessibility, the Company cannot accept liability for any virus
>> introduced by this e-mail or any attachment and you are advised to
>> use up-to-date virus checking software.
>
>
>
>
> --
> Bertrand Dechoux
This email message may contain proprietary, private and confidential information. The information transmitted is intended only for the person(s) or entities to which it is addressed. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited and may be illegal. If you received this in error, please contact the sender and delete the message from your system.
Mu Sigma takes all reasonable steps to ensure that its electronic communications are free from viruses. However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.
Re: NOT IN clause in Hive
Posted by Philip Tromans <ph...@gmail.com>.
Hive doesn't support IN. You'll need to rewrite your query as a left
outer join, and check whether the RHS is null.
Phil.
On 14 August 2012 10:20, Bertrand Dechoux <de...@gmail.com> wrote:
> According to the error message, you are not using the correct synthax :
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-ALLandDISTINCTClauses
>
> Bertrand
>
>
> On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal
> <Pr...@mu-sigma.com> wrote:
>>
>> Dear all,
>>
>>
>>
>> I am trying to execute this query : SELECT distinct(buyerid) from
>> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from transaction)
>>
>> This gives me the following error:
>>
>>
>>
>> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT'
>> 'distinct' '(' in expression specification
>>
>>
>>
>> How can I rectify the query to get it working?
>>
>>
>>
>> Thanks and Regards,
>>
>> Prakrati
>>
>>
>> ________________________________
>> This email message may contain proprietary, private and confidential
>> information. The information transmitted is intended only for the person(s)
>> or entities to which it is addressed. Any review, retransmission,
>> dissemination or other use of, or taking of any action in reliance upon,
>> this information by persons or entities other than the intended recipient is
>> prohibited and may be illegal. If you received this in error, please contact
>> the sender and delete the message from your system.
>>
>> Mu Sigma takes all reasonable steps to ensure that its electronic
>> communications are free from viruses. However, given Internet accessibility,
>> the Company cannot accept liability for any virus introduced by this e-mail
>> or any attachment and you are advised to use up-to-date virus checking
>> software.
>
>
>
>
> --
> Bertrand Dechoux
Re: NOT IN clause in Hive
Posted by Bertrand Dechoux <de...@gmail.com>.
According to the error message, you are not using the correct synthax :
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-ALLandDISTINCTClauses
Bertrand
On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal <
Prakrati.Agrawal@mu-sigma.com> wrote:
> Dear all,****
>
> ** **
>
> I am trying to execute this query : SELECT distinct(buyerid) from
> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from transaction)
> ****
>
> This gives me the following error:****
>
> ** **
>
> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT'
> 'distinct' '(' in expression specification****
>
> ** **
>
> How can I rectify the query to get it working?****
>
> ** **
>
> Thanks and Regards,****
>
> Prakrati****
>
> ------------------------------
> This email message may contain proprietary, private and confidential
> information. The information transmitted is intended only for the person(s)
> or entities to which it is addressed. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited and may be illegal. If you received this in error, please
> contact the sender and delete the message from your system.
>
> Mu Sigma takes all reasonable steps to ensure that its electronic
> communications are free from viruses. However, given Internet
> accessibility, the Company cannot accept liability for any virus introduced
> by this e-mail or any attachment and you are advised to use up-to-date
> virus checking software.
>
--
Bertrand Dechoux