Search code examples
sqlgoogle-cloud-firestoregoogle-bigquery

Can we create the materialized view from the standard view in bigquery

I aim to generate a materialized view in BigQuery based on the standard view. Initially, I crafted a schema view utilizing the command below. However, I encounter errors when attempting to create the materialized view.

CREATE MATERIALIZED VIEW `${process.env.NEXT_PUBLIC_GOOGLE_PROJECT_ID}.${appConfig.BIG_QUERY_DATASET_ID}.view_name` AS
SELECT 
 document_name as document_id,
 JSON_EXTRACT(data, '$.vin') AS vin,
 JSON_EXTRACT(data, '$.attempt') AS attempt
FROM 
`${process.env.NEXT_PUBLIC_GOOGLE_PROJECT_ID}.${appConfig.BIG_QUERY_DATASET_ID}.view_name.notification_raw_latest`;

I am facing this error

The incremental materialized view query contains an unsupported feature. This may be caused by having an expression on top of the grouping keys or aggregated values.

Solution

  • Materialized views can not be created on top of traditional/logical views just yet.

    Check: https://cloud.google.com/bigquery/docs/materialized-views-intro#limitations

    If the logical view changes, then the materialized view becomes invalid and must be fully refreshed.

    Logical view reference support is in preview. For more information, see Reference logical views. [Ref:https://cloud.google.com/bigquery/docs/materialized-views-create]

    All of this may change in the future, but at this date its not natively supported.