Refreshing Materialized View
- Automatic Refresh
- Manual Refresh
Automatic Refresh: By default Bigquery attempts to refresh the Materialized view within 5 minutes whenever the data in the base table gets updated.
Note : We can enable/Disable the refreshing mechanism. Also, you can change the frequency cap to refreshing the Materialized View in the Bigquery as shown below.
CREATE OR REPLACE MATERIALIZED VIEW <PROJECT_ID>.<DATASET>.<MATERIALIZED_VIEW_NAME>
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS (<QUERY_EXPRESSION>);
Manual Refresh: Bigquery also supports manual intervention to refresh Materialized View by calling the below procedure.
CALL BQ.REFRESH_MATERIALIZED_VIEW(<PROJECT_ID>.<DATASET>.<MATERIALIZED_VIEW_NAME>);
What Is Materialized View In Big Query ?
A materialized view is a precomputed snapshot of data in BigQuery, which stores the data physically from the output of a query onto the disk. It automatically refreshes the data from its base table periodically, ensuring the data remains up-to-date with changes to its underlying base tables. They are faster as compared to logical views because of their significant performance.
Materialized View overcomes the need to fetch data from the base tables every time the query is executed. Instead, the precomputed data stored in the view can be quickly accessed, resulting in faster query execution and efficiency.
Base Table : A base table is a Bigquery table where the actual data resides.