Getting Started with Cloud Spanner in C#

Getting Started with Cloud Spanner in C#

Objectives

This tutorial walks you through the following steps using the Cloud Spanner client library for C#:

  • Create a Cloud Spanner instance and database.
  • Write, read, and execute SQL queries on data in the database.
  • Update the database schema.
  • Update data using a read-write transaction.
  • 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.

Costs

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

Before you begin

  1. Complete the steps described in Set Up, which covers creating and setting a default Google Cloud Platform project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.
    In particular, ensure that you run gcloud auth application-default login to set up your local development environment with authentication credentials.

  2. Set the GOOGLE_PROJECT_ID environment variable to your Google Cloud Platform project ID.

    1. First, to set GOOGLE_PROJECT_ID for the current PowerShell session:

      $env:GOOGLE_PROJECT_ID = "MY_PROJECT_ID"

    2. Then, to set GOOGLE_PROJECT_ID for all processes created after this command:

      [Environment]::SetEnvironmentVariable("GOOGLE_PROJECT_ID", "MY_PROJECT_ID", "User")

  3. Download credentials.

    1. Visit the API Manager Dashboard in the Google Cloud Platform Console and click Credentials.
      Go to the API Manager Dashboard
    2. Click Create credentials and choose Service account key.
    3. Under "Service account", choose Compute Engine default service account, and leave JSON selected under "Key type". Click Create. Your computer downloads a JSON file.
  4. Set up credentials. For a file named FILENAME.json in CURRENT_USER's Downloads directory, located on the C drive, run the following commands to set GOOGLE_APPLICATION_CREDENTIALS to point to the JSON key:

    1. First, to set GOOGLE_APPLICATION_CREDENTIALS for this PowerShell session:

      $env:GOOGLE_APPLICATION_CREDENTIALS = "C:\Users\CURRENT_USER\Downloads\FILENAME.json"

    2. Then, to set GOOGLE_APPLICATION_CREDENTIALS for all processes created after this command:

      [Environment]::SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", "C:\Users\CURRENT_USER\Downloads\FILENAME.json", "User")

  5. Clone the sample app repository to your local machine:

    git clone https://github.com/GoogleCloudPlatform/dotnet-docs-samples
    

    Alternatively, you can download the sample as a zip file and extract it.

  6. Open Spanner.sln, located in the dotnet-docs-samples\spanner\api directory of the downloaded repository, with Visual Studio 2015 or later, then build it.

  7. Change to the directory within the downloaded repository that contains the compiled application. For example:

    cd dotnet-docs-samples\spanner\api\bin\Debug
    

Create an instance

When you first use Cloud Spanner, you must create an instance, which is an allocation of resources that are used by Cloud Spanner databases. When you create an instance, you choose where your data is stored and how many nodes are used for your data. For more information, see Instances.

The following command creates a Cloud Spanner instance using these values:

  • Instance ID test-instance
  • Display name Test Instance
  • Regional configuration regional-us-central1
  • Node count of 1 (node_count corresponds to the amount of serving resources available to databases in the instance)
