GoogleSQL data definition language (original) (raw)

Skip to main content

GoogleSQL data definition language

Use the GoogleSQL data definition language (DDL) to do the following:

Notation

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 PRIMARY KEY,
  `Order` INT64
);

Names

The following rules apply to database IDs.

The following rules apply to names for schemas, tables,change streams, columns, constraints, indexes, roles,sequences, and views:

CREATE TABLE MyTable (col1 INT64 PRIMARY KEY);  
CREATE TABLE MYTABLE (col1 INT64 PRIMARY KEY);

The following snippet fails because two different objects use the same name: 

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

The following command fails with the message Table not found: singersbecause it uses a different case for the Singers table. 

CREATE INDEX SingersByFirstLastName ON singers(FirstName, LastName)  
CREATE TABLE MyTable2 (col1 INT64 PRIMARY KEY);

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);  
CREATE TABLE Singer (  
  Singer     INT64 NOT NULL PRIMARY KEY,  
  FirstName  STRING(1024),  
  LastName   STRING(1024),  
  BirthDate  DATE  
);

The following query succeeds because the table uses an alias when the table name is identical to the column name. 

SELECT S.FirstName, S.Singer FROM Singer S;

Data types

The following are the data types used in GoogleSQL.

Scalars

The syntax for using a scalar type in DDL is:

{ BOOL | INT64 | FLOAT32 | FLOAT64 | NUMERIC | STRING( length ) | JSON | BYTES( length ) | DATE | TIMESTAMP }

length: { int64value | MAX }

int64value: { decimalvalue | hexvalue }

decimalvalue: [-]0—9+

hexvalue: [-]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 a0x 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:

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 10 MB.

See Working with JSON andData types For more information.

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:

DATE

TIMESTAMP

Arrays

The syntax for using the ARRAY type in DDL is:

ARRAY<scalartype**> [(vector_length=>vectorlengthvalue**)]

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, SongNames ARRAY<STRING(MAX)> ) PRIMARY KEY (SingerId) ...;

Notes:

Protocol buffers

The syntax for using the protocol buffers (PROTO) data type in DDL is:

prototypename;

GoogleSQL supports PROTO and arrays of PROTO. Protocol buffers are a flexible, efficient mechanism for serializing structured data. For more information, see Work with protocol buffers in GoogleSQL.

The following is an example of a table named Singers with a SingerInfo proto message column and an SingerInfoArray proto message array column:

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL PRIMARY KEY,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo googlesql.example.SingerInfo,
 SingerInfoArray ARRAY<googlesql.example.SingerInfo>,
);

It has the following definition of the SingerInfo proto type:

  package googlesql.example;
  message SingerInfo {
  optional string    nationality = 1;
  repeated Residence residence   = 2;

    message Residence {
      required int64  start_year   = 1;
      optional int64  end_year     = 2;
      optional string city         = 3;
      optional string country      = 4;
    }
  }

SCHEMA statements

This section has information about the CREATE SCHEMA and DROP SCHEMAstatements.

CREATE SCHEMA

Creates a new schema and assigns a name.

CREATE SCHEMA [schema_name]

Parameters

**_schemaname_**

DROP SCHEMA

Removes a named schema.

DROP SCHEMA schema_name

Parameters

**_schemaname_**

Parameters

**_schemaname_**

DATABASE statements

This section has information about the CREATE DATABASE and ALTER DATABASEstatements.

CREATE DATABASE

When creating a GoogleSQL database, you must provide a CREATE DATABASEstatement, which defines the ID of the database:

CREATE DATABASE databaseid

