You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Min Zhou <co...@gmail.com> on 2009/06/10 08:38:18 UTC
about join query
I have a query like below,
SELECT
a.subid, a.id, t.url
FROM
tbl t JOIN aux_tbl a ON t.url rlike a.url_pattern
WHERE
t.dt='20090609'
AND a.dt='20090609';
and parser reported 'FAILED: Error in semantic analysis: line 4:xx Both Left
and Right Aliases Encountered in Join url_pattern'
how can i correct this query?
Thanks,
Min
--
My research interests are distributed systems, parallel computing and
bytecode based virtual machine.
My profile:
http://www.linkedin.com/in/coderplay
My blog:
http://coderplay.javaeye.com
Re: about join query
Posted by Zheng Shao <zs...@gmail.com>.
It work only with join with = condition and the left side comes from one
table, and the right side comes from another table.
You can easily imagine how we implement this with map reduce.
If you can find a good way to do your join in map-reduce, let us know and we
might be able to add that to Hive.
How many url_patterns do you have? If it's a small number, maybe just inline
the url_patterns into the query.
Zheng
On Wed, Jun 10, 2009 at 12:06 AM, Min Zhou <co...@gmail.com> wrote:
> then how about below?
>
> SELECT
> a.subid, a.id, t.url
> FROM
> tbl t JOIN aux_tbl a ON regexp_like(t.url, a.url_pattern)
> WHERE
> t.dt='20090609'
> AND a.dt='20090609';
>
> the udf regexp_like have the same function with rlike, the only difference
> between them is one is a prefix operator and the other is a infix operator.
>
> Thanks,
> Min
>
>
> On Wed, Jun 10, 2009 at 2:58 PM, Zheng Shao <zs...@gmail.com> wrote:
>
>> If this does not work, and you have a concrete idea on how to make it
>> work, please open a jira and post your idea there. We can start the
>> discussion from there.
>>
>> Zheng
>>
>>
>> On Tue, Jun 9, 2009 at 11:54 PM, Zheng Shao <zs...@gmail.com> wrote:
>>
>>> We only support EQUI-JOIN: join with equal condition.
>>>
>>> Is it possible to write a UDF: get_pattern(t.url) ?
>>>
>>> SELECT
>>> a.subid, a.id, t.url
>>> FROM
>>> tbl t JOIN aux_tbl a ON get_pattern(t.url) = a.url_pattern
>>> WHERE
>>> t.dt='20090609'
>>> AND a.dt='20090609';
>>>
>>> Zheng
>>>
>>>
>>> On Tue, Jun 9, 2009 at 11:38 PM, Min Zhou <co...@gmail.com> wrote:
>>>
>>>> I have a query like below,
>>>>
>>>> SELECT
>>>> a.subid, a.id, t.url
>>>> FROM
>>>> tbl t JOIN aux_tbl a ON t.url rlike a.url_pattern
>>>> WHERE
>>>> t.dt='20090609'
>>>> AND a.dt='20090609';
>>>>
>>>> and parser reported 'FAILED: Error in semantic analysis: line 4:xx Both
>>>> Left and Right Aliases Encountered in Join url_pattern'
>>>>
>>>> how can i correct this query?
>>>>
>>>> Thanks,
>>>> Min
>>>> --
>>>> My research interests are distributed systems, parallel computing and
>>>> bytecode based virtual machine.
>>>>
>>>> My profile:
>>>> http://www.linkedin.com/in/coderplay
>>>> My blog:
>>>> http://coderplay.javaeye.com
>>>>
>>>
>>>
>>>
>>> --
>>> Yours,
>>> Zheng
>>>
>>
>>
>>
>> --
>> Yours,
>> Zheng
>>
>
>
>
> --
> My research interests are distributed systems, parallel computing and
> bytecode based virtual machine.
>
> My profile:
> http://www.linkedin.com/in/coderplay
> My blog:
> http://coderplay.javaeye.com
>
--
Yours,
Zheng
Re: about join query
Posted by Min Zhou <co...@gmail.com>.
then how about below?
SELECT
a.subid, a.id, t.url
FROM
tbl t JOIN aux_tbl a ON regexp_like(t.url, a.url_pattern)
WHERE
t.dt='20090609'
AND a.dt='20090609';
the udf regexp_like have the same function with rlike, the only difference
between them is one is a prefix operator and the other is a infix operator.
Thanks,
Min
On Wed, Jun 10, 2009 at 2:58 PM, Zheng Shao <zs...@gmail.com> wrote:
> If this does not work, and you have a concrete idea on how to make it work,
> please open a jira and post your idea there. We can start the discussion
> from there.
>
> Zheng
>
>
> On Tue, Jun 9, 2009 at 11:54 PM, Zheng Shao <zs...@gmail.com> wrote:
>
>> We only support EQUI-JOIN: join with equal condition.
>>
>> Is it possible to write a UDF: get_pattern(t.url) ?
>>
>> SELECT
>> a.subid, a.id, t.url
>> FROM
>> tbl t JOIN aux_tbl a ON get_pattern(t.url) = a.url_pattern
>> WHERE
>> t.dt='20090609'
>> AND a.dt='20090609';
>>
>> Zheng
>>
>>
>> On Tue, Jun 9, 2009 at 11:38 PM, Min Zhou <co...@gmail.com> wrote:
>>
>>> I have a query like below,
>>>
>>> SELECT
>>> a.subid, a.id, t.url
>>> FROM
>>> tbl t JOIN aux_tbl a ON t.url rlike a.url_pattern
>>> WHERE
>>> t.dt='20090609'
>>> AND a.dt='20090609';
>>>
>>> and parser reported 'FAILED: Error in semantic analysis: line 4:xx Both
>>> Left and Right Aliases Encountered in Join url_pattern'
>>>
>>> how can i correct this query?
>>>
>>> Thanks,
>>> Min
>>> --
>>> My research interests are distributed systems, parallel computing and
>>> bytecode based virtual machine.
>>>
>>> My profile:
>>> http://www.linkedin.com/in/coderplay
>>> My blog:
>>> http://coderplay.javaeye.com
>>>
>>
>>
>>
>> --
>> Yours,
>> Zheng
>>
>
>
>
> --
> Yours,
> Zheng
>
--
My research interests are distributed systems, parallel computing and
bytecode based virtual machine.
My profile:
http://www.linkedin.com/in/coderplay
My blog:
http://coderplay.javaeye.com
Re: about join query
Posted by Zheng Shao <zs...@gmail.com>.
If this does not work, and you have a concrete idea on how to make it work,
please open a jira and post your idea there. We can start the discussion
from there.
Zheng
On Tue, Jun 9, 2009 at 11:54 PM, Zheng Shao <zs...@gmail.com> wrote:
> We only support EQUI-JOIN: join with equal condition.
>
> Is it possible to write a UDF: get_pattern(t.url) ?
>
> SELECT
> a.subid, a.id, t.url
> FROM
> tbl t JOIN aux_tbl a ON get_pattern(t.url) = a.url_pattern
> WHERE
> t.dt='20090609'
> AND a.dt='20090609';
>
> Zheng
>
>
> On Tue, Jun 9, 2009 at 11:38 PM, Min Zhou <co...@gmail.com> wrote:
>
>> I have a query like below,
>>
>> SELECT
>> a.subid, a.id, t.url
>> FROM
>> tbl t JOIN aux_tbl a ON t.url rlike a.url_pattern
>> WHERE
>> t.dt='20090609'
>> AND a.dt='20090609';
>>
>> and parser reported 'FAILED: Error in semantic analysis: line 4:xx Both
>> Left and Right Aliases Encountered in Join url_pattern'
>>
>> how can i correct this query?
>>
>> Thanks,
>> Min
>> --
>> My research interests are distributed systems, parallel computing and
>> bytecode based virtual machine.
>>
>> My profile:
>> http://www.linkedin.com/in/coderplay
>> My blog:
>> http://coderplay.javaeye.com
>>
>
>
>
> --
> Yours,
> Zheng
>
--
Yours,
Zheng
Re: about join query
Posted by Zheng Shao <zs...@gmail.com>.
We only support EQUI-JOIN: join with equal condition.
Is it possible to write a UDF: get_pattern(t.url) ?
SELECT
a.subid, a.id, t.url
FROM
tbl t JOIN aux_tbl a ON get_pattern(t.url) = a.url_pattern
WHERE
t.dt='20090609'
AND a.dt='20090609';
Zheng
On Tue, Jun 9, 2009 at 11:38 PM, Min Zhou <co...@gmail.com> wrote:
> I have a query like below,
>
> SELECT
> a.subid, a.id, t.url
> FROM
> tbl t JOIN aux_tbl a ON t.url rlike a.url_pattern
> WHERE
> t.dt='20090609'
> AND a.dt='20090609';
>
> and parser reported 'FAILED: Error in semantic analysis: line 4:xx Both
> Left and Right Aliases Encountered in Join url_pattern'
>
> how can i correct this query?
>
> Thanks,
> Min
> --
> My research interests are distributed systems, parallel computing and
> bytecode based virtual machine.
>
> My profile:
> http://www.linkedin.com/in/coderplay
> My blog:
> http://coderplay.javaeye.com
>
--
Yours,
Zheng