BigQuery - Views

What are Views in SQL?

In SQL, a view is a virtualized table that, instead of containing the output of a data source like a CSV file, contains a pre-executed query that updates as new data is available.

Since views contain only pre-filtered data, they are a popular method for reducing the scope of volume processed and, by extension, can also reduce the execution time for certain data sources.

• While a table is the totality of a data source, a view represents a slice of data generated by a saved query.
• While a query might SELECT everything from a given table, a view might contain only the most recent day's data.

Creating a BigQuery View

BigQuery views can be created by a data manipulation language (DML) statement −

CREATE OR REPLACE VIEW project.dataset.view

Here is an example of creating a view definition containing Austin Bikeshare station data (from the BigQuery public dataset of the same name) for only the year 2022.

Alternatively, BigQuery users can create views within the BigQuery user interface (UI). After clicking on a dataset, instead of selecting "create table", simply select "create view." BigQuery provides a separate icon to distinguish standard tables and views so developers can tell the difference at a glance.

To access the view we created above, simply run a SELECT statement like you would use to access data generated within a standard table.

With this query, you will get an output table like the one shown here −

Materialized Views

In addition to standard views, BigQuery users can also create materialized views. A materialized view sits between a view and standard table.

BigQuery documentation defines materialized views as: "[P]recomputed views that periodically cache the results of the view query. The cached results are stored in BigQuery storage."

It's important to note that standard views do not indefinitely store data and, consequently, do not incur long-term storage fees.