gcloud spanner instances create test-instance --config=regional-us-central1 `
  --description="Test Instance" --nodes=1

You should see:

Creating instance...done.

Look through sample files

The samples repo contains a sample that shows how to use Cloud Spanner with C#.

Take a look through the spanner/api/Program.cs file, which shows how to create a database and modify a database schema. The data uses the example schema shown in the Schema and Data Model page.

Take a look through the Program.cs file, which shows how to use Cloud Spanner. The code shows how to create and use a new database. The data uses the example schema shown in the Schema and Data Model page.

Create a database

Create a database called example-db by running the following at the command line.

.\Spanner createSampleDatabase $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Created database example-db on instance test-instance

You have just created a Cloud Spanner database. The following is the code that created the database.

// Initialize request connection string for database creation.
string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}";
// Make the request.
using (var connection = new SpannerConnection(connectionString))
{
    string createStatement = $"CREATE DATABASE `{databaseId}`";
    var cmd = connection.CreateDdlCommand(createStatement);
    await cmd.ExecuteNonQueryAsync();
}
// Update connection string with Database ID for table creation.
connectionString = connectionString + $"/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
    // Define create table statement for table #1.
    string createTableStatement =
   @"CREATE TABLE Singers (
         SingerId INT64 NOT NULL,
         FirstName    STRING(1024),
         LastName STRING(1024),
         ComposerInfo   BYTES(MAX)
     ) PRIMARY KEY (SingerId)";
    // Make the request.
    var cmd = connection.CreateDdlCommand(createTableStatement);
    await cmd.ExecuteNonQueryAsync();
    // Define create table statement for table #2.
    createTableStatement =
    @"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";
    // Make the request.
    cmd = connection.CreateDdlCommand(createTableStatement);
    await cmd.ExecuteNonQueryAsync();
}

The code also 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.

The next step is to write data to your database.

Create a database client

Before you can do reads or writes, you must create a Spanner​Connection:

using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

namespace GoogleCloudSamples.Spanner
{
    public class QuickStart
    {
        static async Task MainAsync()
        {
            string projectId = "YOUR-GOOGLE-PROJECT-ID";
            string instanceId = "my-instance";
            string databaseId = "my-database";
            string connectionString =
                $"Data Source=projects/{projectId}/instances/{instanceId}/"
                + $"databases/{databaseId}";
            // Create connection to Cloud Spanner.
            using (var connection = new SpannerConnection(connectionString))
            {
                // Execute a simple SQL statement.
                var cmd = connection.CreateSelectCommand(
                    @"SELECT ""Hello World"" as test");
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        Console.WriteLine(
                            reader.GetFieldValue<string>("test"));
                    }
                }
            }
        }
        public static void Main(string[] args)
        {
            MainAsync().Wait();
        }
    }
}

You can think of a Spanner​Connection as a database connection: all of your interactions with Cloud Spanner must go through a Spanner​Connection.

Read more in the Spanner​Connection reference.

Write data

You can insert data using the connection.CreateInsertCommand() method, which creates a new SpannerCommand to insert rows into a Cloud Spanner database table. The SpannerCommand.ExecuteNonQueryAsync() method adds new rows to the table.

This code shows how to insert data:

public class Singer
{
    public int singerId { get; set; }
    public string firstName { get; set; }
    public string lastName { get; set; }
}

public class Album
{
    public int singerId { get; set; }
    public int albumId { get; set; }
    public string albumTitle { get; set; }
}
public static async Task InsertSampleDataAsync(
    string projectId, string instanceId, string databaseId)
{
    const int firstSingerId = 1;
    const int secondSingerId = 2;
    string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";
    List<Singer> singers = new List<Singer> {
        new Singer {singerId = firstSingerId, firstName = "Marc",
            lastName = "Richards"},
        new Singer {singerId = secondSingerId, firstName = "Catalina",
            lastName = "Smith"},
        new Singer {singerId = 3, firstName = "Alice",
            lastName = "Trentor"},
        new Singer {singerId = 4, firstName = "Lea",
            lastName = "Martin"},
        new Singer {singerId = 5, firstName = "David",
            lastName = "Lomond"},
    };
    List<Album> albums = new List<Album> {
        new Album {singerId = firstSingerId, albumId = 1,
            albumTitle = "Go, Go, Go"},
        new Album {singerId = firstSingerId, albumId = 2,
            albumTitle = "Total Junk"},
        new Album {singerId = secondSingerId, albumId = 1,
            albumTitle = "Green"},
        new Album {singerId = secondSingerId, albumId = 2,
            albumTitle = "Forever Hold your Peace"},
        new Album {singerId = secondSingerId, albumId = 3,
            albumTitle = "Terrified"},
    };
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Insert rows into the Singers table.
        var cmd = connection.CreateInsertCommand("Singers",
            new SpannerParameterCollection {
                {"SingerId", SpannerDbType.Int64},
                {"FirstName", SpannerDbType.String},
                {"LastName", SpannerDbType.String}
        });
        await Task.WhenAll(singers.Select(singer =>
        {
            cmd.Parameters["SingerId"].Value = singer.singerId;
            cmd.Parameters["FirstName"].Value = singer.firstName;
            cmd.Parameters["LastName"].Value = singer.lastName;
            return cmd.ExecuteNonQueryAsync();
        }));

        // Insert rows into the Albums table.
        cmd = connection.CreateInsertCommand("Albums",
            new SpannerParameterCollection {
                {"SingerId", SpannerDbType.Int64},
                {"AlbumId", SpannerDbType.Int64},
                {"AlbumTitle", SpannerDbType.String}
        });
        await Task.WhenAll(albums.Select(album =>
        {
            cmd.Parameters["SingerId"].Value = album.singerId;
            cmd.Parameters["AlbumId"].Value = album.albumId;
            cmd.Parameters["AlbumTitle"].Value = album.albumTitle;
            return cmd.ExecuteNonQueryAsync();
        }));
        Console.WriteLine("Inserted data.");
    }
}

(For details about the data, see the example schema for the Singers and Albums tables.)

Run the sample using the insertSampleData command.

.\Spanner insertSampleData $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Inserted data.

Query data using SQL

Cloud Spanner supports a native SQL interface for reading data, which you can access on the command line using the gcloud command-line tool or programmatically using the Cloud Spanner client library for C#.

On the command line

Execute the following SQL statement to read the values of all columns from the Albums table:

gcloud spanner databases execute-sql example-db --instance=test-instance --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

The result should be:

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

Using the Cloud Spanner client library for C#

In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Cloud Spanner client library for C#.

Use ExecuteReaderAsync() to run the SQL query.

string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("SingerId : "
                + reader.GetFieldValue<string>("SingerId")
                + " AlbumId : "
                + reader.GetFieldValue<string>("AlbumId")
                + " AlbumTitle : "
                + reader.GetFieldValue<string>("AlbumTitle"));
        }
    }
}

Here's how to issue the query and access the data:

.\Spanner querySampleData $env:GOOGLE_PROJECT_ID test-instance example-db

You should see the following result:

SingerId: 1 AlbumId: 1 AlbumTitle: Go, Go, Go
SingerId: 1 AlbumId: 2 AlbumTitle: Total Junk
SingerId: 2 AlbumId: 1 AlbumTitle: Green
SingerId: 2 AlbumId: 2 AlbumTitle: Forever Hold your Peace
SingerId: 2 AlbumId: 3 AlbumTitle: Terrified

Update the database schema

Assume you need to add a new column called MarketingBudget to the Albums table. Adding a new column to an existing table requires an update to your database schema. Cloud 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. Read more about supported schema updates and schema change performance in Updating schemas.

Add a column

You can add a column on the command line using the gcloud command-line tool or programmatically using the Cloud Spanner client library for C#.

On the command line

Use the following ALTER TABLE command to add the new column to the table:

gcloud spanner databases ddl update example-db --instance=test-instance `
  --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

