cube-js/cube

Cannot use use_original_sql_pre_aggregations on Postgres

Open

#7,421 opened on 2023年11月15日

GitHub で見る
 (5 comments) (1 reaction) (0 assignees)Rust (1,965 forks)batch import
driver:postgreshelp wantedpre-aggregations

Repository metrics

Stars
 (19,563 stars)
PR merge metrics
 (平均マージ 5d 16h) (30d で 138 merged PRs)

説明

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

コントリビューターガイド