Getting started with Cloud Spanner in C++

Objectives

Note: This tutorial uses the C++ client library for Linux, with Bazel as the build tool. If you would like to use another build tool or platform, please refer to the instructions in the samples/ directory on GitHub.

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. For information on the cost of using Cloud Spanner, see Pricing.

Before you begin

Complete the steps described in Set up, which cover creating and setting a default Google Cloud 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, make sure that you run gcloud auth application-default login to set up your local development environment with authentication credentials.

Note: If you don't plan to keep the resources that you create in this tutorial, consider creating a new Google Cloud project instead of selecting an existing project. After you finish the tutorial, you can delete the project, removing all resources associated with the project.

Prepare your local C++ environment

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

   git clone https://github.com/googleapis/google-cloud-cpp $HOME/google-cloud-cpp
   

2. Install Bazel for Linux using these instructions.

3. Change to the directory that contains the Cloud Spanner sample code:

   cd $HOME/google-cloud-cpp
   

4. Build the samples with this command:

   bazel build //google/cloud/spanner/samples:samples
   

5. Set up authentication and authorization for the google-cloud-cpp project.

   gcloud auth application-default login
   

6. Create an environment variable called GCLOUD_PROJECT. Replace [MY_PROJECT_ID] with your Google Cloud project ID. You can find this ID in your project's dashboard.

   export GCLOUD_PROJECT=[MY_PROJECT_ID]
   

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 an instance configuration, which determines where your data is stored, and also the number of nodes to use, which determines the amount of serving and storage resources in your instance.

Execute the following command to create a Cloud Spanner instance in the region us-central1 with 1 node:

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

Note that this creates an instance with the following characteristics:

• Instance ID test-instance
• Display name Test Instance
• Instance configuration regional-us-central1 (Regional configurations store data in one region, while multi-region configurations distribute data across multiple regions. Learn more in Instances.)
• Node count of 1 (node_count corresponds to the amount of serving and storage resources available to databases in the instance. Learn more in Node count.)

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 google/cloud/spanner/samples/samples.cc 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.

Create a database

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

bazel run //google/cloud/spanner/samples:samples -- \
    create-database $GCLOUD_PROJECT test-instance example-db

Note: Some Bazel syntax, such as --, looks similar to bash syntax but is not. You can learn more about Bazel syntax here.

You should see:

Created database [projects/${GCLOUD_PROJECT}/instances/test-instance/databases/example-db]

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

View on GitHub Feedback

void CreateDatabase(google::cloud::spanner_admin::DatabaseAdminClient client,
                    std::string const& project_id,
                    std::string const& instance_id,
                    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements(R"""(
      CREATE TABLE Singers (
          SingerId   INT64 NOT NULL,
          FirstName  STRING(1024),
          LastName   STRING(1024),
          SingerInfo BYTES(MAX)
      ) PRIMARY KEY (SingerId))""");
  request.add_extra_statements(R"""(
      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)""");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::runtime_error(db.status().message());
  std::cout << "Database " << db->name() << " created.\n";
}

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 Client:

View on GitHub Feedback

auto database = spanner::Database(project_id, instance_id, database_id);
auto connection = spanner::MakeConnection(database);
auto client = spanner::Client(connection);

A Client allows you to read, write, query, and execute transactions on a Cloud Spanner database. Typically you create a Client when your application starts up, then you re-use that Client to read, write, and execute transactions. Each client uses resources in Cloud Spanner. The destructor of Client will automatically clean up the Client resources, including network connections.

Read more about Client in the Google Cloud Spanner C++ Reference.

Write data with DML

You can insert data using Data Manipulation Language (DML) in a read-write transaction.

You use the Client::ExecuteDml() function to execute a DML statement.

View on GitHub Feedback

void DmlGettingStartedInsert(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;

  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES"
                " (12, 'Melissa', 'Garcia'),"
                " (13, 'Russell', 'Morales'),"
                " (14, 'Jacqueline', 'Long'),"
                " (15, 'Dylan', 'Shaw')"));
        if (!insert) return insert.status();
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Insert was successful [spanner_dml_getting_started_insert]\n";
}

Run the sample using the getting-started-insert argument.

