Use GoogleSQL's data definition language (DDL) to:
- Create a database.
- Create, alter, or drop tables in a database.
- Add, alter, or drop columns in a table.
- Create or drop indexes in a database.
- Create, replace, or drop views in a database.
- Create, alter, or drop change streams in a database.
- Create or drop database roles.
- Grant privileges to database roles.
- Grant database roles to other database roles.
- Create, alter, or drop ML models in a database.
Notation
- Square brackets "[ ]" indicate optional clauses.
- Parentheses "( )" indicate literal parentheses.
- The vertical bar "|" indicates a logical OR.
- Curly braces "{ }" enclose a set of options.
- A comma followed by an ellipsis indicates that the preceding item can repeat
in a comma-separated list.
item [, ...]
indicates one or more items, and[item, ...]
indicates zero or more items. - A comma "," indicates the literal comma.
- Angle brackets "<>" indicate literal angle brackets.
- An mdash "—" indicates a range of values between the items on either side of it.
- The plus sign "+" indicates that the preceding item can repeat.
Reserved keywords
Some words have special meaning in the GoogleSQL language and are
reserved in its DDL. To use a reserved keyword as an identifier in your schema,
enclose it in backticks (`
). For the full list of reserved keywords in GoogleSQL,
see GoogleSQL lexical structure and syntax.
For example:
CREATE TABLE MyTable ( RowId INT64 NOT NULL, `Order` INT64 ) PRIMARY KEY (RowId);
Naming conventions
The following rules apply to database IDs.
- Must start with a lowercase letter.
- Can contain lowercase letters, numbers, underscores, and hyphens, but not uppercase letters.
- Cannot end with an underscore or hyphen.
- Must be enclosed in backticks (
`
) if it's a reserved word or contains a hyphen. - Can be between 2-30 characters long.
- Cannot be changed after you create it.
The following rules apply to table, column, index, view, role, and constraint names.
Must be at least one character long.
Can contain a maximum of 128 characters.
Must start with an uppercase or lowercase letter.
Can contain uppercase and lowercase letters, numbers, and underscores, but not hyphens.
No two Spanner objects can be created with the same name, including names that differ only in capitalization. For example, the second statement in the following snippet fails because the table names differ only by case.
CREATE TABLE MyTable (col1 INT64) PRIMARY KEY (col1); CREATE TABLE MYTABLE (col1 INT64) PRIMARY KEY (col1);
When referring to other schema objects in a DDL statement (for example, a column name for a primary key, or table and column names in an index), make sure to use the original case for the name of each entity. As an example, consider the table
Singers
created with the following statement.CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), BirthDate DATE ) PRIMARY KEY(SingerId);
The following command fails with the message
Table not found: singers
because it uses a different case for theSingers
table.CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)
Schema object names are case insensitive in SQL queries. As an example, consider the table
MyTable2
created with the following statement.CREATE TABLE MyTable2 (col1 INT64) PRIMARY KEY (col1);
The following queries all succeed because schema object names are case-insensitive for queries.
SELECT col1 FROM MyTable2 LIMIT 1; SELECT COL1 FROM MYTABLE2 LIMIT 1; SELECT COL1 FROM mytable2 LIMIT 1; INSERT INTO MYTABLE2 (col1) VALUES(1);
Data types
The following are the data types used in GoogleSQL.
Scalars
The syntax for using a scalar type in DDL is:
{ BOOL | INT64 | FLOAT64 | NUMERIC | STRING( length ) | JSON | BYTES( length ) | DATE | TIMESTAMP } length: { int64_value | MAX } int64_value: { decimal_value | hex_value } decimal_value: [-]0—9+ hex_value: [-]0x{0—9|a—f|A—F}+
An int64_value
must correspond to an integer from -9,223,372,036,854,775,808
(-263) to 9,223,372,036,854,775,807 (263 − 1). It can be
specified with decimal or hexadecimal notation. The hexadecimal form requires a
0x
prefix, with a lowercase x
.
STRING
STRING
is a variable length Unicode character string. Its value must be a
valid Unicode string. Length is required, and represents the maximum number of
Unicode characters (not bytes) that can be stored in the field.
Notes:
Writes to the column are rejected if the new value is not a valid Unicode string or exceeds the specified length.
length
can be an integer in the range [1, 2621440].For a field whose length is unpredictable or does not need to be constrained, you can set
length
to the convenience valueMAX
, which is equivalent to 2621440 for validation purposes.Only the actual length of the stored string impacts storage costs; specifying
MAX
does not use any additional storage capacity.GoogleSQL requires Unicode strings to be UTF-8 encoded on receipt at the server.
Collation is done by Unicode character numerical value (technically by code point, which is subtly different due to combining characters). For ASCII strings, this is the traditional sort order.
You can reduce the length of a column after the table has been created, but doing so requires Spanner to validate that the existing data is within the length constraint.
JSON
JSON
is a variable length Unicode character string representing a JSON
object. The string must be UTF-8 encoded on receipt at the server. The maximum
length of the JSON value is 2621440 characters.
See Working with JSON and Data types for details.
BYTES
BYTES
is a variable length binary string. Length is required, and represents
the maximum number of bytes that can be stored in the field.
Notes:
Writes to the column are rejected if the new value exceeds the specified length.
length
can be an integer in the range [1, 10485760] or the convenience valueMAX
, which is equivalent to 10485760 for validation purposes.Only the actual stored bytes impact storage costs; specifying
MAX
does not use any additional storage capacity.You can reduce the length of a column after the table has been created, but doing so requires Spanner to validate that the existing data is within the length constraint.
DATE
- A timezone-independent date.
- The range [0001-01-01, 9999-12-31] is the legal interval for dates. A write to a date column is rejected if the value is outside of that interval.
- See more information and the canonical format in Data Types.
TIMESTAMP
- A timestamp with nanosecond precision.
- Timezone-independent, over the range [0001-01-01 00:00:00 to 10000-01-01 00:00:00).
- See more information and the canonical format in Data Types.
Arrays
The syntax for using the ARRAY
type in DDL is:
ARRAY< scalar_type >
GoogleSQL supports arrays of scalars. The primary purpose of arrays is to store a collection of values in a space efficient way. Arrays are not designed to provide access to individual elements; to read or write a single element, you must read or write the entire array.
If your application uses data structures like vectors or repeated fields, you can persist their state in a GoogleSQL array.
Here's an example of an alternate definition of Singers
that uses multiple
columns of ARRAY
type:
CREATE TABLE Singers ( SingerId INT64, FeaturedSingerIds ARRAY<INT64>, SongNames ARRAY<STRING(MAX)> ) PRIMARY KEY (SingerId) ...;
Notes:
- Arrays with subtype
ARRAY
(nested arrays) are not supported. - Arrays, like scalar values, can never be larger than 10 MiB total.
- Arrays can't be used as key columns.
In a
CREATE TABLE
statement, you can create columns ofARRAY
type with aNOT NULL
annotation.After you create the table, you cannot add a column of
ARRAY
type with aNOT NULL
annotation, and you cannot add aNOT NULL
annotation to an existing column ofARRAY
type.
DATABASE statements
CREATE DATABASE
When creating a GoogleSQL database, you must provide a CREATE DATABASE
statement, which defines the ID of the database:
CREATE DATABASE database_id where database_id {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}
Parameters
database_id
- The name of the database to create. See Naming Conventions.
ALTER DATABASE
Changes the definition of a database.
Syntax
ALTER DATABASE database_id action where database_id is: {a—z}[{a—z|0—9|_|-}+]{a—z|0—9} and action is: SET OPTIONS ( options_def [, ... ] ) and options_def is: { default_leader = { 'region' | null } | optimizer_version = { 1 ... 5 | null } | optimizer_statistics_package = { 'package_name' | null } | version_retention_period = { 'duration' | null } }
Description
ALTER DATABASE
changes the definition of an existing database.
SET OPTIONS
- Use this clause to set an option at the database level of the schema hierarchy.
Parameters
database_id
- The name of the database whose attributes are to be altered. If the name
is a reserved word or contains a hyphen, enclose it in backticks (
`
). For more information on database naming conventions, see Naming Conventions in this document.
options_def
The
optimizer_version = { 1 ... 5 | null }
option allows you to specify the query optimizer version to use. Setting this option tonull
is equivalent to setting it to the default version. For more information, see Query Optimizer.The
optimizer_statistics_package = { 'package_name' | null }
option allows you to specify the query optimizer statistics package name to use. By default, this is the latest collected statistics package, but you can specify any available statistics package version. Setting this option tonull
is equivalent to setting it to the latest version. For more information, see Query statistics package versioning.The
version_retention_period = { 'duration' | null }
is the period for which Spanner retains all versions of data and schema for the database. The duration must be in the range[1h, 7d]
and can be specified in days, hours, minutes, or seconds. For example, the values1d
,24h
,1440m
, and86400s
are equivalent. Setting the value tonull
resets the retention period to the default, which is 1 hour. This option can be used for point-in-time recovery. For more information, see Point-in-time Recovery.The
default_leader = { 'region' | null }
sets the leader region for your database. You can only use this parameter for databases that use a multi-region configuration.default_leader
must be set tonull
, or one of the read-write replicas in your multi-region configuration.null
resets the leader region to the default leader region for your database's multi-region configuration. For more information, see Configuring the default leader region.
TABLE statements
CREATE TABLE
Defines a new table.
Syntax
CREATE TABLE table_name ( [ { column_name data_type [NOT NULL] [ { DEFAULT ( expression ) | AS ( expression ) STORED } ] [ options_def ] | table_constraint } [, ... ] ] ) PRIMARY KEY ( [column_name [ { ASC | DESC } ], ...] ) [, INTERLEAVE IN PARENT table_name [ ON DELETE { CASCADE | NO ACTION } ] ] [, ROW DELETION POLICY ( OLDER_THAN ( timestamp_column, INTERVAL num_days DAY ) ) ] where data_type is: { scalar_type | array_type } and options_def is: { OPTIONS ( allow_commit_timestamp = { true | null } ) } and table_constraint is: [ CONSTRAINT constraint_name ] { CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] ) }
Description
CREATE TABLE
defines a new table in the current database.
Parameters
table_name
- The name of the table to be created. For naming guidance, see Naming conventions.
column_name
- The name of a column to be created. For naming guidance, see Naming conventions.
data_type
timestamp_column
- The name of a column of type
TIMESTAMP
, that is also specified in the CREATE TABLE statement.
num_days
- The number of days after the date in the specified
timestamp_column
, after which the row is marked for deletion. Valid values are non-negative integers.
NOT NULL
This optional column annotation specifies that the column is required for all mutations that insert a new row.
You cannot add a NOT NULL column to an existing table. For most column types, you can work around this limitation:
For columns of
ARRAY
type, the only time you can use a NOT NULL annotation is when you create the table. After that, you cannot add a NOT NULL annotation to a column ofARRAY
type.For all other column types, you can add a nullable column; fill that column by writing values to all rows; and update your schema with a NOT NULL annotation on that column.
DEFAULT (
expression
)
- This clause sets a default value for the column.
- A column with a default value can be a key or non-key column.
- A column can't have a default value and also be a generated column.
- You can insert your own value into a column that has a default value,
overriding the default value. You can also reset a non-key column to its
default value by using
UPDATE ... SET
column-name
= DEFAULT
. - A generated column or a check constraint can depend on a column with a default value.
- A column with a default value can't be a commit timestamp column.
PENDING_COMMIT_TIMESTAMP()
can't be used as a default value.SET OPTIONS (allow_commit_timestamp = true)
is disallowed.
expression
can be a literal or any valid SQL expression that is assignable to the column data type, with the following properties and restrictions:- The expression can be non-deterministic.
- The expression can't reference other columns.
- The expression can't contain subqueries, query parameters, aggregates, or analytic functions.
AS (
expression
) STORED
This clause creates a column as a generated column, which is a column whose value is defined as a function of other columns in the same row.
expression
can be any valid SQL expression that's assignable to the column data type with the following restrictions.The expression can only reference columns in the same table.
The expression can't contain subqueries.
The expression can't contain non-deterministic functions such as
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
, andCURRENT_TIMESTAMP()
.You can't modify the expression of a generated column.
The
STORED
attribute that follows the expression stores the result of the expression along with other columns of the table. Subsequent updates to any of the referenced columns cause Spanner to re-evaluate and store the expression.Spanner doesn't allow generated columns unless they use the
STORED
attribute.Direct writes to generated columns aren't allowed.
Column option
allow_commit_timestamp
isn't allowed on generated columns or any columns that generated columns reference.You can't change the data type of a generated column, or of any columns the generated column references.
You can't drop a column a generated column references.
You can use a generated column as a primary key with the following additional restrictions:
The generated primary key can't reference other generated columns.
The generated primary key can reference, at most, one non-key column.
The generated primary key can't depend on a non-key column with a
DEFAULT
clause.
The following rules apply when using generated key columns:
- Read APIs: You must fully specify the key columns, including the generated key columns.
- Mutation APIs: For
INSERT
,INSERT_OR_UPDATE
, andREPLACE
, Spanner doesn't allow you to specify generated key columns. ForUPDATE
, you can optionally specify generated key columns. ForDELETE
, you need to fully specify the key columns including the generated keys. - DML: You can't explicitly write to generated keys in
INSERT
orUPDATE
statements. - Query: In general, we recommend that you use the generated key column as a
filter in your query. Optionally, if the expression for the generated key
column uses only one column as a reference, the query can apply an equality
(
=
) orIN
condition to the referenced column. For more information and an example, see Create a unique key derived from a value column.
For examples on how to work with generated columns, see Creating and managing generated columns.
PRIMARY KEY ( [
column_name
[ { ASC | DESC } ], ...]
Every table must have a primary key, and that primary key can be composed of zero or more columns of that table.
Adding the
DESC
annotation on a primary key column name changes the physical layout of data from ascending order (default) to descending order.
For more details, see Schema and data model.
[, INTERLEAVE IN PARENT
table_name
[ ON DELETE { CASCADE | NO ACTION } ] ]
This clause defines a child-to-parent table relationship, which results in a physical interleaving of parent and child rows. The primary-key columns of a parent must positionally match, both in name and type, a prefix of the primary-key columns of any child. Adding rows to the child table fails if the corresponding parent row does not exist. The parent row can either exist in the database or be inserted before the insertion of the child rows in the same transaction.
The optional
ON DELETE
clause defines the behavior of rows inChildTable
when a mutation attempts to delete the parent row. The supported options are:CASCADE
: the child rows are deleted.NO ACTION
: the child rows are not deleted. If deleting a parent would leave behind child rows, thus violating parent-child referential integrity, the write will fail.
You can omit the
ON DELETE
clause, in which case the default ofON DELETE NO ACTION
is used.
For more details, see Schema and data model.
CONSTRAINT
constraint_name
- An optional name for a table constraint. If a name is not specified, Spanner generates a name for the constraint. Constraints names, including generated names, can be queried from the Spanner information schema.
CHECK (
expression
)
A
CHECK
constraint lets you specify that the values of one or more columns must satisfy a boolean expression.expression
can be any valid SQL expression that evaluates to aBOOL
.The following restrictions apply to a check constraint
expression
term.The expression can only reference columns in the same table.
The expression must reference at least one non-generated column, whether directly or through a generated column which references a non-generated column.
The expression can't reference columns that have set the
allow_commit_timestamp
option.The expression can't contain subqueries.
The expression can't contain non-deterministic functions, such as
CURRENT_DATE()
andCURRENT_TIMESTAMP()
.
For more information, see Creating and managing check constraints.
FOREIGN KEY (
column_name
[, ... ] ) REFERENCES
ref_table
(
ref_column
[, ... ] )
Use this clause to define a foreign key constraint. A foreign key is defined on the referencing table of the relationship, and it references the referenced table. The foreign key columns of the two tables are called the referencing and referenced columns, and their row values are the keys.
A foreign key constraint requires that one or more columns of this table can contain only values that are in the referenced columns of the referenced table.
When creating a foreign key, a unique constraint is automatically created on the referenced table, unless the entire primary key is referenced. If the unique constraint can't be satisfied, the entire schema change will fail.
The number of referencing and referenced columns must be the same. Order is also significant. That is, the first referencing column refers to the first referenced column, the second to the second, and so on.
The referencing and referenced columns must have matching types and they must support the equality operator ('='). The columns must also be indexable. Columns of type
ARRAY
are not allowed.Foreign keys can't be created on columns with the
allow_commit_timestamp=true
option.
For details, see Foreign keys.
OPTIONS ( allow_commit_timestamp = { true | null } )
- The
allow_commit_timestamp
option allows insert and update operations to request that Spanner write the commit timestamp of the transaction into the column. For details, see Commit timestamps in GoogleSQL-dialect databases.
[, ROW DELETION POLICY ( OLDER_THAN (
timestamp_column
, INTERVAL
num_days
DAY ) ) ]
- Use this clause to set a row deletion policy for this table. See Time to live (TTL) for details.
ALTER TABLE
Changes the definition of a table.
Syntax
ALTER TABLE table_name action where action is: ADD [ COLUMN ] column_name data_type [ column_expression ] [ options_def ] DROP [ COLUMN ] column_name ADD table_constraint DROP CONSTRAINT constraint_name SET ON DELETE { CASCADE | NO ACTION } ALTER [ COLUMN ] column_name { data_type [ NOT NULL ] [ DEFAULT ( expression ) ] | SET ( options_def ) | SET DEFAULT ( expression ) | DROP DEFAULT } ADD ROW DELETION POLICY ( OLDER_THAN ( timestamp_column, INTERVAL num_days DAY )) DROP ROW DELETION POLICY REPLACE ROW DELETION POLICY ( OLDER_THAN ( timestamp_column, INTERVAL num_days DAY )) and data_type is: { scalar_type | array_type } and column_expression is: [ NOT NULL ] [ { DEFAULT ( expression ) | AS ( expression ) STORED } ] and options_def is: OPTIONS (allow_commit_timestamp = { true | null } ) and table_constraint is: [ CONSTRAINT constraint_name ] { CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES ref_table ( ref_column [, ... ] ) }
Description
ALTER TABLE
changes the definition of an existing table.
ADD COLUMN
Adds a new column to the table, using the same syntax as
CREATE TABLE
.You can specify
NOT NULL
in anALTER TABLE...ADD COLUMN
statement if you specifyDEFAULT (
expression
)
orAS (
expression
) STORED
for the column.If you include
DEFAULT (
expression
)
orAS (
expression
) STORED
, the expression is evaluated and the computed value is backfilled for existing rows. The backfill operation is asynchronous. This backfill operation happens only when anADD COLUMN
statement is issued. There's no backfill onALTER COLUMN
.The
DEFAULT
clause has restrictions. See the description of this clause inCREATE TABLE
.
DROP COLUMN
Drops a column from a table.
You can't drop a column referenced by a generated column.
Dropping a column referenced by a
CHECK
constraint is not allowed.
ADD
table_constraint
Adds a new constraint to a table using the same syntax as
CREATE TABLE
.For foreign keys, the existing data is validated before the foreign key is added. If any existing constrained key does not have a corresponding referenced key, or the referenced key is not unique, the constraint is in violation, and the
ALTER
statement fails.For
CHECK
constraints, new data is validated immediately against the constraint. A long-running process is also started to validate the existing data against the constraint. If any existing data does not conform to the constraint, the check constraint is rolled back.The following restrictions apply to a check constraint
expression
term.The expression can only reference columns in the same table.
The expression must reference at least one non-generated column, whether directly or through a generated column which references a non-generated column.
The expression can't reference columns that have set the
allow_commit_timestamp
option.The expression can't contain subqueries.
The expression can't contain non-deterministic functions, such as
CURRENT_DATE()
andCURRENT_TIMESTAMP()
.
DROP CONSTRAINT
constraint_name
- Drops the specified constraint on a table, along with any associated index, if applicable.
SET ON DELETE { CASCADE | NO ACTION }
This alteration can be applied only on child tables of parent-child, interleaved tables relationships. For more information, see Schema and data model.
The
ON DELETE CASCADE
clause signifies that when a row from the parent table is deleted, its child rows in this table will automatically be deleted as well. Child rows are all rows that start with the same primary key. If a child table does not have this annotation, or the annotation isON DELETE NO ACTION
, then you must delete the child rows before you can delete the parent row.
ALTER COLUMN
Changes the definition of an existing column on a table.
data_type
[ NOT NULL ] [ DEFAULT (
expression
) ]
This clause changes the data type of the column and optionally sets a default value for the column.
The
DEFAULT
clause has restrictions. See the description of this clause inCREATE TABLE
.Statements to set, change, or drop default value of an existing column do not affect existing rows.
If the column has data and is altered to have the
NOT NULL
constraint, the statement might fail if there is at least one existing row with aNULL
value. This is true even when aNOT NULL DEFAULT (...)
is specified, because there is no backfill operation forALTER COLUMN
.
SET (options_def)
The
OPTIONS (allow_commit_timestamp = { true | null } )
clause is the only allowed option. Iftrue
, a commit timestamp can be stored into the column.To learn about commit timestamps, see Commit timestamps in GoogleSQL-dialect databases.
SET DEFAULT
( expression )
Sets or changes a default value for the column. Only metadata is affected. Existing data is not changed.
This clause has restrictions. See the description of this clause in
CREATE TABLE
.When you use this clause, the expression result must be assignable to the current column type. To change the column type and default value in a single statement, use:
ALTER TABLE
table-name
ALTER COLUMN
column-name data_type
DEFAULT
expression
DROP DEFAULT
- Drops the column default value. Only metadata is affected. Existing data is not changed.
ADD ROW DELETION POLICY ( OLDER_THAN (
timestamp_column
, INTERVAL
num_days
DAY ) )
- Adds a row deletion policy to the table defining the amount of time after a specific date after which to delete a row. See Time to live. Only one row deletion policy can exist on a table at a time.
DROP ROW DELETION POLICY
- Drops the row deletion policy on a table.
REPLACE ROW DELETION POLICY ( OLDER_THAN (
timestamp_column
, INTERVAL
num_days
DAY ) )
- Replaces the existing row deletion policy with a new policy.
Parameters
table_name
- The name of an existing table to alter.
column_name
- The name of a new or existing column. You can't change the key columns of a table.
data_type
Data type of the new column, or new data type for an existing column.
You can't change the data type of a generated column, or any columns referenced by the generated column.
Changing the data type is not allowed on any columns referenced in a
CHECK
constraint.options_def
The
(allow_commit_timestamp=true)
option allows insert and update operations to request that Spanner write the commit timestamp of the transaction into the column. For details, see Commit timestamps in GoogleSQL-dialect databases.
table_constraint
- New table constraint for the table.
constraint_name
- The name of a new or existing constraint.
ref_table
- The referenced table in a foreign key constraint.
ref_column
- The referenced column in a foreign key constraint.
DROP TABLE
Removes a table.
Syntax
DROP TABLE table_name
Description
Use the DROP TABLE
statement to remove a table from the database.
DROP TABLE
is not recoverable.You can't drop a table if there are indexes over it, or if there are any tables or indexes interleaved within it.
A
DROP TABLE
statement automatically drops the foreign keys and foreign keys backing indexes of a table.
Parameters
table_name
- The name of the table to drop.
INDEX statements
CREATE INDEX
Use the CREATE INDEX
statement to define
secondary indexes.
Syntax
CREATE [ UNIQUE ] [ NULL_FILTERED ] INDEX index_name ON table_name ( key_part [, ...] ) [ storing_clause ] [ , interleave_clause ] where index_name is: {a—z|A—Z}[{a—z|A—Z|0—9|_}+] and key_part is: column_name [ { ASC | DESC } ] and storing_clause is: STORING ( column_name [, ...] ) and interleave_clause is: INTERLEAVE IN table_name
Description
Spanner automatically indexes the primary key columns of each table.
You can use CREATE INDEX
to create secondary indexes for other columns. Adding
a secondary index on a column makes it more efficient to look up data in that
column. For more details, see secondary indexes.
Parameters
UNIQUE
- Indicates that this secondary index enforces a
UNIQUE
constraint on the data being indexed. TheUNIQUE
constraint causes any transaction that would result in a duplicate index key to be rejected. See Unique Indexes for more information.
NULL_FILTERED
- Indicates that this secondary index does not index
NULL
values. See Indexing of NULL values for more information.
index_name
- The name of the index to be created. For naming guidance, see Naming conventions.
table_name
- The name of the table to be indexed.
INTERLEAVE IN
Defines a table to interleave the index in. If
T
is the table into which the index is interleaved, then:T
must be a parent of the table being indexed, and- The primary key of
T
must be the key prefix of the index.
If the index key that you want to use for index operations matches the key of a table, you might want to interleave the index in that table if the row in the table should have a data locality relationship with the corresponding indexed rows.
For example, if you want to index all rows of
Songs
for a particular row ofSingers
, your index keys would containSingerId
andSongName
and your index would be a good candidate for interleaving inSingers
if you frequently fetch information about a singer as you fetch that singer's songs from the index. The definition ofSongsBySingerSongName
in Creating a Secondary Index is an example of creating such an interleaved index.Like interleaved tables, entries in interleaved indexes are stored with the corresponding row of the parent table. See database splits for more details.
DESC
- Defines descending scan order for the corresponding index column. When
scanning a table using an index column marked
DESC
, the scanned rows appear in the descending order with respect to this index column. If you don't specify a sort order, the default is ascending (ASC
).
STORING
- Provides a mechanism for duplicating data from the table into one or more secondary indexes on that table. At the cost of extra storage, this can reduce read latency when looking up data using a secondary index, because it eliminates the need to retrieve data from the main table after having found the desired entries in the index. See STORING clause for an example.
ALTER INDEX
Use the ALTER INDEX
statement to add additional columns or remove stored columns from
the secondary indexes.
Syntax
ALTER INDEX index_name {ADD|DROP} STORED COLUMN column_name
Description
Add an additional column into an index or remove a column from an index.
Parameters
index_name
- The name of the index to alter.
column_name
- The name of the column to add into the index or to remove from the index.
DROP INDEX
Removes a secondary index.
Syntax
DROP INDEX index_name
Description
Use the DROP INDEX
statement to drop a secondary index.
Parameters
index_name
- The name of the index to drop.
VIEW statements
CREATE VIEW and CREATE OR REPLACE VIEW
Use the CREATE VIEW
or CREATE OR REPLACE VIEW
statement to define a
view.
Syntax
{ CREATE VIEW | CREATE OR REPLACE VIEW } view_name SQL SECURITY INVOKER AS query
Description
CREATE VIEW
defines a new view in the current database. If a view named
view_name
exists, the CREATE VIEW
statement fails.
CREATE OR REPLACE VIEW
defines a new view in the current database. If a view
named view_name
exists, its definition is replaced.
Parameters
view_name
- The name of the view to be created. For naming guidance, see Naming conventions.
SQL SECURITY INVOKER
- Indicates that when the view is used in a query, the objects referenced in the view are access-checked against the credentials of the user who invoked the query. This is the only SQL security option in GoogleSQL.
AS query
The query that defines the view content.
The query must specify a name for each item in the SELECT list.
The query cannot include query parameters.
GoogleSQL disregards any ORDER BY clause in this query that isn't paired with a LIMIT clause.
See Query syntax for information on constructing a query.
DROP VIEW
Removes a view.
Syntax
DROP VIEW view_name
Description
Use the DROP VIEW
statement to remove a view from the database.
Parameters
view_name
- The name of the view to drop.
CHANGE STREAM statements
CREATE CHANGE STREAM
Defines a new change stream.
Syntax
CREATE CHANGE STREAM change_stream_name [ FOR { table_columns [, ... ] | ALL } ] [ OPTIONS ( change_stream_option [, ... ] ) ] where table_columns is: table_name [ ( [ column_name, ... ] ) ] and change_stream_option is: { retention_period = 'duration' | value_capture_type = { 'OLD_AND_NEW_VALUES' | 'NEW_ROW' |'NEW_VALUES' } }
Description
CREATE CHANGE STREAM
defines a new change stream in the current database.
For more information, see
Create a change stream.
Parameters
change_stream_name
- The name of the change stream to be created. For naming guidance, see Naming conventions.
FOR {
table_columns
[, ... ] | ALL }
The
FOR
clause defines the tables and columns that are watched by the change stream.You can specify a list of
table_columns
to watch, wheretable_columns
can be either of the following:table_name
: This watches the entire table, including all of the future columns when they are added to this table.table_name
( [
column_name
, ... ] )
: You can optionally specify a list of zero or more non-key columns following the table name. This watches only the primary key and the listed non-key columns of the table. With an empty list of non-key columns,table_name
()
watches only the primary key.
ALL
allows you to watch all tables and columns in the entire database, including all of the future tables and columns as soon as they are created.When the
FOR
clause is omitted, the change stream watches nothing.
OPTIONS (
change_stream_option
[, ... ] )
The
retention_period = 'duration'
option allows you to specify how long a change stream retains its data. The duration must be in the range[1d, 7d]
and can be specified in days, hours, minutes, or seconds. For example, the values1d
,24h
,1440m
, and86400s
are equivalent. The default is 1 day. For details, see Data retention.The
value_capture_type
option controls which values are captured for a changed row. It can beOLD_AND_NEW_VALUES
(default),NEW_VALUES
, orNEW_ROW
. For details, see Value capture type.
ALTER CHANGE STREAM
Changes the definition of a change stream.
Syntax
ALTER CHANGE STREAM change_stream_name action where action is: { SET FOR { table_columns [, ... ] | ALL } | DROP FOR ALL | SET OPTIONS ( change_stream_option [, ... ] ) } and table_columns is: table_name [ ( [ column_name, ... ] ) ] and change_stream_option is: { retention_period = { 'duration' | null } | value_capture_type = { 'OLD_AND_NEW_VALUES' | 'NEW_ROW' | 'NEW_VALUES' | null } }
Description
ALTER CHANGE STREAM
changes the definition of an existing change stream.
For more information, see
Modify a change stream.
Parameters
change_stream_name
- The name of an existing change stream to alter.
SET FOR {
table_columns
[, ... ] | ALL }
- Sets a new
FOR
clause to modify what the change stream watches, using the same syntax asCREATE CHANGE STREAM
.
DROP FOR ALL
- Suspends a change stream to watch nothing.
SET OPTIONS
Sets options on the change stream (such as
retention_period
andvalue_capture_type
), using the same syntax asCREATE CHANGE STREAM
.Setting an option to
null
is equivalent to setting it to the default value.
DROP CHANGE STREAM
Removes a change stream.
Syntax
DROP CHANGE STREAM change_stream_name
Description
Use the DROP CHANGE STREAM
statement to remove a change stream from the
database and delete its data change records.
Parameters
change_stream_name
- The name of the change stream to drop.
ROLE statements
CREATE ROLE
Defines a new database role.
Syntax
CREATE ROLE database_role_name
Description
CREATE ROLE
defines a new database role for fine-grained access control.
Database roles are collections of privileges. You can create only one role with
this statement.
Parameters
database_role_name
- The name of the database role to create. The role name
public
and role names starting withspanner_
are reserved for system roles. See also Naming conventions.
Example
This example creates the database role hr_manager
.
CREATE ROLE hr_manager
DROP ROLE
Drops a database role.
Syntax
DROP ROLE database_role_name
Description
DROP ROLE
drops a database role. You can drop only one role with
this statement.
You can't drop a database role if it has any privileges granted to it. All privileges granted to a database role must be revoked before the role can be dropped. You can drop a database role whether or not it's granted to IAM principals.
Dropping a role automatically revokes its membership in other roles and revokes the membership of its members.
You can't drop system roles.
Parameters
database_role_name
- The name of the database role to drop.
Example
This example drops the database role hr_manager
.
DROP ROLE hr_manager
GRANT and REVOKE statements
GRANT
Syntax
GRANT { SELECT | INSERT | UPDATE | DELETE } ON TABLE table_list TO ROLE role_list GRANT { SELECT | INSERT | UPDATE }(column_list) ON TABLE table_list TO ROLE role_list GRANT SELECT ON VIEW view_list TO ROLE role_list GRANT SELECT ON CHANGE STREAM change_stream_list TO ROLE role_list GRANT EXECUTE ON TABLE FUNCTION tvf_list TO ROLE role_list GRANT ROLE role_list TO ROLE role_list where table_list is: table_name [, ...] and column_list is: column_name [,...] and view_list is: view_name [, ...] and change_stream_list is: change_stream_name [, ...] and tvf_list is: table_valued_function_name [, ...] and role_list is: database_role_name [, ...]
Description
For fine-grained access control, grants privileges
on one or more tables, views, change streams, or table-valued functions to
database roles. Also grants database roles to other
database roles to create a database role hierarchy with inheritance.
When granting SELECT
, INSERT
, or UPDATE
on
a table, optionally grants privileges on only a subset of table columns.
Parameters
table_name
- The name of an existing table.
column_name
- The name of an existing column in the specified table.
view_name
- The name of an existing view.
change_stream_name
- The name of an existing change stream.
table_valued_function_name
- The name of an existing table-valued function for a change stream.
database_role_name
- The name of an existing database role.
Notes and restrictions
Identifiers for database objects named in the
GRANT
statement must use the case that was specified when the object was created. For example, if you created a table with a name that is in all lower case with a capitalized first letter, you must use that same case in theGRANT
statement. Table-valued functions (TVFs) get automatically created with a prefix added to the change stream name, so ensure that you use the proper case for both the prefix and the change stream name. For more information about TVFs, see Change stream query syntax.When granting column-level privileges on multiple tables, each table must contain the named columns.
If a table contains a column that is marked
NOT NULL
and has no default value, you can't insert into the table unless you have theINSERT
privilege on that column.After granting
SELECT
on a change stream to a role, grantEXECUTE
on the change stream's table-valued function (TVF) to that role. For information about TVFs see Change stream query syntax.Granting
SELECT
on a table doesn't grantSELECT
on the change stream that tracks it. You must make a separate grant for the change stream.
Examples
The following example grants SELECT
on the employees
table to the hr_rep
role. Grantees of the hr_rep
role can read all columns of employees
.
GRANT SELECT ON TABLE employees TO ROLE hr_rep;
The next example grants SELECT
on a subset of columns of the contractors
table to the hr_rep
role. Grantees of the hr_rep
role can read only the
named columns.
GRANT SELECT(name, address, phone) ON TABLE contractors TO ROLE hr_rep;
The next example mixes table-level and column-level grants. hr_manager
can read all table columns, but can update only the location
column.
GRANT SELECT, UPDATE(location) ON TABLE employees TO ROLE hr_manager;
The next example makes column-level grants on two tables. Both tables must
contain the location
column.
GRANT SELECT(name, level, location), UPDATE(location) ON TABLE employees, contractors TO ROLE hr_manager;
The next example grants INSERT
on a subset of columns of the employees
table.
GRANT INSERT(name, cost_center, location, manager) ON TABLE employees TO ROLE hr_manager;
The next example grants the database role pii_access
to the roles hr_manager
and
hr_director
. The hr_manager
and hr_director
roles are members of pii_access
and
inherit the privileges that were granted to pii_access
.
GRANT ROLE pii_access TO ROLE hr_manager, hr_director;
REVOKE
Syntax
REVOKE { SELECT | INSERT | UPDATE | DELETE } ON TABLE table_list FROM ROLE role_list REVOKE { SELECT | INSERT | UPDATE }(column_list) ON TABLE table_list FROM ROLE role_list REVOKE SELECT ON VIEW view_list FROM ROLE role_list REVOKE SELECT ON CHANGE STREAM change_stream_list FROM ROLE role_list REVOKE EXECUTE ON TABLE FUNCTION tvf_list FROM ROLE role_list REVOKE ROLE role_list FROM ROLE role_list and table_list is: table_name [, ...] and column_list is: column_name [,...] and view_list is: view_name [, ...] and change_stream_list is: change_stream_name [, ...] and tvf_list is: table_valued_function_name [, ...] and role_list is: database_role_name [, ...]
Description
For fine-grained access control, revokes privileges
on one or more tables, views, change streams, or table-valued functions from
database roles. Also
revokes database roles from other database roles. When revoking SELECT
,
INSERT
, or UPDATE
on a table, optionally revokes privileges on only a subset
of table columns.
Parameters
table_name
- The name of an existing table.
column_name
- The name of an existing column in the previously specified table.
view_name
- The name of an existing view.
change_stream_name
- The name of an existing change stream.
table_valued_function_name
- The name of an existing table-valued function for a change stream.
database_role_name
- The name of an existing database role.
Notes and restrictions
Identifiers for database objects named in the
REVOKE
statement must use the case that was specified when the object was created. For example, if you created a table with a name that is in all lower case with a capitalized first letter, you must use that same case in theREVOKE
statement. Table-valued functions (TVFs) get automatically created with a prefix added to the change stream name, so ensure that you use the proper case for both the prefix and the change stream name. For more information about TVFs, see Change stream query syntax.When revoking column-level privileges on multiple tables, each table must contain the named columns.
A
REVOKE
statement at the column level has no effect if privileges were granted at the table level.After revoking
SELECT
on a change stream from a role, revokeEXECUTE
on the change stream's table-valued function from that role.Revoking
SELECT
on a change stream doesn't revoke any privileges on the table that it tracks.
Examples
The following example revokes SELECT
on the employees
table from the role
hr_rep
.
REVOKE SELECT ON TABLE employees FROM ROLE hr_rep;
The next example revokes SELECT
on a subset of columns of the contractors
table from the role hr_rep
.
REVOKE SELECT(name, address, phone) ON TABLE contractors FROM ROLE hr_rep;
The next example shows revoking both table-level and column-level privileges in a single statement.
REVOKE SELECT, UPDATE(location) ON TABLE employees FROM ROLE hr_manager;
The next example revokes column-level grants on two tables. Both tables must
contain the location
column.
REVOKE SELECT(name, level, location), UPDATE(location) ON TABLE employees, contractors FROM ROLE hr_manager;
The next example revokes INSERT
on a subset of columns.
REVOKE INSERT(name, cost_center, location, manager) ON TABLE employees FROM ROLE hr_manager;
The following example revokes the database role pii_access
from the hr_manager
and
hr_director
database roles. The hr_manager
and hr_director
roles lose any privileges that
they inherited from pii_access
.
REVOKE ROLE pii_access FROM ROLE hr_manager, hr_director;
STATISTICS statements
ALTER STATISTICS
Changes the definition of a query optimizer statistics package.
Syntax
ALTER STATISTICS package_name action where package_name is: {a—z}[{a—z|0—9|_|-}+]{a—z|0—9} and action is: SET OPTIONS ( options_def ) and options_def is: { allow_gc = { true | false } }
Description
ALTER STATISTICS
changes the definition of a query optimizer statistics
package.
SET OPTIONS
- Use this clause to set an option on the specified statistics package.
Parameters
package_name
The name of an existing query optimizer statistics package whose attributes are to be altered.
To fetch existing statistics packages:
SELECT s.package_name AS package_name, s.allow_gc AS allow_gc FROM INFORMATION_SCHEMA.SPANNER_STATISTICS s;
options_def
- The
allow_gc = { true | false }
option allows you to specify whether a given statistics package is garbage collected. A package must be set asallow_gc=false
if it is used in a query hint. For more information, see Garbage collection of statistics packages.
ANALYZE
Start a new query optimizer statistics package construction.
Syntax
ANALYZE
Description
ANALYZE
starts a new query optimizer statistics package construction.
MODEL statements
CREATE MODEL and CREATE OR REPLACE MODEL
Use the CREATE MODEL
or CREATE OR REPLACE MODEL
statement to define an ML
model.
Syntax
{ CREATE MODEL | CREATE OR REPLACE MODEL | CREATE MODEL IF NOT EXISTS } model_name INPUT ( column_list ) OUTPUT ( column_list ) REMOTE [OPTIONS ( model_options )] where column_list is: { column_name data_type [, ... ] } and model_options is: { endpoint = '{endpoint_address}' }
Description
CREATE MODEL
registers a reference to the Vertex AI ML model in the current database.
If a model named model_name
already exists, the CREATE MODEL
statement fails.
CREATE OR REPLACE MODEL
registers a reference to the Vertex AI ML model in the
current database. If a model named model_name
already exists,
its definition is replaced.
CREATE MODEL IF NOT EXISTS
registers a reference to the Vertex AI ML model in the
current database. If a model named model_name
already exists,
the CREATE MODEL IF NOT EXISTS
statement does not have any effect.
As soon as the model reference is registered in a database, it can be used from queries that use the ML.Predict function.
Model registration doesn't result in copying a model from the Vertex AI to a database, but only in creation of a reference to this models' endpoint hosted in the Vertex AI. If the model's endpoint gets removed from the Vertex AI, Cloud Spanner queries referencing this model fail.
Model endpoint access control
To be able to access a registered Vertex AI model endpoint from Cloud Spanner, you need
to grant access permission to Cloud Spanner's service agent
account. For example, it can be done by granting the Vertex AI User
role to the service-{PROJECT}@gcp-sa-spanner.iam.gserviceaccount.com
account, where
{PROJECT}
is the ID of the project that hosts your Cloud Spanner instance.
If the Cloud Spanner service agent account doesn't exist for your project, create it by
running the following command after replacing PROJECT with the project name:
gcloud beta services identity create --service=spanner.googleapis.com --project={PROJECT}
.
Parameters
model_name
- The name of the model to be created. See Naming conventions.
INPUT ( column_list ) OUTPUT ( column_list )
- Lists of columns that define model inputs (e.g. a set of features) and outputs
(for example, labels). The following types (used in the
type
field ofcolumn_list
) are supported:BOOL
,BYTES
,FLOAT64
,INT64
,STRING
, andARRAY
of listed types.
model_options
Specifies connection settings for the remote ML serving (inference) service hosting the model.
endpoint is the address of the Vertex AI endpoint hosting the model provided in one of the following formats:
Full resource URI format:
//aiplatform.googleapis.com/projects/{project}/locations/{location}/endpoints/{endpoint}
.URL format:
https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/endpoints/{endpoint}
.
ALTER MODEL
Changes the definition of a model.
Syntax
ALTER MODEL [ IF EXISTS ] model_name SET OPTIONS ( model_options ) where model_options is: { endpoint = '{endpoint_address}' }
Description
ALTER MODEL
changes the definition of an existing table.
SET OPTIONS
- Use this clause to set an option on the specified model.
Parameters
model_name
- The name of an existing model whose attributes are to be altered.
model_options
Specifies connection settings for the remote ML serving (inference) service hosting the model.
endpoint is the address of the Vertex AI endpoint hosting the model provided in one of the following formats:
Full resource URI format:
//aiplatform.googleapis.com/projects/{project}/locations/{location}/endpoints/{endpoint}
.URL format:
https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/endpoints/{endpoint}
.
DROP MODEL
Removes a model.
Syntax
DROP MODEL [ IF EXISTS ] model_name
Description
Use the DROP MODEL
statement to remove a model definition from the database.
Unless the IF EXISTS
clause is specified, the statement fails if the model
doesn't exist.
After you delete a model definition, all SQL queries referencing the deleted model fail. Dropping a model definition does not affect the underlying the Vertex AI endpoint that this model is attached to.
Parameters
model_name
- The name of the model to drop.