You should see:

DDL updating...done.

Using the Cloud Spanner client library for C#

Use CreateDdlCommand() to modify the schema:

// Initialize request argument(s).
string connectionString =
    $"Data Source=projects/{projectId}/instances/"
    + $"{instanceId}/databases/{databaseId}";
string alterStatement =
    "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";
// Make the request.
using (var connection = new SpannerConnection(connectionString))
{
    var updateCmd = connection.CreateDdlCommand(alterStatement);
    await updateCmd.ExecuteNonQueryAsync();
}
Console.WriteLine("Added the MarketingBudget column.");

Run the sample using the addColumn command.

.\Spanner addColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Added the MarketingBudget column.

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

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateUpdateCommand("Albums",
        new SpannerParameterCollection {
            {"SingerId", SpannerDbType.Int64},
            {"AlbumId", SpannerDbType.Int64},
            {"MarketingBudget", SpannerDbType.Int64},
        });
    var cmdLookup =
        connection.CreateSelectCommand("SELECT * FROM Albums");
    using (var reader = await cmdLookup.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            if (reader.GetFieldValue<int>("SingerId") == 1
                && reader.GetFieldValue<int>("AlbumId") == 1)
            {
                cmd.Parameters["SingerId"].Value =
                    reader.GetFieldValue<int>("SingerId");
                cmd.Parameters["AlbumId"].Value =
                    reader.GetFieldValue<int>("AlbumId");
                cmd.Parameters["MarketingBudget"].Value = 100000;
                await cmd.ExecuteNonQueryAsync();
            }
            if (reader.GetInt64(0) == 2 && reader.GetInt64(1) == 2)
            {
                cmd.Parameters["SingerId"].Value =
                    reader.GetFieldValue<int>("SingerId");
                cmd.Parameters["AlbumId"].Value =
                    reader.GetFieldValue<int>("AlbumId");
                cmd.Parameters["MarketingBudget"].Value = 500000;
                await cmd.ExecuteNonQueryAsync();
            }
        }
    }
}
Console.WriteLine("Updated data.");

