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/05/21 13:49:45 UTC

[jira] Created: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

improvement on distinct: distinguish distinct aggregate function from distinct
------------------------------------------------------------------------------

                 Key: HIVE-503
                 URL: https://issues.apache.org/jira/browse/HIVE-503
             Project: Hadoop Hive
          Issue Type: Improvement
            Reporter: Min Zhou


distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col% 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col2 % 9)
from 
  tbl
{code}

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.


  was:
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could do two different aggregate expression simultaneously.



> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
>  distinguish distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
h4.distinct
# OK
{code:sql}
select 
   distinct col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   distinct  col1,
   distinct  col2
from 
  tbl
{code}

h4.distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.


  was:
h4.distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

h4.distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.



> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could do two different aggregate expression simultaneously.


  was:
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.



> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
>  distinguish distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could do two different aggregate expression simultaneously.

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


[jira] Commented: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou commented on HIVE-503:
-------------------------------

Is that intermediate data outputed by mappers for shuffling the data you mentioned? Could you explain why that amount  brought by your second approach was m+n  which I thought may be mn too?

btw, I think you must caculate in addition the overhead of  at least one piece of join query when you finally merge your m distincted results into one table. That isnot the first approach needed.

> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Issue Comment Edited: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou edited comment on HIVE-503 at 5/22/09 1:17 AM:
--------------------------------------------------------

oops, my mistake. 2 shoule be select  distinct col1, distinct col2, which will fail.

okay,  let 4 for example: 

mapper output: 
{noformat}
<other_key + '1' + "col1%10",  value>
<other_key + '2 + "col2%9",  value>
{noformat}

after doing distinct,  then redcuer:
{noformat}
if(1) {
  count1 ++
} else {
  count2 ++
}
{noformat}
reducer output: 
<count1 + delimiter + count2, ...>

      was (Author: coderplay):
    oops, my mistake. 2 shoule be select  distinct col1, distinct col2, which will fail.

okay,  let 4 for example: 

mapper output: 
<other_key + '1' + "col1%10",  value>
<other_key + '2 + "col2%9",  value>

after doing distinct,  then redcuer:
if(1) {
  count1 ++
} else {
  count2 ++
}
reducer output: 
<count1 + delimiter + count2, ...>
  
> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different cols' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.


  was:
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

Distinct aggregate function is in connection with the all aggregate function,  it essentially is an aggregate function. 
Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.



> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
>  distinguish distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different cols' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.

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


[jira] Issue Comment Edited: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou edited comment on HIVE-503 at 5/22/09 6:00 AM:
--------------------------------------------------------

oops, my mistake. 2 shoule be select  distinct col1, distinct col2, which will fail.

okay,  take 4 for example: 

mapper output: 
{noformat}
<other_key + '1' + "col1%10",  value>
<other_key + '2 + "col2%9",  value>
{noformat}

after doing distinct,  then redcuer:
{noformat}
if(1) {
  sum_result += ..
} else {
  count_result++
}
{noformat}
reducer output: 
<sum_result + delimiter + count_result, ...>

      was (Author: coderplay):
    oops, my mistake. 2 shoule be select  distinct col1, distinct col2, which will fail.

okay,  let 4 for example: 

mapper output: 
{noformat}
<other_key + '1' + "col1%10",  value>
<other_key + '2 + "col2%9",  value>
{noformat}

after doing distinct,  then redcuer:
{noformat}
if(1) {
  count1 ++
} else {
  count2 ++
}
{noformat}
reducer output: 
<count1 + delimiter + count2, ...>
  
> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
h4.distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

h4.distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.


  was:
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.



> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Commented: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou commented on HIVE-503:
-------------------------------

consider about a real world example

Approach 1: 
{code:sql}
set hive.groupby.skewindata=false;

create table rslt_tbg_pvuv (tbg STRING, memeber_pv int, guest_pv int, memeber_uv int, guest_uv int);
INSERT OVERWRITE TABLE rslt_tbg_pvuv 
SELECT
  tbg,
  cast(sum(CASE WHEN is_member=0 THEN 1 ELSE 0 END) AS int) AS member_pv,
  cast(sum(CASE WHEN is_member=1 THEN 1 ELSE 0 END) AS int) AS guest_pv,
  count(distinct CASE WHEN is_member=0 THEN id END) AS member_uv,
  count(distinct CASE WHEN is_member=1 THEN id END) AS guest_uv
