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.\n

Create Materialized view example

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

Materialized view with partitioning and clustering

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

Materialized view with options

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

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 }\n[[project_name.]dataset_name.]materialized_view_name\n[PARTITION BY partition_expression]\n[CLUSTER BY clustering_column_list]\n[OPTIONS(materialized_view_option_list)]\nAS query_expression