gcloud spanner

The gcloud command-line tool enables you to interact with Cloud Spanner. You use the gcloud spanner command to interact with Cloud Spanner.

If you haven't already set up your environment to use Cloud Spanner, follow the Set Up steps.

This page describes common gcloud spanner commands. You can also get command-line help by invoking a command with a --help argument. For example:

gcloud spanner instances create --help
SYNOPSIS
    gcloud spanner instances create INSTANCE --config=CONFIG
        --description=DESCRIPTION --nodes=NODES [--async] [GLOBAL-FLAG ...]

DESCRIPTION
    Cloud Spanner instances create command.

...

This page uses several variables which you should replace with values from your Cloud Spanner project.

  • PROJECT_ID: project ID
  • INSTANCE_ID: instance ID
  • NODE_COUNT: number of nodes in an instance
  • DATABASE_ID: database ID
  • SQL_STATEMENT: SQL statement
  • TABLE_NAME: table name
  • COL_NAME: column name
  • COL_VALUE: value for a column
  • KEY_VALUE: value for a primary key column
  • SESSION_ID: session ID
  • POLICY_FILE: filename of a JSON or YAML policy file

For the complete gcloud spanner reference, see gcloud spanner.

Set a default project

If you haven't already done so, set the ID of a Google Cloud Platform project as the default project for the gcloud command-line tool:

gcloud config set project PROJECT_ID

If you don't set the default project, you must pass --project PROJECT_ID to each of the commands below as the first argument to gcloud spanner. For example:

gcloud spanner --project=PROJECT_ID instance-configs list

Instances and instance configurations

A Cloud Spanner instance is an allocation of resources that are used by Cloud Spanner databases. To create an instance, you must select an instance configuration, which is like a blueprint for your instance that defines the geographic placement and replication of your Cloud Spanner data.

List instance configurations

To see the set of instance configurations that are available for your project:

gcloud spanner instance-configs list

You should see a list of regional and multi-region configurations.

Regional configurations distribute data in a single region, while multi-region configurations distribute data geographically across multiple regions. Read more about these in Instances.

Create instances

After you've selected an instance configuration that meets your application's needs, the next step is to choose the number of nodes to use when creating your instance. The number of nodes corresponds to the amount of resources that are available to the databases in your instance. Note that regional instances are always resilient to single zone outages, regardless of the number of nodes they contain. For more information about regions and zones, refer to Geography and Regions.

To create an instance named test-instance with the display name My Instance using the regional instance configuration regional-us-central1 with 5 nodes:

gcloud spanner instances create test-instance --config=regional-us-central1 \
    --description="My Instance" --nodes=5

In the command above, the instance name is set to test-instance and --description sets the display name of the instance. Both of these values must be unique within a Google Cloud Platform project.

Set the default instance

You can set the default instance that Cloud Spanner uses when you have not specified an instance in your command. To set the default instance:

gcloud config set spanner/instance INSTANCE_ID

List instances

To list all the live instances and their configurations (e.g., node count, state) in a project:

gcloud spanner instances list

List a specific instance

To get the configuration of a specific instance:

gcloud spanner instances describe INSTANCE_ID

Update instances

You can update only the display name and the node count of an existing instance. To update the number of nodes in an instance:

gcloud spanner instances update INSTANCE_ID --nodes=NODE_COUNT

You can find the existing set of properties using gcloud spanner instances list or gcloud spanner instances describe INSTANCE_ID.

Delete instances

To delete an existing instance:

gcloud spanner instances delete INSTANCE_ID

Note that deleting an instance also drops all of the databases in that instance. Deleting an instance is not reversible.

Create and manage databases

Create databases

To use Cloud Spanner's read and write APIs, you must create a database. You can create multiple databases in an instance.

To create a database with the ID DATABASE_ID in the instance INSTANCE_ID:

gcloud spanner databases create DATABASE_ID --instance=INSTANCE_ID

List databases

To list the databases in an instance:

gcloud spanner databases list --instance=INSTANCE_ID

Delete databases

