View is a technique to create a virtual copy of a table by querying it. The technique is beneficial when we repeatedly need the latest data from the original table. We will discuss two types of views, Standard and Materialized, in this blog.
Standard View:
A standard view is helpful when we do not want to write complex queries every time. Instead, one can save the query as a view and it will contain all the properties from the base table. The schema of view depends on the query result.
Ways to create a view in bigquery:
1. By clicking on the “Save view” button from the BigQuery UI.
2. Using the “CREATE VIEW” statement in the BigQuery DDL.
create view `myproject.mydataset.myview` as
select * from `myproject.mydataset.mytable`;Organizational use of the standard view:
- When you do not want to expose the logic of the complex query, then you can save it as a view.
- We can share views to the specific user group without providing the access of base tables.
Materialized View:
To increase performance and efficiency, google bigquery has provided materialized view technique. Materialized views are pre-calculated views that update and store results from time to time. The reason behind its fast performance is, it only reads delta changes from the base table. The difference between simple aggregate query and materialized view is shown below:
Simple aggregate query:
SELECT
httpRequest.status,
httpRequest.requestUrl,
count(*) requests,
AVG(timestamp_diff(receiveTimestamp, timestamp, SECOND)) as avgTimeDiff,
FROM `projectId.datasetId.requests`
GROUP BY 1, 2
ORDER BY requests DESC
Materialized view of the aggregate query:
CREATE MATERIALIZED VIEW `projectId.datasetId.requestsOverview` AS
SELECT
httpRequest.status,
httpRequest.requestUrl,
count(*) requests,
AVG(timestamp_diff(receiveTimestamp, timestamp, SECOND))
FROM `projectId.datasetId.requests`
GROUP BY 1, 2
Once the materialized view has been created, one can query it as one would query a table. It will reduce the amount of data needed to scan.
You may have noticed that I have removed the sorting operation from the base query, because it is not supported in the materialized view. And Materialized view should be created in the same database as reference table.
Materialized view does not support –
- COUNT DISTINCT
- Analytics functions
- Sorting
- Joins
- Current time functions
- Subqueries
- More than 20 views per table
Advantages of materialized views:
- Materialized view does not require any user action to specify changes in the base table. It automatically recalculates in the background when the base table changes.
- It supports aggregation of real-time data.
- BigQuery automatically fine-tunes the query performance by rerouting the query to a materialized view when needed.
With the right use cases, views can optimize the query cost and/or can improve the query performance.
Reference: