Getting started with Spanner using REST

Objectives

This tutorial walks you through the following steps using the Cloud Spanner API with REST:

Create a Spanner instance and database.
Write, read, and execute SQL queries on data in the database.
Update the database schema.
Add a secondary index to the database.
Use the index to read and execute SQL queries on data.
Retrieve data using a read-only transaction.

If you want to use Spanner client libraries instead of using the REST API, see Tutorials.

Costs

This tutorial uses Spanner, which is a billable component of the Google Cloud. For information on the cost of using Spanner, see Pricing.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

Ways to make REST calls

You can make Spanner REST calls using:

The Try-It! feature found in the Spanner API reference documentation. The examples shown on this page use the Try-It! feature.
Google APIs Explorer, which contains the Cloud Spanner API and other Google APIs.
Other tools or frameworks that support HTTP REST calls.

Conventions used on this page

The examples use [PROJECT_ID] as the Google Cloud project ID. Substitute your Google Cloud project ID for [PROJECT_ID]. Do not include [ and ] in your project ID.
The examples create and use an instance ID of test-instance. Substitute your instance ID if you are not using test-instance.
The examples create and use a database ID of example-db. Substitute your database ID if you are not using example-db.
The examples use [SESSION] as part of a session name. Substitute the value you receive when you create a session for [SESSION]. (Do not include [ and ] in your session name.)
The examples use a transaction ID of [TRANSACTION_ID]. Substitute the value you receive when you create a transaction for [TRANSACTION_ID]. (Do not include [ and ] in your transaction ID.)
The Try-It! functionality supports interactively adding individual HTTP request fields. Most examples in this topic provide the entire request instead of describing how to interactively add individual fields to the request.

Instances

When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases. When you create an instance, you choose where your data is stored and how much compute capacity the instance has.

List instance configurations

When you create an instance, you specify an instance configuration, which defines the geographic placement and replication of your databases in that instance. You can choose a regional configuration, which stores data in one region, or a multi-region configuration, which distributes data across multiple regions. Learn more in Instances.

Use projects.instanceConfigs.list to determine which configurations are available for your Google Cloud project.

Click projects.instanceConfigs.list.
For parent, enter:
```
projects/[PROJECT_ID]
```

Click Execute. The available instance configurations are shown in the response. Here's an example response (your project may have different instance configurations):

{
  "instanceConfigs": [
    {
      "name": "projects/[PROJECT_ID]/instanceConfigs/regional-asia-south1",
      "displayName": "asia-south1"
    },
    {
      "name": "projects/[PROJECT_ID]/instanceConfigs/regional-asia-east1",
      "displayName": "asia-east1"
    },
    {
      "name": "projects/[PROJECT_ID]/instanceConfigs/regional-asia-northeast1",
      "displayName": "asia-northeast1"
    },
    {
      "name": "projects/[PROJECT_ID]/instanceConfigs/regional-europe-west1",
      "displayName": "europe-west1"
    },
    {
      "name": "projects/[PROJECT_ID]/instanceConfigs/regional-us-east4",
      "displayName": "us-east4"
    },
    {
      "name": "projects/[PROJECT_ID]/instanceConfigs/regional-us-central1",
      "displayName": "us-central1"
    }
  ]
}

You use the name value for one of the instance configurations when you create your instance.

Create an instance

Click projects.instances.create.
For parent, enter:
```
projects/[PROJECT_ID]
```
Click Add request body parameters and select instance.
Click the hint bubble for instance to see the possible fields. Add values for the following fields:
1. nodeCount: Enter 1.
2. config: Enter the name value of one of the regional instance configurations returned when you list instance configurations.
3. displayName: Enter Test Instance.
Click the hint bubble that follows the closing bracket for instance and select instanceId.
For instanceId, enter test-instance.
Your Try It! instance creation page should now look like this:
Click Execute. The response returns a long-running operation which you can query to check its status.

You can list your instances using projects.instances.list.

Create a database

Create a database named example-db.

Click projects.instances.databases.create.

For parent, enter:

