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

• Create and alter a database.
• Create and drop a placement.
• Create, alter, or drop a locality group.
• Create, alter, or drop tables in a database.
• Add, alter, or drop columns in a table.
• Create, alter, or drop indexes in a database.
• Create, alter, or drop search 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 repeatin a comma-separated list.item [, ...] indicates one or moreitems, 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 eitherside of it.
• The plus sign "+" indicates that the preceding item can repeat.

Reserved keywords

Some words have special meaning in the GoogleSQL language and arereserved 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,seeGoogleSQL lexical structure and syntax.

For example:

CREATETABLEMyTable(RowIdINT64NOTNULLPRIMARYKEY,`Order`INT64);

Names

The following rules apply todatabase IDs.

• Must start with a lowercase letter.
• Can contain lowercase letters, numbers, underscores, and hyphens, but notuppercase letters.
• Cannot end with an underscore or hyphen.
• Must be enclosed in backticks (`) if it's a reserved word or containsa hyphen.
• Can be between 2-30 characters long.
• Cannot be changed after you create it.

The following rules apply to names forschemas,tables,change streams,columns,constraints,indexes,roles,sequences, andviews:

• 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, butnot hyphens.

• Spanner objects can't be created with the same name asanother object in the same database, including names that only differin capitalization. For example, the second statement in the followingsnippet fails because the table names differ only by case.

  CREATETABLEMyTable(col1INT64PRIMARYKEY);CREATETABLEMYTABLE(col1INT64PRIMARYKEY);

  The following snippet fails because two different objects use the samename:

  CREATETABLEMyTable(col1INT64PRIMARYKEY);CREATESCHEMAMyTable;

• When referring to other schema objects in a DDL statement (for example, acolumn name for a primary key, or table and column names in an index), makesure to use the original case for the name of each entity. As an example,consider the tableSingers created with the following statement.

  CREATETABLESingers(SingerIdINT64NOTNULLPRIMARYKEY,FirstNameSTRING(1024),LastNameSTRING(1024),SingerInfoBYTES(MAX),BirthDateDATE);

  The following command fails with the messageTable not found: singersbecause it uses a different case for theSingers table.

  CREATEINDEXSingersByFirstLastNameONsingers(FirstName,LastName)

• Schema object names are case insensitive in SQL queries. As an example,consider the tableMyTable2 created with the following statement.

  CREATETABLEMyTable2(col1INT64PRIMARYKEY);

  The following queries all succeed because schema object names arecase-insensitive for queries.

  SELECTcol1FROMMyTable2LIMIT1;SELECTCOL1FROMMYTABLE2LIMIT1;SELECTCOL1FROMmytable2LIMIT1;INSERTINTOMYTABLE2(col1)VALUES(1);

• When a column name in a table is identical to the table name, the table mustuse an alias for the query to work. As an example, consider the tableSinger created with the following statement.

  CREATETABLESinger(SingerINT64NOTNULLPRIMARYKEY,FirstNameSTRING(1024),LastNameSTRING(1024),BirthDateDATE);

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

  SELECTS.FirstName,S.SingerFROMSingerS;

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:    {int64_value | MAX }int64_value:    {decimal_value |hex_value }decimal_value:    [-]0—9+hex_value:    [-]0x{0—9|a—f|A—F}+

Anint64_value must correspond to an integer from -9,223,372,036,854,775,808(-2⁶³) to 9,223,372,036,854,775,807 (2⁶³ − 1). It can bespecified with decimal or hexadecimal notation. The hexadecimal form requires a0x prefix, with a lowercasex.

STRING

STRING is a variable length Unicode character string. Its value must be avalid Unicode string. Length is required, and represents the maximum number ofUnicode 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 Unicodestring 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 setlength to the convenience valueMAX, which is equivalent to2621440 for validation purposes.

  Only the actual length of the stored string impacts storage costs; specifyingMAX does not use any additional storage capacity.

• GoogleSQL requires Unicode strings to be UTF-8 encoded on receipt atthe server.

• Collation is done by Unicode character numerical value (technically bycodepoint, which is subtly different due tocombining characters). ForASCII strings, this is the standard lexicographical sort order.

• You can reduce the length of a column after the table has been created, butdoing so requires Spanner tovalidatethat the existing data is within the length constraint.

JSON

JSON is a variable length Unicode character string representing a JSONobject. The string must be UTF-8 encoded on receipt at the server. The maximumlength of the JSON value is 10 MB.

SeeWorking with JSON andData types For more information.

BYTES

BYTES is a variable length binary string. Length is required, and representsthe maximum number of bytes that can be stored in the field.

Notes:

• Writes to the column are rejected if the new value exceeds the specifiedlength.

• length can be an integer in the range [1, 10485760] or theconvenience valueMAX, which is equivalent to 10485760 for validationpurposes.

  Only the actual stored bytes impact storage costs; specifyingMAX does not use any additional storage capacity.

• You can reduce the length of a column after the table has been created, butdoing so requires Spanner tovalidatethat 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 writeto a date column is rejected if the value is outside of that interval.
• For more information and to see the canonical format, seeData Types.

TIMESTAMP

• A timestamp with nanosecond precision.
• Timezone-independent, over the range [0001-01-01 00:00:00 to10000-01-01 00:00:00].
• For more information and to see the canonical format, seeData Types.

Arrays

The syntax for using theARRAY type in DDL is:

ARRAY<scalar_type> [(vector_length=>vector_length_value)]

GoogleSQL supports arrays of scalars. The primary purpose of arrays isto store a collection of values in a space efficient way. Arrays are notdesigned to provide access to individual elements; to read or write a singleelement, you must read or write the entire array.

If your application uses data structures like vectors or repeated fields, youcan persist their state in a GoogleSQL array.

Here's an example of an alternate definition ofSingers that uses multiplecolumns ofARRAY type:

CREATETABLESingers(SingerIdINT64,FeaturedSingerIdsARRAY<INT64>,SongNamesARRAY<STRING(MAX)>)PRIMARYKEY(SingerId)...;

Notes:

• Arrays with subtypeARRAY (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 aCREATE TABLE statement, you can create columns ofARRAY type withaNOT NULL annotation.

  After you create the table, you cannot add a column ofARRAY type withaNOT NULL annotation, and you cannot add aNOT NULL annotation toan existing column ofARRAY type.

• vector_length sets an array column to a fixed size for use in a vectorsearch. The value must be an integer greater than or equal to zero. You canonly use this parameter with an array that uses theFLOAT32 orFLOAT64data types. That is,ARRAY<FLOAT32> (vector_length=>INT) orARRAY<FLOAT64> (vector_length=>INT). Setting an array withvector_lengthis required to performapproximate nearest neighborsvector search. It can also provide performance benefits when performingK-nearest neighbors vector search.You can create this array column and set its value usingCREATE TABLE.

Protocol buffers

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

proto_type_name;

GoogleSQL supportsPROTO and arrays ofPROTO. Protocol buffers area flexible, efficient mechanism for serializing structured data. For moreinformation, seeWork with protocol buffers in GoogleSQL.

The following is an example of a table namedSingers with aSingerInfo protomessage column and anSingerInfoArray proto message array column:

CREATETABLESingers(SingerIdINT64NOTNULLPRIMARYKEY,FirstNameSTRING(1024),LastNameSTRING(1024),SingerInfogooglesql.example.SingerInfo,SingerInfoArrayARRAY<googlesql.example.SingerInfo>,);

It has the following definition of theSingerInfo proto type:

packagegooglesql.example;messageSingerInfo{optionalstringnationality=1;repeatedResidenceresidence=2;messageResidence{requiredint64start_year=1;optionalint64end_year=2;optionalstringcity=3;optionalstringcountry=4;}}

SCHEMA statements

This section has information about theCREATE SCHEMA andDROP SCHEMAstatements.

CREATE SCHEMA

Creates a new schema and assigns a name.

CREATE SCHEMA [schema_name]

Parameters

schema_name

• Contains a name for a schema.
• When querying data, use fully qualified names (FQNs) to specify objects thatbelong to a specific schema. FQNs combine the schema name and the object nameto identify database objects. For example,products.albums for theproducts schema andalbums table. For more information, seeNamed schemas.

DROP SCHEMA

Removes a named schema.

DROP SCHEMAschema_name

Parameters

schema_name

• Contains the name for the schema to drop.

Parameters

schema_name

• Contains the name of the schema that you want to drop.

DATABASE statements

This section has information about theCREATE DATABASE andALTER DATABASEstatements.

CREATE DATABASE

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

CREATE DATABASEdatabase_idwheredatabase_id    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

Parameters

database_id

• The name of the database to create.See Names.

ALTER DATABASE

Changes the definition of a database.

Syntax

ALTER DATABASEdatabase_id    actionwheredatabase_id is:    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}andaction is:    SET OPTIONS ( options_def [, ... ] )andoptions_def is:    { default_leader = { 'region' | null } |      optimizer_version = { 1 ... 8 | null } |      optimizer_statistics_package = { 'package_name' | null } |      version_retention_period = { 'duration' | null } |      default_sequence_kind = { 'bit_reversed_positive' | null } |      default_time_zone = { 'time_zone_name' | null } |      read_lease_regions = {'read_lease_region_name[, ... ]' | 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 nameis a reserved word or contains a hyphen, enclose it in backticks (`). Forinformation on database naming rules, seeNames.

options_def