bazel run //google/cloud/spanner/samples:samples -- \
    getting-started-insert $GCLOUD_PROJECT test-instance example-db

You should see:

Insert was successful [spanner_dml_getting_started_insert]

Note: There are limits to commit size. See CRUD limit for more information.

Write data with mutations

You can also insert data using mutations.

You write data using a Client object. The Client::Commit() function creates and commits a transaction for writes that execute atomically at a single logical point in time across columns, rows, and tables in a database.

This code shows how to write the data using mutations:

View on GitHub Feedback

void InsertData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto insert_singers = spanner::InsertMutationBuilder(
                            "Singers", {"SingerId", "FirstName", "LastName"})
                            .EmplaceRow(1, "Marc", "Richards")
                            .EmplaceRow(2, "Catalina", "Smith")
                            .EmplaceRow(3, "Alice", "Trentor")
                            .EmplaceRow(4, "Lea", "Martin")
                            .EmplaceRow(5, "David", "Lomond")
                            .Build();

  auto insert_albums = spanner::InsertMutationBuilder(
                           "Albums", {"SingerId", "AlbumId", "AlbumTitle"})
                           .EmplaceRow(1, 1, "Total Junk")
                           .EmplaceRow(1, 2, "Go, Go, Go")
                           .EmplaceRow(2, 1, "Green")
                           .EmplaceRow(2, 2, "Forever Hold Your Peace")
                           .EmplaceRow(2, 3, "Terrified")
                           .Build();

  auto commit_result =
      client.Commit(spanner::Mutations{insert_singers, insert_albums});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Insert was successful [spanner_insert_data]\n";
}

Run the sample using the insert-data argument.

bazel run //google/cloud/spanner/samples:samples -- \
    insert-data $GCLOUD_PROJECT test-instance example-db

You should see:

Insert was successful [spanner_insert_data]

Note: There are limits to commit size. See CRUD limit for more information.

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'

Note: See SQL syntax for the Cloud Spanner SQL reference.

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

You use the Client::ExecuteQuery() function to run the SQL query. Here's how to issue the query and access the data:

View on GitHub Feedback

void QueryData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select("SELECT SingerId, LastName FROM Singers");
  using RowType = std::tuple<std::int64_t, std::string>;
  auto rows = client.ExecuteQuery(std::move(select));
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "LastName: " << std::get<1>(*row) << "\n";
  }

  std::cout << "Query completed for [spanner_query_data]\n";
}

Run the sample using the query_data argument.

bazel run //google/cloud/spanner/samples:samples -- \
    query-data $GCLOUD_PROJECT test-instance example-db

You should see the following result:

SingerId: 1     LastName: Richards
SingerId: 2     LastName: Smith
SingerId: 3     LastName: Trentor
SingerId: 4     LastName: Martin
SingerId: 5     LastName: Lomond
SingerId: 12    LastName: Garcia
SingerId: 13    LastName: Morales
SingerId: 14    LastName: Long
SingerId: 15    LastName: Shaw

Query using a SQL parameter

You can include custom values in SQL statements using supported SQL types.

Here is an example of using @lastName as a parameter in the WHERE clause to query records containing a specific value for LastName.

View on GitHub Feedback

void QueryWithParameter(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(
      "SELECT SingerId, FirstName, LastName FROM Singers"
      " WHERE LastName = @last_name",
      {{"last_name", spanner::Value("Garcia")}});
  using RowType = std::tuple<std::int64_t, std::string, std::string>;
  auto rows = client.ExecuteQuery(std::move(select));
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "FirstName: " << std::get<1>(*row) << "\t";
    std::cout << "LastName: " << std::get<2>(*row) << "\n";
  }

  std::cout << "Query completed for [spanner_query_with_parameter]\n";
}

Run the sample using the query-with-parameter command.

bazel run //google/cloud/spanner/samples:samples -- \
    query-with-parameter $GCLOUD_PROJECT test-instance example-db

You should see the following result:

SingerId: 12    FirstName: Melissa      LastName: Garcia

Read data using the read API

In addition to Cloud Spanner's SQL interface, Cloud Spanner also supports a read interface.

You use the Client::Read() function to read rows from the database. Use a KeySet object to define a collection of keys and key ranges to read.

Here's how to read the data:

View on GitHub Feedback

