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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Ways to make REST calls

You can make Spanner REST calls using:

• TheTry-It! feature found in theSpanner API referencedocumentation.
• Google APIs Explorer, whichcontains theCloud Spanner API and other Google APIs.
• Other tools or frameworks that support HTTP REST calls.

Conventions used on this page

• The examples use<var>PROJECT_ID</var> as the Google Cloud project ID.Substitute your Google Cloud project ID for<var>PROJECT_ID</var>.

• The examples create and use an instance ID oftest-instance. Substituteyour instance ID if you are not usingtest-instance.

• The examples create and use a database ID ofexample-db. Substitute yourdatabase ID if you are not usingexample-db.

• The examples use<var>SESSION</var> as part of a session name. Substitutethe value you receive when youcreate a session for<var>SESSION</var>.

• The examples use a transaction ID of<var>TRANSACTION_ID</var>. Substitutethe value you receive when you create a transaction for<var>TRANSACTION_ID</var>.

• TheTry-It! functionality supports interactively adding individual HTTPrequest fields. Most examples in this document provide the entire requestinstead of describing how to interactively add individual fields to therequest.

Instances

When you first use Spanner, create an instance. An instance allocatesresources that Spanner databases use. When you create an instance, youchoose where your data is stored and how muchcompute capacity theinstance has.

List instance configurations

When you create an instance, you specify aninstance configuration, whichdefines the geographic placement and replication of your databases in thatinstance. Choose a regional configuration to store data in one region, or amulti-region configuration to distribute data across multiple regions. Learnmore inInstances.

Useprojects.instanceConfigs.list to determine which configurations areavailable for your Google Cloud project.

1. Clickprojects.instanceConfigs.list.

2. Forparent, enter:

   projects/PROJECT_ID

3. ClickExecute. The response shows the available instance configurations.Here's an example response (your project may have different instanceconfigurations):

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

You use thename value for one of the instance configurations when you createyour instance.

Create an instance

1. Clickprojects.instances.create.

2. Forparent, enter:

   projects/<var>PROJECT_ID</var>

3. ClickAdd request body parameters and selectinstance.

4. Click the hint bubble forinstance to see the possible fields. Add valuesfor the following fields:

   • nodeCount: Enter1.
   • config: Enter thename value of one of the regional instanceconfigurations returned when youlist instanceconfigurations.
   • displayName: EnterTest Instance.

5. Click the hint bubble that follows the closing bracket forinstance andselectinstanceId.

6. ForinstanceId, entertest-instance.
   YourTry It! instance creation page should now look like this:

   

7. ClickExecute. The response returns along-runningoperation. Query this operation to check itsstatus.

List instances usingprojects.instances.list.

Create a database

Create a database namedexample-db.

1. Clickprojects.instances.databases.create.

2. Forparent, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance

3. ClickAdd request body parameters and selectcreateStatement.

4. ForcreateStatement, enter:

   CREATE DATABASE `example-db`