• Theoptimizer_version = { 1 ... 8 | null }option lets you specify the query optimizer version to use. Setting thisoption tonull is equivalent to setting it to the default version. For moreinformation, seeQuery Optimizer.

• Theoptimizer_statistics_package = { 'package_name' | null }option lets you specify the query optimizer statistics package name touse. By default, this is the latest collected statistics package, but you canspecify any available statistics package version. Setting this option tonullis equivalent to setting it to the latest version. For more information,seeQuery statistics package versioning.

• Theversion_retention_period = { 'duration' | null } is the period forwhich Spanner retains all versions of data and schema for thedatabase. The duration must be in the range[1h, 7d] and can be specifiedin days, hours, minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent. Setting the value tonull resets theretention period to the default, which is 1 hour. This option can be usedfor point-in-time recovery. For more information, seePoint-in-timeRecovery.

• Thedefault_leader = { 'region' | null } sets the leader region for yourdatabase. You can only use this parameter for databases that use amulti-region configuration.default_leader must be set tonull, or oneof the read-write replicas in your multi-region configuration.null resetsthe leader region to the default leader region for your database'smulti-region configuration. For more information, seeConfiguring thedefault leaderregion.

• Thedefault_sequence_kind = { 'bit_reversed_positive' | null } sets thedefault sequence kind for your database.bit_reversed_positive is the only valid sequencekind. Thebit_reversed_positive option specifies that the values generatedby the sequence are of typeINT64, are greater than zero, and aren'tsequential. You don't need to specify a sequence type when usingdefault_sequence_kind. When you usedefault_sequence_kindfor a sequence or identity column, you can't change the sequence kind later.For more information, seePrimary key default values management.

• Theuse_unenforced_foreign_key_for_query_optimization = { true | false | null }lets you specify whether the query optimizer can rely oninformational foreign key relationshipsto improve query performance. For example, the optimizer can remove redundantscans, and push someLIMIT operators through the join operators. Settinguse_unenforced_foreign_key_for_query_optimization tonull is equivalent tosetting it totrue. Note that enabling this might lead to incorrect results ifthe data is inconsistent with the foreign key relationships.

• Thedefault_time_zone = { 'time_zone_name' | null } option sets thedefault time zone for your database. If set toNULL, the system defaults toAmerica/Los_Angeles. Specifying a time zone within aDATEorTIMESTAMP function overrides this setting. Thetime_zone_name must be avalid entry from theIANA Time Zone Database.This option can only be set on empty databases without any tables.

• Theread_lease_regions = {'read_lease_region_name' | null } option sets theread lease region for your database. Bydefault, or when set toNULL, the database doesn't use any read leaseregions. If you set one or more read lease regions for your database,Spanner gives the right to serve reads locally to one or morenon-leader, read-write, or read-only regions. This allows the non-leaderregions to directly serve strong reads and reduce strong read latency.

LOCALITY GROUP statements

This section has information about theCREATE LOCALITY GROUP,ALTER LOCALITY GROUP, andDROP LOCALITY GROUP statements.

CREATE LOCALITY GROUP

Use theCREATE LOCALITY GROUP statement to define a locality group to storesome columns separately or to use tiered storage. For more information,seeLocality groupsandTiered storage overview.

Syntax

CREATE LOCALITY GROUPlocality_group_name [storage_def ]wherestorage_def 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

locality_group_name

• The name of the locality group.

OPTIONS

• Usestorage to define the storage type of thelocality group. You can set the storage type as 'ssd' or 'hdd'.

• Usessd_to_hdd_spill_timespan to define theamount of time that data is stored in SSD storage before it moves to HDDstorage. After the specified time passes, Spanner migratesthe data to HDD storage during its normal compaction cycle, which typicallyoccurs over the course of seven days from the specified time. The durationmust be at least one hour (1h) and at most 365 days (365d) long. It can bespecified in days, hours, minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent.

ALTER LOCALITY GROUP

Use theALTER LOCALITY GROUP statement to change the storage option orage-based policy of a locality group.

Syntax

ALTER LOCALITY GROUPlocality_group_name [storage_def ]wherestorage_def 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 alocality group. You can change these options together or individually.

Parameters

locality_group_name

• The name of the locality group. When updating thedefault locality group,default must be within backticks (`default`). You only need toinclude the backticks for thedefault locality group.

OPTIONS

• Usestorage to define the new storage type of thelocality group.

• Use thessd_to_hdd_spill_timespan = 'duration' option to set the newage-based policy of the locality group. The duration must be at least one hour(1h) and at most 365 days (365d) long. It can be specified in days, hours,minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent.

DROP LOCALITY GROUP

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

Syntax

DROP LOCALITY GROUPlocality_group_name

Description

DROP LOCALITY GROUP drops the locality group.

PLACEMENT statements

Preview —Geo-partitioning

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This section has information aboutPLACEMENT statements.

CREATE PLACEMENT

Use theCREATE PLACEMENT statement to define a placement to partition rowdata in your database. For more information, see theGeo-partitioning overview.

Syntax

CREATE PLACEMENTplacement_name [partition_def ]wherepartition_def is:    { OPTIONS ( instance_partition="partition_id" [, default_leader="leader_region_id" ] ) }

Description

CREATE PLACEMENT defines a new placement in the current database.

Parameters

placement_name

• The name of the placement.

partition_id

• The unique identifier of the user-created partition associated with theplacement.

leader_region_id

• This optional parameter sets the default leader region for the partition.Similar tosetting the default leader at the database level.However, this only applies to the partition.

DROP PLACEMENT

Use theDROP PLACEMENT statement to delete a placement.

Syntax

DROP PLACEMENTplacement_name

Description

DROP PLACEMENT drops a placement.

Parameters

placement_name

• The name of the placement to drop.

PROTO BUNDLE statements

ThePROTO files you create need to be loaded into your database schema usingPROTO BUNDLE, making thePROTO files available for use by tables and querieskeyed byPROTO andENUM fields.

CREATE PROTO BUNDLE

Use theCREATE PROTO BUNDLE statement to load types available from importedproto files into the schema.

Syntax

CREATEPROTOBUNDLE("                      (<proto_type_name>) ("," <proto_type_name>)*                    ")

Description

CREATE PROTO BUNDLE loads types available from imported proto files.

Parameters

proto_type_name

• The proto types included in yourPROTO BUNDLE.

Notes:

• Spanner requires some proto types to be included in yourPROTO BUNDLE.In particular:
  • Any message type that is used as the type of aPROTO column.
  • Any enum type that is used by anENUM column.
  • Any type needed to resolve a proto field path.
  • Any enum type that is referenced by a message type in thePROTO BUNDLE.
  • Any message type that nests a message or enum type already in thePROTO BUNDLE.
  • Any nested message type that is used as the type of aPROTO column.
• If you're using a protocol buffer type and any part of the type name is aSpanner reserved keyword, enclose the entire protocol buffertype name in backticks. For example, if you created a message namedBytes in the packagemy.awesome.proto, and you wanted to create a columnof that type, you can use the column definition:MyColumn my.awesome.proto.Bytes.

ALTER PROTO BUNDLE

TheALTER PROTO BUNDLE statement is used to update the proto informationstored in the schema.

Syntax