void ReadData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  auto rows = client.Read("Albums", google::cloud::spanner::KeySet::All(),
                          {"SingerId", "AlbumId", "AlbumTitle"});
  using RowType = std::tuple<std::int64_t, std::int64_t, std::string>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumId: " << std::get<1>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<2>(*row) << "\n";
  }

  std::cout << "Read completed for [spanner_read_data]\n";
}

Run the sample using the read-data argument.

bazel run //google/cloud/spanner/samples:samples -- \
    read-data $GCLOUD_PROJECT test-instance example-db

You should see output similar to:

SingerId: 1, AlbumId: 1, AlbumTitle: Total Junk
SingerId: 1, AlbumId: 2, AlbumTitle: Go, Go, Go
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 Schema updates.

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:

Schema updating...done.

Using the Cloud Spanner client library for C++

Use the DatabaseAdminClient::UpdateDatabase() function to modify the schema.

View on GitHub Feedback

void AddColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
               std::string const& project_id, std::string const& instance_id,
               std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client
          .UpdateDatabaseDdl(
              database.FullName(),
              {"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"})
          .get();
  if (!metadata) throw std::runtime_error(metadata.status().message());
  std::cout << "Added MarketingBudget column\n";
}

Run the sample using the add-column command.

bazel run //google/cloud/spanner/samples:samples -- \
    add-column $GCLOUD_PROJECT test-instance example-db

You should see:

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

View on GitHub Feedback

void UpdateData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(spanner::Mutations{
      spanner::UpdateMutationBuilder("Albums",
                                     {"SingerId", "AlbumId", "MarketingBudget"})
          .EmplaceRow(1, 1, 100000)
          .EmplaceRow(2, 2, 500000)
          .Build()});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Update was successful [spanner_update_data]\n";
}

Run the sample using the update-data argument.

bazel run //google/cloud/spanner/samples:samples -- \
    update-data $GCLOUD_PROJECT test-instance example-db

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

Here's the code to execute the query:

View on GitHub Feedback

void QueryNewColumn(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::SqlStatement select(
      "SELECT SingerId, AlbumId, MarketingBudget FROM Albums");
  using RowType =
      std::tuple<std::int64_t, std::int64_t, absl::optional<std::int64_t>>;

  auto rows = client.ExecuteQuery(std::move(select));
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumId: " << std::get<1>(*row) << "\t";
    auto marketing_budget = std::get<2>(*row);
    if (marketing_budget) {
      std::cout << "MarketingBudget: " << *marketing_budget << "\n";
    } else {
      std::cout << "MarketingBudget: NULL\n";
    }
  }
  std::cout << "Read completed for [spanner_read_data_with_new_column]\n";
}

To execute this query, run the sample using the query-new-column argument.

bazel run //google/cloud/spanner/samples:samples -- \
    query-new-column $GCLOUD_PROJECT test-instance example-db

You should see:

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

Update data

You can update data using DML in a read-write transaction.

You use the Client::ExecuteDml() function to execute a DML statement.

View on GitHub Feedback

void DmlGettingStartedUpdate(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;

  // A helper to read the budget for the given album and singer.
  auto get_budget = [&](spanner::Transaction txn, std::int64_t album_id,
                        std::int64_t singer_id) -> StatusOr<std::int64_t> {
    auto key = spanner::KeySet().AddKey(spanner::MakeKey(album_id, singer_id));
    auto rows = client.Read(std::move(txn), "Albums", key, {"MarketingBudget"});
    using RowType = std::tuple<absl::optional<std::int64_t>>;
    auto row = spanner::GetSingularRow(spanner::StreamOf<RowType>(rows));
    if (!row) return row.status();
    auto const budget = std::get<0>(*row);
    return budget ? *budget : 0;
  };

  // A helper to update the budget for the given album and singer.
  auto update_budget = [&](spanner::Transaction txn, std::int64_t album_id,
                           std::int64_t singer_id, std::int64_t budget) {
    auto sql = spanner::SqlStatement(
        "UPDATE Albums SET MarketingBudget = @AlbumBudget "
        "WHERE SingerId = @SingerId AND AlbumId = @AlbumId",
        {{"AlbumBudget", spanner::Value(budget)},
         {"AlbumId", spanner::Value(album_id)},
         {"SingerId", spanner::Value(singer_id)}});
    return client.ExecuteDml(std::move(txn), std::move(sql));
  };

  auto const transfer_amount = 20000;
  auto commit_result = client.Commit(
      [&](spanner::Transaction const& txn) -> StatusOr<spanner::Mutations> {
        auto budget1 = get_budget(txn, 1, 1);
        if (!budget1) return budget1.status();
        if (*budget1 < transfer_amount) {
          return google::cloud::Status(
              google::cloud::StatusCode::kUnknown,
              "cannot transfer " + std::to_string(transfer_amount) +
                  " from budget of " + std::to_string(*budget1));
        }
        auto budget2 = get_budget(txn, 2, 2);
        if (!budget2) return budget2.status();
        auto update = update_budget(txn, 1, 1, *budget1 - transfer_amount);
        if (!update) return update.status();
        update = update_budget(txn, 2, 2, *budget2 + transfer_amount);
        if (!update) return update.status();
        return spanner::Mutations{};
      });
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Update was successful [spanner_dml_getting_started_update]\n";
}