projects/[PROJECT_ID]/instances/test-instance

Click Add request body parameters and select createStatement.
For createStatement, enter:
```
CREATE DATABASE `example-db`
```
(The database name, example-db, contains a hyphen, so it must be enclosed in backticks (`).
Click Execute. The response returns a long-running operation which you can query to check its status.

You can list your databases using projects.instances.databases.list.

Create a schema

Use Spanner's Data Definition Language (DDL) to create, alter, or drop tables, and to create or drop indexes.

Click projects.instances.databases.updateDdl.

For database, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db

For Request body, use the following:

{
  "statements": [
    "CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX) ) PRIMARY KEY (SingerId)",
    "CREATE TABLE Albums ( SingerId INT64 NOT NULL, AlbumId INT64 NOT NULL, AlbumTitle STRING(MAX)) PRIMARY KEY (SingerId, AlbumId), INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
  ]
}

The statements array contains the DDL statements that define the schema.

Click Execute. The response returns a long-running operation which you can query to check its status.

The schema defines two tables, Singers and Albums, for a basic music application. These tables are used throughout this page. Take a look at the example schema if you haven't already.

You can retrieve your schema using projects.instances.databases.getDdl.

Create a session

Before you can add, update, delete, or query data, you must create a session, which represents a communication channel with the Spanner database service. (You do not directly use a session if you are using a Spanner client library, because the client library manages sessions on your behalf.)

Click projects.instances.databases.sessions.create.

For database, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db

Click Execute.
The response shows the session that you created, in the form
```
projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]
```
You will use this session when you read or write to your database.

Sessions are intended to be long-lived. The Spanner database service can delete a session when the session is idle for more than one hour. Attempts to use a deleted session result in NOT_FOUND. If you encounter this error, create and use a new session. You can see if a session is still alive using projects.instances.databases.sessions.get. For related information, see Keep an idle session alive.

The next step is to write data to your database.

Write data

You write data using the Mutation type. A Mutation is a container for mutation operations. A Mutation represents a sequence of inserts, updates, deletes, and other actions that can be applied atomically to different rows and tables in a Spanner database.

Click projects.instances.databases.sessions.commit.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "singleUseTransaction": {
    "readWrite": {}
  },
  "mutations": [
    {
      "insertOrUpdate": {
        "table": "Singers",
        "columns": [
          "SingerId",
          "FirstName",
          "LastName"
        ],
        "values": [
          [
            "1",
            "Marc",
            "Richards"
          ],
          [
            "2",
            "Catalina",
            "Smith"
          ],
          [
            "3",
            "Alice",
            "Trentor"
          ],
          [
            "4",
            "Lea",
            "Martin"
          ],
          [
            "5",
            "David",
            "Lomond"
          ]
        ]
      }
    },
    {
      "insertOrUpdate": {
        "table": "Albums",
        "columns": [
          "SingerId",
          "AlbumId",
          "AlbumTitle"
        ],
        "values": [
          [
            "1",
            "1",
            "Total Junk"
          ],
          [
            "1",
            "2",
            "Go, Go, Go"
          ],
          [
            "2",
            "1",
            "Green"
          ],
          [
            "2",
            "2",
            "Forever Hold Your Peace"
          ],
          [
            "2",
            "3",
            "Terrified"
          ]
        ]
      }
    }
  ]
}

Click Execute. The response shows the commit timestamp.

This example used insertOrUpdate. Other operations for Mutations are insert, update, replace, and delete.

For information on how to encode data types, see TypeCode.

Query data using SQL

Click projects.instances.databases.sessions.executeSql.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "sql": "SELECT SingerId, AlbumId, AlbumTitle FROM Albums"
}

Click Execute. The response shows the query results.

Read data using the read API

Click projects.instances.databases.sessions.read.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "table": "Albums",
  "columns": [
    "SingerId",
    "AlbumId",
    "AlbumTitle"
  ],
  "keySet": {
    "all": true
  }
}

Click Execute. The response shows the read results.

Update the database schema

Assume you need to add a new column called MarketingBudget to the Albums table, which requires an update to your database schema. Spanner supports schema updates to a database while the database continues to serve traffic. Schema updates do not require taking the database offline and they do not lock entire tables or columns; you can continue writing data to the database during the schema update.

Add a column

Click projects.instances.databases.updateDdl.

For database, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db

For Request body, use the following:
```
{
  "statements": [
    "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"
  ]
}
```
The statements array contains the DDL statements that define the schema.
Click Execute. This may take a few minutes to complete, even after the REST call returns a response. The response returns a long-running operation which you can query to check its status.

Write data to the new column

The following code writes data to the new column. It sets MarketingBudget to 100000 for the row keyed by Albums(1, 1) and to 500000 for the row keyed by Albums(2, 2).

Click projects.instances.databases.sessions.commit.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "singleUseTransaction": {
    "readWrite": {}
  },
  "mutations": [
    {
      "update": {
        "table": "Albums",
        "columns": [
          "SingerId",
          "AlbumId",
          "MarketingBudget"
        ],
        "values": [
          [
            "1",
            "1",
            "100000"
          ],
          [
            "2",
            "2",
            "500000"
          ]
        ]
      }
    }
  ]
}

Click Execute. The response shows the commit timestamp.

You can also execute a SQL query or a read call to fetch the values that you just wrote.

Here's how to execute the query:

Click projects.instances.databases.sessions.executeSql.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "sql": "SELECT SingerId, AlbumId, MarketingBudget FROM Albums"
}

Click Execute. As part of the response you should see two rows that contain the updated MarketingBudget values:

"rows": [
  [
    "1",
    "1",
    "100000"
  ],
  [
    "1",
    "2",
    null
  ],
  [
    "2",
    "1",
    null
  ],
  [
    "2",
    "2",
    "500000"
  ],
  [
    "2",
    "3",
    null
  ]
]

Use a secondary index

Suppose you wanted to fetch all rows of Albums that have AlbumTitle values in a certain range. You could read all values from the AlbumTitle column using a SQL statement or a read call, and then discard the rows that don't meet the criteria, but doing this full table scan is expensive, especially for tables with a lot of rows. Instead you can speed up the retrieval of rows when searching by non-primary key columns by creating a secondary index on the table.

Adding a secondary index to an existing table requires a schema update. Like other schema updates, Spanner supports adding an index while the database continues to serve traffic. Spanner automatically backfills the index with your existing data. Backfills might take a few minutes to complete, but you don't need to take the database offline or avoid writing to certain tables or columns during this process. For more details, see index backfilling.

After you add a secondary index, Spanner automatically uses it for SQL queries that are likely to run faster with the index. If you use the read interface, you must specify the index that you want to use.

Add a secondary index

You can add an index using updateDdl.

Click projects.instances.databases.updateDdl.

For database, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db

For Request body, use the following:

{
  "statements": [
    "CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"
  ]
}

Click Execute. This may take a few minutes to complete, even after the REST call returns a response. The response returns a long-running operation which you can query to check its status.

Query using the index

Click projects.instances.databases.sessions.executeSql.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "sql": "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums WHERE AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo'"
}

Click Execute. As part of the response you should see the following rows:

"rows": [
  [
    "2",
    "Go, Go, Go",
    null
  ],
  [
    "2",
    "Forever Hold Your Peace",
    "500000"
  ]
]

Read using the index

Click projects.instances.databases.sessions.read.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "table": "Albums",
  "columns": [
    "AlbumId",
    "AlbumTitle"
  ],
  "keySet": {
    "all": true
  },
  "index": "AlbumsByAlbumTitle"
}

Click Execute. As part of the response you should see the following rows:

"rows": [
  [
    "2",
    "Forever Hold Your Peace"
  ],
  [
    "2",
    "Go, Go, Go"
  ],
  [
    "1",
    "Green"
  ],
  [
    "3",
    "Terrified"
  ],
  [
    "1",
    "Total Junk"
  ]
]

Add an index with the STORING clause

You might have noticed that the read example above did not include reading the MarketingBudget column. This is because Spanner's read interface does not support the ability to join an index with a data table to look up values that are not stored in the index.

Create an alternate definition of AlbumsByAlbumTitle that stores a copy of MarketingBudget in the index.

You can add a STORING index using updateDdl.

Click projects.instances.databases.updateDdl.

For database, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db

For Request body, use the following:

{
  "statements": [
    "CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)"
  ]
}

Click Execute. This may take a few minutes to complete, even after the REST call returns a response. The response returns a long-running operation which you can query to check its status.

Now you can execute a read that fetches all AlbumId, AlbumTitle, and MarketingBudget columns from the AlbumsByAlbumTitle2 index:

Click projects.instances.databases.sessions.read.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "table": "Albums",
  "columns": [
    "AlbumId",
    "AlbumTitle",
    "MarketingBudget"
  ],
  "keySet": {
    "all": true
  },
  "index": "AlbumsByAlbumTitle2"
}

Click Execute. As part of the response you should see the following rows:

"rows": [
  [
    "2",
    "Forever Hold Your Peace",
    "500000"
  ],
  [
    "2",
    "Go, Go, Go",
    null
  ],
  [
    "1",
    "Green",
    null
  ],
  [
    "3",
    "Terrified",
    null
  ],
  [
    "1",
    "Total Junk",
    "100000"
  ]
]

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp. Read-only transactions observe a consistent prefix of the transaction commit history, so your application always gets consistent data.

Create a read-only transaction

Click projects.instances.databases.sessions.beginTransaction.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

For Request Body, use the following:

{
  "options": {
    "readOnly": {}
  }
}

Click Execute.
The response shows the ID of the transaction that you created.

You can now use the read-only transaction to retrieve data at a consistent timestamp, even if the data has changed since you created the read-only transaction.

Run a query using the read-only transaction

Click projects.instances.databases.sessions.executeSql.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "sql": "SELECT SingerId, AlbumId, AlbumTitle FROM Albums",
  "transaction": {
    "id": "[TRANSACTION_ID]"
  }
}

Click Execute. You should see rows similar to the following in the response:

"rows": [
  [
    "2",
    "2",
    "Forever Hold Your Peace"
  ],
  [
    "1",
    "2",
    "Go, Go, Go"
  ],
  [
    "2",
    "1",
    "Green"
  ],
  [
    "2",
    "3",
    "Terrified"
  ],
  [
    "1",
    "1",
    "Total Junk"
  ]
]

Read using the read-only transaction

Click projects.instances.databases.sessions.read.

For session, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db/sessions/[SESSION]

(You receive this value when you create a session.)

For Request body, use the following:

{
  "table": "Albums",
  "columns": [
    "SingerId",
    "AlbumId",
    "AlbumTitle"
  ],
  "keySet": {
    "all": true
  },
  "transaction": {
    "id": "[TRANSACTION_ID]"
  }
}

Click Execute. You should see rows similar to the following in the response:

"rows": [
  [
    "1",
    "1",
    "Total Junk"
  ],
  [
    "1",
    "2",
    "Go, Go, Go"
  ],
  [
    "2",
    "1",
    "Green"
  ],
  [
    "2",
    "2",
    "Forever Hold Your Peace"
  ],
  [
    "2",
    "3",
    "Terrified"
  ]
]

Spanner also supports read-write transactions, which execute a set of reads and writes atomically at a single logical point in time. For more information, see Read-write transactions. (The Try-It! functionality is not suitable for demonstrating a read-write transaction.)

Cleanup

To avoid incurring additional charges to your Google Cloud account for the resources used in this tutorial, drop the database and delete the instance that you created.

Drop a database

Click projects.instances.databases.dropDatabase.

For name, enter:

projects/[PROJECT_ID]/instances/test-instance/databases/example-db

Click Execute.

Delete an instance

Click projects.instances.delete.

For name, enter:

projects/[PROJECT_ID]/instances/test-instance

Click Execute.

What's next

Access Spanner in a Virtual Machine Instance: create a virtual machine instance with access to your Spanner database.
Learn more Spanner concepts.