Support dynamic GROUP BY about mosaic HOT 4 CLOSED

To give a concrete example, we're rolling up raw data like this

CREATE TEMP TABLE domain_agg IF NOT EXISTS AS
SELECT
  requested,
  device_code,
  domain,

  AVG(rank) AS rank,
  SUM(estimated_traffic) AS estimated_traffic,
  CAST(AVG(pixels_from_top) AS INT64) AS pixels_from_top,
  AVG(above_the_fold_percentage) AS above_the_fold_percentage,
  AVG(serp_percentage) AS serp_percentage,

FROM 'http://localhost:3002/mosaic-data.parquet'
GROUP BY requested, device_code, domain
ORDER BY requested, device_code, domain

Given a UI toggle with these three fields available in the parquet file:

• domain
• subdomain
• url

Do we have to make separate subdomain_agg and url_agg temp tables? Is there a way to do that lazily?

from mosaic.

Not yet sure if this is best viewed as a Mosaic question or a DuckDB question... at the DuckDB level, maybe take a look at GROUPING_SETS to see if you can build the table you want in a single go and then simply issue filtering queries depending on the desired groupby dimensions?

There is not currently support for dynamic generation of tables in Mosaic itself, but you could do it externally. Other than data loading operations (which are there for convenience and decoupled from all downstream operations) and internally generated data cube indexes, Mosaic doesn't currently generate its own tables.

from mosaic.

Thanks. We're definitely able to handle that dynamic generation from a workflow perspective. Given the issue about adding multi-table support, I wasn't sure if there was a lifecycle requirement we needed to tie into. We're still figuring out where the coordinator ends and DuckDB begins.

from mosaic.

I'll close this issue for now, and once we get a bit deeper, we can contribute to docs and/or examples showing something like this.

from mosaic.