Support generating the data model for nested records in BigQuery

[sharno]

Describe the bug
I have a bigquery schema that uses nested repeated records, any reference of a field in the nested record throws an error like this:

Error: Error: Cannot access field balance on a value with type ARRAY<STRUCT<price FLOAT64, balance FLOAT64, currency_iso STRING, ...>> at [5:41]

I think for this to be solved cube.js needs to use the UNNEST sql keyword to flatten the array of records:
https://stackoverflow.com/questions/39109817/cannot-access-field-in-big-query-with-type-arraystructhitnumber-int64-time-in

The issue was mentioned at the end here, but it was closed so I opened a new one: #182

[paveltiunov]

@sharno Hey Mohamed! Thanks for posting this! For now you'd need to put this UNNEST expression in cube's sql in order to access nested fields. Even if schema generator creates these dimensions right now it can't generate right cube sql to access those.

As a first step I propose not to generate dimensions that require nested array field access. As a second step we can make generator smarter to support UNNEST sql generation.

[sharno]

Thanks Pavel, I fixed temporarily by flattening the tables ahead of time, although a smarter generator would definitely be pleasant

[ydennisy]

hey @paveltiunov I have been looking into Cube and one thing that worries me is running into any limitations with things such as BQ arrays etc, is that a possibility or due to the fact we can use raw SQL to create cubes that should not be the case?

[paveltiunov]

@ydennisy Hey Dennis! Yep. Exactly! As you can write SQL in cubes, it gives you an opportunity to flatten your arrays in whatever way you want.

[igorlukanin]

Hey everyone! I've tested how data model generation works right now, on the latest version (v0.36.2), both in Cube Core and in Cube Cloud. So, nested record columns are generated as dimensions of the string type. Querying such dimensions doesn't yield any sensible results but doesn't lead to an error as well.

I think it would be better to change this from a bug to an issue to improve the data model code generation for BigQuery.

---

Table and data:

CREATE TABLE igor_bigquery_test.table_name (
  id INT64,
  name STRING,
  address ARRAY<
    STRUCT< 
      price FLOAT64,
      balance FLOAT64,
      currency_iso STRING
    > 
  >
);

INSERT INTO `cube-devrel-team.igor_bigquery_test.table_name`
(id, name, address)
VALUES
  (1, 'Alice', [
    (1.0, 10.0, 'USD'),
    (2.0, 11.0, 'EUR'),
    (3.0, 12.0, 'YEN')
  ]),
  (2, 'Bob', [
    (1.0, 10.0, 'USD'),
    (2.0, 11.0, 'EUR'),
    (3.0, 12.0, 'YEN')
  ]),
  (3, 'Eve', [
    (1.0, 10.0, 'USD'),
    (2.0, 11.0, 'EUR'),
    (3.0, 12.0, 'YEN')
  ])

Generated data model in Cube—note that address is string:

cubes:
  - name: table_name
    sql_table: igor_bigquery_test.table_name

    joins: []

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: name
        sql: name
        type: string

      - name: address
        sql: address
        type: string

    measures:
      - name: count
        type: count

    pre_aggregations:
      # Pre-aggregation definitions go here.
      # Learn more in the documentation: https://cube.dev/docs/caching/pre-aggregations/getting-started

Query in Playground: