cube-js / cube-js/cube

Add WITH FILL modifier to Clickhouse driver

Open
#6,893 2 comments 1 reaction 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Is your feature request related to a problem? Please describe.**
When running date ranged queries it is often advantageous to provide data for the entire date range.

Cube already does this partially with [`fillMissingDates`](https://cube.dev/docs/@cubejs-client-core) but this only fills between two data points. It doesn't span the entire date range provided unless you have data at the very beginning and end of the date range.

This can cause confusion and misrepresentation when using graphs because the time axis doesn't make use of the full date range selected:

![image](https://github.com/cube-js/cube/assets/1901969/6cf5721a-9e6c-4779-93b0-5fe6b42fea54)

Clickhouse provides a [`WITH FILL` modifier](https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier) which provides this functionality out-of-the-box, but the current driver doesn't support applying the modifier.

**Describe the solution you'd like**
To support filling the entire date range provided, I would propose updating `fillMissingDates` to span the entire date range. This would most likely entail changes to https://github.com/cube-js/cube/blob/master/packages/cubejs-schema-compiler/src/adapter/ClickHouseQuery.js or https://github.com/cube-js/cube/blob/master/packages/cubejs-clickhouse-driver/src/ClickHouseDriver.ts.

This change would be Clickhouse driver specific as I'm not sure if all the supported drivers have equivalent `WITH FILL` modifiers. Using `fillMissingDates` may be the most appropriate as its existing functionality, only being augmented for Clickhouse. Documentation would be need to be updated to describe the enhancement though.

Alternatively, to maintain backwards compatibility, a new pivot option could be used for the same purpose. Although, I'm not sure if having a pivot option for one driver is a viable solution.

I would be more than happy to contribute a PR if we can agree on an acceptable path forward.

**Describe alternatives you've considered**
We have written a [transformer layer](https://gitlab.com/gitlab-org/gitlab/-/issues/415687) in Ruby on Rails which takes the data returned by Cube and fills in the missing dates. However, this isn't really scalable as it's all done in memory whilst Clickhouse is designed for this kind of work. Our transformer also doesn't support the more complex query options provided by Cube.

**Additional context**
https://gitlab.com/gitlab-org/gitlab/-/issues/417231

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.