The database name,example-db, contains a hyphen, so enclose it inbackticks (`).

1. ClickExecute. The response returns along-runningoperation. Query this operation to check itsstatus.

List your databases usingprojects.instances.databases.list.

Create a schema

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

1. Clickprojects.instances.databases.updateDdl.

2. Fordatabase, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db

3. ForRequest 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"  ]}

   Thestatements array contains the DDL statements that define the schema.

4. ClickExecute. The response returns along-running operation. Query this operationto check its status.

The schema defines two tables,Singers andAlbums, for a basic musicapplication. This document uses these tables. Review theexample schema.

Retrieve your schema usingprojects.instances.databases.getDdl.

Create a session

Before you add, update, delete, or query data, create asession. A session represents a communication channel withthe Spanner database service. (You do not directly use a session if youare using a Spannerclient library,because the client library manages sessions on your behalf.)

1. Clickprojects.instances.databases.sessions.create.

2. Fordatabase, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db

3. ClickExecute.

4. The response shows the session that you created, in the form

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

   Use this session when you read or write to your database.

Sessions are intended to be long-lived. The Spanner database servicedeletes a session when the session is idle for more than one hour. Attempts touse a deleted session result inNOT_FOUND. If you encounter this error, createand use a new session. See if a session is still alive usingprojects.instances.databases.sessions.get.

For related information, seeKeep an idle session alive.

Sessions are an advanced concept. For more details and best practices, seeSessions.

Next, write data to your database.

Write data

You write data using theMutationtype. AMutation is a container for mutation operations. AMutationrepresents a sequence of inserts, updates, deletes, and other actions thatapply atomically to different rows and tables in a Spannerdatabase.

1. Clickprojects.instances.databases.sessions.commit.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest 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"           ]         ]       }     }   ] }

4. ClickExecute. The response shows the commit timestamp.

This example usedinsertOrUpdate. OtheroperationsforMutations areinsert,update,replace, anddelete.

For information on how to encode data types, seeTypeCode.

Query data using SQL

1. Clickprojects.instances.databases.sessions.executeSql.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows the query results.

Read data using the read API

1. Clickprojects.instances.databases.sessions.read.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows the read results.

Update the database schema

Add a new column calledMarketingBudget to theAlbumstable. This requires an update to your database schema. Spannersupports schema updates to a database while the database continues to servetraffic. Schema updates do not require taking the database offline and they donot lock entire tables or columns; you can continue writing data to the databaseduring the schema update.

Add a column

1. Clickprojects.instances.databases.updateDdl.

2. Fordatabase, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db

3. ForRequest body, use the following:

    {   "statements": [     "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"   ] } ```The `statements` array contains the DDL statements that define the schema.

4. ClickExecute. This may take a few minutes to complete, even after theREST call returns a response. The response returns along-runningoperation. Query this operation to check itsstatus.

Write data to the new column

This code writes data to the new column. It setsMarketingBudget to100000for the row keyed byAlbums(1, 1) and to500000 for the row keyed byAlbums(2, 2).

1. Clickprojects.instances.databases.sessions.commit.

2. Forsession, enter:

        projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

   (You receive this value when youcreate a session.)

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows the commit timestamp.

Execute a SQL query or a read call to fetch the values you just wrote.

1. Clickprojects.instances.databases.sessions.executeSql.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows two rows that contain the updatedMarketingBudget values:

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

Use a secondary index

To fetch all rows ofAlbums that haveAlbumTitle valuesin a certain range, read all values from theAlbumTitle column usinga SQL statement or a read call, and then discard the rows that do not meet thecriteria. However, this full table scan is expensive, especially for tableswith many rows. To speed up row retrieval when searching by non-primary key columns, create asecondary index on the table.

Adding a secondary index to an existing table requires a schema update. Similar toother schema updates, Spanner supports adding an index while thedatabase continues to serve traffic. Spanner automatically backfills theindex 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 certaintables or columns during this process. For more information, seeindex backfilling.

After you add a secondary index, Spanner automatically uses it forSQL queries that run faster with the index. If you use the readinterface, specify the index that you want to use.

Add a secondary index

Add an index usingupdateDdl.

1. Clickprojects.instances.databases.updateDdl.

2. Fordatabase, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db

3. ForRequest body, use the following:

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

4. ClickExecute. This may take a few minutes to complete, even after theREST call returns a response. The response returns along-runningoperation. Query this operation to check itsstatus.

Query using the index

1. Clickprojects.instances.databases.sessions.executeSql.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows the following rows:

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

Read using the index

1. Clickprojects.instances.databases.sessions.read.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows 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

The previous read example did not include theMarketingBudget column. Thisoccurs because the Spanner read interface does not support joining anindex with a data table to look up values not stored in the index.

Create an alternate definition ofAlbumsByAlbumTitle that stores a copy ofMarketingBudget in the index.

Add a STORING index usingupdateDdl.

1. Clickprojects.instances.databases.updateDdl.

2. Fordatabase, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db

3. ForRequest body, use the following:

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

4. ClickExecute. This may take a few minutes to complete, even after theREST call returns a response. The response returns along-runningoperation. Query this operation to check itsstatus.

Now, execute a read that fetches allAlbumId,AlbumTitle, andMarketingBudget columns from theAlbumsByAlbumTitle2 index:

1. Clickprojects.instances.databases.sessions.read.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows 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

To execute more than one read at the same timestamp, useRead-onlytransactions. These transactionsobserve a consistent prefix of the transaction commit history, so yourapplication always gets consistent data.

Create a read-only transaction

1. Clickprojects.instances.databases.sessions.beginTransaction.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest Body, use the following:

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

4. ClickExecute.

5. The response shows the ID of the transaction that you created.

Use the read-only transaction to retrieve data at a consistent timestamp, evenif the data changed after you created the read-only transaction.

Run a query using the read-only transaction

1. Clickprojects.instances.databases.sessions.executeSql.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

   {  "sql": "SELECT SingerId, AlbumId, AlbumTitle FROM Albums",  "transaction": {    "id": "<var>TRANSACTION_ID</var>"  }}

4. ClickExecute. The response shows rows similar to the following:

   "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

1. Clickprojects.instances.databases.sessions.read.

2. Forsession, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db/sessions/<var>SESSION</var>

3. ForRequest body, use the following:

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

4. ClickExecute. The response shows rows similar to the following:

   "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 ofreads and writes atomically at a single logical point in time. For moreinformation, seeRead-writetransactions. (TheTry-It! functionality is not suitable for demonstrating a read-writetransaction.)

Cleanup

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

Drop a database

1. Clickprojects.instances.databases.dropDatabase.

2. Forname, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance/databases/example-db

3. ClickExecute.

Delete an instance

1. Clickprojects.instances.delete.

2. Forname, enter:

   projects/<var>PROJECT_ID</var>/instances/test-instance

3. ClickExecute.