Views

Stay organized with collections Save and categorize content based on your preferences.

This topic introduces and describes Spanner views and shows how to create and manage them.

About views

A view is a virtual table defined by a SQL query. When you create a view, you specify the SQL query it represents. Once you have created a view, you can execute queries that refer to the view as though it were a table.

When a query that refers to a view is executed, Spanner creates the virtual table by executing the query defined in the view, and that virtual table's content is used by the referring query.

Because the query defining a view is executed every time a query referring to the view is executed, views are sometimes called logical views or dynamic views to distinguish them from SQL materialized views, which store the results of the query defining the view as an actual table in data storage.

All Cloud Spanner views are invoker rights views. This means that when a user executes a query against the view, Cloud Spanner checks the user's permissions on the schema objects that the view references. The user must have permissions on all objects, otherwise the query fails. Because Identity and Access Management (IAM) permissions are granted at the database level, if the user has read permissions on the database, they have read permissions on all user schema objects.

Benefits of views

Views offer several benefits over including the queries they define in application logic.

  • Views can provide logical data-modeling to applications.

    Sometimes the choices that make sense for physical data-modeling on Spanner are not the best abstraction for applications reading that data. A view can present an alternate table schema that is a more appropriate abstraction for applications.

  • Views centralize query definitions and so simplify maintenance.

    By creating views for widely used or complex queries, you can factor query text out of applications and centralize it. Doing so makes keeping query text up-to-date across applications much simpler and permits revision and tuning of queries without requiring application code to change.

  • Views provide stability across schema changes.

    Because the query that defines a view is stored in the database schema instead of in application logic, Spanner can and does ensure that schema changes to the objects (tables, columns and so on) the query refers to do not invalidate the query.

Limitations of views

Views have limitations compared to actual tables that make them inappropriate for certain use cases.

  • Views are read-only. They cannot be used to add, update or delete data.

    You cannot use views in DML statements (INSERT, UPDATE, DELETE).

  • The query that defines a view cannot use query parameters.

  • Views cannot be indexed.

  • References to views cannot use table hints.

    However, the query that defines a view can include table hints on the tables it refers to.

  • Views are not supported by the Read API.

Query performance when using views

A query that refers to a view performs comparably to that same query with its view reference replaced by the view's definition.

Quotas and limits that apply to views

  • The Quotas & limits page lists quota and limit information specifically for views.

  • Using a view in a query can affect that query's conformance to query limits because the view's definition becomes part of the query.

Cost impact of using views

Using views has a very small impact on the cost of an instance:

  • Using views has no impact on the compute capacity needs of an instance, as compared to embedding their defined query text in queries that refer to them.

  • Using views has very small impact on the database storage of an instance because the table generated by executing a view's query definition is not saved to persistent database storage.

Create a view

To create a view, use the DDL statement CREATE VIEW to name the view and provide the query that defines it. This statement has two forms:

  • CREATE VIEW defines a new view in the current database. If a view named view_name already exists, the CREATE VIEW statement fails.

  • CREATE OR REPLACE VIEW defines a new view in the current database. If a view named view_name already exists, its definition is replaced.

The syntax for the CREATE VIEW statement is:

CREATE [OR REPLACE] VIEW view_name
SQL SECURITY INVOKER
AS query

Because a view is a virtual table, the query you specify must provide names for all the columns in that virtual table.

Additionally, Spanner checks the query you specify using strict name resolution, meaning that all schema object names used in the query must be qualified such that they unambiguously identify a single schema object. For example, in the examples that follow the SingerId column in the Singers table must be qualified as Singers.SingerId.

Views that access SQL objects such as tables, queues, or views that transitively access such objects must specify SQL SECURITY INVOKER in the statement.

For example, assume the Singers table is defined as follows.

GoogleSQL 

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE TABLE Singers (
  SingerId   BIGINT PRIMARY KEY,
  FirstName  VARCHAR(1024),
  LastName   VARCHAR(1024),
  SingerInfo BYTEA
);

You could define the SingerNames view as follows.

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

The virtual table created when the SingerNames view is used in a query has two columns, SingerId and Name.

While this definition of the SingerNames view is valid, it does not abide by the best practice of casting data types to ensure stability across schema changes, as described in the next section.

Best practices when creating views

To minimize the need to update a view's definition, explicitly cast the data type of all table columns in the query that defines the view. When you do so, the view's definition can remain valid across schema changes to a column's type.

For example, the following definition of the SingerNames view could become invalid as the result of changing a column's data type in the Singers table.

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

You can avoid the view becoming invalid by explicitly casting the columns to the desired data types, as follows.

GoogleSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS INT64) AS SingerId,
 CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name
FROM Singers;

PostgreSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS bigint) AS SingerId,
 CAST(Singers.FirstName AS varchar) || ' ' || CAST(Singers.LastName AS varchar) AS Name
FROM Singers;

Update a view

To update a view, use the CREATE OR REPLACE VIEW form of the CREATE VIEW statement.

Delete a view

To delete a view, use the DROP VIEW statement.

For example:

DROP VIEW SingerNames;

Get information about a view

You can get information about views in a database by querying tables in its INFORMATION_SCHEMA schema: