Materialized views in BigQuery

A materialized view stores the result of an aggregation query and keeps it fresh. BigQuery rewrites matching queries on the base table to read it. What the rewrite is worth in bytes, what one UPDATE does to it, what a refresh costs, and which SQL is allowed.

A materialized view is a table that BigQuery fills from a query and keeps up to date. Unlike a logical view, it stores its result. A query that reads it scans the stored aggregate instead of the base table, and BigQuery adds the rows that changed since the last refresh, so the answer is as fresh as a query on the base table. BigQuery also rewrites queries on the base table to use the view when it can, which is called smart tuning. On the table we tested, a 14.4 MB aggregation dropped to 120 KB.

CREATE MATERIALIZED VIEW shop.daily_totals AS
SELECT event_date, user_id, SUM(amount) AS total, COUNT(*) AS n
FROM shop.events
GROUP BY event_date, user_id;

The tests on this page ran on a 48.8 MB table with 600,000 rows, partitioned by day into 10 partitions. The materialized view above has 5,000 rows and takes 200 KB. All byte counts are from dry runs unless we say otherwise, and a dry run repeats the rewrite logic, so it shows what a real run reads.

What the rewrite saves

Once the view is refreshed, a query on the base table that the view can answer reads the view:

The rule is Google’s: the view must contain every column and every row the query needs. MAX is not in the view. A filter on kind needs a column the view dropped when it grouped. A filter on user_id works because user_id is a grouping column of the view. Even AVG is not rebuilt from the view’s SUM and COUNT. If the query does not match, BigQuery reads the base table, and the job statistics have no materialized view entry for it.

When a query was rewritten, the job statistics list the view with chosen: true under materializedViewStatistics. The plan has a READ step for it. A dry run is the quickest test: if the estimate is small, the view is used. See how queries are billed.

You can also query the view directly, like a table. A new view that has never been refreshed and was created with enable_refresh = false is empty. A direct SELECT * on it then read the base table, 19.2 MB. After the refresh it read 200 KB.

What the view is allowed to contain

A default materialized view is incremental: BigQuery can update it from just the new rows. That limits the SQL. We ran each of these against BigQuery.

Google’s list also rules out user-defined functions, TABLESAMPLE, FOR SYSTEM_TIME AS OF, RAND(), SESSION_USER(), RIGHT and FULL OUTER JOIN, and array subqueries. UNION ALL and LEFT OUTER JOIN are in Preview. The view can’t read external tables, other materialized views, wildcard tables or snapshots, and, Google says, its query can’t be changed after creation.

COUNT(DISTINCT x) is out, but APPROX_COUNT_DISTINCT is in. See approximate aggregation.

What an UPDATE or DELETE does to it

An insert is cheap. BigQuery reads the new rows from the base table and merges them in. After we appended 2 rows to the base table, the same 120 KB aggregation cost 120,048 bytes, which is 48 more bytes for the two new rows. The answer included them.

An UPDATE, DELETE, MERGE, truncation or partition expiration on the base table is different. The stored result can’t be patched, so the view is not used until it is refreshed:

One changed row sent every query back to the full table, and a direct query on the view read the base table as well. Nothing breaks and the answers stay right. The bill is what changes.

Partitioning the view limits the damage. If the base table is partitioned, partition the view on the same column and granularity. BigQuery then tracks each partition separately. We updated one row in a day’s partition of a partitioned view:

The unpartitioned view in the same situation read the whole 14.4 MB. Google says partition expiration on the base table invalidates an unpartitioned view completely.

A second trap: when we had two views that could both answer a query, an unpartitioned one that was stale and a partitioned one that was fresh, the unfiltered query read 14.4 MB. It read 120 KB once we refreshed the stale view, and 1.55 MB with only the partitioned view left. So a stale view can stop the rewrite from using a fresh one. Drop views you don’t need.

Refresh and what it costs

A view is refreshed automatically by default, every 30 minutes (refresh_interval_minutes) and only when the base table has changed. A materialized view’s Details tab in Querylab.io shows its refresh interval, last refresh and max staleness (see Table information). A view created with default settings had its rows within seconds of CREATE. The CREATE statement itself processed 0 bytes. With enable_refresh = false the view stays empty until you refresh it:

CALL BQ.REFRESH_MATERIALIZED_VIEW('shop.daily_totals');

A refresh is a query. With on-demand pricing it bills the bytes it processes, and Google says automatic refreshes are billed to the project that owns the view, and a manual refresh to the project that runs it. With capacity pricing it uses slots. A refresh is incremental when only rows were appended, and a full recompute after an update or delete. We measured both on the unpartitioned view:

Both of the first two refreshes were billed 20 MiB, because small tables are rounded up to a 10 MiB minimum each. The processed bytes are the number to compare, and they scale with the table. Storage of the view is billed like a table’s, on the stored bytes. For AVG, ARRAY_AGG and APPROX_COUNT_DISTINCT, BigQuery stores an intermediate sketch of type BYTES.

The cost of a view is then its storage, plus its refreshes, plus the cheaper queries. A view that is invalidated by an update every few minutes and refreshed in full can cost more than it saves.

max_staleness: allow old data, skip the merge

max_staleness tells BigQuery how old a direct query’s answer may be. If the last refresh is inside the interval, a query reads the view only and never looks at the base table. The value must be between 30 minutes and 3 days. We tried 10 minutes and 4 days and got must be between 1800000 and 259200000 (milliseconds).

CREATE MATERIALIZED VIEW shop.big_spenders
OPTIONS (
  allow_non_incremental_definition = true,
  max_staleness = INTERVAL 1 HOUR
)
AS
SELECT user_id, SUM(amount) AS total
FROM shop.events
GROUP BY user_id
HAVING SUM(amount) > 100;

This is a non-incremental view. allow_non_incremental_definition lifts the SQL limits (outer joins, HAVING, window functions), and it has to come with max_staleness: without it BigQuery says The max_staleness must be set for non-incremental materialized views. The cost is that every refresh recomputes the whole query, smart tuning does not use these views, and ALTER MATERIALIZED VIEW doesn’t accept the option, so you can’t change it later. We checked the staleness. After the view was built we appended a row for one user and updated a row for another, and the direct query still cost 120 KB. Comparing it with the base table, the two users’ totals were out by 1.5 and 1.0. That is the trade you accepted.

Other limits

• You can’t write to a materialized view. INSERT fails with is not allowed for this operation because it currently has type MATERIALIZED_VIEW.
• Deleting the base table breaks the view. Google says queries and refreshes then fail, and recreating the table doesn’t repair it: you recreate the view too.
• A view whose max_staleness is set and that has not refreshed in over 3 days fails on tables with streaming data, with Streaming data from ... is temporarily unavailable, according to Google. Keep the refresh going.
• The view must be in the same project or organization as its base tables. For smart tuning, the view must be in the same project as one of its base tables or as the query, and must use the same set of base tables as the query.
• Materialized views are listed as MATERIALIZED_VIEW in INFORMATION_SCHEMA.TABLES, and by bq ls.

For sharing a materialized view without access to the table, see authorized views. The usual choice between this and a scheduled query is freshness: a scheduled query runs in full each time and is as stale as its schedule, a materialized view is never staler than its max_staleness.