ALTERPROTOBUNDLE[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

proto_type_name

• The proto types included in yourPROTO BUNDLE.

Notes:

• All the same notes that apply toCREATE PROTO BUNDLEapply toALTER PROTO BUNDLE, but they apply to the final proto bundle, notthe alteration itself.
• INSERT,UPDATE, andDELETE clauses all execute atomically as a singlechange to your database's type information.

DROP PROTO BUNDLE

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

Syntax

DROPPROTOBUNDLE

Description

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

Notes:

• All the same notes that apply toCREATE PROTO BUNDLE apply toDROP PROTO BUNDLE. You can't drop a proto bundle if your databaseuses types in the proto bundle.

TABLE statements

This section has information about theCREATE TABLE,ALTER TABLE,DROP TABLE, ANDRENAME TABLE statements.

CREATE TABLE

Defines a new table.

Syntax

CREATE TABLE [ IF NOT EXISTS ]table_name ( [   {column_namedata_type [NOT NULL]     [ { DEFAULT (expression ) | AS (expression ) [ STORED ]       | GENERATED BY DEFAULT AS IDENTITY [ (sequence_option_clause ... ) ]       | AUTO_INCREMENT } ]     [ PRIMARY KEY ]     [options_def ]   |location_name STRING(MAX) NOT NULL PLACEMENT KEY   |table_constraint   |synonym_definition }   [, ... ]] ) [ PRIMARY KEY ( [column_name [ { ASC | DESC } ], ...] ) ][, INTERLEAVE IN [PARENT]table_name [ ON DELETE { CASCADE | NO ACTION } ] ][, ROW DELETION POLICY ( OLDER_THAN (timestamp_column, INTERVALnum_days DAY ) ) ][, OPTIONS ( locality_group = 'locality_group_name' ) ]wheredata_type is:    { scalar_type | array_type | proto_type_name }andoptions_def is:    { OPTIONS ( allow_commit_timestamp = { true | null } |                locality_group = 'locality_group_name' ) }andtable_constraint is:    [ CONSTRAINTconstraint_name ]    { CHECK (expression ) |      FOREIGN KEY (column_name [, ... ] ) REFERENCESref_table  (ref_column [, ... ] )        [ ON DELETE { CASCADE | NO ACTION } ] [ { ENFORCED | NOT ENFORCED } ]    }andsynonym_definition is:    [ SYNONYM (synonym) ]andsequence_option_clause is:    { BIT_REVERSED_POSITIVE    | SKIP RANGEskip_range_min,skip_range_max    | START COUNTER WITHstart_with_counter }

Description

CREATE TABLE defines a new table in the current database.

Parameters

IF NOT EXISTS

• If a table exists with the same name, theCREATE statement hasno effect and no error is generated.

table_name

• The name of the table to be created. For naming rules, seeNames.

column_name

• The name of a column to be created. For naming rules, seeNames.

data_type

• The data type of the column, which can be aScalaror anArray type.

vector_length

• vector_length sets an array column to a fixed size for use in a vectorsearch. The value must be an integer greater than or equal to zero. You canonly use this parameter with an array that uses theFLOAT32 orFLOAT64data types. That is,ARRAY<FLOAT32> (vector_length=>INT) orARRAY<FLOAT64> (vector_length=>INT).

timestamp_column

• The name of a column of typeTIMESTAMP, that is also specified in the CREATETABLE statement.

num_days

• The number of days after the date in the specifiedtimestamp_column, afterwhich the row is marked for deletion. Valid values are non-negativeintegers.

NOT NULL

• This optional column annotation specifies that the column is required for allmutations 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 ofARRAY type, the only time you can use a NOT NULL annotationis when you create the table. After that, you cannot add a NOT NULL annotationto a column ofARRAY type.

  • For all other column types, you can add a nullable column; fill that columnby writing values to all rows; and update your schema with a NOT NULLannotation 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 itsdefault value by usingUPDATE ... SETcolumn-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 SQLexpression that is assignable to the column data type, with the followingproperties 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.

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

• This clause auto-generates integer values for the column.
• BIT_REVERSED_POSITIVE is the only valid type.
• An identity column can be a key or non-key column.
• An identity column can't have a default value or be a generated column.
• You can insert your own value into an identity column. You can also reset anon-key column to use generated value by usingUPDATE ... SETcolumn-name= DEFAULT.
• A generated column or a check constraint can depend on an identity column.
• An identity column accepts the following option clauses:
  • BIT_REVERSED_POSITIVE indicates the type of identity column.
  • SKIP RANGEskip_range_min,skip_range_maxallows the underlying sequence to skip the numbers in this range when callingGET_NEXT_SEQUENCE_VALUE. The skipped range is an integer value and inclusive.The accepted values forskip_range_min is anyvalue that is less than or equal toskip_range_max. The accepted valuesforskip_range_max is any value that is greater than or equal toskip_range_min.
  • START COUNTER WITHstart_with_counter is apositiveINT64 value that Spanner uses to set the nextvalue for the internal sequence counter. For example, whenSpanner obtains a value from the bit-reversed sequence, itbegins withstart_with_counter. Spanner bit reverses thisvalue before returning it. The default value is1.

AS (expression) [STORED]

• This clause creates a column as agenerated column, which is a column whosevalue is defined as a function of other columns in the same row.

• expression can be any valid SQL expression that'sassignable to the column data type with the following restrictions.

  • The expression can only reference columns in the same table.

  • The expression can't containsubqueries.

  • Expressions with non-deterministic functions such asPENDING_COMMIT_TIMESTAMP(),CURRENT_DATE(), andCURRENT_TIMESTAMP()can't be made into aSTORED generated column or a generated column thatis indexed.

  • You can't modify the expression of aSTORED or indexed generated column.

• For GoogleSQL-dialect databases, a non-stored generated column oftypeSTRING orBYTES must have a length ofMAX.

• For PostgreSQL-dialect databases, a non-stored, or virtual, generatedcolumn of typeVARCHAR must have a length ofMAX.

• TheSTORED attribute that follows the expression stores the result of theexpression along with other columns of the table. Subsequentupdates to any of the referenced columns cause Spanner tore-evaluate and store the expression.

• Generated columns that are notSTORED can't be marked asNOT NULL.

• Direct writes to generated columns aren't allowed.

• Column optionallow_commit_timestamp isn't allowed on generated columnsor any columns that generated columns reference.

• ForSTORED or generated columns that are indexed, you can't change the datatype of the column, or of any columns that 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 followingadditional 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 aDEFAULT clause.

• The following rules apply when using generated key columns:

  • Read APIs: You must fully specify the key columns, including thegenerated key columns.
  • Mutation APIs: ForINSERT,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 thegenerated keys.
  • DML: You can't explicitly write to generated keys inINSERT orUPDATEstatements.
  • Query: In general, we recommend that you use the generated key column as afilter in your query. Optionally, if the expression for the generated keycolumn uses only one column as a reference, the query can apply an equality(=) orIN condition to the referenced column. For more information andan example, seeCreate a unique key derived from a value column.

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

AUTO_INCREMENT

• This clause creates a column as anidentity column, which is a column whosevalue is generated by a sequence. To useAUTO_INCREMENT, the database optiondefault_sequence_kind must be explicitly set.

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

location_nameSTRING(MAX) NOT NULL PLACEMENT KEY

• location_name: The name of the column.
• PLACEMENT KEY is the required attribute that defines this column as thecolumn that contains the placement information for rows in this table.

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

• Every table must have a primary key and that primary key can be composed ofzero or more columns of that table.

• A single-column primary key can be defined either inline within the columndefinition or at the table-level.

• A zero or multi-column primary key must be defined at the table-level with thePRIMARY KEY ( [column_name[ { ASC | DESC } ], ...]syntax.

• A primary key can't be defined at both the column and table-level.

• Adding theDESC annotation on a primary key column name changes the physicallayout of data from ascending order (default) to descending order. TheASCorDESC option can be specified only when defining the primary key at thetable-level.

  For more details, seeSchema and data model.

[, INTERLEAVE IN PARENTtable_name[ ON DELETE { CASCADE | NO ACTION } ] ]

• INTERLEAVE IN PARENT defines a child-to-parent table relationship, whichresults in a physical interleaving of parent and child rows. The primary-keycolumns of a parent must positionally match, both in name and type, a prefixof the primary-key columns of any child. Adding rows to the child table failsif the corresponding parent row does not exist. The parent row caneither exist in the database or be inserted before the insertionof the child rows in the same transaction.

• The optionalON DELETE clause is only allowed forINTERLEAVE IN PARENT.ON DELETE defines the behavior of rowsinChildTable when a mutation attempts to delete the parent row. Thesupported options are:

  • CASCADE: the child rows are deleted.

  • NO ACTION: the child rows are not deleted. If deleting a parent wouldleave behind child rows, thus violating parent-child referentialintegrity, the write will fail.

  You can omit theON DELETE clause, in which case the default ofONDELETE NO ACTION is used.

    For more details, seeSchema and data model.

INTERLEAVE INparent_table_name

• INTERLEAVE IN defines the same parent-child relationship and physicalinterleaving of parent and child rows asINTERLEAVE IN PARENT, but theparent-child referential integrity constraint isn't enforced. Rows in thechild table can be inserted before the corresponding rows in the parenttable. Like withIN PARENT, the primary-key columns of a parent mustpositionally match, both in name and type, a prefix of the primary-key columnsof any child.

CONSTRAINTconstraint_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 Spannerinformation schema.

CHECK (expression)

• ACHECK constraint lets you specify that the values of one or morecolumns must satisfy a boolean expression.

• expression can be any valid SQL expression that evaluates to aBOOL.

• The following restrictions apply to a check constraintexpression term.

  • The expression can only reference columns in the same table.

  • The expression must reference at least one non-generated column, whetherdirectly or through a generated column which references a non-generatedcolumn.

  • The expression can't reference columns that have set theallow_commit_timestamp option.

  • The expression can't containsubqueries.

  • The expression can't contain non-deterministic functions, such asCURRENT_DATE()andCURRENT_TIMESTAMP().

• For more information, seeCreating and managing check constraints.

FOREIGN KEY (column_name[, ... ] ) REFERENCESref_table(ref_column[, ... ] [ ON DELETE { CASCADE | NO ACTION } ] [ { ENFORCED | NOT ENFORCED } ] )

• Use this clause to define a foreign key constraint. A foreign key is definedon thereferencing table of the relationship, and it references thereferenced table. The foreign key columns of the two tables are called thereferencing andreferenced columns, and their row values are the keys.

• Foreign key constraints can be declared with or without the enforcementclause. If you don't specify an enforcement clause, the foreign key constraintdefaults to enforced.

• An enforced foreign key constraint requires that one or more columns of thistable must contain only values that are in the referenced columns of thereferenced table. Ainformational (NOT ENFORCED) foreign key constraint doesn't require this.

• When creating a foreign key, a unique constraint is automatically created onthe referenced table, unless the entire primary key is referenced. If the uniqueconstraint can't be satisfied, the entire schema change will fail.

• The number of referencing and referenced columns must be the same. Order isalso significant. That is, the first referencing column refers to the firstreferenced column, and the second to the second.

• The referencing and referenced columns must have matching types and they mustsupport the equality operator ('='). The columns must also be indexable.Columns of typeARRAY are not allowed.

• When you create a foreign key with the ON DELETE CASCADE action, deleting arow in the referenced table atomically deletes all rows from the referencingtable that references the deleted row in the same transaction.

• If you don't specify a foreign key action, the default action is NO ACTION.

• Foreign keys can't be created on columns with theallow_commit_timestamp=true option.

    For more information, seeForeign keys.

OPTIONS ( allow_commit_timestamp = { true | null } )

• Theallow_commit_timestamp option allows insert and update operationsto request that Spanner write the commit timestamp of thetransaction into the column. For more information, seeCommit timestamps in GoogleSQL-dialect databases.

[, ROW DELETION POLICY ( OLDER_THAN (timestamp_column, INTERVALnum_daysDAY ) ) ]

• Use this clause to set a row deletion policy for this table. For moreinformation, seeTime to live (TTL).

OPTIONS ( locality_group = '<code><b><i>locality_group_name</b></i></code>' )

• Use this clause to store columns together or to set a tiered storage policy.For more information, seeLocality groupsandTiered storage overview.

SYNONYM (synonym)

• Defines asynonymfor a table, which is an additional name that an application can use toaccess the table. A table can have one synonym. You can only use asynonym for queries and DML. You can't use the synonym for DDL or schemachanges. You can see the synonym in the DDL representation of the table.

ALTER TABLE

Changes the definition of a table.

Syntax

ALTER TABLEtable_name    actionwhereaction is:    ADD SYNONYMsynonym    DROP SYNONYMsynonym    RENAME TOnew_table_name [, ADD SYNONYMsynonym]    ADD [ COLUMN ] [ IF NOT EXISTS]column_namedata_type [column_expression ] [options_def ]    DROP [ COLUMN ]column_name    ADDtable_constraint    DROP CONSTRAINTconstraint_name    SET ON DELETE { CASCADE | NO ACTION }    SET INTERLEAVE IN [ PARENT ]parent_table_name [ ON DELETE { CASCADE | NO ACTION } ]    ALTER [ COLUMN ]column_name      {data_type  [ NOT NULL ] [ DEFAULT (expression )        | AS (expression )        | GENERATED BY DEFAULT AS IDENTITY [ (sequence_option_clause ... ) ] ]        | SET OPTIONS (options_def )        | SET DEFAULT (expression )        | DROP DEFAULT        | ALTER IDENTITY          {            SET { SKIP RANGEskip_range_min,skip_range_max | NO SKIP RANGE }            | RESTART COUNTER WITHcounter_restart          }      }    ADD ROW DELETION POLICY ( OLDER_THAN (timestamp_column, INTERVALnum_days DAY ))    DROP ROW DELETION POLICY    REPLACE ROW DELETION POLICY ( OLDER_THAN (timestamp_column, INTERVALnum_days DAY ))    OPTIONS ( locality_group = 'locality_group_name' )anddata_type is:    {scalar_type |array_type }andcolumn_expression is:    [ NOT NULL ] [ { DEFAULT (expression ) | AS (expression ) STORED    | GENERATED BY DEFAULT AS IDENTITY [ (sequence_option_clause ) ] } ]andoptions_def is:    allow_commit_timestamp = { true | null } |    locality_group = 'locality_group_name'andtable_constraint is:    [ CONSTRAINTconstraint_name ]    { CHECK (expression ) |      FOREIGN KEY (column_name [, ... ] ) REFERENCESref_table (ref_column [, ... ] )        [ ON DELETE { CASCADE | NO ACTION } ] [ { ENFORCED | NOT ENFORCED } ]    }

Description

ALTER TABLE changes the definition of an existing table.

ADD SYNONYMsynonym

• Adds a synonym to a table to give it an alternate name. Youcan use the synonym for reads, writes, queries, and for use with DML.You can't useADD SYNONYM with DDL, such as to create an index. A tablecan have one synonym. For moreinformation, seeAdd a table name synonym.

DROP SYNONYMsynonym

• Removes a synonym from a table. For moreinformation, seeRemove a synonym.

RENAME TOnew_table_name

• Renames a table, for example, if the table name is misspelled.For more information, seeRename a table.

RENAME TOnew_table_name [, ADD SYNONYMsynonym]

• Adds a synonym to a table so that when you rename the table,you can add the old table name to the synonym. This gives you time toupdate applications with the new table name while still allowing themto access the table with the old name. For more information, seeRename a table and add a synonym.

ADD COLUMN

• Adds a new column to the table, using the same syntax asCREATE TABLE.

• If you specifyIF NOT EXISTS and a column of the same name alreadyexists, the statement has no effect and no error is generated.

• You can specifyNOT NULL in anALTER TABLE...ADD COLUMN statement ifyou specifyDEFAULT (expression) orAS (expression) STORED for the column.

• If you includeDEFAULT (expression) orAS (expression) STORED, the expressionis evaluated and the computed value is backfilledfor existing rows. The backfill operation is asynchronous. This backfilloperation happens only when anADD COLUMN statement is issued. There'sno backfill onALTER COLUMN.

• TheDEFAULT 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 aCHECK constraint is not allowed.

ADDtable_constraint

• Adds a new constraint to a table using the same syntax asCREATE TABLE.

• For foreign keys, the existing data is validated before the foreign key isadded. If any existing constrained key doesn't have a corresponding referencedkey for an enforced foreign key, or the referenced key isn't unique for aforeign key, the foreign key constraint is violated, and theALTER statementfails.

• Changing the enforcement or adding a foreign key action on an existing foreignkey constraint isn't supported. Instead, you need to add a new foreign keyconstraint with the enforcement or action.

• If you don't specify a foreign key action, the default action is NO ACTION.

• If you don't specify the type of a foreign key, it defaults to anenforced foreign key.

• ForCHECK constraints, new data is validated immediately against theconstraint. A long-running process is also started to validate the existingdata against the constraint. If any existing data does not conform to theconstraint, the check constraint is rolled back.

• The following restrictions apply to a check constraintexpression term.

  • The expression can only reference columns in the same table.

  • The expression must reference at least one non-generated column, whetherdirectly or through a generated column which references a non-generatedcolumn.

  • The expression can't reference columns that have set theallow_commit_timestamp option.

  • The expression can't containsubqueries.

  • The expression can't contain non-deterministic functions, such asCURRENT_DATE()andCURRENT_TIMESTAMP().

DROP CONSTRAINTconstraint_name

• Drops the specified constraint on a table, along with any associated index, ifapplicable.

SET ON DELETE { CASCADE | NO ACTION }

• This alteration can be applied only on child tables of parent-child,interleaved tables relationships. For more information, seeSchema and datamodel.

• TheON DELETE CASCADE clause signifies that when a row from the parenttable is deleted, its child rows in this table will automatically be deletedas well. Child rows are all rows that start with the same primary key. If achild table does not have this annotation, or the annotation isON DELETENO ACTION, then you must delete the child rows before you can delete theparent row.

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

• SET INTERLEAVE IN PARENT migrates an interleaved table to useIN PARENTsemantics, which require that the parent row exist for each child row. Whileexecuting this schema change, the child rows are validated to ensure thereare no referential integrity violations. If there are, the schema changefails. If noON DELETE clause is specified,NO ACTION is the default.Note that directly migrating from anINTERLEAVE IN table toIN PARENT ON DELETE CASCADE is not supported. This must be done in twosteps. The first step is to migrateINTERLEAVE IN toINTERLEAVE IN PARENT T [ON DELETE NO ACTION] and the second step is tomigrate toINTERLEAVE IN PARENT T ON DELETE CASCADE. If referentialintegrity validation fails, use a query like the following to identify missingparent rows.

  SELECTpk1,pk2FROMchildEXCEPTDISTINCTSELECTpk1,pk2FROMparent;

  • SET INTERLEAVE IN, likeSET INTERLEAVE IN PARENT, migrates anINTERLEAVE IN PARENT interleaved table toINTERLEAVE IN, thus removingthe parent-child enforcement between the two tables.

  • TheON DELETE clause is only supported when migrating toINTERLEAVE IN PARENT.

ALTER COLUMN

• Changes the definition of an existing column on a table.

• data_type[ NOT NULL ][ DEFAULT (expression)| AS ( <b><i>expression</i></b> ) ]

  • This clause changes the data type of the column.

  • TheDEFAULT clause has restrictions. See the description of this clauseinCREATE TABLE.

  • Statements to set, change, or drop default value of an existing column don'taffect existing rows.

  • If the column has data and is altered to have theNOT NULLconstraint, the statement might fail if there is at least one existing rowwith aNULL value. This is true even when aNOT NULL DEFAULT (...) isspecified, because there is no backfill operation forALTER COLUMN.

  • IfDEFAULT orNOT NULL are unspecified, these properties are removedfrom the column.

  • TheAS clause is used toModify a generated column expression.

• SET OPTIONS(options_def )

  • Use this clause to set an option at the column level of the schemahierarchy.

• SET DEFAULT(expression )

  • Sets or changes a default value for the column. Only the metadata isaffected. Existing data is not changed.

  • This clause has restrictions. See thedescription of this clause inCREATE TABLE.

  • When you use this clause, the result of the expression must be assignable tothe current column type. To change the column type and default value in asingle statement, use te following:

    ALTER TABLEtable-nameALTER COLUMNcolumn-namedata_typeDEFAULTexpression

• DROP DEFAULT

  • Drops the column default value. Only metadata is affected. Existing data isnot changed.

• ALTER IDENTITY

  • Sets or unsets the skipped range usingSET { SKIP RANGEskip_range_min,skip_range_max| NO SKIP RANGE }.

  • Restarts the internal counter with a specific value usingRESTART COUNTER WITHcounter_restart.

  • These clauses are similar toIdentity Columns in CREATE TABLE.

ADD ROW DELETION POLICY ( OLDER_THAN (timestamp_column, INTERVALnum_daysDAY ) )

• Adds a row deletion policy to the table defining the amount of time after aspecific date after which to delete a row. SeeTime to live. Only onerow 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, INTERVALnum_daysDAY ) )