To delete the database DATABASE_ID in the instance INSTANCE_ID:

gcloud spanner databases delete DATABASE_ID --instance=INSTANCE_ID

Update databases

You can modify the schema of an existing database. For example, to create a table named MyTable:

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='CREATE TABLE MyTable ( MyKey INT64, MyValue STRING(MAX) ) PRIMARY KEY (MyKey)'

Refer to Data Definition Language for the schema syntax.

Execute SQL statements

To execute a SQL SELECT statement on the command line, use the following syntax:

gcloud spanner databases execute-sql DATABASE_ID --instance=INSTANCE_ID \
    --sql=SQL_STATEMENT

For example, to execute a SQL SELECT statement:

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT * FROM MyTable WHERE MyKey = 1'

For the Cloud Spanner SQL reference, refer to Query Syntax.

Insert, update, and delete rows

Use the gcloud beta spanner rows command group to modify data in a database:

  • Insert new rows into a table.
  • Update columns in existing rows in a table.
  • Delete rows from a table.

The rows command group recognizes literals for all valid column types.

Insert a row in a table

To insert a new row in a table, you must include values for the key columns and any other required columns:

gcloud beta spanner rows insert --instance=INSTANCE_ID --database=DATABASE_ID \
    --table=TABLE_NAME \
    --data=COL_NAME_1=COL_VALUE_1,COL_NAME_2=COL_VALUE_2,COL_NAME_3=COL_VALUE_3,...,COL_NAME_N=COL_VALUE_N

The following example inserts a new row in the Singers table:

gcloud beta spanner rows insert --instance=test-instance --database=example-db \
    --table=Singers \
    --data=SingerId=1,SingerName='Marc Richards'

Update a row in a table

To update a row in a table, you must include values for the key columns and the columns you want to update:

gcloud beta spanner rows update --instance=INSTANCE_ID --database=DATABASE_ID \
    --table=TABLE_NAME \
    --data=COL_NAME_1=COL_VALUE_1,COL_NAME_2=COL_VALUE_2,COL_NAME_3=COL_VALUE_3,...,COL_NAME_N=COL_VALUE_N

The following example updates a row in the Singers table:

gcloud beta spanner rows update --instance=test-instance --database=example-db \
    --table=Singers \
    --data=SingerId=1,SingerName='Marc Richards'

You cannot change the key values using the update command. To update a key value, you must create a new row and delete the existing row.

Delete a row from a table

To delete a row, you must specify the values for the primary key columns:

gcloud beta spanner rows delete --instance=INSTANCE_ID --database=DATABASE_ID  \
    --table=TABLE_NAME \
    --keys=KEY_VALUE_1,KEY_VALUE_2,KEY_VALUE_3

The following example deletes a row from the Singers table:

gcloud beta spanner rows delete --instance=test-instance --database=example-db \
    --table=Singers \
    --keys=1

Specify ARRAY values

To pass values to an ARRAY column, include the values in a comma-separated list surrounded by square brackets. The following example writes the array [1,2,3] in the Numbers column:

gcloud beta spanner rows insert --instance=test-instance --database=example-db \
     --table=Stats \
     --data=Id=1,Locked=True,Numbers=[1,2,3]

For a NULL array, pass NULL. For an empty array, pass [].

Specify commit timestamps

To insert or update a value automatically in a commit timestamp column, pass spanner.commit_timestamp() as the value of the column. The following example writes the commit timestamp in the UpdateTime column when the row is inserted.

gcloud beta spanner rows insert --instance=test-instance --database=example-db \
    --table=Stats \
    --data=Id=1,Locked=True,Message=’Hi, too!’,UpdateTime='spanner.commit_timestamp()'

The following example writes a specific timestamp value in the UpdateTime column:

gcloud beta spanner rows update --instance=test-instance --database=example-db \
    --table=Stats \
    --data=Id=1,UpdateTime=2017-01-02T12:34:00.45Z

Manage IAM policies

You can use the gcloud tool to manage IAM policies for Cloud Spanner instances and databases.