where databaseid {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

Parameters

**_databaseid_**

ALTER DATABASE

Changes the definition of a database.

Syntax

ALTER DATABASE databaseid     action

where databaseid is: {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

and action is: SET OPTIONS ( options_def [, ... ] )

and optionsdef is: { default_leader = { 'region' | null } | optimizer_version = { 1 ... 8 | null } | optimizer_statistics_package = { 'packagename' | null } | version_retention_period = { 'duration' | null } | default_sequence_kind = { 'bitreversedpositive' | null } | default_time_zone = { 'timezonename' | null } | read_lease_regions = {'readleaseregionname [, ... ]' | null } | columnar_policy = {'columnarpolicy' | null } }

Description

ALTER DATABASE changes the definition of an existing database.

SET OPTIONS

Parameters

**_databaseid_**

**_optionsdef_**

LOCALITY GROUP statements

This section has information about the CREATE LOCALITY GROUP,ALTER LOCALITY GROUP, and DROP LOCALITY GROUP statements.

CREATE LOCALITY GROUP

Use the CREATE LOCALITY GROUP statement to define a locality group to store some columns separately or to use tiered storage. For more information, see Locality groupsand Tiered storage overview.

Syntax

CREATE LOCALITY GROUP localitygroupname [ storagedef ]

where storagedef is: { OPTIONS ( storage = '{ ssd | hdd }' [, ssd_to_hdd_spill_timespan='duration' ] ) }

Description

CREATE LOCALITY GROUP defines a new locality group in the current database.

Parameters

**_localitygroupname_**

**OPTIONS**

ALTER LOCALITY GROUP

Use the ALTER LOCALITY GROUP statement to change the storage option or age-based policy of a locality group.

Syntax

ALTER LOCALITY GROUP localitygroupname [ storagedef ]

where storagedef is: { SET OPTIONS ( [ storage = '{ ssd | hdd }' ssd_to_hdd_spill_timespan='duration' ] ) }

Description

ALTER LOCALITY GROUP changes the storage option or age-based policy of a locality group. You can change these options together or individually.

Parameters

**_localitygroupname_**

**OPTIONS**

DROP LOCALITY GROUP

Use the DROP LOCALITY GROUP statement to drop the locality group. You can't drop a locality group if it contains data. You must first move all data that's in the locality group to another locality group.

Syntax

DROP LOCALITY GROUP localitygroupname

Description

DROP LOCALITY GROUP drops the locality group.

PLACEMENT statements

This section has information about PLACEMENT statements.

CREATE PLACEMENT

Use the CREATE PLACEMENT statement to define a placement to partition row data in your database. For more information, see theGeo-partitioning overview.

Syntax

CREATE PLACEMENT placementname [ partitiondef ]

where partitiondef is: { OPTIONS ( instance_partition="partitionid" [, default_leader="leaderregionid" ] [, read_lease_regions = {'readleaseregionname[, ... ]' | null } ] ) }

Description

CREATE PLACEMENT defines a new placement in the current database.

Parameters

**_placementname_**

**_partitionid_**

**_leaderregionid_**

DROP PLACEMENT

Use the DROP PLACEMENT statement to delete a placement.

Syntax

DROP PLACEMENT placementname

Description

DROP PLACEMENT drops a placement.

Parameters

**_placementname_**

PROTO BUNDLE statements

The PROTO files you create need to be loaded into your database schema usingPROTO BUNDLE, making the PROTO files available for use by tables and queries keyed by PROTO and ENUM fields.

CREATE PROTO BUNDLE

Use the CREATE PROTO BUNDLE statement to load types available from imported proto files into the schema.

Syntax

CREATE PROTO BUNDLE ("
                      (<proto_type_name>) ("," <proto_type_name>)*
                    ")

Description

CREATE PROTO BUNDLE loads types available from imported proto files.

Parameters

prototypename

Notes:

ALTER PROTO BUNDLE

The ALTER PROTO BUNDLE statement is used to update the proto information stored in the schema.

Syntax

ALTER PROTO BUNDLE
[ INSERT ( <proto_type_name> , .... ) ]
[ UPDATE ( <proto_type_name> , .... ) ]
[ DELETE ( <proto_type_name> , .... ) ]

Description

ALTER PROTO BUNDLE updates the proto information already stored in the schema.

Parameters

prototypename

Notes:

DROP PROTO BUNDLE

The DROP PROTO BUNDLE statement is used to drop all proto type information stored in the schema.

Syntax

DROP PROTO BUNDLE

Description

DROP PROTO BUNDLE drops all proto type information stored in the schema.

Notes:

TABLE statements

This section has information about the CREATE TABLE, ALTER TABLE,DROP TABLE, AND RENAME TABLE statements.

CREATE TABLE

Defines a new table.

Syntax

CREATE TABLE [ IF NOT EXISTS ] tablename ( [ { columnname datatype [NOT NULL] [ { DEFAULT ( expression ) [ ON UPDATE ( expression )] | AS ( expression ) [ STORED ] | GENERATED BY DEFAULT AS IDENTITY [ ( sequenceoptionclause ... ) ] | AUTO_INCREMENT } ] [ HIDDEN ] [ PRIMARY KEY ] [ OPTIONS ( columnoptionsdef [ , ... ] ) ] | locationname STRING(MAX) NOT NULL PLACEMENT KEY | tableconstraint | synonymdefinition } [, ... ] ] ) [ PRIMARY KEY ( [columnname [ { ASC | DESC } ], ...] ) ] [, INTERLEAVE IN [PARENT] tablename [ ON DELETE { CASCADE | NO ACTION } ] ] [, ROW DELETION POLICY ( OLDER_THAN ( timestampcolumn, INTERVAL numdays DAY ) ) ] [, OPTIONS ( tableoptionsdef [ , ... ] ) ]

where datatype is: { scalar_type | array_type | proto_type_name }

and columnoptionsdef is: { allow_commit_timestamp = { true | null } | locality_group = 'localitygroupname' }

and tableconstraint is: [ CONSTRAINT constraintname ] { CHECK ( expression ) | FOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable ( refcolumn [, ... ] ) [ ON DELETE { CASCADE | NO ACTION } ] [ { ENFORCED | NOT ENFORCED } ] }

and synonymdefinition is: [ SYNONYM (synonym) ]

and sequenceoptionclause is: { BIT_REVERSED_POSITIVE | SKIP RANGE skiprangemin, skiprangemax | START COUNTER WITH startwithcounter }

and tableoptionsdef is: { locality_group = 'localitygroupname' | columnar_policy = 'columnarpolicy' | fulltext_dictionary_table = { true | false | null } | fulltext_dictionary_staleness = 'staleness' }

Description

CREATE TABLE defines a new table in the current database.

Parameters

IF NOT EXISTS

**_tablename_**

**_columnname_**

**_datatype_**

**_vectorlength_**

**_timestampcolumn_**

**_numdays_**

NOT NULL

HIDDEN

Hides a column if it shouldn't appear in SELECT * statements. If the column is hidden, you can still select it using its name. For example,SELECT Id, Name, ColHidden FROM TableWithHiddenColumn.

The primary use case for HIDDEN columns is to omit TOKENLIST columns from a SELECT * statement.

DEFAULT ( **_expression_** )

ON UPDATE ( **_expression_** )

GENERATED BY DEFAULT AS IDENTITY [ ( sequence_option_clause ... )]

AS ( **_expression_** ) [STORED]

For examples on how to work with generated columns, seeCreating and managing generated columns.

AUTO_INCREMENT

For examples of how to work with AUTO_INCREMENT, seePrimary key default values management.

**_locationname_** STRING(MAX) NOT NULL PLACEMENT KEY

PRIMARY KEY in column definition orPRIMARY KEY ( [ **_columnname_** [ { ASC | DESC } ], ...]in table definition

[, INTERLEAVE IN PARENT **_tablename_** [ ON DELETE { CASCADE | NO ACTION } ] ]

For more details, see Schema and data model.

INTERLEAVE IN **_parenttablename_**

CONSTRAINT **_constraintname_**

CHECK ( **_expression_** )

FOREIGN KEY ( **_columnname_** [, ... ] ) REFERENCES **_reftable_** ( **_refcolumn_** [, ... ] [ ON DELETE { CASCADE | NO ACTION } ] [ { ENFORCED | NOT ENFORCED } ] )

For more information, see Foreign keys.

**_columnoptionsdef_**

**_tableoptionsdef_**

[, ROW DELETION POLICY ( OLDER_THAN ( **_timestampcolumn_** , INTERVAL **_numdays_** DAY ) ) ]

SYNONYM (**_synonym_**)

ALTER TABLE

Changes the definition of a table.

Syntax

ALTER TABLE [ IF EXISTS ] tablename     action

where action is: ADD SYNONYM synonym DROP SYNONYM synonym RENAME TO newtablename [, ADD SYNONYM synonym] ADD [ COLUMN ] [ IF NOT EXISTS] columnname datatype [ columnexpression ] [ columnoptionsdef ]     DROP [ COLUMN ] columnname ADD tableconstraint DROP CONSTRAINT constraintname SET ON DELETE { CASCADE | NO ACTION } SET INTERLEAVE IN [ PARENT ] parenttablename [ ON DELETE { CASCADE | NO ACTION } ] ALTER [ COLUMN ] columnname { datatype [ NOT NULL ] [ { DEFAULT ( expression ) [ ON UPDATE ( expression )] | AS ( expression ) | GENERATED BY DEFAULT AS IDENTITY [ ( sequenceoptionclause ... ) ] } ] | SET OPTIONS ( columnoptionsdef [ , ... ] ) | SET DEFAULT ( expression ) | DROP DEFAULT | SET ON UPDATE ( expression ) | DROP ON UPDATE | ALTER IDENTITY { SET { SKIP RANGE skiprangemin, skiprangemax | NO SKIP RANGE } | RESTART COUNTER WITH counterrestart } } ADD ROW DELETION POLICY ( OLDER_THAN ( timestampcolumn, INTERVAL numdays DAY )) DROP ROW DELETION POLICY REPLACE ROW DELETION POLICY ( OLDER_THAN ( timestampcolumn, INTERVAL numdays DAY )) SET OPTIONS ( tableoptionsdef [ , ... ] )

and datatype is: { scalartype | arraytype }

and columnexpression is: [ NOT NULL ] [ { DEFAULT ( expression ) [ ON UPDATE ( expression )] | AS ( expression ) STORED | GENERATED BY DEFAULT AS IDENTITY [ ( sequenceoptionclause ) ] } ]

and columnoptionsdef is: allow_commit_timestamp = { true | null } | locality_group = 'localitygroupname'

and tableconstraint is: [ CONSTRAINT constraintname ] { CHECK ( expression ) | FOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable ( refcolumn [, ... ] ) [ ON DELETE { CASCADE | NO ACTION } ] [ { ENFORCED | NOT ENFORCED } ] }

and tableoptionsdef is: { locality_group = 'localitygroupname' | columnar_policy = { 'columnarpolicy' | null } | fulltext_dictionary_staleness = 'staleness' }

Description

ALTER TABLE changes the definition of an existing table.

ADD SYNONYM **_synonym_**

DROP SYNONYM **_synonym_**

RENAME TO **_newtablename_**

RENAME TO **_newtablename_** [, ADD SYNONYM **_synonym_**]

ADD COLUMN

DROP COLUMN

ADD **_tableconstraint_**

DROP CONSTRAINT **_constraintname_**

SET ON DELETE { CASCADE | NO ACTION }

SET INTERLEAVE IN [ PARENT ] **_parenttablename_** [ ON DELETE { CASCADE | NO ACTION } ]

    SELECT pk1, pk2 FROM child  
    EXCEPT DISTINCT  
    SELECT pk1, pk2 FROM parent;

ALTER COLUMN

ADD ROW DELETION POLICY ( OLDER_THAN ( **_timestampcolumn_** , INTERVAL **_numdays_** DAY ) )

DROP ROW DELETION POLICY

REPLACE ROW DELETION POLICY ( OLDER_THAN ( **_timestampcolumn_** , INTERVAL **_numdays_** DAY ) )

SET OPTIONS ( **_tableoptionsdef_** )

Parameters

IF EXISTS

**_tablename_**

**_columnname_**

**_datatype_**

**_columnoptionsdef_**

**_tableconstraint_**

**_constraintname_**

**_reftable_**

**_refcolumn_**

DROP TABLE

Removes a table.

Syntax

DROP TABLE [ IF EXISTS ] tablename

Description

Use the DROP TABLE statement to remove a table from the database.

Parameters

IF EXISTS

**_tablename_**

RENAME TABLE

Renames a table or multiple tables at once.

Syntax

RENAME TABLE oldtablename TO newtablename ... [, oldtablename2 TO newtablename2 ...]

Description

Renames a table or multiple tables simultaneously, for example, if the table name is misspelled. For more information, see Rename a table.

Parameters

**_oldtablename_**

**_newtablename_**

Example

This example shows how to change the names of multiple tables atomically.

RENAME TABLE Singers TO Artists, Albums TO Recordings;

INDEX statements

This section has information about the CREATE INDEX, ALTER INDEX, and DROP INDEX statements.

CREATE INDEX

Use the CREATE INDEX statement to definesecondary indexes.

Syntax

CREATE [ UNIQUE ] [ NULL_FILTERED ] INDEX [ IF NOT EXISTS ] indexname ON tablename ( keypart [, ...] ) [ storingclause ] [ whereclause ] [ , interleaveclause ] [ OPTIONS ( indexoptions [, ...] ) ]

where indexname is: {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

and keypart is: columnname [ { ASC | DESC } ]

and storingclause is: STORING ( columnname [, ...] )

and whereclause is: WHERE columnname IS NOT NULL [AND ...]

and interleaveclause is: INTERLEAVE IN tablename

and indexoptions is: { locality_group = 'localitygroupname' | columnar_policy = 'columnarpolicy' }

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

NULL_FILTERED

IF NOT EXISTS

**_indexname_**

**_tablename_**

WHERE IS NOT NULL

INTERLEAVE IN

DESC

STORING

index_options

ALTER INDEX

Use the ALTER INDEX statement to add additional columns or remove stored columns from the secondary indexes or to modify index options.

Syntax

ALTER INDEX indexname {ADD|DROP} STORED COLUMN columnname [ OPTIONS ( indexoptions [, ...] ) ]

where indexoptions is: { locality_group = 'localitygroupname' | columnar_policy = { 'columnarpolicy' | null } }

Description

Add an additional column into an index, remove a column from an index or modify index options.

Parameters

**_indexname_**

**_columnname_**

**_indexoptions_**

DROP INDEX

Removes a secondary index.

Syntax

DROP INDEX [ IF EXISTS ] indexname

Description

Use the DROP INDEX statement to drop a secondary index.

Parameters

IF EXISTS

**_indexname_**

SEARCH INDEX statements

This section has information about the CREATE SEARCH INDEX, ALTER SEARCH INDEX, and DROP SEARCH INDEX statements.

CREATE SEARCH INDEX

Use the CREATE SEARCH INDEX statement to define search indexes. For more information, see Search indexes.

Syntax

CREATE SEARCH INDEX indexname ON tablename ( tokencolumnlist ) [ storingclause ] [ partitionclause ] [ orderbyclause ] [ whereclause ] [ interleaveclause ] [ optionsclause ]

where indexname is: {a—z|A—Z}[{a—z|A—Z|0—9|_}+]

and tokencolumnlist is: columnname [, ...]

and storingclause is: STORING ( columnname [, ...] )

and partitionclause is: PARTITION BY columnname [, ...]

and orderbyclause is: ORDER BY columnname [ {ASC | DESC} ]

and whereclause is: WHERE columnname IS NOT NULL [AND ...]

and interleaveclause is: , INTERLEAVE IN tablename

and optionsclause is: OPTIONS ( optionname=optionvalue [, ...] )

Description

You can use CREATE SEARCH INDEX to create search indexes for TOKENLISTcolumns. Adding a search index on a column makes it more efficient to search data in the source column of the TOKENLIST.

Parameters

**_indexname_**

**_tablename_**

token_column_list

STORING

PARTITION BY

ORDER BY

WHERE IS NOT NULL

INTERLEAVE IN

OPTIONS

ALTER SEARCH INDEX

Use the ALTER SEARCH INDEX statement to add or remove columns from the search indexes.

Syntax

ALTER SEARCH INDEX indexname {ADD|DROP} [STORED] COLUMN columnname

Description

Add a TOKENLIST column into a search index or remove an existing TOKENLISTcolumn from a search index. Use STORED COLUMN to add or remove stored columns from a search index.

Parameters

**_indexname_**

**_columnname_**

DROP SEARCH INDEX

Removes a search index.

Syntax

DROP SEARCH INDEX [ IF EXISTS ] indexname

Description

Use the DROP SEARCH INDEX statement to drop a search index.

Parameters

IF EXISTS

**_indexname_**

VIEW statements

This section has information about the CREATE VIEW, CREATE OR REPLACE VIEW, and DROP VIEW statements.

CREATE VIEW and CREATE OR REPLACE VIEW

Use the CREATE VIEW or CREATE OR REPLACE VIEW statement to define aview.

Syntax

{ CREATE VIEW | CREATE OR REPLACE VIEW } viewname SQL SECURITY { INVOKER | DEFINER } AS query

Description

CREATE VIEW defines a new view in the current database. If a view named_viewname_ exists, the CREATE VIEW statement fails.

CREATE OR REPLACE VIEW defines a new view in the current database. If a view named _viewname_ exists, its definition is replaced. Use this statement to replace the security type of a view.

Parameters

**_viewname_**

SQL SECURITY

AS **_query_**

DROP VIEW

Removes a view.

Syntax

DROP VIEW [ IF EXISTS ] viewname

Description

Use the DROP VIEW statement to remove a view from the database. Unless the IF EXISTS clause is specified, the statement fails if the view doesn't exist.

Parameters

**_IF EXISTS_**

**_viewname_**

CHANGE STREAM statements

This section has information about the CREATE CHANGE STREAM, ALTER CHANGE STREAM, and DROP CHANGE STREAM statements.

CREATE CHANGE STREAM

Defines a new change stream.

Syntax

CREATE CHANGE STREAM [ IF NOT EXISTS ] changestreamname [ FOR { tablecolumns [, ... ] | ALL } ] [ OPTIONS ( changestreamoption [, ... ] ) ]

where tablecolumns is: tablename [ ( [ columnname, ... ] ) ]

and changestreamoption is: { retention_period = 'duration' | value_capture_type = { 'OLDANDNEWVALUES' | 'NEWROW' |'NEWVALUES' | 'NEWROWANDOLDVALUES' } | exclude_ttl_deletes = { false | true } | exclude_insert = { false | true } | exclude_update = { false | true } | exclude_delete = { false | true } | allow_txn_exclusion = { false | true } }

Description

CREATE CHANGE STREAM defines a new change stream in the current database. For more information, seeCreate a change stream.

Parameters

IF NOT EXISTS

**_changestreamname_**

FOR { **_tablecolumns_** [, ... ] | ALL }

OPTIONS ( **_changestreamoption_** [, ... ] )

ALTER CHANGE STREAM

Changes the definition of a change stream.

Syntax

ALTER CHANGE STREAM changestreamname action

where action is: { SET FOR { tablecolumns [, ... ] | ALL } | DROP FOR ALL | SET OPTIONS ( changestreamoption [, ... ] ) }

and tablecolumns is: tablename [ ( [ columnname, ... ] ) ]

and changestreamoption is: { retention_period = { 'duration' | null } | value_capture_type = { 'OLDANDNEWVALUES' | 'NEWROW' | 'NEWVALUES' | 'NEWROWANDOLDVALUES' | null } | exclude_ttl_deletes = { false | true | null } | exclude_insert = { false | true | null } | exclude_update = { false | true | null } | exclude_delete = { false | true | null } | allow_txn_exclusion = { false | true | null } }

Description

ALTER CHANGE STREAM changes the definition of an existing change stream. For more information, seeModify a change stream.

Parameters

**_changestreamname_**

SET FOR { **_tablecolumns_** [, ... ] | ALL }

DROP FOR ALL

SET OPTIONS

DROP CHANGE STREAM

Removes a change stream.

Syntax

DROP CHANGE STREAM [ IF EXISTS ] changestreamname

Description

Use the DROP CHANGE STREAM statement to remove a change stream from the database and delete its data change records.

Parameters

IF EXISTS

**_changestreamname_**

ROLE statements

This section has information about the CREATE ROLE and DROP ROLE statements.

CREATE ROLE

Defines a new database role.

Syntax

CREATE ROLE databaserolename

Description

CREATE ROLE defines a new database role. Database roles are collections offine-grained access control privileges. You can create only one role with this statement.

Parameters

**_databaserolename_**

Example

This example creates the database role hr_manager.

CREATE ROLE hr_manager

DROP ROLE

Drops a database role.

Syntax

DROP ROLE databaserolename

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 access to it is 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

**_databaserolename_**

Example

This example drops the database role hr_manager.

DROP ROLE hr_manager

GRANT and REVOKE statements

This section has information about the GRANT and REVOKE statements.

GRANT

Grants privileges that allow database roles to access database objects.

Syntax

GRANT { SELECT | INSERT | UPDATE | DELETE } ON TABLE tablelist | ON ALL TABLES IN SCHEMA schema_name [, ...] TO ROLE rolelist

GRANT { SELECT | INSERT | UPDATE } (columnlist) ON TABLE tablelist TO ROLE rolelist

GRANT SELECT ON CHANGE STREAM changestreamlist | ON ALL CHANGE STREAMS IN SCHEMA schema_name [, ...] TO ROLE rolelist

GRANT { SELECT | UPDATE } ON SEQUENCE sequencelist | ON ALL SEQUENCES IN SCHEMA schema_name [, ...] TO ROLE rolelist

GRANT SELECT ON VIEW viewlist | ON ALL VIEWS IN SCHEMA schema_name [, ...] TO ROLE rolelist

GRANT EXECUTE ON TABLE FUNCTION functionlist TO ROLE rolelist

GRANT ROLE rolelist TO ROLE rolelist

GRANT USAGE ON SCHEMA [DEFAULT | schemanamelist] TO ROLE rolelist

where tablelist is: tablename [, ...]

and columnlist is: columnname [,...]

and viewlist is: viewname [, ...]

and changestreamlist is: changestreamname [, ...]

and sequencelist is: sequencename [, ...]

and functionlist is: changestreamreadfunctionname [, ...]

and schemanamelist is: schemaname [, ...]

and rolelist is: databaserolename [, ...]

Description

For fine-grained access control, grants privileges on one or more tables, views, change streams, SEQUENCE objects, or change stream read 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

**_tablename_**

**_columnname_**

**_viewname_**

**_changestreamname_**

**_changestreamreadfunctionname_**

**_schemaname_**

**_sequencename_**

**_databaserolename_**

Notes and restrictions

Examples

The following example grants SELECT on the employees table to the hr_reprole. 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 contractorstable 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_managercan 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 name, level, and location columns.

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 employeestable.

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_managerand hr_director. The hr_manager and hr_director roles are members ofpii_access and inherit the privileges that were granted to pii_access. For more information, see Database role hierarchies and inheritance.

GRANT ROLE pii_access TO ROLE hr_manager, hr_director;

The following example grants SELECT and UPDATE on the sequence_objectsequence to the app_developer role.

GRANT SELECT, UPDATE ON SEQUENCE sequence_object TO ROLE app_developer;

The next example grants SELECT on all sequences in the default schema to the data_analyst role.

GRANT SELECT ON ALL SEQUENCES IN SCHEMA default TO ROLE data_analyst;

REVOKE

Revokes privileges that allow database roles access to database objects.

Syntax

REVOKE { SELECT | INSERT | UPDATE | DELETE } ON TABLE tablelist | ON ALL TABLES IN SCHEMA schema_name [, ...] FROM ROLE rolelist

REVOKE { SELECT | INSERT | UPDATE }(columnlist) ON TABLE tablelist FROM ROLE rolelist

REVOKE SELECT ON VIEW viewlist FROM ROLE rolelist

REVOKE SELECT ON CHANGE STREAM changestreamlist FROM ROLE rolelist

REVOKE { SELECT | UPDATE } ON SEQUENCE sequencelist | ON ALL SEQUENCES IN SCHEMA schema_name [, ...] FROM ROLE rolelist

REVOKE EXECUTE ON TABLE FUNCTION functionlist FROM ROLE rolelist

REVOKE ROLE rolelist FROM ROLE rolelist

and tablelist is: tablename [, ...]

and columnlist is: columnname [,...]

and viewlist is: viewname [, ...]

and changestreamlist is: changestreamname [, ...]

and sequencelist is: sequencename [, ...]

and functionlist is: changestreamreadfunctionname [, ...]

and rolelist is: databaserolename [, ...]

Description

For fine-grained access control, revokes privileges on one or more tables, views, change streams, SEQUENCE objects, or change stream read 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

**_tablename_**

**_columnname_**

**_viewname_**

**_changestreamname_**

**_changestreamreadfunctionname_**

**_databaserolename_**

**_sequencename_**

Notes and restrictions

Examples

The following example revokes SELECT on the employees table from the rolehr_rep.

REVOKE SELECT ON TABLE employees FROM ROLE hr_rep;

The next example revokes SELECT on a subset of columns of the contractorstable 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 name, level, and location columns.

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 thehr_manager and hr_director database roles. The hr_manager andhr_director roles lose any privileges that they inherited from pii_access.

REVOKE ROLE pii_access FROM ROLE hr_manager, hr_director;

The following example revokes UPDATE on the sequence_object sequence from theapp_developer role.

REVOKE UPDATE ON SEQUENCE sequence_object FROM ROLE app_developer;

SEQUENCE statements

This section has information about the CREATE SEQUENCE,ALTER SEQUENCE, andDROP SEQUENCE` statements.

CREATE SEQUENCE

Creates a sequence object.

Syntax

CREATE SEQUENCE [ IF NOT EXISTS ] sequencename [ sequenceoptionclause ... ] [ OPTIONS ( sequenceoptions ) ]

where sequenceoptionclause is: BIT_REVERSED_POSITIVE | SKIP RANGE skiprangemin, skiprangemax | START COUNTER WITH startwithcounter

Description

When you use a CREATE SEQUENCE statement, Spanner creates a schema object that you can poll for values using the GET_NEXT_SEQUENCE_VALUEfunction.

Parameters

**IF NOT EXISTS**

**_sequencename_**

**OPTIONS (_sequenceoptions_**)

OPTIONS (option_name = value [,...])

A sequence accepts the following options:

Examples

# Create a positive bit-reversed sequence to use in a primary key.

CREATE SEQUENCE MySequence OPTIONS (
    sequence_kind='bit_reversed_positive',
    skip_range_min = 1,
    skip_range_max = 1000,
    start_with_counter = 50);

# Create a table that uses the sequence for a key column.
CREATE TABLE Singers (
  SingerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE MySequence)),
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo googlesql.example.SingerInfo,
  BirthDate  DATE
) PRIMARY KEY (SingerId);

Use the following SQL to query information about sequences.

SELECT * FROM information_schema.sequences;
SELECT * FROM information_schema.sequence_options;

ALTER SEQUENCE

Makes changes to the sequence object.

Syntax

ALTER SEQUENCE sequencename { SET OPTIONS sequenceoptions | sequenceoptionclause ... }

where sequenceoptionclause is: { { SKIP RANGE skiprangemin, skiprangemax | NO SKIP RANGE } | RESTART COUNTER WITH counterrestart }

Description

ALTER SEQUENCE makes changes to the specified sequence schema object. Executing this statement doesn't affect values the sequence already generated. If the ALTER SEQUENCE statement doesn't include an option, the current value of the option remains the same.

Parameters

**_sequencename_**

**SET OPTIONS (_sequenceoptions_**)

SET OPTIONS (option_name = value [,...])

This parameter offers the same options as CREATE SEQUENCE.

Examples

# Alter the sequence to include a skipped range. This is useful when you are
# migrating from a regular sequence with sequential data
ALTER SEQUENCE MySequence
SET OPTIONS (skip_range_min=1, skip_range_max=1234567);

DROP SEQUENCE

Drops a specific sequence.

Syntax

DROP SEQUENCE [IF EXISTS] sequencename

Description

DROP SEQUENCE drops a specific sequence. Spanner can't drop a sequence if its name appears in a sequence function that is used in a column default value or a view.

Parameters

**_sequencename_**

STATISTICS statements

This section has information about the ALTER STATISTICS and ANALYZEstatements.

ALTER STATISTICS

Changes the definition of a query optimizer statistics package.

Syntax

ALTER STATISTICS packagename action

where packagename is: {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

and action is: SET OPTIONS ( options_def )

and optionsdef is: { allow_gc = { true | false } }

Description

ALTER STATISTICS changes the definition of a query optimizer statistics package.

SET OPTIONS

Parameters

**_packagename_**

SELECT s.package_name AS package_name, s.allow_gc AS allow_gc FROM INFORMATION_SCHEMA.SPANNER_STATISTICS s;

**_optionsdef_**

ANALYZE

Start a new query optimizer statistics package construction.

Syntax

ANALYZE

Description

ANALYZE starts a new query optimizer statistics package construction.

MODEL statements

This section has information about the CREATE MODEL, ALTER MODEL, andDROP 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 } modelname [INPUT ( columnlist ) OUTPUT ( columnlist )] REMOTE [OPTIONS ( modeloptions )]

where columnlist is: { columnname datatype [OPTIONS ( modelcolumnoptions )] [, ... ] }

and modelcolumnoptions is: { required = { true | false } }

and modeloptions is: { endpoint = '{endpointaddress}', endpoints = [ '{endpointaddress}' [, ...] ], default_batch_size = int64value }

Description

CREATE MODEL registers a reference to the Gemini Enterprise Agent Platform 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 Agent Platform 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 Agent Platform 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 and no error is generated.

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 Agent Platform to a database, but only in creation of a reference to this models' endpoint hosted in the Agent Platform. If the model's endpoint gets removed from the Agent Platform, Spanner queries referencing this model fail.

Model endpoint access control

To be able to access a registered Agent Platform model endpoint from Spanner, you need to grant access permission to Spanner's service agent account.

Spanner creates the service agent and grants the necessary permissions when Spanner executes the first MODEL DDL statement. If both the Spanner database and the Agent Platform endpoint are in the same project, then no additional setup is required.

If the Spanner service agent account doesn't exist for your Spanner project, create it by running the following command:

gcloud beta services identity create --service=spanner.googleapis.com --project={PROJECT}`

Follow the steps described in the following tutorial to grant the Spanner API Service Agent role to the Spanner service agent accountservice-{PROJECT}@gcp-sa-spanner.iam.gserviceaccount.comon your Agent Platform project.

Parameters

**_modelname_**

**_INPUT ( columnlist ) OUTPUT ( columnlist )_**

**_modelcolumnoptions_**

**_modeloptions_**

ALTER MODEL

Changes the definition of a model.

Syntax

ALTER MODEL [ IF EXISTS ] modelname SET OPTIONS ( model_options )

where modeloptions is: { endpoint = '{endpointaddress}', endpoints = [ '{endpointaddress}' [, ...] ], default_batch_size = int64value }

Description

ALTER MODEL changes the definition of an existing table.

Parameters

**_modelname_**

SET OPTIONS

DROP MODEL

Removes a model.

Syntax

DROP MODEL [ IF EXISTS ] modelname

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 Agent Platform endpoint that this model is attached to.

Parameters

**_modelname_**

VECTOR INDEX statements

If you have a table with a large amount of vector data, you can use a vector index to perform similarity searches and approximate nearest neighbor (ANN)queries efficiently, with the trade-off of reduced recalland more approximate results.

CREATE VECTOR INDEX

Creates a new vector index on a column of a table.

Syntax

CREATE VECTOR INDEX [ IF NOT EXISTS ] indexname ON tablename (columnname [, extrakeycolumnname, ...] ) [ STORING ( columnname [, ...] ) ] [ WHERE columnname IS NOT NULL ] OPTIONS(index_option_list)

Parameters

IF NOT EXISTS

**_indexname_**

**_tablename_**

**_columnname_**

**_extrakeycolumnname_**

WHERE IS NOT NULL

STORING

index_option_list

Description

You can only create a new vector index on a column of a table.

index_option_list

The index option list specifies options for the vector index. Spanner creates tree-based vector indexes which use a tree-like structure to partition vector data. Using index_option_list, you can define the specific distance metric and search tree specification used to create the vector index. Specify the options in the following format: NAME=VALUE, ....

The following index options are supported:

NAME VALUE Details
distance_type STRING Required. The distance metric used to build the vector index. This value can be COSINE,DOT_PRODUCT, or EUCLIDEAN.
tree_depth INT The tree depth (level). This value can be either 2 or 3. A tree with 2 levels only has leaves (num_leaves) as nodes. If the dataset has more than 100 million rows, then you can use a tree with 3 levels and add branches (num_branches) to further partition the dataset.
num_leaves INT The number of leaves (i.e. potential partitions) for the vector data. You can designate num_leaves for trees with 2 or 3 levels. We recommend that the number of leaves is number_of_rows_in_dataset/1000.
num_branches INT The number of branches to further parititon the vector data. You can only designate num_branches for trees with 3 levels. The number of branches must be fewer than the number of leaves. We recommend that the number of leaves is between 1000 andsqrt(number_of_rows_in_dataset).
disable_search BOOL The disable_search = true option prevents Spanner from using a vector index in your database. If you use the FORCE_INDEX hint to specify a vector index which has the disable_search option set to true, the query fails.

Examples

The following example creates a vector index Singer_vector_index on theembedding column of the Singers table and defines the distance type:

CREATE TABLE Singers(id INT64, genre STRING, embedding ARRAY<FLOAT32>(vector_length=>128))
PRIMARY KEY(id);

CREATE VECTOR INDEX Singer_vector_index ON Singers(embedding)
STORING (genre)
WHERE embedding IS NOT NULL
OPTIONS(distance_type = 'COSINE');

The following example creates a vector index Singer_vector_index on theembedding column of the Singers table and defines the distance type and search tree specifications, which are optional:

CREATE TABLE Singers(id INT64, embedding ARRAY<FLOAT32>(vector_length=>128))
PRIMARY KEY(id);

CREATE VECTOR INDEX Singer_vector_index ON Singers(embedding)
STORING (genre)
WHERE embedding IS NOT NULL
OPTIONS(distance_type = 'COSINE', tree_depth = 3, num_branches = 1000, num_leaves = 1000000);

ALTER VECTOR INDEX statement

Use the ALTER VECTOR INDEX statement to add additional stored columns or remove stored columns from the vector index.

Syntax

ALTER VECTOR INDEX indexname action

where action is: { ADD STORED COLUMN columnname | DROP STORED COLUMN columnname | SET OPTIONS ( options_def ) }

and optionsdef is: { disable_search = { true | false | null } }

Parameters

**_indexname_**

**_columnname_**

**_optionsdef_**

Description

Add an additional stored column into a vector index or remove a stored column from the index.

Examples

The following ALTER VECTOR INDEX statement modifies the vector index by removing the stored columngenre:

ALTER VECTOR INDEX Singer_vector_index
DROP STORED COLUMN genre;

DROP VECTOR INDEX statement

Deletes a vector index on a table.

Syntax

DROP [ VECTOR ] INDEX index_name;

Parameters

Example

The following example deletes the vector index Singer_vector_index:

DROP VECTOR INDEX Singer_vector_index;

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-06-15 UTC.