Run the sample using the writeDataToNewColumn command.

.\Spanner writeDataToNewColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Updated data.

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

Here's the code to execute the query:

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd =
        connection.CreateSelectCommand("SELECT * FROM Albums");
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("SingerId : "
            + reader.GetFieldValue<string>("SingerId")
            + " AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " MarketingBudget : "
            + reader.GetFieldValue<string>("MarketingBudget"));
        }
    }
}

To execute this query, run the sample using the queryNewColumn argument.

.\Spanner queryNewColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

SingerId : 1 AlbumId : 1 MarketingBudget : 100000
SingerId : 1 AlbumId : 2 MarketingBudget :
SingerId : 2 AlbumId : 1 MarketingBudget :
SingerId : 2 AlbumId : 2 MarketingBudget : 500000
SingerId : 2 AlbumId : 3 MarketingBudget :

Update data using a read-write transaction

Suppose that sales of Albums(1, 1) are lower than expected. As a result, you want to move $200,000 from the marketing budget of Albums(2, 2) to Albums(1, 1), but only if the budget of Albums(2, 2) is at least $300,000.

Because this transaction might write data that differs depending on the values that are read, you should use a read-write transaction to perform the reads and writes atomically.

For .NET Standard 2.0 (or .NET 4.5) and above, you can use the .NET framework's TransactionScope() to run a transaction. For all supported versions of .NET, you can create a transaction by setting the result of SpannerConection.BeginTransactionAsync as the Transaction property of SpannerCommand.

Here are the two ways to run the transaction:

.NET Standard 2.0

public static async Task ReadWriteWithTransactionAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    // This sample transfers 200,000 from the MarketingBudget 
    // field of the second Album to the first Album. Make sure to run
    // the addColumn and writeDataToNewColumn samples first,
    // in that order.

    string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";

    using (TransactionScope scope = new TransactionScope(
        TransactionScopeAsyncFlowOption.Enabled))
    {
        decimal transferAmount = 200000;
        decimal minimumAmountToTransfer = 300000;
        decimal secondBudget = 0;
        decimal firstBudget = 0;

        // Create connection to Cloud Spanner.
        using (var connection =
            new SpannerConnection(connectionString))
        {
            // Create statement to select the second album's data.
            var cmdLookup = connection.CreateSelectCommand(
            "SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
            // Excecute the select query.
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    // Read the second album's budget.
                    secondBudget =
                      reader.GetFieldValue<decimal>("MarketingBudget");
                    // Confirm second Album's budget is sufficient and
                    // if not raise an exception. Raising an exception
                    // will automatically roll back the transaction.
                    if (secondBudget < minimumAmountToTransfer)
                    {
                        throw new Exception("The second album's "
                            + $"budget {secondBudget} "
                            + "is less than the minimum required "
                            + "amount to transfer.");
                    }
                }
            }
            // Read the first album's budget.
            cmdLookup = connection.CreateSelectCommand(
            "SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    firstBudget =
                      reader.GetFieldValue<decimal>("MarketingBudget");
                }
            }

            // Specify update command parameters.
            var cmd = connection.CreateUpdateCommand("Albums",
                new SpannerParameterCollection {
                {"SingerId", SpannerDbType.Int64},
                {"AlbumId", SpannerDbType.Int64},
                {"MarketingBudget", SpannerDbType.Int64},
            });
            // Update second album to remove the transfer amount.
            secondBudget -= transferAmount;
            cmd.Parameters["SingerId"].Value = 2;
            cmd.Parameters["AlbumId"].Value = 2;
            cmd.Parameters["MarketingBudget"].Value = secondBudget;
            await cmd.ExecuteNonQueryAsync();
            // Update first album to add the transfer amount.
            firstBudget += transferAmount;
            cmd.Parameters["SingerId"].Value = 1;
            cmd.Parameters["AlbumId"].Value = 1;
            cmd.Parameters["MarketingBudget"].Value = firstBudget;
            await cmd.ExecuteNonQueryAsync();
            scope.Complete();
            // Yield Task thread back to the current context.
            await Task.Yield();
            Console.WriteLine("Transaction complete.");
        }
    }
}

.NET Standard 1.5

public static async Task ReadWriteWithTransactionCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    // This sample transfers 200,000 from the MarketingBudget 
    // field of the second Album to the first Album. Make sure to run
    // the addColumn and writeDataToNewColumn samples first,
    // in that order.
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    decimal transferAmount = 200000;
    decimal minimumAmountToTransfer = 300000;
    decimal secondBudget = 0;
    decimal firstBudget = 0;

    Console.WriteLine(".NetCore API sample.");

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Create a readwrite transaction that we'll assign
        // to each SpannerCommand.
        using (var transaction =
                await connection.BeginTransactionAsync())
        {
            // Create statement to select the second album's data.
            var cmdLookup = connection.CreateSelectCommand(
             "SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
            cmdLookup.Transaction = transaction;
            // Excecute the select query.
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    // Read the second album's budget.
                    secondBudget =
                       reader.GetFieldValue<decimal>("MarketingBudget");
                    // Confirm second Album's budget is sufficient and
                    // if not raise an exception. Raising an exception
                    // will automatically roll back the transaction.
                    if (secondBudget < minimumAmountToTransfer)
                    {
                        throw new Exception("The second album's "
                                + $"budget {secondBudget} "
                                + "is less than the minimum required "
                                + "amount to transfer.");
                    }
                }
            }
            // Read the first album's budget.
            cmdLookup = connection.CreateSelectCommand(
             "SELECT * FROM Albums WHERE SingerId = 1 and AlbumId = 1");
            cmdLookup.Transaction = transaction;
            using (var reader = await cmdLookup.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    firstBudget =
                      reader.GetFieldValue<decimal>("MarketingBudget");
                }
            }

            // Specify update command parameters.
            var cmd = connection.CreateUpdateCommand("Albums",
                new SpannerParameterCollection
                {
                    {"SingerId", SpannerDbType.Int64},
                    {"AlbumId", SpannerDbType.Int64},
                    {"MarketingBudget", SpannerDbType.Int64},
                });
            cmd.Transaction = transaction;
            // Update second album to remove the transfer amount.
            secondBudget -= transferAmount;
            cmd.Parameters["SingerId"].Value = 2;
            cmd.Parameters["AlbumId"].Value = 2;
            cmd.Parameters["MarketingBudget"].Value = secondBudget;
            await cmd.ExecuteNonQueryAsync();
            // Update first album to add the transfer amount.
            firstBudget += transferAmount;
            cmd.Parameters["SingerId"].Value = 1;
            cmd.Parameters["AlbumId"].Value = 1;
            cmd.Parameters["MarketingBudget"].Value = firstBudget;
            await cmd.ExecuteNonQueryAsync();

            await transaction.CommitAsync();
        }
        // Yield Task thread back to the current context.
        await Task.Yield();
        Console.WriteLine("Transaction complete.");
    }
}