• Replaces the existing row deletion policy with a new policy.

SET OPTIONS ( locality_group = '<code><b><i>locality_group_name</b></i></code>' )

• Alters the locality group used by the table.

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

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 columnsreferenced by the generated column.

• Changing the data type is not allowed on any columns referenced in aCHECK constraint.options_def

• The(allow_commit_timestamp=true) option allows insert and update operationsto request that Spanner write the commit timestamp of thetransaction into the column. For more information, seeCommit timestamps in GoogleSQL-dialect databases.

options_def

• Theallow_commit_timestamp = { true | null } clause is the only allowedoption. Iftrue, a commit timestamp can be stored into the column.

  To learn about commit timestamps, seeCommit 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

• Thereferenced table in a foreign key constraint.

ref_column

• Thereferenced column in a foreign key constraint.

DROP TABLE

Removes a table.

Syntax

DROP TABLE [ IF EXISTS ]table_name

Description

Use theDROP 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 areany tables or indexes interleaved within it.

• ADROP TABLE statement automatically drops the foreign keys andforeign keys backing indexes of a table.

Parameters

IF EXISTS

• If a table of the specified name doesn't exist, then theDROP statementhas no effect and no error is generated.

table_name

• The name of the table to drop.

RENAME TABLE

Renames a table or multiple tables at once.

