cube-js / cube-js/cube

original_sql pre aggregation not working with ClickHouse driver when it's a Replicated db

Open
#9,074 2 comments 0 reactions 0 assignees View on GitHub
driver:clickhouse help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
Using `original_sql` pre aggregation on ClickHouse driver doesn't work, due to `CREATE AS SELECT` operation not supported with Replicated databases.

**Expected behavior**
Using `original_sql` pre aggregation type on clickhouse driver should work on any database type, including Replicated one.

**Screenshots**
![image](https://github.com/user-attachments/assets/8ea4f0e4-35ed-472a-8914-eab14c6152b7)

**Minimally reproducible Cube Schema**

```
- name: users_auto_complete_props
sql: >
select property_name, property_value, any(tenantId) as tenantId, max(date) as date from
(SELECT tenantId,
toDate(eventTime / 1000) date,
arrayJoin(JSONExtractKeys(ifNull(props, ''))) AS property_name,
JSONExtractString(props, property_name) AS property_value
From coho_db."users_replacing" where
tenantId = '{{ COMPILE_CONTEXT.securityContext.tenantId | safe }}' and
date > DATEADD(DAY,-2,today())
AND date < today() and props is not null and property_value is not null)
group by property_name, property_value

dimensions:
- name: property_name
type: string
sql: property_name

- name: date
type: time
sql: date

- name: property_value
type: string
sql: property_value

- name: tenant_id
type: string
sql: tenantId
public: false

pre_aggregations:
- name: live
type: rollup_lambda
union_with_source_data: true
rollups:
- CUBE.batch

- name: batch
type: original_sql
time_dimension: CUBE.date
partition_granularity: month
refresh_key:
every: 1 day
indexes:
- name: main_index
columns:
- tenant_id
- property_name
```

**Version:**
0.35.54

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.