Cannot use use_original_sql_pre_aggregations on Postgres
#7.421 aberto em 15 de nov. de 2023
Métricas do repositório
- Stars
- (19.563 stars)
- Métricas de merge de PR
- (Mesclagem média 5d 16h) (138 fundiu PRs em 30d)
Description
Describe the bug
We would like to reuse an original_sql pre-aggregation stored in Postgres in a rollup pre-aggregation. Setting use_original_sql_pre_aggregations: true fails with the following error message during scheduled refresh:
"... FROM pre_agg_tenant_123.base_positions_main AS "main__base_positions"..." {"message":"Refresh Scheduler Error","error":"Error: relation "pre_agg_tenant_123.base_positions_main" does not exist",
When I introspect the database, I can see that Cube appends certain hashes to the original_sql table name. Those hashes are missing in the sql query to the database, which causes it to fail.
Expected behavior It should be possible to reuse the original_sql pre-aggregation stored in Postgres, as stated in the docs. We have a heavy sql query in the original data model.
Minimally reproducible Cube Schema
cubes:
- name: base_positions
public: false
sql: SELECT "id_1" AS position_id, "test" AS position_name
measures:
- name: position_count
type: count
dimensions:
- name: position_id
sql: position_id
type: string
primary_key: true
- name: position_name
sql: position_name
type: string
- name: position_date
sql: "date"
type: time
pre_aggregations:
- name: main
type: original_sql
external: false
- name: positions_per_month
measures:
- CUBE.position_count
time_dimension: CUBE.position_date
granularity: month
use_original_sql_pre_aggregations: true
Version: 0.34.19