Run the sample using the readWriteWithTransaction command.

.\Spanner readWriteWithTransaction $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Transaction complete.

Query the data again:

.\Spanner queryNewColumn $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

SingerId : 1 AlbumId : 1 MarketingBudget : 300000
SingerId : 1 AlbumId : 2 MarketingBudget :
SingerId : 2 AlbumId : 1 MarketingBudget :
SingerId : 2 AlbumId : 2 MarketingBudget : 300000
SingerId : 2 AlbumId : 3 MarketingBudget :

Performing retries on read-write transactions

Cloud Spanner performs retries for each network call and is resilient to network failures. However, a deadlock can occur under heavy stress, which causes a Cloud Spanner transaction to throw an "Aborted" SpannerException. To handle this exception, you must use the Transient Fault Application block, as shown below, to retry the entire transaction.

First, define the error detection strategy that the Transient Fault Application block uses.

Here is an example of how to define the strategy:

internal class CustomTransientErrorDetectionStrategy
    : ITransientErrorDetectionStrategy
{
    public bool IsTransient(Exception ex) =>
        ex.IsTransientSpannerFault();
}

Next, use the CustomTransientErrorDetectionStrategy you defined to create a retryPolicy, then execute the entire transaction with it.

Here is the code to perform a retry:

var retryPolicy = new
    RetryPolicy<CustomTransientErrorDetectionStrategy>
        (RetryStrategy.DefaultExponential);

await retryPolicy.ExecuteAsync(() =>
    ReadWriteWithTransactionAsync(
            projectId, instanceId, databaseId));

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, Cloud Spanner supports adding an index while the database continues to serve traffic. Cloud Spanner populates the index with data (aka "backfills") under the hood. Backfills might take a few minutes to complete, but you don't have to take the database offline or avoid writing to certain tables or columns during this process. For more details, see index backfilling.

Add a secondary index

You can add an index on the command line using the gcloud command line tool or programmatically using the Cloud Spanner client library for C#.

On the command line

Use the following CREATE INDEX command to add an index to the database:

gcloud spanner databases ddl update example-db --instance=test-instance `
  --ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

DDL updating...done.

Using the Cloud Spanner client library for C#

Use CreateDdlCommand() to add an index:

// Initialize request argument(s).
string connectionString =
    $"Data Source=projects/{projectId}/instances/"
    + $"{instanceId}/databases/{databaseId}";
string createStatement =
    "CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)";
// Make the request.
using (var connection = new SpannerConnection(connectionString))
{
    var createCmd = connection.CreateDdlCommand(createStatement);
    await createCmd.ExecuteNonQueryAsync();
}
Console.WriteLine("Added the AlbumsByAlbumTitle index.");

Run the sample using the addIndex command.

  .\Spanner addIndex $env:GOOGLE_PROJECT_ID test-instance example-db

Adding an index can take a few minutes. After the index is added, you should see:

  Added the AlbumsByAlbumTitle index.

Query using the index

You can query using the new index either on the command line or using the client library.

On the command line

Execute a SQL statement using the gcloud command-line tool to fetch AlbumId, AlbumTitle, and MarketingBudget from Albums using the AlbumsByAlbumTitle index, for the range of AlbumsTitle in ["Aardvark", "Goo").

gcloud spanner databases execute-sql example-db --instance=test-instance --sql='SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo"'

The result should be:

AlbumId  AlbumTitle               MarketingBudget
2        Go, Go, Go
2        Forever Hold Your Peace  300000

Using the Cloud Spanner client library for C#

The code to programmatically use the index is similar to the query code used earlier.

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + reader.GetFieldValue<string>("MarketingBudget"));
        }
    }
}

Run the sample using the queryDataWithIndex command.

.\Spanner queryDataWithIndex $env:GOOGLE_PROJECT_ID test-instance example-db

You should see output similar to:

AlbumId : 1 AlbumTitle : Go, Go, Go MarketingBudget : 300000
AlbumId : 2 AlbumTitle : Forever Hold your Peace MarketingBudget : 300000

For more details, consult the reference for:

Add an index with a STORING clause

You might have noticed that the read example above did not include reading the MarketingBudget column. This is because Cloud 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.

On the command line

gcloud spanner databases ddl update example-db --instance=test-instance `
  --ddl='CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)'

