[
https://issues.apache.org/jira/browse/CALCITE-1787?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16031215#comment-16031215
]
Jesus Camacho Rodriguez edited comment on CALCITE-1787 at 6/3/17 10:05 AM:
---------------------------------------------------------------------------
[~zhumayun], [~bslim], if I understand correctly, the query you are talking
about could be expressed in SQL with CTEs:
{code:sql}
WITH cte AS
(
SELECT state, SUM(sales) AS state_sales
FROM table_x
WHERE state = 'CA' OR state = 'NY'
GROUP BY state
)
SELECT (cte1.state_sales / cte2.state_sales) as ratio
FROM cte cte1 JOIN cte cte2
WHERE cte1.state = 'CA' AND cte2.state = 'NY'
{code}
Problem is that currently CTEs are expanded by the optimizer, thus you end up
executing the WITH clause twice. It seems till CALCITE-481 is completed and
Spool operator introduced in Calcite, we will not be able to push this kind of
queries to Druid.
Back to [~zhumayun] work and due to the complexity of the extension, I was
wondering if this patch should be already checked in? [~bslim], do you have
concerns about the correctness or is it about the filtering extension? If I
understand correctly the discussion above, the only issue is that we will not
be pushing some of the filters to Druid, but maybe I am mistaken.
If we do not produce incorrect results and we can get significant gains in some
cases, I am inclined to check it in already.
EDIT:
Related to CALCITE-1828, I did not realize this could be make much
simpler/efficient without CTE as:
{code:sql}
SELECT (
SUM(CASE WHEN state = 'CA' THEN state_sales END) /
SUM(CASE WHEN state = 'NY' THEN state_sales END)
) as ratio
FROM table_x
GROUP BY state
{code}
was (Author: jcamachorodriguez):
[~zhumayun], [~bslim], if I understand correctly, the query you are talking
about could be expressed in SQL with CTEs:
{code:sql}
WITH cte AS
(
SELECT state, SUM(sales) AS state_sales
FROM table_x
WHERE state = 'CA' OR state = 'NY'
GROUP BY state
)
SELECT (cte1.state_sales / cte2.state_sales) as ratio
FROM cte cte1 JOIN cte cte2
WHERE cte1.state = 'CA' AND cte2.state = 'NY'
{code}
Problem is that currently CTEs are expanded by the optimizer, thus you end up
executing the WITH clause twice. It seems till CALCITE-481 is completed and
Spool operator introduced in Calcite, we will not be able to push this kind of
queries to Druid.
Back to [~zhumayun] work and due to the complexity of the extension, I was
wondering if this patch should be already checked in? [~bslim], do you have
concerns about the correctness or is it about the filtering extension? If I
understand correctly the discussion above, the only issue is that we will not
be pushing some of the filters to Druid, but maybe I am mistaken.
If we do not produce incorrect results and we can get significant gains in some
cases, I am inclined to check it in already.
> thetaSketch Support for Druid Adapter
> -------------------------------------
>
> Key: CALCITE-1787
> URL: https://issues.apache.org/jira/browse/CALCITE-1787
> Project: Calcite
> Issue Type: New Feature
> Components: druid
> Affects Versions: 1.12.0
> Reporter: Zain Humayun
> Assignee: Zain Humayun
> Priority: Minor
>
> Currently, the Druid adapter does not support the
> [thetaSketch|http://druid.io/docs/latest/development/extensions-core/datasketches-aggregators.html]
> aggregate type, which is used to measure the cardinality of a column
> quickly. Many Druid instances support theta sketches, so I think it would be
> a nice feature to have.
> I've been looking at the Druid adapter, and propose we add a new DruidType
> called {{thetaSketch}} and then add logic in the {{getJsonAggregation}}
> method in class {{DruidQuery}} to generate the {{thetaSketch}} aggregate.
> This will require accessing information about the columns (what data type
> they are) so that the thetaSketch aggregate is only produced if the column's
> type is {{thetaSketch}}.
> Also, I've noticed that a {{hyperUnique}} DruidType is currently defined, but
> a {{hyperUnique}} aggregate is never produced. Since both are approximate
> aggregators, I could also couple in the logic for {{hyperUnique}}.
> I'd love to hear your thoughts on my approach, and any suggestions you have
> for this feature.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
