You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Min Zhou (JIRA)" <ji...@apache.org> on 2009/06/11 06:21:07 UTC

[jira] Created: (HIVE-556) let hive support theta join

let hive support theta join
---------------------------

                 Key: HIVE-556
                 URL: https://issues.apache.org/jira/browse/HIVE-556
             Project: Hadoop Hive
          Issue Type: New Feature
    Affects Versions: 0.4.0
            Reporter: Min Zhou
             Fix For: 0.4.0


Right now , hive only support equal joins .  Somethings it's not enough, we must consider implementing theta joins like

{code:sql}
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';
{code}

any condition expression following 'ON' is  appropriate.



-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12719666#action_12719666 ] 

Namit Jain commented on HIVE-556:
---------------------------------

a.dt='20090609' is the filter

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718733#action_12718733 ] 

Namit Jain commented on HIVE-556:
---------------------------------

I think what Ashish meant was that if one table is small (aux table with 10 records), you can do a cartesian product with that table via map-join which wont be very expensive and then apply your join condition in the form of a filter. Still, no sorting is required - so it should not be all that bad.

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718563#action_12718563 ] 

Namit Jain commented on HIVE-556:
---------------------------------

I don't see a use case for this right now. 

The workaround is do a cartesian product (no ON condition) and push the condition in the WHERE clause - the plan will be sub-optimal currently, 
since all data will go to a single reducer.

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Somethings it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Updated: (HIVE-556) let hive support theta join

Posted by "Min Zhou (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Min Zhou updated HIVE-556:
--------------------------

    Description: 
Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like

{code:sql}
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';
{code}

any condition expression following 'ON' is  appropriate.



  was:
Right now , hive only support equal joins .  Somethings it's not enough, we must consider implementing theta joins like

{code:sql}
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';
{code}

any condition expression following 'ON' is  appropriate.




> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Min Zhou (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718699#action_12718699 ] 

Min Zhou commented on HIVE-556:
-------------------------------

@Ashish
I agree with you, map-side joins is okay. however,  it doesnot support theta joins right now.  we used to load aux tables into the memory of each map node,  scan major tables and do our joins. 

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Min Zhou (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718687#action_12718687 ] 

Min Zhou commented on HIVE-556:
-------------------------------

it's very common for us, and blocked us badly. we ofen have one or more aux tables with about 10k records, which the major table would do theta joins on. I don't think current solution by the means of cartesian product is a good way, it would bring so terrible  sorting and i/o overhead to us.


> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Min Zhou (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12719518#action_12719518 ] 

Min Zhou commented on HIVE-556:
-------------------------------

I didn't see any filter there,  hive will put all fields of my small table into HTree.

{noformat}
hive>explain select /*+ MAPJOIN(a) */ a.url_pattern, w.url from application a join web_log w where w.logdate='20090611' and w.url rlike a.url_pattern and a.dt='20090609';

Common Join Operator
              condition map:
                   Inner Join 0 to 1
              condition expressions:
                0 {bussiness_id} {subclass_id} {class_id} {note} {name} {url_pattern} {dt}
                1....
{noformat}

We only put a.url_pattern into a HashMap in our raw map-reduce implemenation.

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Commented: (HIVE-556) let hive support theta join

Posted by "Ashish Thusoo (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718689#action_12718689 ] 

Ashish Thusoo commented on HIVE-556:
------------------------------------

I think with map-side joins this becomes quite easy. How would you do it with a normal map-reduce join without actually doing a cartesian product at some level?

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>             Fix For: 0.4.0
>
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


[jira] Updated: (HIVE-556) let hive support theta join

Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/HIVE-556?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Namit Jain updated HIVE-556:
----------------------------

    Fix Version/s:     (was: 0.5.0)

> let hive support theta join
> ---------------------------
>
>                 Key: HIVE-556
>                 URL: https://issues.apache.org/jira/browse/HIVE-556
>             Project: Hadoop Hive
>          Issue Type: New Feature
>    Affects Versions: 0.4.0
>            Reporter: Min Zhou
>
> Right now , hive only support equal joins .  Sometimes it's not enough, we must consider implementing theta joins like
> {code:sql}
> 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';
> {code}
> any condition expression following 'ON' is  appropriate.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.