cube-js/cube

Data model generation with postgres and numeric types

Open

#8,149 opened on Apr 15, 2024

View on GitHub
 (2 comments) (0 reactions) (0 assignees)Rust (19,563 stars) (1,965 forks)batch import
driver:postgreshelp wanted

Description

Hi cube.js devs!

Using cube version 0.35.6, I am traying to generate a model for a postgres database using the playground. When generating model for tables with numeric types, ie:

onln=# \d acm_coin_in_statistics2
                      Table "public.acm_coin_in_statistics2"
        Column         |           Type           | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
 device_id             | numeric(10,0)            |           | not null |
 game_id               | numeric(5,0)             |           | not null |
 statistics_date       | timestamp with time zone |           | not null |
 hour_00               | numeric(10,0)            |           |          |
 hour_01               | numeric(10,0)            |           |          |
...
Indexes:
    "acm_coin_in_statistics2_pk" PRIMARY KEY, btree (device_id, game_id, statistics_date)

dimension for numeric values are generated as strings and primary key is not extracted correctly. If I use other data types (for example, integer, everithing works fine)

cubes:
  - name: acm_coin_in_statistics
    sql_table: public.acm_coin_in_statistics
    data_source: default

    joins: []

    dimensions:
      - name: game_id
        sql: game_id
        type: string

      - name: device_id
        sql: device_id
        type: string
		
      - name: statistics_date
        sql: statistics_date
        type: time

      - name: hour_02
        sql: hour_02
        type: string
		
	  - name: hour_01
        sql: hour_01
        type: string

Is this the expected behaviour of the current version?

Thank you in advance! Miguel

Contributor guide

Tech stack
postgresqlrustjavascript
Domain
backenddatabase
Issue type
bug
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
3
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-3 hours
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
active
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
clear
Prerequisites
Basic understanding of cube.js data modelFamiliarity with PostgreSQL numeric typesAbility to read Rust codeKnowledge of SQL type mapping
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
60
Research direction
Investigate the data model generation logic for PostgreSQL in the cube js codebase, particularly the type mapping from SQL numeric types to cube dimension types. Look for files related to SQL introspection or schema generation in the Rust backend (e.g., in the `cube js/cube` repository under `packages/cubejs backend` or similar). The issue suggests that numeric columns are incorrectly mapped to 'string' type; the fix would involve updating the type mapping to produce 'number' dimensions for numeric columns and correctly identifying primary keys. Check the comments on the issue for any maintainer guidance or existing PRs.