A view is a virtual table defined by a SQL query. When you create a view, you query it in the same way you query a table. When a user queries the view, the query results contain data only from the tables and fields specified in the query that defines the view.
You can query views in BigQuery by using the:
- Query editor box in the Cloud Console
bq
command-line tool'sbq query
command- BigQuery REST API to
programmatically call the
jobs.query
or query-typejobs.insert
methods - BigQuery client libraries
You can also use a view as a data source for a visualization tool such as Google Data Studio.
View limitations
BigQuery views are subject to the following limitations:
- 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
TableDataList
JSON API method to retrieve data from a view. For more information, see Tabledata: list. - You cannot mix standard SQL and legacy SQL queries when using views. A standard SQL 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, and so on after the view is created, the reported schema will be 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 standard SQL syntax. To
modify the query used to define a view, use the Edit query option in the
Cloud Console, use the
bq update --view
command in thebq
command-line tool, use the client libraries, or use the update or patch API methods. - 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.
View quotas
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.
View pricing
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.
Next steps
- For information on creating views, see Creating views.
- For information on creating an authorized view, see Creating authorized views.
- For information on listing views, see Listing views.
- For information on getting view metadata, see Getting information about views.
- For information on updating views, see Updating views.
- For more information on managing views, see Managing views.