phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-418) Support approximate COUNT DISTINCT
Date Mon, 31 Jul 2017 19:29:00 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-418?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16107832#comment-16107832
] 

Julian Hyde commented on PHOENIX-418:
-------------------------------------

In CALCITE-1588 [~gian] points out that Oracle, BigQuery and MemSQL support {{APPROX_COUNT_DISTINCT}}.
I also see it in VoltDB.

A quick survey of other databases:
* Vertica has {{APPROXIMATE_COUNT_DISTINCT}}
* Redshift has {{[ APPROXIMATE ] COUNT ( [ DISTINCT | ALL ] * | expression )}}.
* In PostgreSQL you can bolt on your own hyperloglog function, but there doesn't seem to have
a unified approach.
* I don't see anything in DB2 or MySQL

I think that is a sufficient de facto standard to support {{APPROX_COUNT_DISTINCT}} in Calcite.

Also I think Calcite should support an APPROXIMATE clause. Allowed both as a clause in the
SELECT statement, and also after the aggregate function (but before the OVER clause, if present).
Algorithm and any parameters go inside parentheses. Examples:

{code}
select count(distinct name)
from person
APPROXIMATE ()

select count(distinct name)
from person
APPROXIMATE (ALGORITHM 'hll')

select count(distinct name)
from person
APPROXIMATE (ALGORITHM 'ABC' WITHIN 10 PERCENT)

select count(distinct name) APPROXIMATE ()
from person

select count(distinct name) APPROXIMATE (ALGORITHM 'hll')
from person

select count(distinct name)
  APPROXIMATE (ALGORITHM 'ABC' WITHIN 10 PERCENT)
from person 
{code}

For now, I think Phoenix should support APPROX_COUNT_DISTINCT. We could add support for the
APPROXIMATE clause later.

> Support approximate COUNT DISTINCT
> ----------------------------------
>
>                 Key: PHOENIX-418
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-418
>             Project: Phoenix
>          Issue Type: Task
>            Reporter: James Taylor
>            Assignee: Ethan Wang
>              Labels: gsoc2016
>
> Support an "approximation" of count distinct to prevent having to hold on to all distinct
values (since this will not scale well when the number of distinct values is huge). The Apache
Drill folks have had some interesting discussions on this [here](http://mail-archives.apache.org/mod_mbox/incubator-drill-dev/201306.mbox/%3CJIRA.12650169.1369931282407.88049.1370645900553%40arcas%3E).
They recommend using  [Welford's method](http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance_Online_algorithm).
I'm open to having a config option that uses exact versus approximate. I don't have experience
implementing an approximate implementation, so I'm not sure how much state is required to
keep on the server and return to the client (other than realizing it'd be much less that returning
all distinct values and their counts).



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message