Run the sample using the getting-started-update argument.

bazel run //google/cloud/spanner/samples:samples -- \
    getting-started-update $GCLOUD_PROJECT test-instance example-db

You should see:

Update was successful [spanner_dml_getting_started_update]

Note: You can also update data using mutations.

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 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 the indexed table during this process. For more details, see index backfilling.

After you add a secondary index, Cloud 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 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:

Schema updating...done.

Using the Cloud Spanner client library for C++

You use the DatabaseAdminClient::UpdateDatabase() function to add an index:

View on GitHub Feedback

void AddIndex(google::cloud::spanner_admin::DatabaseAdminClient client,
              std::string const& project_id, std::string const& instance_id,
              std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client
          .UpdateDatabaseDdl(
              database.FullName(),
              {"CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"})
          .get();
  if (!metadata) throw std::runtime_error(metadata.status().message());
  std::cout << "`AlbumsByAlbumTitle` Index successfully added, new DDL:\n"
            << metadata->DebugString();
}

Run the sample using the add-index argument.

bazel run //google/cloud/spanner/samples:samples -- \
    add-index $GCLOUD_PROJECT test-instance example-db

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

`AlbumsByAlbumTitle` Index successfully added, new DDL:
database: "projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db"
statements: "CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"
commit_timestamps {
  seconds: 1581011550
  nanos: 531102000
}

Read using the index

For SQL queries, Cloud Spanner automatically uses an appropriate index. In the read interface, you must specify the index in your request.

To use the index in the read interface, use the Client::Read() function, which reads zero or more rows from a database using an index.

The following code fetches all AlbumId, and AlbumTitle columns from the AlbumsByAlbumTitle index.

View on GitHub Feedback

void ReadDataWithIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::ReadOptions read_options;
  read_options.index_name = "AlbumsByAlbumTitle";
  auto rows = client.Read("Albums", google::cloud::spanner::KeySet::All(),
                          {"AlbumId", "AlbumTitle"}, read_options);
  using RowType = std::tuple<std::int64_t, std::string>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\n";
  }
  std::cout << "Read completed for [spanner_read_data_with_index]\n";
}

Run the sample using the read-data-with-index argument.

bazel run //google/cloud/spanner/samples:samples -- \
    read-data-with-index $GCLOUD_PROJECT test-instance example-db

You should see:

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

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:

Schema updating...done.

Using the Cloud Spanner client library for C++

You use the DatabaseAdminClient::UpdateDatabase() function to add an index with a STORING clause:

View on GitHub Feedback

void AddStoringIndex(google::cloud::spanner_admin::DatabaseAdminClient client,
                     std::string const& project_id,
                     std::string const& instance_id,
                     std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata = client
                      .UpdateDatabaseDdl(database.FullName(), {R"""(
                        CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
                            STORING (MarketingBudget))"""})
                      .get();
  if (!metadata) throw std::runtime_error(metadata.status().message());
  std::cout << "`AlbumsByAlbumTitle2` Index successfully added, new DDL:\n"
            << metadata->DebugString();
}

Run the sample using the add-storing-index argument.

