Prepare your local C++ environment

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

   gitclonehttps://github.com/googleapis/google-cloud-cpp $HOME/google-cloud-cpp

2. Install Bazel for Linux usingthese instructions.

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

   cd$HOME/google-cloud-cpp

4. Build the samples with this command:

   bazelbuild//google/cloud/spanner/samples:samples

5. Set up authentication and authorization for thegoogle-cloud-cppproject.

   gcloudauthapplication-defaultlogin

6. Create an environment variable calledPROJECT_ID.Replace[MY_PROJECT_ID] with your Google Cloud project ID.You can find this ID in your project'sWelcome page.

   exportPROJECT_ID=[MY_PROJECT_ID]

Create an instance

When you first use Spanner, you must create an instance, which is anallocation of resources that are used by Spanner databases. When youcreate an instance, you choose aninstance configuration, which determineswhere your data is stored, and also the number of nodes to use, which determinesthe amount of serving and storage resources in your instance.

SeeCreate an instanceto learn how to create a Spanner instance using any of thefollowing methods. You can name your instancetest-instance to use it withother topics in this document that reference an instance namedtest-instance.

• The Google Cloud CLI
• The Google Cloud console
• A client library (C++, C#, Go, Java, Node.js, PHP, Python, or Ruby)

Look through sample files

The samples repository contains a sample that shows how to use Spannerwith C++.

Take a look through thegoogle/cloud/spanner/samples/samples.cc file, which shows how to createa database and modify a database schema. The data uses the example schema shownin theSchema and data model page.

Create a database

bazelrun//google/cloud/spanner/samples:samples -- \      create-databasePROJECT_ID test-instance example-db

bazelrun//google/cloud/spanner/samples:postgresql_samples -- \      create-databasePROJECT_ID test-instance example-dbbazelrun//google/cloud/spanner/samples:postgresql_samples -- \      interleaved-tablePROJECT_ID test-instance example-db

You should see:

Createddatabase[projects/${PROJECT_ID}/instances/test-instance/databases/example-db]

voidCreateDatabase(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);google::spanner::admin::database::v1::CreateDatabaseRequestrequest;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),          FullName   STRING(2049)              AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED      ) 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)""");autodb=client.CreateDatabase(request).get();if(!db)throwstd::move(db).status();std::cout <<"Database " <<db->name() <<" created.\n";}

voidCreateDatabase(google::cloud::spanner_admin::DatabaseAdminClientclient,google::cloud::spanner::Databaseconst&database){google::spanner::admin::database::v1::CreateDatabaseRequestrequest;request.set_parent(database.instance().FullName());request.set_create_statement("CREATE DATABASE\""+database.database_id()+"\"");request.set_database_dialect(google::spanner::admin::database::v1::DatabaseDialect::POSTGRESQL);autodb=client.CreateDatabase(request).get();if(!db)throwstd::move(db).status();std::cout <<"Database " <<db->name() <<" created.\n";}

voidInterleavedTable(google::cloud::spanner_admin::DatabaseAdminClientclient,google::cloud::spanner::Databaseconst&database){// The Spanner PostgreSQL dialect extends the PostgreSQL dialect with// certain Spanner specific features, such as interleaved tables. See// https://cloud.google.com/spanner/docs/postgresql/data-definition-language#create_table// for the full CREATE TABLE syntax.std::vector<std::string>statements={R"""(        CREATE TABLE Singers (            SingerId        BIGINT NOT NULL,            FirstName       CHARACTER VARYING(1024) NOT NULL,            LastName        CHARACTER VARYING(1024) NOT NULL,            PRIMARY KEY(SingerId)        ))""",R"""(        CREATE TABLE Albums (            SingerId        BIGINT NOT NULL,            AlbumId         BIGINT NOT NULL,            AlbumTitle      CHARACTER VARYING NOT NULL,            MarketingBudget BIGINT,            PRIMARY KEY(SingerId, AlbumId)        ) INTERLEAVE IN PARENT Singers ON DELETE CASCADE)""",};autometadata=client.UpdateDatabaseDdl(database.FullName(),statements).get();google::cloud::spanner_testing::LogUpdateDatabaseDdl(//! TODO(#4758)client,database,metadata.status());//! TODO(#4758)if(!metadata)throwstd::move(metadata).status();std::cout <<"Tables created.\nNew DDL:\n" <<metadata->DebugString();}

The next step is to write data to your database.

Create a database client

Before you can do reads or writes, you must create aClient:

autodatabase=spanner::Database(project_id,instance_id,database_id);autoconnection=spanner::MakeConnection(database);autoclient=spanner::Client(connection);

AClient lets you read, write, query, and execute transactions on aSpanner database. Typically you create aClient when your application starts up, then you re-use thatClient toread, write, and execute transactions. Each client uses resources inSpanner. The destructor ofClient automatically cleans up theClient resources, including network connections.

Read more aboutClient in theGoogle Cloud Spanner C++ Reference.

Write data with DML

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

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

voidDmlGettingStartedInsert(google::cloud::spanner::Clientclient){using::google::cloud::StatusOr;namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit([&client](spanner::Transactiontxn)->StatusOr<spanner::Mutations>{autoinsert=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)returnstd::move(insert).status();returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Insert was successful [spanner_dml_getting_started_insert]\n";}

Run the sample using thegetting-started-insert argument.

bazelrun//google/cloud/spanner/samples:samples -- \    getting-started-insertPROJECT_ID test-instance example-db

You should see:

Insertwassuccessful[spanner_dml_getting_started_insert]

Write data with mutations

You can also insert data usingmutations.

You write data using aClient object. TheClient::Commit()function creates and commits a transaction for writes that execute atomicallyat a single logical point in time across columns, rows, and tables in adatabase.

This code shows how to write the data using mutations:

voidInsertData(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoinsert_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();autoinsert_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();autocommit_result=client.Commit(spanner::Mutations{insert_singers,insert_albums});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Insert was successful [spanner_insert_data]\n";}

Run the sample using theinsert-data argument.

bazelrun//google/cloud/spanner/samples:samples -- \    insert-dataPROJECT_ID test-instance example-db

You should see:

Insertwassuccessful[spanner_insert_data]

Query data using SQL

Spanner supports a SQL interface for reading data, which you canaccess on the command line using the Google Cloud CLI orprogrammatically usingthe Spanner client library for C++.

On the command line

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

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance\--sql='SELECTSingerId,AlbumId,AlbumTitleFROMAlbums'

The result shows:

SingerIdAlbumIdAlbumTitle11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Use the Spanner client library for C++

In addition to executing a SQL statement on the command line, you can issue thesame SQL statement programmatically using the Spanner client library forC++.

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

voidQueryData(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;spanner::SqlStatementselect("SELECT SingerId, LastName FROM Singers");usingRowType=std::tuple<std::int64_t,std::string>;autorows=client.ExecuteQuery(std::move(select));for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();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 thequery_data argument.

bazelrun//google/cloud/spanner/samples:samples -- \    query-dataPROJECT_ID test-instance example-db

You should see the following result:

SingerId:1LastName:RichardsSingerId:2LastName:SmithSingerId:3LastName:TrentorSingerId:4LastName:MartinSingerId:5LastName:LomondSingerId:12LastName:GarciaSingerId:13LastName:MoralesSingerId:14LastName:LongSingerId:15LastName:Shaw

Query using a SQL parameter

If your application has a frequently executed query, you can improve itsperformance by parameterizing it. The resulting parametric query can be cachedand reused, which reduces compilation costs. For more information, seeUse query parameters to speed up frequently executed queries.

Here is an example of using a parameter in theWHERE clause to query recordscontaining a specific value forLastName.

voidQueryWithParameter(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;spanner::SqlStatementselect("SELECT SingerId, FirstName, LastName FROM Singers"" WHERE LastName = @last_name",{{"last_name", spanner::Value("Garcia")}});usingRowType=std::tuple<std::int64_t,std::string,std::string>;autorows=client.ExecuteQuery(std::move(select));for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();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";}

voidQueryWithParameter(google::cloud::spanner::Clientclient){std::cout <<"Listing all singers with a last name that starts with 'S'\n";autosql=google::cloud::spanner::SqlStatement("SELECT SingerId, FirstName, LastName FROM Singers""  WHERE LastName LIKE $1",{{"p1", google::cloud::spanner::Value("S%")}});usingRowType=std::tuple<std::int64_t,std::string,std::string>;autorows=client.ExecuteQuery(std::move(sql));for(auto&row:google::cloud::spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();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.\n";}

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

bazelrun//google/cloud/spanner/samples:samples -- \    query-with-parameterPROJECT_ID test-instance example-db

You should see the following result:

SingerId:12FirstName:MelissaLastName:Garcia

Read data using the read API

In addition to Spanner's SQL interface, Spanner also supports aread interface.

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

Here's how to read the data:

voidReadData(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autorows=client.Read("Albums",google::cloud::spanner::KeySet::All(),{"SingerId","AlbumId","AlbumTitle"});usingRowType=std::tuple<std::int64_t,std::int64_t,std::string>;for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();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 theread-data argument.

bazelrun//google/cloud/spanner/samples:samples -- \    read-dataPROJECT_ID test-instance example-db

You should see output similar to:

SingerId:1,AlbumId:1,AlbumTitle:TotalJunkSingerId:1,AlbumId:2,AlbumTitle:Go,Go,GoSingerId:2,AlbumId:1,AlbumTitle:GreenSingerId:2,AlbumId:2,AlbumTitle:ForeverHoldYourPeaceSingerId:2,AlbumId:3,AlbumTitle:Terrified

Update the database schema

Assume you need to add a new column calledMarketingBudget to theAlbumstable. Adding a new column to an existing table requires an update to yourdatabase schema. Spanner supports schema updates to a database while thedatabase continues to serve traffic. Schema updates don't require taking thedatabase offline and they don't lock entire tables or columns; you can continuewriting data to the database during the schema update. Read more about supportedschema updates and schema change performance inMake schema updates.

Add a column

You can add a column on the command line using the Google Cloud CLI orprogrammatically usingthe Spanner client library for C++.

On the command line

Use the followingALTER TABLE command toadd the new column to the table:

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='ALTERTABLEAlbumsADDCOLUMNMarketingBudgetINT64'

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='ALTERTABLEAlbumsADDCOLUMNMarketingBudgetBIGINT'

You should see:

Schemaupdating...done.

Use the Spanner client library for C++

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

voidAddColumn(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);autometadata=client.UpdateDatabaseDdl(database.FullName(),{"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"}).get();if(!metadata)throwstd::move(metadata).status();std::cout <<"Added MarketingBudget column\n";}

voidAddColumn(google::cloud::spanner_admin::DatabaseAdminClientclient,google::cloud::spanner::Databaseconst&database){std::vector<std::string>statements={R"""(        ALTER TABLE Albums            ADD COLUMN MarketingBudget BIGINT)""",};autometadata=client.UpdateDatabaseDdl(database.FullName(),statements).get();google::cloud::spanner_testing::LogUpdateDatabaseDdl(//! TODO(#4758)client,database,metadata.status());//! TODO(#4758)if(!metadata)throwstd::move(metadata).status();std::cout <<"Column added.\nNew DDL:\n" <<metadata->DebugString();}

Run the sample using theadd-column command.

bazelrun//google/cloud/spanner/samples:samples -- \    add-columnPROJECT_ID test-instance example-db

You should see:

AddedMarketingBudgetcolumn

Write data to the new column

The following code writes data to the new column. It setsMarketingBudget to100000 for the row keyed byAlbums(1, 1) and to500000 for the row keyedbyAlbums(2, 2).

voidUpdateData(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit(spanner::Mutations{spanner::UpdateMutationBuilder("Albums",{"SingerId","AlbumId","MarketingBudget"}).EmplaceRow(1,1,100000).EmplaceRow(2,2,500000).Build()});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Update was successful [spanner_update_data]\n";}

Run the sample using theupdate-data argument.

bazelrun//google/cloud/spanner/samples:samples -- \    update-dataPROJECT_ID test-instance example-db

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

Here's the code to execute the query:

voidQueryNewColumn(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;spanner::SqlStatementselect("SELECT SingerId, AlbumId, MarketingBudget FROM Albums");usingRowType=std::tuple<std::int64_t,std::int64_t,absl::optional<std::int64_t>>;autorows=client.ExecuteQuery(std::move(select));for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();std::cout <<"SingerId: " <<std::get<0>(*row) <<"\t";std::cout <<"AlbumId: " <<std::get<1>(*row) <<"\t";automarketing_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 thequery-new-column argument.

bazelrun//google/cloud/spanner/samples:samples -- \    query-new-columnPROJECT_ID test-instance example-db

You should see:

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

Update data

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

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

voidDmlGettingStartedUpdate(google::cloud::spanner::Clientclient){using::google::cloud::StatusOr;namespacespanner=::google::cloud::spanner;// A helper to read the budget for the given album and singer.autoget_budget=[&](spanner::Transactiontxn,std::int64_talbum_id,std::int64_tsinger_id)->StatusOr<std::int64_t>{autokey=spanner::KeySet().AddKey(spanner::MakeKey(album_id,singer_id));autorows=client.Read(std::move(txn),"Albums",key,{"MarketingBudget"});usingRowType=std::tuple<absl::optional<std::int64_t>>;autorow=spanner::GetSingularRow(spanner::StreamOf<RowType>(rows));if(!row)returnstd::move(row).status();autoconstbudget=std::get<0>(*row);returnbudget?*budget:0;};// A helper to update the budget for the given album and singer.autoupdate_budget=[&](spanner::Transactiontxn,std::int64_talbum_id,std::int64_tsinger_id,std::int64_tbudget){autosql=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)}});returnclient.ExecuteDml(std::move(txn),std::move(sql));};autoconsttransfer_amount=20000;autocommit_result=client.Commit([&](spanner::Transactionconst&txn)->StatusOr<spanner::Mutations>{autobudget1=get_budget(txn,1,1);if(!budget1)returnstd::move(budget1).status();if(*budget1 <transfer_amount){returngoogle::cloud::Status(google::cloud::StatusCode::kUnknown,"cannot transfer "+std::to_string(transfer_amount)+" from budget of "+std::to_string(*budget1));}autobudget2=get_budget(txn,2,2);if(!budget2)returnstd::move(budget2).status();autoupdate=update_budget(txn,1,1,*budget1-transfer_amount);if(!update)returnstd::move(update).status();update=update_budget(txn,2,2,*budget2+transfer_amount);if(!update)returnstd::move(update).status();returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Update was successful [spanner_dml_getting_started_update]\n";}

voidDmlGettingStartedUpdate(google::cloud::spanner::Clientclient){// A helper to read the budget for the given album and singer.autoget_budget=[&](google::cloud::spanner::Transactiontxn,std::int64_talbum_id,std::int64_tsinger_id)->google::cloud::StatusOr<std::int64_t>{autokey=google::cloud::spanner::KeySet().AddKey(google::cloud::spanner::MakeKey(album_id,singer_id));autorows=client.Read(std::move(txn),"Albums",key,{"MarketingBudget"});usingRowType=std::tuple<absl::optional<std::int64_t>>;autorow=google::cloud::spanner::GetSingularRow(google::cloud::spanner::StreamOf<RowType>(rows));if(!row)returnstd::move(row).status();autoconstbudget=std::get<0>(*row);returnbudget?*budget:0;};// A helper to update the budget for the given album and singer.autoupdate_budget=[&](google::cloud::spanner::Transactiontxn,std::int64_tsinger_id,std::int64_talbum_id,std::int64_tbudget){autosql=google::cloud::spanner::SqlStatement("UPDATE Albums SET MarketingBudget = $1""  WHERE SingerId = $2 AND AlbumId = $3",{{"p1", google::cloud::spanner::Value(budget)},         {"p2", google::cloud::spanner::Value(singer_id)},         {"p3", google::cloud::spanner::Value(album_id)}});returnclient.ExecuteDml(std::move(txn),std::move(sql));};autoconsttransfer_amount=20000;autocommit=client.Commit([&](google::cloud::spanner::Transactionconst&txn)->google::cloud::StatusOr<google::cloud::spanner::Mutations>{autobudget1=get_budget(txn,1,1);if(!budget1)returnstd::move(budget1).status();if(*budget1 <transfer_amount){returngoogle::cloud::Status(google::cloud::StatusCode::kUnknown,"cannot transfer "+std::to_string(transfer_amount)+" from budget of "+std::to_string(*budget1));}autobudget2=get_budget(txn,2,2);if(!budget2)returnstd::move(budget2).status();autoupdate=update_budget(txn,1,1,*budget1-transfer_amount);if(!update)returnstd::move(update).status();update=update_budget(txn,2,2,*budget2+transfer_amount);if(!update)returnstd::move(update).status();returngoogle::cloud::spanner::Mutations{};});if(!commit)throwstd::move(commit).status();std::cout <<"Update was successful.\n";}

Run the sample using thegetting-started-update argument.

bazelrun//google/cloud/spanner/samples:samples -- \    getting-started-updatePROJECT_ID test-instance example-db

You should see:

Updatewassuccessful[spanner_dml_getting_started_update]

Use a secondary index

Suppose you wanted to fetch all rows ofAlbums that haveAlbumTitle valuesin a certain range. You could read all values from theAlbumTitle column usinga SQL statement or a read call, and then discard the rows that don't meet thecriteria, but doing this full table scan is expensive, especially for tableswith a lot of rows. Instead you can speed up the retrieval of rows whensearching by non-primary key columns by creating asecondary index on the table.

Adding a secondary index to an existing table requires a schema update. Likeother 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 the indexedtable during this process. For more details, seeAdd a secondary index.

After you add a secondary index, Spanner automatically uses it forSQL queries that are likely to run faster with the index. If you use the readinterface, 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 CLI orprogrammatically using the Spanner client library for C++.

On the command line

Use the followingCREATE INDEXcommand to add an index to the database:

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

Schemaupdating...done.

Using the Spanner client library for C++

You use theDatabaseAdminClient::UpdateDatabase() function to add an index:

voidAddIndex(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);autometadata=client.UpdateDatabaseDdl(database.FullName(),{"CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"}).get();if(!metadata)throwstd::move(metadata).status();std::cout <<"`AlbumsByAlbumTitle` Index successfully added, new DDL:\n"            <<metadata->DebugString();}

Run the sample using theadd-index argument.

bazelrun//google/cloud/spanner/samples:samples -- \    add-indexPROJECT_ID test-instance example-db

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

`AlbumsByAlbumTitle`Indexsuccessfullyadded,newDDL:database:"projects/PROJECT_ID/instances/test-instance/databases/example-db"statements:"CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"commit_timestamps{seconds:1581011550nanos:531102000}

Read using the index

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

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

The following code fetches allAlbumId, andAlbumTitle columns from theAlbumsByAlbumTitle index.

voidReadDataWithIndex(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autorows=client.Read("Albums",google::cloud::spanner::KeySet::All(),{"AlbumId","AlbumTitle"},google::cloud::Options{}.set<spanner::ReadIndexNameOption>("AlbumsByAlbumTitle"));usingRowType=std::tuple<std::int64_t,std::string>;for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();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 theread-data-with-index argument.

bazelrun//google/cloud/spanner/samples:samples -- \    read-data-with-indexPROJECT_ID test-instance example-db

You should see:

AlbumId:2AlbumTitle:ForeverHoldYourPeaceAlbumId:2AlbumTitle:Go,Go,GoAlbumId:1AlbumTitle:GreenAlbumId:3AlbumTitle:TerrifiedAlbumId:1AlbumTitle:TotalJunk

Add an index for index-only reads

You might have noticed that the previous read example doesn't include readingtheMarketingBudget column. This is because Spanner's read interfacedoesn't support the ability to join an index with a data table to look up valuesthat are not stored in the index.

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

On the command line

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATEINDEXAlbumsByAlbumTitle2ONAlbums(AlbumTitle)STORING(MarketingBudget)

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATEINDEXAlbumsByAlbumTitle2ONAlbums(AlbumTitle)INCLUDE(MarketingBudget)

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

Schemaupdating...done.

Using the Spanner client library for C++

You use theDatabaseAdminClient::UpdateDatabase() function to add an indexwith aSTORING clause for :

voidAddStoringIndex(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);autometadata=client.UpdateDatabaseDdl(database.FullName(),{R"""(                        CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)                            STORING (MarketingBudget))"""}).get();if(!metadata)throwstd::move(metadata).status();std::cout <<"`AlbumsByAlbumTitle2` Index successfully added, new DDL:\n"            <<metadata->DebugString();}

Run the sample using theadd-storing-index argument.

bazelrun//google/cloud/spanner/samples:samples -- \    add-storing-indexPROJECT_ID test-instance example-db

You should see output similar to this:

`AlbumsByAlbumTitle2`Indexsuccessfullyadded,newDDL:database:"projects/PROJECT_ID/instances/test-instance/databases/example-db"statements:"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)"commit_timestamps{seconds:1581012328nanos:416682000}

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

Read data using the storing index you created by executing a query thatexplicitly specifies the index:

voidReadDataWithStoringIndex(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autorows=client.Read("Albums",google::cloud::spanner::KeySet::All(),{"AlbumId","AlbumTitle","MarketingBudget"},google::cloud::Options{}.set<spanner::ReadIndexNameOption>("AlbumsByAlbumTitle2"));usingRowType=std::tuple<std::int64_t,std::string,absl::optional<std::int64_t>>;for(auto&row:spanner::StreamOf<RowType>(rows)){if(!row)throwstd::move(row).status();std::cout <<"AlbumId: " <<std::get<0>(*row) <<"\t";std::cout <<"AlbumTitle: " <<std::get<1>(*row) <<"\t";automarketing_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 theread-data-with-storing-index argument.

bazelrun//google/cloud/spanner/samples:samples -- \    read-data-with-storing-indexPROJECT_ID test-instance example-db

You should see output similar to:

AlbumId:2AlbumTitle:ForeverHoldYourPeaceMarketingBudget:520000AlbumId:2AlbumTitle:Go,Go,GoMarketingBudget:NULLAlbumId:1AlbumTitle:GreenMarketingBudget:NULLAlbumId:3AlbumTitle:TerrifiedMarketingBudget:NULLAlbumId:1AlbumTitle:TotalJunkMarketingBudget:80000

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp.Read-onlytransactions observe a consistentprefix of the transaction commit history, so your application always getsconsistent data.TheTransaction type is used to represent all kinds of transactions.Use theMakeReadOnlyTransaction() factory function to create a read-onlytransaction.

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

voidReadOnlyTransaction(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoread_only=spanner::MakeReadOnlyTransaction();spanner::SqlStatementselect("SELECT SingerId, AlbumId, AlbumTitle FROM Albums");usingRowType=std::tuple<std::int64_t,std::int64_t,std::string>;// Read#1.autorows1=client.ExecuteQuery(read_only,select);std::cout <<"Read 1 results\n";for(auto&row:spanner::StreamOf<RowType>(rows1)){if(!row)throwstd::move(row).status();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.autorows2=client.ExecuteQuery(read_only,select);std::cout <<"Read 2 results\n";for(auto&row:spanner::StreamOf<RowType>(rows2)){if(!row)throwstd::move(row).status();std::cout <<"SingerId: " <<std::get<0>(*row)              <<" AlbumId: " <<std::get<1>(*row)              <<" AlbumTitle: " <<std::get<2>(*row) <<"\n";}}

Run the sample using theread-only-transaction argument.

bazelrun//google/cloud/spanner/samples:samples -- \    read-only-transactionPROJECT_ID test-instance example-db

You should see output similar to:

Read1resultsSingerId:2AlbumId:2AlbumTitle:ForeverHoldYourPeaceSingerId:1AlbumId:2AlbumTitle:Go,Go,GoSingerId:2AlbumId:1AlbumTitle:GreenSingerId:2AlbumId:3AlbumTitle:TerrifiedSingerId:1AlbumId:1AlbumTitle:TotalJunkRead2resultsSingerId:2AlbumId:2AlbumTitle:ForeverHoldYourPeaceSingerId:1AlbumId:2AlbumTitle:Go,Go,GoSingerId:2AlbumId:1AlbumTitle:GreenSingerId:2AlbumId:3AlbumTitle:TerrifiedSingerId:1AlbumId:1AlbumTitle:TotalJunk

Cleanup

To avoid incurring additional charges to your Cloud Billing account for theresources used in this tutorial, drop the database and delete the instance thatyou 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 wouldstill incur charges for the instance).

On the command line

gcloudspannerdatabasesdeleteexample-db--instance=test-instance

Using the Google Cloud console

1. Go to theSpanner 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 theDatabase details page, clickDelete.

5. Confirm that you want to delete the database and clickDelete.

Delete the instance

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

On the command line

gcloudspannerinstancesdeletetest-instance

Using the Google Cloud console

1. Go to theSpanner Instances page in the Google Cloud console.

   Go to the Instances page

2. Click your instance.

3. ClickDelete.

4. Confirm that you want to delete the instance and clickDelete.