Adding an index can take a few minutes. After the index is added, you should see:

DDL updating...done.

Using the Cloud Spanner client library for C#

Use CreateDdlCommand() to add an index with a STORING clause:

// Initialize request argument(s).
string connectionString =
    $"Data Source=projects/{projectId}/instances/"
    + $"{instanceId}/databases/{databaseId}";
string createStatement =
    "CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) "
    + "STORING (MarketingBudget)";
// Make the request.
using (var connection = new SpannerConnection(connectionString))
{
    var createCmd = connection.CreateDdlCommand(createStatement);
    await createCmd.ExecuteNonQueryAsync();
}
Console.WriteLine("Added the AlbumsByAlbumTitle2 index.");

Run the sample using the addStoringIndex command.

.\Spanner addStoringIndex $env:GOOGLE_PROJECT_ID test-instance example-db

You should see:

Added the AlbumsByAlbumTitle2 index.

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

Read data using the storing index you created by excuting a query that explicitly specifies the index:

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    var cmd = connection.CreateSelectCommand(
        "SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@ "
        + "{FORCE_INDEX=AlbumsByAlbumTitle2} "
        + $"WHERE AlbumTitle >= @startTitle "
        + $"AND AlbumTitle < @endTitle",
        new SpannerParameterCollection {
            {"startTitle", SpannerDbType.String},
            {"endTitle", SpannerDbType.String} });
    cmd.Parameters["startTitle"].Value = startTitle;
    cmd.Parameters["endTitle"].Value = endTitle;
    using (var reader = await cmd.ExecuteReaderAsync())
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine("AlbumId : "
            + reader.GetFieldValue<string>("AlbumId")
            + " AlbumTitle : "
            + reader.GetFieldValue<string>("AlbumTitle")
            + " MarketingBudget : "
            + reader.GetFieldValue<string>("MarketingBudget"));
        }
    }
}

Run the sample using the queryDataWithStoringIndex command.

.\Spanner queryDataWithStoringIndex $env:GOOGLE_PROJECT_ID test-instance example-db

You should see output similar to:

AlbumId : 2 AlbumTitle : Forever Hold your Peace MarketingBudget : 300000
AlbumId : 1 AlbumTitle : Go, Go, Go MarketingBudget : 300000

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. Use the .NET framework's TransactionScope() along with OpenAsReadOnlyAsync() for executing read-only transactions.

The following shows how to run a query and perform a read in the same read-only transaction:

.NET Standard 2.0