An IAM policy is a collection of statements that define who has what access to a resource in Google Cloud Platform.

Get IAM policies

Instance

To get the IAM policy for an instance:

gcloud spanner instances get-iam-policy INSTANCE_ID

Database

To get the IAM policy for a database:

gcloud spanner databases get-iam-policy DATABASE_ID --instance=INSTANCE_ID

Set IAM policies

To set the IAM policy for a resource, define the policy in a JSON or YAML file and reference that file in your set-iam-policy command. If your resource already has an IAM policy, this command will replace the entire policy with the policy in your policy file. For help working with IAM policy files, see Cloud IAM policies.

Once you set your IAM policy, you can update it by adding or removing IAM policy bindings. You do not need to run set-iam-policy to update your policy.

Instance

To set an IAM policy for an instance:

gcloud spanner instances set-iam-policy INSTANCE_ID POLICY_FILE

For example, this command sets the IAM policy defined in myPolicy.json:

gcloud spanner instances set-iam-policy test-instance myPolicy.json

Database

To set an IAM policy for the database DATABASE_ID in the instance INSTANCE_ID:

gcloud spanner databases set-iam-policy DATABASE_ID --instance=INSTANCE_ID POLICY_FILE

For example, this command sets the IAM policy defined in myPolicy.json:

gcloud spanner databases set-iam-policy example-db --instance=test-instance myPolicy.json

Add IAM policy bindings

Adding an IAM policy binding updates your IAM policy by granting permissions to a specific user, group, or service. Each IAM policy binding identifies a member (such as a Google account) and grants that member a bundle of permissions called a role (such as databaseAdmin). See Access Control for Cloud Spanner for a list of roles that control access to Cloud Spanner resources.

The sample commands below grant databaseAdmin permissions to email1@gmail.com. They add a policy binding that grants the databaseAdmin role to the Google account associated with email1@gmail.com.

Instance

You can add a policy binding to an instance. For example, to grant permissions to an account:

gcloud spanner instances add-iam-policy-binding test-instance --member="user:email1@gmail.com" \
    --role="roles/spanner.databaseAdmin"

Database

You can add a policy binding to a database. For example, to grant permissions to an account:

gcloud spanner databases add-iam-policy-binding example-db --instance=test-instance \
    --member="user:email1@gmail.com" --role="roles/spanner.databaseAdmin"

Remove IAM policy bindings

Removing an IAM policy binding removes a set of permissions from the IAM policy used by a resource.

Each IAM policy binding identifies a member (such as a Google account) and grants that member a bundle of permissions called a role (such as databaseAdmin). Removing a binding revokes its bundle of permissions from the member identified in the binding. See Access Control for Cloud Spanner for a list of roles that control access to Cloud Spanner resources.

The sample commands below revoke databaseAdmin permissions from email1@gmail.com. In other words, these commands update the IAM policy to remove the policy binding that grants the databaseAdmin role to the Google account associated with email1@gmail.com. Note that this only affects permissions for the resource that uses this particular IAM policy.

Instance

You can remove a policy binding from an instance. For example, to remove permissions from an account:

gcloud spanner instances remove-iam-policy-binding test-instance \
    --member="user:email1@gmail.com" --role="roles/spanner.databaseAdmin"

Database

You can remove a policy binding from a database. For example, to remove permissions from an account:

gcloud spanner databases remove-iam-policy-binding example-db --instance=test-instance \
    --member="user:email1@gmail.com" --role="roles/spanner.databaseAdmin"

Manage sessions

If you are creating a client library or using the REST API, it can be useful to know how many active sessions you have, especially if you often exceed the limit (10,000 sessions per node per database) for active sessions. See Sessions to learn more.

List all active sessions

To list all active sessions for a database:

gcloud spanner databases sessions list --database=DATABASE_ID --instance=INSTANCE_ID

Delete session

To delete a session:

gcloud spanner databases sessions delete SESSION_ID --database=DATABASE_ID --instance=INSTANCE_ID
Was this page helpful? Let us know how we did:

Send feedback about...

Cloud Spanner Documentation