Monitor materialized views

You can monitor materialized view usage and refresh jobs by viewing the BigQuery INFORMATION_SCHEMA view. To create a list of materialized views, see List materialized views.

Materialized view INFORMATION_SCHEMA view

To discover materialized views, query the INFORMATION_SCHEMA.TABLES view. To retrieve the properties of a materialized view, query the INFORMATION_SCHEMA.TABLE_OPTIONS view.

Materialized views are not listed in the INFORMATION_SCHEMA.VIEWS views table.

Monitor automatic refresh

This section shows how to view refresh details for materialized views.

View last refresh status

To retrieve the current status of materialized views, call the tables.get method, or query the INFORMATION_SCHEMA.MATERIALIZED_VIEWS view.

For example:

SELECT
  table_name, last_refresh_time, refresh_watermark, last_refresh_status
FROM
  `DATASET`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;

If the value for last_refresh_status is not NULL, then the last automatic refresh job failed. Manual refresh requests are not reflected here. Changes to base tables can invalidate a materialized view definition, resulting in an error during automatic refresh. For more information, see Incremental updates. For example, if a column that is referenced by the materialized view gets dropped from the base table, then the last_refresh_status field returns an invalidQuery error. For more information, see Error messages.

List automatic refresh jobs

To list materialized view automatic refresh jobs, call the jobs.list method. To retrieve details about the jobs, call the jobs.get method. You can also query the INFORMATION_SCHEMA.JOBS_BY_* views to get the jobs. Automatic refresh jobs contain the materialized_view_refresh prefix within the job ID and are started by a BigQuery administrator account.

For example:

SELECT
  job_id, total_slot_ms, total_bytes_processed,
  materialized_view_statistics.materialized_view[SAFE_OFFSET(0)].rejected_reason
  AS full_refresh_reason
FROM
  `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
  job_id LIKE '%materialized_view_refresh_%'
LIMIT 10;

To monitor the cost of refresh jobs and adjust the automatic refresh interval if needed, view the total_bytes_processed and total_slot_ms fields.

For example, if the ingestion rate in the base tables is relatively small, it makes sense to refresh the view less often. If the underlying data changes quickly, it makes sense to refresh more often.

If the base tables ingest data at predefined points in time, such as by using a nightly extract, transform, and load (ETL) pipeline, consider taking control of the materialized view maintenance schedule as follows:

1. Disable automatic refresh.

2. Perform a manual refresh, either as part of the ETL pipeline, or by configuring a scheduled query at specific times of the day.

Note: Table truncation, partition truncation, partition expiration, and UPDATE, DELETE, and MERGE data manipulation language (DML) statements on a base table can all invalidate their materialized views. If the materialized view is partitioned, then the modified partitions are invalidated; if not partitioned, then the whole materialized view is invalidated. For this reason, you might want to batch your DML statements and perform the manual refresh at the end of your query.

For more information on pricing for materialized views, see materialized views pricing.

Monitor materialized view usage

To view the materialized view usage for a query job, you can call the jobs.get method or query the INFORMATION_SCHEMA.JOBS_BY_* view, and view the materialized_view_statistics field. It provides details about the use of materialized views by the query, including the following details:

• Whether the materialized view was used.
• If the materialized view was not used, the reason it was rejected.

For example:

SELECT
  job_id, materialized_view_statistics
FROM
  region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  job_id = '<my-query-job-id>';

To view the usage of a materialized view over time, query the INFORMATION_SCHEMA.JOBS_BY_* views.

For example, the following query returns a summary of recent query jobs that use the target materialized view:

SELECT
  mv.table_reference.dataset_id,
  mv.table_reference.table_id,
  MAX(job.creation_time) latest_job_time,
  COUNT(job_id) job_count
FROM
  region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT job,
  UNNEST(materialized_view_statistics.materialized_view) mv
WHERE
  job.creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
  AND mv.table_reference.dataset_id = 'my_dataset'
  AND mv.table_reference.table_id = 'my_materialized_view'
  AND mv.chosen = TRUE
GROUP BY 1, 2;

Troubleshoot slow queries with materialized views

If your query uses materialized views and is running slower than expected, do the following:

1. Verify that the intended materialized views are actually being used by the query. For detailed instructions, see Monitor materialized view usage.
2. Check the freshness of your materialized view.
3. Review the materialized view definition and the data it references and consider techniques to optimize your materialized view usage.