gabotorresruiz opened a new pull request, #45150:
URL: https://github.com/apache/superset/pull/45150
### SUMMARY
Virtual datasets over BigQuery tables with STRUCT (RECORD) columns only
register the top-level columns.
The STRUCT shows up as a single untyped `RECORD` column, its cells render as
JSON strings, and its fields cannot be used as dimensions, filters or time
columns.
A physical dataset over the same table gets one dotted column per field
(`calendar.day`, `calendar.client.week_number`, ...).
#### Root cause
- Physical datasets get their columns from the SQLAlchemy inspector, and
`sqlalchemy-bigquery` flattens non-repeated STRUCT fields into dotted columns.
- Virtual datasets get their columns from `get_columns_description`, which
builds them from the DB-API `cursor.description` of a metadata probe.
- The BigQuery DB-API builds `cursor.description` from the top-level schema
fields only, so the nested fields never reach Superset.
#### Fix
- New `BaseEngineSpec.expand_nested_columns(cursor, columns)` hook, called
by `get_columns_description` on the probe's columns.
The default returns the columns unchanged, so every other engine is
untouched.
- `BigQueryEngineSpec` implements it by reading the result schema the DB-API
cursor keeps for its last `execute()`, and appends one dotted column per
non-repeated STRUCT field right after its parent.
This is the same set and order of names the inspector returns for physical
tables.
- Repeated STRUCT columns (`ARRAY<STRUCT<...>>`) are listed but not
expanded, because `a.b` is not selectable on them, again matching the inspector.
- A selected column that already has a member's name (an alias) is kept and
the member is skipped, so there are no duplicate names.
- Types, `type_generic` and `is_dttm` of the new columns come from the
engine spec's column specs, so a nested `DATE` field is a temporal column.
#### Calculated columns named like a STRUCT field
Until now the way to use a STRUCT field on a virtual dataset was a
calculated column named like the field, for example `calendar.day` with
expression `UPPER(calendar.day)`.
`fetch_metadata` matches source columns to existing columns by name, so
without care the first refresh after this change would turn such a calculated
column into a plain column and drop its expression.
- The expanded columns carry a `nested_field` flag on `ResultSetColumnType`.
- `fetch_metadata` skips a flagged column whose name belongs to an existing
calculated column, so the calculated column keeps its expression and no
duplicate is created.
- Any other source column still replaces a calculated column of the same
name, as before (covered by
`test_fetch_metadata_source_column_replaces_calculated_column`).
- The flag is never persisted: `TableColumn` is built from explicit fields,
and the frontend sync builds new columns from explicit fields too.
#### Design notes
- Expansion is unconditional for BigQuery because the BigQuery inspector
already flattens STRUCT fields unconditionally for physical datasets.
Trino's ROW expansion stays where it is (inspector path, opt in through
`expand_rows`); Trino could implement the same hook for virtual datasets in a
follow-up.
- Existing virtual datasets are not migrated.
They gain the new columns the next time their columns are synced or
refreshed; nothing is removed or retyped.
### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF
#### Before
Virtual dataset over `SELECT * FROM struct_repro`: 4 columns, `calendar` is
an untyped `RECORD`.
<!-- before-02-virtual-columns.png -->
Explore: `calendar` cells are JSON strings and no `calendar.*` column can be
picked.
<!-- before-04-explore-calendar-json.png -->
#### After
"Sync columns from source" adds the 6 STRUCT fields and keeps the original 4
columns.
<!-- after-01-virtual-sync-added.png -->
`calendar.day` as a dimension with a filter on `calendar.client.week_number`.
<!-- after-03-explore-calendar-day-dimension.png -->
A calculated column `calendar.day` = `UPPER(calendar.day)` keeps its
expression after a sync.
<!-- after-06-calculated-column-coexists.png -->
### TESTING INSTRUCTIONS
#### Unit tests
```bash
pytest tests/unit_tests/db_engine_specs/test_bigquery.py \
tests/unit_tests/db_engine_specs/test_base.py \
tests/unit_tests/connectors/sqla/utils_test.py \
tests/unit_tests/connectors/sqla/models_test.py
```
#### Manual
1. In a BigQuery project (or the `goccy/bigquery-emulator`), create a table
with a nested STRUCT:
```sql
CREATE TABLE my_dataset.struct_repro AS
SELECT
d AS date,
STRUCT(
EXTRACT(DAY FROM d) AS day_num_of_month,
FORMAT_DATE('%A', d) AS day,
STRUCT(
EXTRACT(WEEK FROM d) AS week_number,
DATE_TRUNC(d, WEEK) AS week_start_date
) AS client
) AS calendar,
'USD' AS currency,
1 AS event_count
FROM UNNEST(GENERATE_DATE_ARRAY('2025-01-01', '2025-12-31')) AS d
```
2. In SQL Lab run `SELECT * FROM my_dataset.struct_repro` and save it as a
dataset.
3. Edit the dataset and open Columns.
Expect `calendar.day_num_of_month`, `calendar.day`, `calendar.client`,
`calendar.client.week_number` and `calendar.client.week_start_date` next to the
top-level columns, with `calendar.client.week_start_date` marked temporal.
4. Build a Table chart with `calendar.day` as a dimension and a filter on
`calendar.client.week_number`.
View query shows `` `calendar`.`day` `` and ``
`calendar`.`client`.`week_number` ``.
5. Build a line chart with `calendar.client.week_start_date` on the x axis
and a weekly time grain.
6. On a virtual dataset created before this change, add a calculated column
`calendar.day` with expression `UPPER(calendar.day)`, then run "Sync columns
from source" and Save, and also call `PUT /api/v1/dataset/<id>/refresh`.
The calculated column keeps its expression, the other STRUCT fields are
added, and there is a single `calendar.day`.
7. Regression checks: a physical dataset over the same table, SQL Lab
results, and a virtual dataset on another engine are unchanged.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Migration is atomic, supports rollback & is backwards-compatible
- [ ] Confirm DB migration upgrade and downgrade tested
- [ ] Runtime estimates and downtime expectations provided
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