Syntax

RENAME TABLEold_table_name TOnew_table_name ...   [,old_table_name2 TOnew_table_name2 ...]

Description

Renames a table or multiple tables simultaneously, for example, if the tablename is misspelled. For more information, seeRename a table.

Parameters

old_table_name

• The old name of the table.

new_table_name

• The new name for the table.

Example

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

RENAMETABLESingersTOArtists,AlbumsTORecordings;

INDEX statements

This section has information about theCREATE INDEX,ALTER INDEX, andDROP INDEX statements.

CREATE INDEX

Use theCREATE INDEX statement to definesecondary indexes.

Syntax

CREATE [ UNIQUE ] [ NULL_FILTERED ] INDEX [ IF NOT EXISTS ]index_nameONtable_name (key_part [, ...] ) [storing_clause ][where_clause ] [ ,interleave_clause ][ OPTIONS ( locality_group = 'locality_group_name' ) ]whereindex_name is:    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]andkey_part is:column_name [ { ASC | DESC } ]andstoring_clause is:    STORING (column_name [, ...] )andwhere_clause is:    WHEREcolumn_name IS NOT NULL [AND ...]andinterleave_clause is:    INTERLEAVE INtable_name

Description

Spanner automatically indexes the primary key columns of eachtable.

You can useCREATE INDEX to create secondary indexes for other columns. Addinga secondary index on a column makes it more efficient to look up data in thatcolumn. For more details, seesecondary indexes.

Parameters

UNIQUE

• Indicates that this secondary index enforces aUNIQUE constraint onthe data being indexed. TheUNIQUE constraint causes any transaction thatwould result in a duplicate index key to be rejected. SeeUnique Indexes for moreinformation.

NULL_FILTERED

• Indicates that this secondary index does not indexNULLvalues. For more information, seeIndexing of NULL values.

IF NOT EXISTS

• If an index already exists with the same name, then theCREATE statementhas no effect and no error is generated.

index_name

• The name of the index to be created. For information about naming rules, seeNames.

table_name

• The name of the table to be indexed.

WHERE IS NOT NULL

• Rows that contain NULL in any of the columns listed in this clause aren'tincluded in the index. The columns must be stored in the index, includingkey columns and columns present in theSTORING clause.

INTERLEAVE IN

• Defines a table to interleave the index in. IfT is thetable into which the index is interleaved, then the primary key ofT must bethe key prefix of the index, with each key matching in type, sort order, andnullability. Matching by name is not required.

  If the index key that you want to use for index operations matches the keyof a table, you might want to interleave the index in that table if the rowin the table should have a data locality relationship with the correspondingindexed rows.

  For example, if you want to index all rows ofSongs for a particular rowofSingers, your index keys would containSingerId andSongName andyour index would be a good candidate for interleaving inSingers if youfrequently fetch information about a singer as you fetch that singer'ssongs from the index. The definition ofSongsBySingerSongName inCreating a Secondary Indexis an example of creating such an interleaved index.

  Like interleaved tables, entries in interleaved indexes are stored withthe corresponding row of the parent table. Seedatabase splits for more details.

DESC

• Defines descending scan order for the corresponding index column. Whenscanning a table using an index column markedDESC, the scanned rows appearin the descending order with respect to this index column. If you don'tspecify a sort order, the default is ascending (ASC).

STORING

• Provides a mechanism for duplicating data from the table into one ormore secondary indexes on that table. At the cost of extra storage, this canreduce read latency when looking up data using a secondary index, because iteliminates the need to retrieve data from the main table after having foundthe selected entries in the index. SeeSTORINGclause for an example.

[ OPTIONS ( locality_group = 'locality_group_name' ) ]

• Use this clause to set a secondary index-level locality group override. Formore information, seeLocality groupsandTiered storage overview.

ALTER INDEX

Use theALTER INDEX statement to add additional columns or remove storedcolumns from thesecondary indexes.

Syntax

ALTER INDEXindex_name {ADD|DROP} STORED COLUMNcolumn_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 [ IF EXISTS ]index_name

Description

Use theDROP INDEX statement to drop a secondary index.

Parameters

IF EXISTS

• If an index of the specified name doesn't exist, then theDROP statementhas no effect and no error is generated.

index_name

• The name of the index to drop.

SEARCH INDEX statements

This section has information about theCREATE SEARCH INDEX,ALTER SEARCH INDEX, andDROP SEARCH INDEX statements.

CREATE SEARCH INDEX

Use theCREATE SEARCH INDEX statement to define search indexes. For moreinformation, seeSearch indexes.

Syntax

CREATE SEARCH INDEXindex_nameONtable_name (token_column_list )[storing_clause ] [partition_clause ][orderby_clause ] [where_clause ][interleave_clause ] [options_clause ]whereindex_name is:    {a—z|A—Z}[{a—z|A—Z|0—9|_}+]andtoken_column_list is:column_name [, ...]andstoring_clause is:    STORING (column_name [, ...] )andpartition_clause is:    PARTITION BYcolumn_name [, ...]andorderby_clause is:    ORDER BYcolumn_name [ {ASC | DESC} ]andwhere_clause is:    WHEREcolumn_name IS NOT NULL [AND ...]andinterleave_clause is:    , INTERLEAVE INtable_nameandoptions_clause is:    OPTIONS (option_name=option_value [, ...] )

Description

You can useCREATE SEARCH INDEX to create search indexes forTOKENLISTcolumns. Adding a search index on a column makes it more efficient to searchdata in the source column of theTOKENLIST.

Parameters

index_name

• The name of the search index to be created. For naming rules, seeNames.

table_name

• The name of the table to be indexed for search.

token_column_list

• A list ofTOKENLIST columns to be indexed for search.

STORING

• Provides a mechanism for duplicating data from the table into the searchindex. This is the same asSTORING in secondary indexes. For moreinformation, seeSTORING clause.

PARTITION BY

• A list of columns to partition the search index by. Partition columnssubdivide the index into smaller units, one for each unique partition.Queries can only search within a single partition at a time. Queries againstpartitioned indexes are generally more efficient than queries againstunpartitioned indexes because only splits from a single partition need to beread.

ORDER BY

• A list ofINT64 columns that the search index will store rows in that orderwithin a partition. The column must beNOT NULL, or the index must defineWHERE IS NOT NULL. This property can support at most one column.

WHERE IS NOT NULL

• Rows that contain NULL in any of the columns listed in this clause aren'tincluded in the index. The columns must be stored in the index, includingkey columns and columns present in theSTORING clause.

INTERLEAVE IN

• Similarly to secondary indexesINTERLEAVE IN,search indexes can be interleaved in an ancestor table of the base table. Theprimary reason to use interleaved search indexes is to colocate base tabledata with index data for small partitions.

• Interleaved search indexes have three restrictions:

  • Only sort-order sharded indexes can be interleaved.
  • Search indexes can only be interleaved in top-level tables (and not inchild tables).
  • Like interleaved tables and secondary indexes, the key of the parent tablemust be a prefix of the interleaved search index'sPARTITION BY columns.

OPTIONS

• A list of key value pairs that overrides the default settings of the searchindex.

  • sort_order_shardingWhentrue, the search index will be sharded by one or more columnsspecified in theORDER BY clause. Whenfalse, the search index issharded uniformly. Default value isfalse. Seesearch index shardingfor more details.

ALTER SEARCH INDEX

Use theALTER SEARCH INDEX statement to add or remove columns from the searchindexes.

Syntax

ALTER SEARCH INDEXindex_name {ADD|DROP} [STORED] COLUMNcolumn_name

Description

Add aTOKENLIST column into a search index or remove an existingTOKENLISTcolumn from a search index. UseSTORED COLUMN to add or remove stored columnsfrom a search index.

Parameters

index_name

• The name of the search index to alter.

column_name

• The name of the column to add into the index or to remove from the searchindex.

DROP SEARCH INDEX

Removes a search index.

Syntax

DROP SEARCH INDEX [ IF EXISTS ]index_name

Description

Use theDROP SEARCH INDEX statement to drop a search index.

Parameters

IF EXISTS

• If a search index with the specified name doesn't exist, then theDROPstatement has no effect and no error is generated.

index_name

• The name of the search index to drop.

VIEW statements

This section has information about theCREATE VIEW,CREATE OR REPLACE VIEW, andDROP VIEW statements.

CREATE VIEW and CREATE OR REPLACE VIEW

Use theCREATE VIEW orCREATE OR REPLACE VIEW statement to define aview.

Syntax

{ CREATE VIEW | CREATE OR REPLACE VIEW }view_nameSQL SECURITY { INVOKER | DEFINER }ASquery

Description

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

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

Parameters

view_name

• The name of the view to be created. For naming rules, seeNames.

SQL SECURITY

• The security type can be eitherINVOKER orDEFINER.Depending on the security type of the view, Spanner may or maynot access check the objects referenced in the view against the database roleof the principal who invoked the query. For more information, seeAbout views.

ASquery

• The query that defines the view content.

  • The query must specify a name for each item in theSELECT list.

  • The query cannot includequery parameters.

  • GoogleSQL disregards anyORDER BY clause in thisquery that isn't paired with aLIMIT clause.

  SeeQuery syntax for information on constructing aquery.

DROP VIEW

Removes a view.

Syntax

DROP VIEW [ IF EXISTS ]view_name

Description

Use theDROP VIEW statement to remove a view from the database. UnlesstheIF EXISTS clause is specified, the statement fails if the view doesn'texist.

Parameters

IF EXISTS

• If the view doesn't exist, theDROP statement has no effect and doesn'tgenerate an error.

view_name

• The name of the view to drop.

CHANGE STREAM statements

This section has information about theCREATE CHANGE STREAM,ALTER CHANGE STREAM, andDROP CHANGE STREAM statements.

CREATE CHANGE STREAM

Defines a newchange stream.

Syntax

