cube-js/cube

Cannot use use_original_sql_pre_aggregations on Postgres

Open

#7421 aperta il 15 nov 2023

Vedi su GitHub
 (5 commenti) (1 reazione) (0 assegnatari)Rust (1965 fork)batch import
driver:postgreshelp wantedpre-aggregations

Metriche repository

Star
 (19.563 star)
Metriche merge PR
 (Merge medio 5g 16h) (138 PR mergiate in 30 g)

Descrizione

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

Guida contributor