FROM 
  (SELECT
    regexp_extract(url, 'http://.*TBG=([0-9]+\.[0-9]+\.[0-9]+\.[0-9]+).*', 1) as tbg,
    if(uid <> '-', 0, 1) as is_member,
    if(uid <> '-', uid, mid) as id
  FROM
    web_log
  WHERE
    (  url rlike 'http://.*TBG=1.3.1.1'
    OR url rlike 'http://.*TBG=1.3.2.2'
    OR url rlike 'http://.*TBG=1.3.3.3'
    OR url rlike 'http://.*TBG=1.3.4.4'
    OR url rlike 'http://.*TBG=1.3.5.5'
    OR url rlike 'http://.*TBG=1.3.6.6'
    OR url rlike 'http://.*TBG=1.3.7.7' )
    AND refer like '/1.gif?%'
    AND logdate='20090510')
GROUP BY tbg;
{code}

Approach 2:
{code:sql}
set hive.groupby.skewindata=false;

create table tmp_tbg_pvuv_1 (tbg STRING, is_member tinyint, id STRING);
INSERT OVERWRITE TABLE tmp_tbg_pvuv_1
SELECT
  regexp_extract(url, 'http://.*TBG=([0-9]+\.[0-9]+\.[0-9]+\.[0-9]+).*', 1) as tbg,
  if(uid <> '-', 0, 1) as is_member,
  if(uid <> '-', uid, mid) as id
FROM
  web_log
WHERE
  (  url rlike 'http://.*TBG=1.3.1.1'
  OR url rlike 'http://.*TBG=1.3.2.2'
  OR url rlike 'http://.*TBG=1.3.3.3'
  OR url rlike 'http://.*TBG=1.3.4.4'
  OR url rlike 'http://.*TBG=1.3.5.5'
  OR url rlike 'http://.*TBG=1.3.6.6'
  OR url rlike 'http://.*TBG=1.3.7.7' )
 AND refer like '/1.gif?%'
 AND logdate='20090510';

create table tmp_tbg_pvuv_2 (tbg STRING, member_pv int, guest_pv int);
INSERT OVERWRITE TABLE tmp_tbg_pvuv_2
SELECT
  tbg,
  cast(sum(CASE WHEN is_member=0 THEN 1 ELSE 0 END) AS int) AS member_pv,
  cast(sum(CASE WHEN is_member=1 THEN 1 ELSE 0 END) AS int) AS guest_pv
FROM 
 tmp_tbg_pvuv_1
GROUP BY tbg

create table tmp_tbg_pvuv_3 (tbg STRING, member_uv int);
INSERT OVERWRITE TABLE tmp_tbg_pvuv_3
SELECT
  tbg,
  count(distinct CASE WHEN is_member=0 THEN id ELSE 'NULL' END) - 1 AS member_uv
FROM 
 tmp_tbg_pvuv_1
GROUP BY tbg

create table tmp_tbg_pvuv_4 (tbg STRING, guest_uv int);
INSERT OVERWRITE TABLE tmp_tbg_pvuv_4
SELECT
  tbg,
  count(distinct CASE WHEN is_member=1 THEN id ELSE 'NULL' END) - 1 AS guest_uv
FROM 
 tmp_tbg_pvuv_1
GROUP BY tbg

create table rslt_tbg_pvuv (tbg STRING, memeber_pv int, guest_pv int, memeber_uv int, guest_uv int);
INSERT OVERWRITE TABLE rslt_tbg_pvuv
SELECT
  t2.tbg,
  member_pv,
  guest_pv,
  member_uv,
  guest_uv
FROM
    tmp_tbg_pvuv_2 t2 
    join tmp_tbg_pvuv_3 t3 on t2.tbg=t3.tbg
    join tmp_tbg_pvuv_4 t4 on t2.tbg=t4.tbg
{code}

Do you insist the second approach is faster than the first one?


> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

Distinct aggregate function is in connection with the all aggregate function,  it essentially is an aggregate function. 
Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.


  was:
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col% 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col2 % 9)
from 
  tbl
{code}


> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
>  distinguish distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> Distinct aggregate function is in connection with the all aggregate function,  it essentially is an aggregate function. 
> Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.

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


[jira] Commented: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

Posted by "Zheng Shao (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/HIVE-503?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12711961#action_12711961 ] 

Zheng Shao commented on HIVE-503:
---------------------------------

Can you explain how to do it in one mapreduce job simultaneously?

Also, what does "distinct 1.OK and 2.Failed" mean? I don't think 2 will fail.

> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Commented: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Ashish Thusoo commented on HIVE-503:
------------------------------------

Actually we had talked about this approach a long time back but we were not sure that this would be better than running 2 map/reduce jobs. The reason being that this approach leads to a sort of mn amount of data where m is the number of distincts and n the number of rows as opposed to a sort of m+n data if we do this with m map/reduce jobs. Granted that we also scan the data mn times in the second approach as opposed to 1 time in the first approach but we find in our cluster that scan bandwidth is not an issue (mostly because we store data compressed) and the sort and memory used in the reducer or the mapper becomes the issue. I think this does call for some experimentation to determine the value of m where one approach becomes better than other..


> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Commented: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou commented on HIVE-503:
-------------------------------

oops, my mistake. 2 shoule be select  distinct col1, distinct col2, which will fail.

okay,  let 4 for example: 

mapper output: 
<other_key + '1' + "col1%10",  value>
<other_key + '2 + "col2%9",  value>

after doing distinct,  then redcuer:
if(1) {
  count1 ++
} else {
  count2 ++
}
reducer output: 
<count1 + delimiter + count2, ...>

> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> h4.distinct
> # OK
> {code:sql}
> select 
>    distinct col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    distinct  col1,
>    distinct  col2
> from 
>   tbl
> {code}
> h4.distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very possible one mapreduce job could deal with two or more different aggregate expression simultaneously.

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


[jira] Updated: (HIVE-503) improvement on distinct: distinguish distinct aggregate function from distinct

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

Min Zhou updated HIVE-503:
--------------------------

    Description: 
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.


  was:
distinct
# OK
{code:sql}
select 
   col
from 
  tbl
{code}
# FAILED
{code:sql}
select 
   col1,
   col2
from 
  tbl
{code}

 distinguish distinct aggregate function
# OK
{code:sql}
select 
   count(distinct col % 10)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1% 10)
   count(distinct col1% 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
   count(distinct col1 % 10)
   count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  sum(distinct col1 % 10),
  count(distinct col2 % 9)
from 
  tbl
{code}
# OK
{code:sql}
select 
  max(distinct substr(col1, 1, 10)),
  count(distinct col2 % 9)
from 
  tbl
{code}

The keyword "distinct" ofen produce more than one results, so it's impossible removing two different cols' duplicates in only one mapreduce job, so it failed.

But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.



> improvement on distinct: distinguish distinct aggregate function from distinct
> ------------------------------------------------------------------------------
>
>                 Key: HIVE-503
>                 URL: https://issues.apache.org/jira/browse/HIVE-503
>             Project: Hadoop Hive
>          Issue Type: Improvement
>            Reporter: Min Zhou
>
> distinct
> # OK
> {code:sql}
> select 
>    col
> from 
>   tbl
> {code}
> # FAILED
> {code:sql}
> select 
>    col1,
>    col2
> from 
>   tbl
> {code}
>  distinguish distinct aggregate function
> # OK
> {code:sql}
> select 
>    count(distinct col % 10)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1% 10)
>    count(distinct col1% 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>    count(distinct col1 % 10)
>    count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   sum(distinct col1 % 10),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> # OK
> {code:sql}
> select 
>   max(distinct substr(col1, 1, 10)),
>   count(distinct col2 % 9)
> from 
>   tbl
> {code}
> The keyword "distinct" ofen produce more than one results, so it's impossible removing two different columns' duplicates in only one mapreduce job, so it failed.
> But the term "distinct aggregate function" with a form like aggregate_function(distinct ....),  is in connection with the term "all aggregate function",  it essentially is an aggregate function. Only one result each aggregate function will produce,  it's very able one mapreduce job do two different aggregate expression simultaneously.

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