CREATE CHANGE STREAM [ IF NOT EXISTS ]change_stream_name[ FOR {table_columns [, ... ] | ALL } ][ OPTIONS (change_stream_option [, ... ] ) ]wheretable_columns is:table_name [ ( [column_name, ... ] ) ]andchange_stream_option is:    { retention_period = 'duration' |      value_capture_type = { 'OLD_AND_NEW_VALUES' | 'NEW_ROW' |'NEW_VALUES' | 'NEW_ROW_AND_OLD_VALUES' } |      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

• If a change stream exists with the same name, theCREATE statement has noeffect and doesn't generate an error.

change_stream_name

• The name of the change stream to be created. The maximum number of charactersof a change stream name is 128. However, the name you provide is prependedwith the 10 character prefix,READ_JSON_. Because of this, the maximumnumber of characters you can assign tochange_stream_name` is 118. For further namingrules, seeNames.

FOR {table_columns[, ... ] | ALL }

• TheFOR clause defines the tables and columns that are watched by the changestream.

• You can specify a list oftable_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 followingthe table name. This watches only the primary key and the listed non-keycolumns of the table. With an empty list of non-key columns,table_name() watches only the primary key.

  Note: Primary key columns are always watched by the change stream.You only need to list the non-key columns. Listing any primary keycolumns is not allowed.

• ALL lets you watch all tables and columns in the entire database,including all of the future tables and columns as soon as they are created.

• When theFOR clause is omitted, the change stream watches nothing.

OPTIONS (change_stream_option[, ... ] )

• Theretention_period = 'duration' option lets you specify how long achange stream retains its data. The duration must be in the range[1d, 7d] andcan be specified in days, hours, minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent. The default is 1 day.For more information, seeData retention.

• Thevalue_capture_type option controls which values are captured for achanged row. It can beOLD_AND_NEW_VALUES (default),NEW_VALUES,NEW_ROW,orNEW_ROW_AND_OLD_VALUES. For more information,seeValue capture type.

• Theexclude_ttl_deletes configuration parameter lets you filter outtime to live based deletes from yourchange stream. When you set this filter, only future TTL-based deletes areremoved. It can be set tofalse (default) ortrue. For more information,seeTTL-based deletes filter.

• Theexclude_insert configuration parameter lets you filter out allINSERT table modifications from your change stream. It can beset tofalse (default) ortrue. For more information, seeTable modification type filters.

• Theexclude_update configuration parameter lets you filter out allUPDATE table modifications from your change stream. It can beset tofalse (default) ortrue. For more information, seeTable modification type filters.

• Theexclude_delete configuration parameter lets you filter out allDELETE table modifications from your change stream. It can beset tofalse (default) ortrue. For more information, seeTable modification type filters.

• Theallow_txn_exclusion configuration parameter lets you enabletransaction-level records exclusion. It can be set tofalse (default) ortrue. For more information, seeTransaction-level records exclusion.

ALTER CHANGE STREAM

Changes the definition of a change stream.

Syntax

ALTER CHANGE STREAMchange_stream_nameactionwhereaction is:    { SET FOR {table_columns [, ... ] | ALL } |      DROP FOR ALL |      SET OPTIONS (change_stream_option [, ... ] ) }andtable_columns is:table_name [ ( [column_name, ... ] ) ]andchange_stream_option is:    { retention_period = { 'duration' | null } |      value_capture_type = { 'OLD_AND_NEW_VALUES' | 'NEW_ROW' | 'NEW_VALUES' | 'NEW_ROW_AND_OLD_VALUES' | 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

change_stream_name

• The name of an existing change stream to alter.

SET FOR {table_columns[, ... ] | ALL }

• Sets a newFOR clause to modify what the change stream watches, using thesame syntax asCREATE CHANGE STREAM.

DROP FOR ALL

• Suspends a change stream to watchnothing.

SET OPTIONS

• Sets options on the change stream (such asretention_period,value_capture_type,exclude_ttl_deletes,exclude_insert,exclude_update,exclude_delete, andallow_txn_exclusion), using the samesyntax asCREATE CHANGE STREAM.

• Setting an option tonull is equivalent to setting it to the default value.

DROP CHANGE STREAM

Removes a change stream.

Syntax

DROP CHANGE STREAM [ IF EXISTS ]change_stream_name

Description

Use theDROP CHANGE STREAM statement to remove a change stream from thedatabase and delete its data change records.

Parameters

IF EXISTS

• If a change stream of the specified name doesn't exist, theDROP statementhas no effect and doesn't generate an error.

change_stream_name

• The name of the change stream to drop.

ROLE statements

This section has information about theCREATE ROLE andDROP ROLE statements.

CREATE ROLE

Defines a new database role.

Syntax

CREATE ROLEdatabase_role_name

Description

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

Parameters

database_role_name

• The name of the database role to create. The role namepublic and role namesstarting withspanner_ arereserved forsystem roles.See alsoNames.

Example

This example creates the database rolehr_manager.

CREATE ROLE hr_manager

DROP ROLE

Drops a database role.

Syntax

DROP ROLEdatabase_role_name

Description

DROP ROLE drops a database role. You can drop only one role withthis 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 bedropped. You can drop a database role whether or not access to it isgranted to IAM principals.

Dropping a role automatically revokes its membership in other roles and revokesthe membership of its members.

You can't dropsystem roles.

Parameters

database_role_name

• The name of the database role to drop.

Example

This example drops the database rolehr_manager.

DROP ROLE hr_manager

GRANT and REVOKE statements

This section has information about theGRANT andREVOKE statements.

GRANT

Grants privileges that allow database roles to access database objects.

Syntax

GRANT { SELECT | INSERT | UPDATE | DELETE } ON TABLEtable_list TO ROLErole_listGRANT { SELECT | INSERT | UPDATE }(column_list)   ON TABLEtable_list | ON ALL TABLES IN SCHEMA schema_name [, ...]   TO ROLErole_listGRANT SELECT    ON CHANGE STREAMchange_stream_list        | ON ALL CHANGE STREAMS IN SCHEMA schema_name [, ...] }    TO ROLErole_listGRANT SELECT ON VIEWview_list | ON ALL VIEWS IN SCHEMA schema_name [, ...]    TO ROLErole_listGRANT EXECUTE ON TABLE FUNCTIONfunction_list    TO ROLErole_list.GRANT ROLErole_list    TO ROLErole_listGRANT USAGE ON SCHEMA [DEFAULT |schema_name_list] TO ROLErole_listwheretable_list is:table_name [, ...]andcolumn_list is:column_name [,...]andview_list is:view_name [, ...]andchange_stream_list is:change_stream_name [, ...]andfunction_list is:change_stream_read_function_name [, ...]andschema_name_list is:schema_name [, ...]androle_list is:database_role_name [, ...]

Description

Forfine-grained access control, grants privilegeson one or more tables, views, change streams, or change stream read functions todatabase roles. Also grants database roles to otherdatabase roles to create a database role hierarchy with inheritance.When grantingSELECT,INSERT, orUPDATE ona 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.

change_stream_read_function_name

• The name of an existing read function for a change stream. For moreinformation, seeChange stream read functions and query syntax.

schema_name

• The name of the schema.

database_role_name

• The name of an existing database role.

Notes and restrictions

• Identifiers for database objects named in theGRANT statement must use thecase that was specified when the object was created. For example, if youcreated a table with a name that is in all lower case with acapitalized first letter, you must use that same case in theGRANT statement.Table-valued functions (TVFs) get automatically created with a prefix added tothe change stream name, so ensure that you use the proper case for both theprefix and the change stream name. For more information about TVFs, seeChange stream query syntax.created a table with a name that is in all lower case with a capitalizedfirst letter, you must use that same case in theGRANT statement.For each change stream, GoogleSQL automatically creates a changestream read function with a name that consists ofa prefix added to the change stream name, so ensure that you use the propercase for both the prefix and the change stream name. For more informationabout change stream read functions, seeChange stream querysyntax.

• When granting column-level privileges on multiple tables, each table mustcontain the named columns.

• If a table contains a column that is markedNOT NULL and has no defaultvalue, you can't insert into the table unless you have theINSERT privilegeon that column.

• After grantingSELECT on a change stream to a role, grantEXECUTE to thatrole on the read function for the change stream. For informationabout change stream read functions, seeChange stream read functions and query syntax.

• GrantingSELECT on a table doesn't grantSELECT on the change stream thattracks it. You must make a separate grant for the change stream.

• ALL TABLES IN SCHEMA,ALL CHANGE STREAMS IN SCHEMA, andALL VIEWS IN SCHEMA performs a one-time bulk grant for a role toall those database objects that use the schema, but not to future objects thatuse the schema.

Examples

The following example grantsSELECT on theemployees table to thehr_reprole. Grantees of thehr_rep role can read all columns ofemployees.

GRANTSELECTONTABLEemployeesTOROLEhr_rep;

The next example grantsSELECT on a subset of columns of thecontractorstable to thehr_rep role. Grantees of thehr_rep role can read-only thenamed columns.

GRANTSELECT(name,address,phone)ONTABLEcontractorsTOROLEhr_rep;

The next example mixes table-level and column-level grants.hr_managercan read all table columns, but can update only thelocation column.

GRANTSELECT,UPDATE(location)ONTABLEemployeesTOROLEhr_manager;

The next example makes column-level grants on two tables. Both tables mustcontain thename,level, andlocation columns.

GRANTSELECT(name,level,location),UPDATE(location)ONTABLEemployees,contractorsTOROLEhr_manager;

The next example grantsINSERT on a subset of columns of theemployeestable.

GRANTINSERT(name,cost_center,location,manager)ONTABLEemployeesTOROLEhr_manager;

The next example grants the database rolepii_access to the roleshr_managerandhr_director. Thehr_manager andhr_director roles aremembers ofpii_access and inherit the privileges that were granted topii_access. Formore information, seeDatabase role hierarchies and inheritance.

GRANTROLEpii_accessTOROLEhr_manager,hr_director;

REVOKE

Revokes privileges that allow database roles access to database objects.

Syntax

REVOKE { SELECT | INSERT | UPDATE | DELETE } ON TABLEtable_list FROM ROLErole_listREVOKE { SELECT | INSERT | UPDATE }(column_list) ON TABLEtable_list FROM ROLErole_listREVOKE SELECT ON VIEWview_list FROM ROLErole_listREVOKE SELECT ON CHANGE STREAMchange_stream_list FROM ROLErole_listREVOKE EXECUTE ON TABLE FUNCTIONfunction_list FROM ROLErole_listREVOKE ROLErole_list FROM ROLErole_listandtable_list is:table_name [, ...]andcolumn_list is:column_name [,...]andview_list is:view_name [, ...]andchange_stream_list is:change_stream_name [, ...]andfunction_list is:change_stream_read_function_name [, ...]androle_list is:database_role_name [, ...]

Description

Forfine-grained access control, revokes privilegeson one or more tables, views, change streams, or change stream read functionsfrom database roles. Also revokes database roles from other database roles. WhenrevokingSELECT,INSERT, orUPDATE on a table, optionally revokesprivileges 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.

change_stream_read_function_name

• The name of an existing read function for a change stream. For moreinformation, seeChange stream read functions and query syntax.

database_role_name

• The name of an existing database role.

Notes and restrictions

• Identifiers for database objects named in theREVOKE statement must usethe case that was specified when the object was created. For example, if youcreated a table with a name that is in all lower case with a capitalizedfirst letter, you must use that same case in theREVOKE statement. Foreach change stream, GoogleSQL automatically creates a changestream read function with a name that consists of a prefix added to thechange stream name, so ensure that you use the proper case for both theprefix and the change stream name. For more information about change streamread functions, seeChange stream querysyntax.

• When revoking column-level privileges on multiple tables, each table mustcontain the named columns.

• AREVOKE statement at the column level has no effect if privilegeswere granted at the table level.

• After revokingSELECT on a change stream from a role, revokeEXECUTE onthe change stream's read function from that role.

• RevokingSELECT on a change stream doesn't revoke any privileges on thetable that it tracks.

Examples

The following example revokesSELECT on theemployees table from the rolehr_rep.

REVOKE SELECT ON TABLE employees FROM ROLE hr_rep;

The next example revokesSELECT on a subset of columns of thecontractorstable from the rolehr_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 ina 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 mustcontain thename,level, andlocation columns.

REVOKE SELECT(name, level, location), UPDATE(location) ON TABLE employees, contractors FROM ROLE hr_manager;

The next example revokesINSERT 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 rolepii_access from thehr_manager andhr_director database roles. Thehr_manager andhr_director roles lose any privileges that they inherited frompii_access.

REVOKE ROLE pii_access FROM ROLE hr_manager, hr_director;

SEQUENCE statements

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

CREATE SEQUENCE

Creates a sequence object.

Syntax

CREATESEQUENCE[IFNOTEXISTS]sequence_name[sequence_option_clause...][OPTIONS(sequence_options)]wheresequence_option_clauseis:BIT_REVERSED_POSITIVE|SKIPRANGEskip_range_min,skip_range_max|STARTCOUNTERWITHstart_with_counter

Description

When you use aCREATE SEQUENCE statement, Spanner creates aschema object that you can poll for values using theGET_NEXT_SEQUENCE_VALUEfunction.

Parameters

IF NOT EXISTS

• If a sequence already exists with the same name, then the CREATE statementhas no effect and no error is generated.

sequence_name

• The name of the sequence to create. For naming rules, seeNames.

OPTIONS (sequence_options)

• Use this clause to set an option on the specified sequence.Each sequence option uses akey=value pair, where key is the option name, andvalue is a literal. Multiple options are separated by commas. Options use thefollowing syntax:

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

  A sequence accepts the following options:

  • Thesequence_kind optionaccepts aSTRING to indicate the type of sequence to use. At this time,bit_reversed_positive is the only valid type and it's a required option.
  • Theskip_range_min andskip_range_max parameters cause the sequence toskip the numbers in this range when callingGET_NEXT_SEQUENCE_VALUE. Theskipped range is inclusive. These parameters are both integers that have adefault value of NULL. The accepted values forskip_range_min is anyvalue that is less than or equal toskip_range_max. The accepted valuesforskip_range_max is any value that is more than or equal toskip_range_min.
  • Thestart_with_counter option is a positiveINT64 value that Spanner uses to set the next value forthe internal sequence counter. For example, the next time thatSpanner obtains a value from the bit-reversed sequence, itbegins withstart_with_counter. Spanner bit reverses thisvalue before returning it to the client. The default value is1.

Examples

#Createapositivebit-reversedsequencetouseinaprimarykey.CREATESEQUENCEMySequenceOPTIONS(sequence_kind='bit_reversed_positive',skip_range_min=1,skip_range_max=1000,start_with_counter=50);#Createatablethatusesthesequenceforakeycolumn.CREATETABLESingers(SingerIdINT64DEFAULT(GET_NEXT_SEQUENCE_VALUE(SEQUENCEMySequence)),FirstNameSTRING(1024),LastNameSTRING(1024),SingerInfogooglesql.example.SingerInfo,BirthDateDATE)PRIMARYKEY(SingerId);

Use the following SQL to query information about sequences.

SELECT*FROMinformation_schema.sequences;SELECT*FROMinformation_schema.sequence_options;

ALTER SEQUENCE

Makes changes to the sequence object.

Syntax

ALTERSEQUENCEsequence_name{SETOPTIONSsequence_options|sequence_option_clause...}wheresequence_option_clauseis:{{SKIPRANGEskip_range_min,skip_range_max|NOSKIPRANGE}|RESTARTCOUNTERWITHcounter_restart}

Description

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

Parameters

sequence_name

• The name of an existing sequence to alter.sequence_name is case sensitive. Don't include thepath in thesequence_name.

SET OPTIONS (sequence_options)

• Use this clause to set an option on the specified sequence.Each sequence option uses akey=value pair, where key is the option name, andvalue is a literal. Multiple options are separated by commas. Options use thefollowing syntax:

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

  This parameter offers the same options asCREATE SEQUENCE.

Examples

#Alterthesequencetoincludeaskippedrange.Thisisusefulwhenyouare#migratingfromaregularsequencewithsequentialdataALTERSEQUENCEMySequenceSETOPTIONS(skip_range_min=1,skip_range_max=1234567);

DROP SEQUENCE

Drops a specific sequence.

Syntax

DROPSEQUENCE[IFEXISTS]sequence_name

Description

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

Parameters

sequence_name

• The name of the existing sequence to drop.IF EXISTS

• If a sequence of the specified name doesn't exist, then theDROP statementhas no effect and no error is generated.

STATISTICS statements

This section has information about theALTER STATISTICS andANALYZEstatements.

ALTER STATISTICS

Changes the definition of a query optimizer statistics package.

Syntax

ALTER STATISTICSpackage_nameactionwherepackage_name is:    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}andaction is:    SET OPTIONS ( options_def )andoptions_def is:    { allow_gc = { true | false } }

Description

ALTER STATISTICS changes the definition of a query optimizer statisticspackage.

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 attributesare to be altered.

  To fetch existing statistics packages, run the following query:

  SELECTs.package_nameASpackage_name,s.allow_gcASallow_gcFROMINFORMATION_SCHEMA.SPANNER_STATISTICSs;

options_def

• Theallow_gc = { true | false } option lets you specify whether a givenstatistics package is garbage collected. A package must be set asallow_gc=false if it is used in a query hint. For more information,seeGarbage 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

This section has information about theCREATE MODEL,ALTER MODEL, andDROP MODEL statements.

CREATE MODEL and CREATE OR REPLACE MODEL

Use theCREATE MODEL orCREATE OR REPLACE MODEL statement to define an MLmodel.

{ CREATE MODEL | CREATE OR REPLACE MODEL | CREATE MODEL IF NOT EXISTS }model_name[INPUT (column_list ) OUTPUT (column_list )]REMOTE[OPTIONS (model_options )]wherecolumn_list is:   {column_namedata_type [OPTIONS (model_column_options )] [, ... ] }andmodel_column_options is:    {      required = { true | false }    }andmodel_options is:    {      endpoint = '{endpoint_address}',      endpoints = [ '{endpoint_address}' [, ...] ],      default_batch_size =int64_value    }

As soon as the model reference is registered in a database, it can be used fromqueries that use theML.Predict function.

Model registration doesn't result in copying a model from the Vertex AIto a database, but only in creation of a reference to this models' endpointhosted in the Vertex AI. If the model's endpoint gets removed from theVertex AI, Spanner queries referencing this model fail.

Model endpoint access control

To be able to access a registered Vertex AI model endpoint fromSpanner, you need to grant access permission toSpanner'sservice agent account.

Spanner creates the service agent and grants the necessarypermissions when Spanner executes the firstMODEL DDLstatement. If both the Spanner database and theVertex AI endpoint are in the same project, then no additional setup isrequired.

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

gcloudbetaservicesidentitycreate--service=spanner.googleapis.com--project={PROJECT}`

Follow the steps described in thefollowing tutorial togrant theSpanner API Service Agent roleto the Spannerservice agent accountservice-{PROJECT}@gcp-sa-spanner.iam.gserviceaccount.comon your Vertex AI project.

Parameters

model_name

• The name of the model to be created. SeeNames.

INPUT ( column_list ) OUTPUT ( column_list )

• Lists of columns that define model inputs (that is, features) and outputs(that is, labels). The followingtypes (used in thetype fieldofcolumn_list) are supported:BOOL,BYTES,FLOAT32,FLOAT64,INT64,STRING,andARRAY of listed types.

  • Map the model's input or output columns with 32-bit integer types toINT64.

• If the Vertex AI endpoint hasinstance and prediction schemas,Spanner validates the providedINPUT andOUTPUTclauses against those remote schemas. You can also omitINPUT andOUTPUT clauses,letting Spanner automatically discover the endpoint schema.

• If the Vertex AI endpoint does not haveinstance and prediction schemas,INPUT andOUTPUT clauses must be provided. Spanner doesn't perform validation andmismatches result in runtime errors. We strongly recommend providing instanceand prediction schemas, especially when using custom models.

model_column_options

• required lets you mark input or output columns as optional tomatch your Vertex AI schema.
  • Input columns cannot be declared optional if the instance field is required.
  • Optional input columns can be omitted in ML function calls.
  • Required input columns must be provided to ML function calls.
  • Output columns cannot be declared as required if the prediction field is optional.
  • Optional outputs columns can return NULL if the endpoint does not produce them.
  • Required outputs columns must be produced by the endpoint.

model_options

• endpoint is the address of the Vertex AI endpoint to connect to. Mutually exclusive with endpoints option. Supported formats:

  • //aiplatform.googleapis.com/projects/{project}/locations/{location}/endpoints/{endpoint}.
  • //aiplatform.googleapis.com/projects/{project}/locations/{location}/publishers/{publisher}/models/{endpoint}.
  • https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/endpoints/{endpoint}.
  • https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/publishers/{publisher}/models/{endpoint}.

• endpoints is a list of addresses of Vertex AI endpoints to connect to.Mutually exclusive with endpoint option.Prediction starts with the first endpoint on the list and fails over in the specified order. Endpointscan host different models as long as their schemas can be merged together:

  • Each column's name must use the same case across all endpoints
  • Each column's type must be the same across all endpoints.
  • Each input column is considered required if at least one endpoint requires it
  • Each output column is considered required only if all endpoints require it

• default_batch_size specifies the maximum number of rows perremote inference call. The value must be between 1 and 10. For models that don'tsupport batching, you must set the value to 1. This default value can beoverridden with per-query hints.

ALTER MODEL

Changes the definition of a model.

ALTER MODEL [ IF EXISTS ]model_nameSET OPTIONS ( model_options )wheremodel_options is:    {      endpoint = '{endpoint_address}',      endpoints = [ '{endpoint_address}' [, ...] ],      default_batch_size =int64_value    }

• Sets options on the model, using the same syntax asCREATE MODEL.

• Setting an option tonull is equivalent to setting it to the default value.

• The following list of options which can be updated:

  • endpoint is the address of the Vertex AI endpoint toconnect to.
  • endpoints is a list of addresses of Vertex AIendpoints to connect to. Mutually exclusive with endpoint option.
  • default_batch_size specifies the maximum number of rows perremote inference call. The value must be between 1 and 10. For models thatdon't support batching, you must set the value to 1. This default value can beoverridden with per-query hints.

DROP MODEL

Removes a model.

Syntax

DROP MODEL [ IF EXISTS ]model_name

Description

Use theDROP MODEL statement to remove a model definition from the database.Unless theIF EXISTS clause is specified, the statement fails if the modeldoesn't exist.

After you delete a model definition, all SQL queries referencing the deleted modelfail. Dropping a model definition does not affect the underlying theVertex AI endpoint that this model is attached to.

Parameters

model_name

• The name of the model to drop.

VECTOR INDEX statements

If you have a table with a large amount of vector data, you can use a vectorindex to perform similarity searches and nearest neighbor queries efficiently,with the trade-off of reducedrecalland more approximate results.

CREATE VECTOR INDEX

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

Syntax

CREATE VECTOR INDEX [ IF NOT EXISTS ]index_nameONtable_name(column_name)[ STORING (column_name [, ...] ) ][ WHEREcolumn_name IS NOT NULL ]OPTIONS(index_option_list)

Parameters

IF NOT EXISTS

• If there is already a vector index with that name in the table, do nothing.

index_name

• The name of the vector index you're creating. This name must be unique foreach database.

table_name

• The name of the table.

column_name

• The name of a column with a type ofARRAY<FLOAT64>(vector_length=>INT)orARRAY<FLOAT32>(vector_length=>INT). The column can't have anychild fields. All elements in the array must be non-NULL, and all values inthe column must have the same array dimensions as defined byvector_length.If the embedding column is not defined asNOT NULL, then use theWHERE column_name IS NOT NULL clause when creating the vector index.

WHERE IS NOT NULL

• Rows that contain NULL in any of the columns listed in this clause aren'tincluded in the index. The columns must be present in the indexed columns orSTORING clause.

STORING

• Provides a mechanism for duplicating data from the table into the vectorindex. This is the same asSTORING in a secondary index. For moreinformation, seeSTORING clause.

index_option_list

• The list of options to set on the vector index.

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-likestructure to partition vector data. Usingindex_option_list, you can definethe specific distance metric and search tree specification used to create thevector index. Specify the options in the following format:NAME=VALUE, ....

The following index options are supported:

Examples

The following example creates a vector indexSinger_vector_index on theembedding column of theSingers table and defines the distance type:

CREATETABLESingers(idINT64,genreSTRING,embeddingARRAY<FLOAT32>(vector_length=>128))PRIMARYKEY(id);CREATEVECTORINDEXSinger_vector_indexONSingers(embedding)STORING(genre)WHEREembeddingISNOTNULLOPTIONS(distance_type='COSINE');

The following example creates a vector indexSinger_vector_index on theembedding column of theSingers table and defines the distance type andsearch tree specifications, which are optional:

CREATETABLESingers(idINT64,embeddingARRAY<FLOAT32>(vector_length=>128))PRIMARYKEY(id);CREATEVECTORINDEXSinger_vector_indexONSingers(embedding)STORING(genre)WHEREembeddingISNOTNULLOPTIONS(distance_type='COSINE',tree_depth=3,num_branches=1000,num_leaves=1000000);

ALTER VECTOR INDEX statement

Use theALTER VECTOR INDEX statement to add additional stored columns orremove stored columns from the vector index.

Syntax

ALTER VECTOR INDEXindex_name{ADD|DROP} STORED COLUMNcolumn_name

Parameters

index_name

• The name of the vector index to alter.

column_name

• The name of the stored column to add or remove from the vector index.

Description

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

Examples

The followingALTER VECTOR INDEX statement modifies the vector index byremoving the stored columngenre:

ALTERVECTORINDEXSinger_vector_indexDROPSTOREDCOLUMNgenre;

DROP VECTOR INDEX statement

Deletes a vector index on a table.

Syntax

DROP[VECTOR]INDEXindex_name;

Parameters

• index_name: The name of the vector index to be deleted.

Example

The following example deletes the vector indexSinger_vector_index:

DROPVECTORINDEXSinger_vector_index;