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 namedview_name
already exists, theCREATE VIEW
statement fails.CREATE OR REPLACE VIEW
defines a new view in the current database. If a view namedview_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:
The
INFORMATION_SCHEMA.TABLES
table provides the names of all defined views.The
INFORMATION_SCHEMA.VIEWS
table provides the names and query text of all defined views.