Introduction to logical views

This document provides an overview of BigQuery support for logicalviews.

Overview

A view is a virtual table defined by a SQL query. The default type of view for BigQuery is alogical view. Query results contain only the data from the tables and fields specified in the query that defines the view.

The query that defines a view is run each time the view is queried.

Types of views

Although logical views are the default type of view, if you frequently query a large orcomputationally expensive view, then you should consider creating amaterialized view, which is aprecomputed view that periodically caches the results of a query for increased performance andefficiency.

However, you can often improve performance of a logical view without the need to create amaterialized view by querying only a subset of your data, or byusing other techniques.

You can also create anauthorized view toshare a subset of data from a source dataset to a view in a secondary dataset. You can then sharethis view to specific users and groups (principals) who can view the data you share and runqueries on it, but who can't access the source dataset directly.

You can create an authorized view for either a logical or materialized view. An authorized viewfor a materialized view is called anauthorized materialized view.

Use cases

Common use cases for views include the following:

• Provide an easily reusable name for a complex query or a limited set of data that you can thenauthorize other users to access. After you create a view, a user can thenquery the view as they would a table.
• 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 providing access to the base tables.
• Optimize queries with high computation cost and small dataset results forseveral use cases.

You can also use views in other contexts:

• As a data source for a visualization tool such asLooker Studio.
• As a means of sharing data to subscribers ofBigQuery sharing (formerly Analytics Hub).

Comparison to materialized views

Logical views are virtual and provide a reusable reference to a set of data, butdon't physically store any data. Materialized views are defined using SQL,like a logical view, but physically store the data which BigQueryuses to improve performance. For further comparison, seematerialized views features.

Logical views limitations

BigQuery views are subject to the following limitations:

• Views are read-only. For example, you can't run queries that insert, update,or delete data.
• The dataset that contains your view and the dataset that contains the tablesreferenced by the view must be in the samelocation.
• A reference inside of a view must be qualified with a dataset. The defaultdataset doesn't affect a view body.
• You cannot use theTableDataList JSON API method to retrieve data from aview. For more information, seeTabledata: list.
• You cannot mix GoogleSQL and legacy SQL queries when using views.A GoogleSQL query cannot reference a view defined usinglegacy SQL syntax.
• You cannot referencequery parameters in views.
• The schemas of the underlying tables are stored with the view when the viewis created. If columns are added, deleted, or modified after the view iscreated, the view isn't automatically updated and the reported schemawill remain inaccurate until the view SQL definition is changed or the viewis recreated. Even though the reported schema may be inaccurate, allsubmitted queries produce accurate results.
• You cannot automatically update a legacy SQL view to GoogleSQLsyntax. To modify the query used to define a view, you can use thefollowing:
  • TheEdit query option in the Google Cloud console
  • Thebq update --view command in the bq command-line tool
  • TheBigQuery Client libraries
  • Theupdate orpatch API methods.
• You cannot include a temporary user-defined function or a temporary tablein the SQL query that defines a view.
• You cannot reference a view in awildcard tablequery.

Logical views quotas

For information on quotas and limits that apply to views, seeView limits.

SQL queries used to define views are also subject to the quotas onquery jobs.

Logical views pricing

BigQuery uses logical views by default, notmaterialized views.Because views are not materialized by default, the query that defines the viewis run each time the view is queried. Queries are billed according to the totalamount of data in all table fields referenced directly or indirectly by the top-levelquery.

• For general query pricing, seeOn-demand compute pricing.
• For pricing associated with materialized views, seeMaterialized views pricing.

Logical views security

To control access to views in BigQuery, seeAuthorized views.

What's next

• For information on creating views, seeCreating views.
• For information on creating an authorized view, seeCreating authorized views.
• For information on getting view metadata, seeGetting information about views.
• For more information on managing views, seeManaging views.