You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mohit Anchlia <mo...@gmail.com> on 2012/10/01 22:42:42 UTC
HIVE NOT EXISTS
Could someone help me understand what alternative do I have for this query?
I am trying to check if a given row exists in the table.
select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
from page_temp_ext a
where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype =
b.pagetype);
Re: HIVE NOT EXISTS
Posted by Mohit Anchlia <mo...@gmail.com>.
thanks! that works
On Mon, Oct 1, 2012 at 3:43 PM, Matt Tucker <ma...@gmail.com> wrote:
> Hi Mohit,
>
>
>
> Hive doesn’t support correlated subqueries. In this instance, you can do
> a left outer join to find values that are not in a table.
>
>
>
> SELECT
>
> “”,
>
> a.pagename,
>
> a.pagedetail,
>
> “”,
>
> a.pagetitle,
>
> a.page_id,
>
> a.pagetype
>
> FROM page_temp_ext a
>
> LEFT OUTER JOIN page_temp b ON
>
> a.pagename = b.pagename AND
>
> a.pagetitle = b.pagetitle AND
>
> a.page_id = b.pageid AND
>
> a.pagetype = b.pagetype
>
> WHERE
>
> b.pagename IS NULL
>
>
> Hope that helps,
>
> Matt
>
>
> On Mon, Oct 1, 2012 at 4:42 PM, Mohit Anchlia <
> mohitanchlia@gmail.com> wrote:
>
>> Could someone help me understand what alternative do I have for this
>> query? I am trying to check if a given row exists in the table.
>>
>> select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
>> from page_temp_ext a
>> where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
>> and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype =
>> b.pagetype);
>>
>
>
Re: HIVE NOT EXISTS
Posted by Matt Tucker <ma...@gmail.com>.
Hi Mohit,
Hive doesn’t support correlated subqueries. In this instance, you can do a
left outer join to find values that are not in a table.
SELECT
“”,
a.pagename,
a.pagedetail,
“”,
a.pagetitle,
a.page_id,
a.pagetype
FROM page_temp_ext a
LEFT OUTER JOIN page_temp b ON
a.pagename = b.pagename AND
a.pagetitle = b.pagetitle AND
a.page_id = b.pageid AND
a.pagetype = b.pagetype
WHERE
b.pagename IS NULL
Hope that helps,
Matt
On Mon, Oct 1, 2012 at 4:42 PM, Mohit Anchlia <mohitanchlia@gmail.com
> wrote:
> Could someone help me understand what alternative do I have for this
> query? I am trying to check if a given row exists in the table.
>
> select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
> from page_temp_ext a
> where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
> and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype =
> b.pagetype);
>