bazel run //google/cloud/spanner/samples:samples -- \
    add-storing-index $GCLOUD_PROJECT test-instance example-db

You should see output similar to this:

`AlbumsByAlbumTitle2` Index successfully added, new DDL:
database: "projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db"
statements: "CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)"
commit_timestamps {
  seconds: 1581012328
  nanos: 416682000
}

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 executing a query that explicitly specifies the index:

View on GitHub Feedback

void ReadDataWithStoringIndex(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  spanner::ReadOptions read_options;
  read_options.index_name = "AlbumsByAlbumTitle2";
  auto rows =
      client.Read("Albums", google::cloud::spanner::KeySet::All(),
                  {"AlbumId", "AlbumTitle", "MarketingBudget"}, read_options);
  using RowType =
      std::tuple<std::int64_t, std::string, absl::optional<std::int64_t>>;
  for (auto const& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "AlbumId: " << std::get<0>(*row) << "\t";
    std::cout << "AlbumTitle: " << std::get<1>(*row) << "\t";
    auto marketing_budget = std::get<2>(*row);
    if (marketing_budget) {
      std::cout << "MarketingBudget: " << *marketing_budget << "\n";
    } else {
      std::cout << "MarketingBudget: NULL\n";
    }
  }
  std::cout << "Read completed for [spanner_read_data_with_storing_index]\n";
}

Run the sample using the read-data-with-storing-index argument.

bazel run //google/cloud/spanner/samples:samples -- \
    read-data-with-storing-index $GCLOUD_PROJECT test-instance example-db

You should see output similar to:

AlbumId: 2  AlbumTitle: Forever Hold Your Peace MarketingBudget: 520000
AlbumId: 2  AlbumTitle: Go, Go, Go  MarketingBudget: NULL
AlbumId: 1  AlbumTitle: Green   MarketingBudget: NULL
AlbumId: 3  AlbumTitle: Terrified   MarketingBudget: NULL
AlbumId: 1  AlbumTitle: Total Junk  MarketingBudget: 80000

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. The Transaction type is used to represent all kinds of transactions. Use the MakeReadOnlyTransaction() factory function to create a read-only transaction.

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

View on GitHub Feedback

void ReadOnlyTransaction(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto read_only = spanner::MakeReadOnlyTransaction();

  spanner::SqlStatement select(
      "SELECT SingerId, AlbumId, AlbumTitle FROM Albums");
  using RowType = std::tuple<std::int64_t, std::int64_t, std::string>;

  // Read#1.
  auto rows1 = client.ExecuteQuery(read_only, select);
  std::cout << "Read 1 results\n";
  for (auto const& row : spanner::StreamOf<RowType>(rows1)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row)
              << " AlbumId: " << std::get<1>(*row)
              << " AlbumTitle: " << std::get<2>(*row) << "\n";
  }
  // Read#2. Even if changes occur in-between the reads the transaction ensures
  // that Read #1 and Read #2 return the same data.
  auto rows2 = client.ExecuteQuery(read_only, select);
  std::cout << "Read 2 results\n";
  for (auto const& row : spanner::StreamOf<RowType>(rows2)) {
    if (!row) throw std::runtime_error(row.status().message());
    std::cout << "SingerId: " << std::get<0>(*row)
              << " AlbumId: " << std::get<1>(*row)
              << " AlbumTitle: " << std::get<2>(*row) << "\n";
  }
}

Run the sample using the read-only-transaction argument.

bazel run //google/cloud/spanner/samples:samples -- \
    read-only-transaction $GCLOUD_PROJECT test-instance example-db

You should see output similar to:

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

Cleanup

To avoid incurring additional charges to your Cloud Billing 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 Console

1. Go to the Cloud Spanner Instances page in the Google Cloud Console.

   Go to the 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 Console

1. Go to the Cloud Spanner Instances page in the Google Cloud Console.

   Go to the Instances page

2. Click your instance.

3. Click Delete.

4. Confirm that you want to delete the instance and click Delete.

What's next

• Access Cloud Spanner in a virtual machine instance: create a virtual machine instance with access to your Cloud Spanner database.

• Learn about authorization and authentication credentials in Getting started with authentication.

• Learn more Cloud Spanner concepts.