[FEATURE] ClickHouse catalog: preserve PROJECTION definitions on round-trip
- Dominant language
- Java
- Stars
- 3.2k
- Forks
- 935
- Avg merge
- 1d 16h
- Merged PRs (30d)
- 298
Description
### Describe the feature
ClickHouse's `PROJECTION` is a pre-computed materialized view defined inline within the `CREATE TABLE` statement. When loading a table that contains PROJECTIONs through the ClickHouse catalog, the PROJECTION definitions are silently lost — no warning, no error. The definitions simply do not appear in the returned schema.
PROJECTION has two types: `Normal` (non-aggregating, may include `ORDER BY`) and `Aggregate` (with `GROUP BY`). Both types need to be preserved.
### Motivation
PROJECTION is a ClickHouse-specific optimization that pre-computes and stores aggregated or differently-ordered data, improving performance for certain query patterns. With PROJECTION support missing, table schemas round-tripped through Gravitino lose all PROJECTION definitions permanently. Users who rely on Gravitino for metadata management cannot use this ClickHouse feature.
### Describe the solution
Expose PROJECTION definitions as part of the Gravitino table model so they survive round-trips. The exact representation needs design discussion — options include storing PROJECTION definitions in `table.properties()` or adding a new API construct.
This depends on a related bug fix that prevents sort-key parsing corruption when PROJECTIONs contain `ORDER BY`.
### Test plan
| # | Scenario | Expected after fix |
|---|----------|-------------------|
| 1 | Table with 1 Normal PROJECTION → loadTable → createTable | PROJECTION definition preserved in round-trip |
| 2 | Table with 1 Aggregate PROJECTION → loadTable → createTable | PROJECTION definition preserved in round-trip |
| 3 | Table with multiple PROJECTIONs (Normal + Aggregate) | all PROJECTIONs preserved |
| 4 | Table without PROJECTION → loadTable → createTable | no regression (no spurious PROJECTION appears) |
| 5 | PROJECTION with quoted identifiers / special characters in name | definition preserved correctly |
### Additional context
**Test results** (ClickHouse + Gravitino 1.2.0, before fix):
| # | Scenario | Result |
|---|----------|:------:|
| 1 | Normal PROJECTION `(SELECT id, dt ORDER BY dt)` → loadTable | ❌ PROJECTION not in response, sortOrders garbled (tracked separately as a bug) |
| 2 | Aggregate PROJECTION `(SELECT name, sum(amount) GROUP BY name)` → loadTable | ❌ PROJECTION not in response |
| 3 | 2 Normal PROJECTIONs on same table → loadTable | ❌ both lost |
| 4 | Table w/o PROJECTION → loadTable | ✅ works, no PROJECTION-related noise |
Example of lost PROJECTION:
```sql
-- SHOW CREATE TABLE output:
CREATE TABLE t (...)
PROJECTION p1 (SELECT id, dt, amount ORDER BY dt)
-- Gravitino loadTable response:
{
"columns": [{"name":"id",...},{"name":"dt",...},{"name":"amount",...}],
"indexes": [{"indexType":"PRIMARY_KEY","name":"PRIMARY","fieldNames":[["id"]]}]
}
-- PROJECTION p1 is absent from the entire response
```
I'd like to pick this up. Please let me know if you have any feedback.
Contributor guide
Research direction
Start at the ClickHouse catalog's loadTable and createTable entry points, comparing SHOW CREATE TABLE output with the returned Gravitino schema. Review the proposed table.properties or new API representation and the related sort-key parsing bug before settling the design. Done means Normal, Aggregate, multiple, quoted-name, and no-PROJECTION round-trips preserve the expected definitions without regressions.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, java
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Needs clarification
- Newbie friendliness
- 38/100