BigQuery Create Materialized view

In BigQuery, materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency. BigQuery leverages precomputed results from materialized views and whenever possible reads only delta changes from the base table to compute up-to-date results. Materialized views can be queried directly or can be used by the BigQuery optimizer to process queries to the base tables.

Create Materialized view example

    CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
    AS SELECT column_1, SUM(column_2) AS sum_2, AVG(column_3) AS avg_3
    FROM `myproject.mydataset.mytable`
    GROUP BY column_1

Materialized view with partitioning and clustering

    CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
    PARTITION BY DATE(col_datetime)
    CLUSTER BY col_int
    AS SELECT col_int, col_datetime, COUNT(1) as cnt
       FROM `myproject.mydataset.mv_base_table`
       GROUP BY col_int, col_datetime

Materialized view with options

    CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
    OPTIONS(
      expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
      friendly_name="new_mv",
      description="a materialized view that expires in 2 days",
      labels=[("org_unit", "development")],
      enable_refresh=true,
      refresh_interval_minutes=20
    )
    AS SELECT column_1, SUM(column_2) AS sum_2, AVG(column_3) AS avg_3
    FROM `myproject.mydataset.mytable`
    GROUP BY column_1

Explanation

Queries that use materialized views are generally faster and consume fewer resources than queries that retrieve the same data only from the base table. Materialized views can significantly improve the performance of workloads that have the characteristic of common and repeated queries.

Materialized views has these limitations

Syntax reference

{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS }
[[project_name.]dataset_name.]materialized_view_name
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(materialized_view_option_list)]
AS query_expression