Introduction to views
A view is a virtual table defined by a SQL query. You can use views to provide an easily reusable name for a complex query or a limited set of data that you can then authorize other users to access. Once you create a view, a user can then query the view as they would a table. Query results contain only the data from the tables and fields specified in the query that defines the view.
The query that defines the view is run each time the view is queried. If you frequently query a large or computationally expensive view, then you should consider creating a materialized view.
BigQuery views are commonly used to:
- Abstract and store calculation and join logic in a common object to simplify query use
- Provide access to a subset of data and calculation logic without accessing to the base tables
You can also use a view as a data source for a visualization tool such as Looker Studio.
Comparison to materialized views
Views are virtual and provide a reusable reference to a set of data, but do not physically store any data. Materialized views are defined using SQL, like a regular view, but physically store the data which BigQuery uses to improve performance. For further comparison, see materialized views features.
BigQuery views are subject to the following limitations:
- Views are read-only. You cannot run DML (insert, update, delete) queries against a view.
- The dataset that contains your view and the dataset that contains the tables referenced by the view must be in the same location.
- You cannot run a BigQuery job that exports data from a view.
- You cannot use the
TableDataListJSON API method to retrieve data from a view. For more information, see Tabledata: list.
- You cannot mix GoogleSQL and legacy SQL queries when using views. A GoogleSQL query cannot reference a view defined using legacy SQL syntax.
- You cannot reference query parameters in views.
- The schemas of the underlying tables are stored with the view when the view is created. If columns are added, deleted, or modified after the view is created, then the reported schema is inaccurate until the view is updated. Even though the reported schema may be inaccurate, all submitted queries produce accurate results.
- You cannot automatically update a legacy SQL view to GoogleSQL syntax. To modify the query used to define a view, you can use the following:
- You cannot include a temporary user-defined function or a temporary table in the SQL query that defines a view.
- You cannot reference a view in a wildcard table query.
For information on quotas and limits that apply to views, see View limits.
SQL queries used to define views are also subject to the quotas on query jobs.
BigQuery's views are logical views, not materialized views. Because views are not materialized, the query that defines the view is run each time the view is queried. Queries are billed according to the total amount of data in all table fields referenced directly or indirectly by the top-level query. For more information, see query pricing.
To control access to views in BigQuery, see Authorized views.
- For information on creating views, see Creating views.
- For information on creating an authorized view, see Creating authorized views.
- For information on getting view metadata, see Getting information about views.
- For more information on managing views, see Managing views.