string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Gets a transaction object that captures the database state
// at a specific point in time.
using (TransactionScope scope = new TransactionScope(
    TransactionScopeAsyncFlowOption.Enabled))
{
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        // Open the connection, making the implicitly created
        // transaction read only when it connects to the outer
        // transaction scope.
        await connection.OpenAsReadOnlyAsync()
            .ConfigureAwait(false);
        var cmd = connection.CreateSelectCommand(
            "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
        // Read #1.
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine("SingerId : "
                    + reader.GetFieldValue<string>("SingerId")
                    + " AlbumId : "
                    + reader.GetFieldValue<string>("AlbumId")
                    + " AlbumTitle : "
                    + reader.GetFieldValue<string>("AlbumTitle"));
            }
        }
        // Read #2. Even if changes occur in-between the reads, 
        // the transaction ensures that Read #1 and Read #2 
        // return the same data.
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine("SingerId : "
                    + reader.GetFieldValue<string>("SingerId")
                    + " AlbumId : "
                    + reader.GetFieldValue<string>("AlbumId")
                    + " AlbumTitle : "
                    + reader.GetFieldValue<string>("AlbumTitle"));
            }
        }
    }
    scope.Complete();
    // Yield Task thread back to the current context.
    await Task.Yield();
    Console.WriteLine("Transaction complete.");
}

.NET Standard 1.5

string connectionString =
    $"Data Source=projects/{projectId}/instances/{instanceId}"
    + $"/databases/{databaseId}";

// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
    await connection.OpenAsync();

    // Open a new read only transaction.
    using (var transaction =
        await connection.BeginReadOnlyTransactionAsync())
    {
        var cmd = connection.CreateSelectCommand(
            "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
        cmd.Transaction = transaction;

        // Read #1.
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine("SingerId : "
                    + reader.GetFieldValue<string>("SingerId")
                    + " AlbumId : "
                    + reader.GetFieldValue<string>("AlbumId")
                    + " AlbumTitle : "
                    + reader.GetFieldValue<string>("AlbumTitle"));
            }
        }
        // Read #2. Even if changes occur in-between the reads, 
        // the transaction ensures that Read #1 and Read #2 
        // return the same data.
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine("SingerId : "
                    + reader.GetFieldValue<string>("SingerId")
                    + " AlbumId : "
                    + reader.GetFieldValue<string>("AlbumId")
                    + " AlbumTitle : "
                    + reader.GetFieldValue<string>("AlbumTitle"));
            }
        }
    }
}
// Yield Task thread back to the current context.
await Task.Yield();
Console.WriteLine("Transaction complete.");

Run the sample using the queryDataWithTransaction command.

.\Spanner queryDataWithTransaction $env:GOOGLE_PROJECT_ID test-instance example-db

You should see output similar to:

SingerId : 2 AlbumId : 2 AlbumTitle : Forever Hold your Peace
SingerId : 1 AlbumId : 1 AlbumTitle : Go, Go, Go
SingerId : 2 AlbumId : 1 AlbumTitle : Green
SingerId : 2 AlbumId : 3 AlbumTitle : Terrified
SingerId : 1 AlbumId : 2 AlbumTitle : Total Junk
SingerId : 2 AlbumId : 2 AlbumTitle : Forever Hold your Peace
SingerId : 1 AlbumId : 1 AlbumTitle : Go, Go, Go
SingerId : 2 AlbumId : 1 AlbumTitle : Green
SingerId : 2 AlbumId : 3 AlbumTitle : Terrified
SingerId : 1 AlbumId : 2 AlbumTitle : Total Junk

Cleanup

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

Delete the database

If you delete an instance, all databases within it are automatically deleted. This step shows how to delete a database without deleting an instance (you would still incur charges for the instance).

On the command line

gcloud spanner databases delete example-db --instance=test-instance

Using the Cloud Platform Console

  1. Go to the Cloud Spanner Instances page in the Google Cloud Platform Console.
    Go to the Cloud Spanner Instances page
  2. Click the instance.
  3. Click the database that you want to delete.
  4. In the Database details page, click Delete.
  5. Confirm that you want to delete the database and click Delete.

Delete the instance

Deleting an instance automatically drops all databases created in that instance.

On the command line

gcloud spanner instances delete test-instance

Using the Cloud Platform Console

  1. Go to the Cloud Spanner Instances page in the Google Cloud Platform Console.
    Go to the Cloud Spanner Instances page
  2. Click your instance.
  3. Click Delete.
  4. Confirm that you want to delete the instance and click Delete.

What's next

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

Cloud Spanner Documentation