SQL: support rollup mode [COUNT() -> SUM(count), AVG() -> SUM() / SUM(count)]
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### Motivation
In my company, we use Druid as our OLAP engine that enables business users to analyze large quantities of data in real-time. However, druid will rollup data(if enable) at ingestion time. So when our users use `count()` or `avg()` in their sql, they often get unexpected results. Our users are not familiar with druid, it's hard for them to write a sql that will work as expected. So it would be better that druid support rollup mode, i.e. rewrite COUNT() to SUM(count), make it transparent to users.
### Proposed solution
Add a boolean property `rollup` in `PlannerConfig` to indicate whether using the rollup mode or not. If it's true, in `CountSqlAggregator#toDruidAggregation` create a `LongSumAggregatorFactory` instead of a `CountAggregatorFactory`.
In `DruidSchema#analysisToRowSignature`, we can get aggregators info and find the defined count metric name, then using this name to construct a `LongSumAggregatorFactory`. However, currently I could not infer which one is the count aggregator from segment metadata query result, the related issue is #6578.
In order to support rollup mode, #6578 must be fixed first. Looking forward to your comments.
Contributor guide
Research direction
Start with CountSqlAggregator#toDruidAggregation and DruidSchema#analysisToRowSignature, then review the metadata limitation described in #6578. Confirm how the count metric is identified before designing rollup handling for COUNT() and AVG(); done means rollup mode consistently produces the intended aggregate results.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- data, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100