1. Overview
Google Cloud Spanner is a fully managed horizontally scalable, globally distributed, relational database service that provides ACID transactions and SQL semantics without giving up performance and high availability.
In this lab, you will learn how to setup a Cloud Spanner instance. You will go through the steps of creating a database and schema that can be used for a gaming leaderboard. You'll start by creating a Players table for storing player information and a Scores table to store player scores.
Next you'll populate the tables with sample data. Then you'll conclude the lab by running some Top Ten sample queries and finally deleting the instance to free up resources.
What you'll learn
- How to setup a Cloud Spanner instance.
- How to create a database and tables.
- How to use a commit timestamp column.
- How to load data into your Cloud Spanner database table with timestamps.
- How to query your Cloud Spanner database.
- How to delete your Cloud Spanner instance.
W****hat you'll need
How will you use this tutorial?
How would you rate your experience with Google Cloud Platform?
2. Setup and Requirements
Self-paced environment setup
If you don't already have a Google Account (Gmail or Google Apps), you must create one. Sign-in to Google Cloud Platform console ( console.cloud.google.com) and create a new project.
If you already have a project, click on the project selection pull down menu in the upper left of the console:
and click the ‘NEW PROJECT' button in the resulting dialog to create a new project:
If you don't already have a project, you should see a dialog like this to create your first one:
The subsequent project creation dialog allows you to enter the details of your new project:
Remember the project ID, which is a unique name across all Google Cloud projects (the name above has already been taken and will not work for you, sorry!). It will be referred to later in this codelab as PROJECT_ID.
Next, if you haven't already done so, you'll need to enable billing in the Developers Console in order to use Google Cloud resources and enable the Cloud Spanner API.
Running through this codelab shouldn't cost you more than a few dollars, but it could be more if you decide to use more resources or if you leave them running (see "cleanup" section at the end of this document). Google Cloud Spanner pricing is documented here.
New users of Google Cloud Platform are eligible for a $300 free trial, which should make this codelab entirely free of charge.
Google Cloud Shell Setup
While Google Cloud and Spanner can be operated remotely from your laptop, in this codelab we will be using Google Cloud Shell, a command line environment running in the Cloud.
This Debian-based virtual machine is loaded with all the development tools you'll need. It offers a persistent 5GB home directory and runs in Google Cloud, greatly enhancing network performance and authentication. This means that all you will need for this codelab is a browser (yes, it works on a Chromebook).
- To activate Cloud Shell from the Cloud Console, simply click Activate Cloud Shell
(it should only take a few moments to provision and connect to the environment).
Once connected to Cloud Shell, you should see that you are already authenticated and that the project is already set to your PROJECT_ID.
gcloud auth list
Command output
Credentialed accounts: - <myaccount>@<mydomain>.com (active)
gcloud config list project
Command output
[core] project = <PROJECT_ID>
If, for some reason, the project is not set, simply issue the following command:
gcloud config set project <PROJECT_ID>
Looking for your PROJECT_ID? Check out what ID you used in the setup steps or look it up in the Cloud Console dashboard:
Cloud Shell also sets some environment variables by default, which may be useful as you run future commands.
echo $GOOGLE_CLOUD_PROJECT
Command output
<PROJECT_ID>
- Finally, set the default zone and project configuration.
gcloud config set compute/zone us-central1-f
You can choose a variety of different zones. For more information, see Regions & Zones.
Summary
In this step, you setup your environment.
Next up
Next, you will setup a Cloud Spanner Instance.
3. Setup a Cloud Spanner Instance
In this step we setup our Cloud Spanner Instance for this codelab. Search for the Spanner entry 
in the left top Hamburger Menu 
or search for Spanner by pressing "/" and type "Spanner"
Next, click on 
and fill out the form by entering the instance name cloudspanner-leaderboard for your instance, choosing a configuration (select a regional instance), and set the number of nodes, for this codelab we will only need 1 node. For production instances and to qualify for the Cloud Spanner SLA you will need to run 3 or more nodes in your Cloud Spanner instance.
Last, but not least, click on "Create" and within seconds you have a Cloud Spanner instance at your disposal.
In the next step we are going to use the Go client library to create a database and schema in our new instance.
4. Create a database and schema
In this step we're going to create our sample database and schema.
Let's use the Go client library to create two tables; a Players table for player info and a Scores table for storing player scores. To do this we'll walk through the steps of creating a Go console application in Cloud Shell.
First clone the sample code for this codelab from Github by typing the following command in Cloud Shell:
export GO111MODULE=auto
go get -u github.com/GoogleCloudPlatform/golang-samples/spanner/...
Then change directory to the "leaderboard" directory where you will create your application.
cd gopath/src/github.com/GoogleCloudPlatform/golang-samples/spanner/spanner_leaderboard
All the code required for this codelab is located in the existing golang-samples/spanner/spanner_leaderboard/directory as a runnable Go application named leaderboard to serve as reference as you progress through the codelab. We'll create a new directory and build a copy of the Leaderboard application in stages.
Create a new directory named "codelab" for the application and change directory into it with the following command:
mkdir codelab && cd $_
Now let's create a basic Go application named "Leaderboard" that uses the Spanner client library to create a leaderboard consisting of two tables; Players and Scores. You can do that right in the Cloud Shell Editor:
Open the Cloud Shell Editor, by clicking on the "Open editor" icon highlighted below:
Create a file named "leaderboard.go" in the ~/gopath/src/github.com/GoogleCloudPlatform/golang-samples/spanner/codelab folder.
- First be sure that you've got the "codelab" folder selected in the Cloud Shell Editor's list of folders.
- Then select "New File" under the Cloud Shell Editor's "File" menu.
- Enter "leaderboard.go" as the name for the new file.
This is the main file of the application that will contain our application code and references to include any dependencies.
To create the leaderboard database and the Players and Scores tables, copy (Ctrl + P) and paste (Ctrl + V) the following Go code into the leaderboard.go file:
package main
import (
"context"
"flag"
"fmt"
"io"
"log"
"os"
"regexp"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
type adminCommand func(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error
func createDatabase(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, db string) error {
matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
if matches == nil || len(matches) != 3 {
return fmt.Errorf("Invalid database id %s", db)
}
op, err := adminClient.CreateDatabase(ctx, &adminpb.CreateDatabaseRequest{
Parent: matches[1],
CreateStatement: "CREATE DATABASE `" + matches[2] + "`",
ExtraStatements: []string{
`CREATE TABLE Players(
PlayerId INT64 NOT NULL,
PlayerName STRING(2048) NOT NULL
) PRIMARY KEY(PlayerId)`,
`CREATE TABLE Scores(
PlayerId INT64 NOT NULL,
Score INT64 NOT NULL,
Timestamp TIMESTAMP NOT NULL
OPTIONS(allow_commit_timestamp=true)
) PRIMARY KEY(PlayerId, Timestamp),
INTERLEAVE IN PARENT Players ON DELETE NO ACTION`,
},
})
if err != nil {
return err
}
if _, err := op.Wait(ctx); err != nil {
return err
}
fmt.Fprintf(w, "Created database [%s]\n", db)
return nil
}
func createClients(ctx context.Context, db string) (*database.DatabaseAdminClient, *spanner.Client) {
adminClient, err := database.NewDatabaseAdminClient(ctx)
if err != nil {
log.Fatal(err)
}
dataClient, err := spanner.NewClient(ctx, db)
if err != nil {
log.Fatal(err)
}
return adminClient, dataClient
}
func run(ctx context.Context, adminClient *database.DatabaseAdminClient, dataClient *spanner.Client, w io.Writer,
cmd string, db string, timespan int) error {
// createdatabase command
if cmd == "createdatabase" {
err := createDatabase(ctx, w, adminClient, db)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
return nil
}
func main() {
flag.Usage = func() {
fmt.Fprintf(os.Stderr, `Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
`)
}
flag.Parse()
flagCount := len(flag.Args())
if flagCount != 2 {
flag.Usage()
os.Exit(2)
}
cmd, db := flag.Arg(0), flag.Arg(1)
// Set timespan to zero, as it's not currently being used
var timespan int = 0
ctx, cancel := context.WithTimeout(context.Background(), 1*time.Minute)
defer cancel()
adminClient, dataClient := createClients(ctx, db)
if err := run(ctx, adminClient, dataClient, os.Stdout, cmd, db, timespan); err != nil {
os.Exit(1)
}
}
Save the changes you made to the leaderboard.go file by selecting "Save" under the Cloud Shell Editor's "File" menu.
You can use the leaderboard.go file in the golang-samples/spanner/spanner_leaderboard directory to see an example of how your leaderboard.go file should look after you've added the code to enable the createdatabase command.
To build your app in the Cloud Shell run "go build" from the codelab directory where your leaderboard.go file is located:
go build leaderboard.go
Once your application is successfully built, run the resulting application in the Cloud Shell by entering the following command:
./leaderboard
You should see output like the following:
Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
From this response we can see that this is the Leaderboard application which currently has one possible command: createdatabase. We can see that the createdatabase command's expected argument is a string containing a specific Instance ID and Database ID.
Now run the following command. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard createdatabase projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
After a couple seconds you should see a response like the following:
Created database [projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard]
In the Cloud Spanner databases overview section of the Cloud Console you should see your new database and tables coming up in the left hand-side menu.
In the next step we will update our application to load some data into your new database.
5. Load Data
We now have a database called leaderboard containing two tables; Players and Scores. Now let's use the Go client library to populate our Players table with players and our Scores table with random scores for each player.
If it's not already open, open the Cloud Shell Editor, by clicking on the icon highlighted below:
Next, edit the leaderboard.go file in the Cloud Shell Editor to add an insertplayers command that can be used to insert 100 players into the Players table. We'll also add an insertscores command that can be used to insert 4 random scores in the Scores table for each player in the Players table.
First update the imports section at the top of the leaderboard.go file, replacing what's currently there so that once you're done it should look like the following:
import (
"context"
"flag"
"fmt"
"io"
"log"
"math/rand"
"os"
"regexp"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
"google.golang.org/api/iterator"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
Next add a new command type along with a list of commands at the top of the file, just below the line that starts with "type adminCommand ..." so that once you're done it should look like the following:
type adminCommand func(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error
type command func(ctx context.Context, w io.Writer, client *spanner.Client) error
var (
commands = map[string]command{
"insertplayers": insertPlayers,
"insertscores": insertScores,
}
)
Next add the following insertPlayers, and insertScores functions below the existing createdatabase() function:
func insertPlayers(ctx context.Context, w io.Writer, client *spanner.Client) error {
// Get number of players to use as an incrementing value for each PlayerName to be inserted
stmt := spanner.Statement{
SQL: `SELECT Count(PlayerId) as PlayerCount FROM Players`,
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
row, err := iter.Next()
if err != nil {
return err
}
var numberOfPlayers int64 = 0
if err := row.Columns(&numberOfPlayers); err != nil {
return err
}
// Initialize values for random PlayerId
rand.Seed(time.Now().UnixNano())
min := 1000000000
max := 9000000000
// Insert 100 player records into the Players table
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmts := []spanner.Statement{}
for i := 1; i <= 100; i++ {
numberOfPlayers++
playerID := rand.Intn(max-min) + min
playerName := fmt.Sprintf("Player %d", numberOfPlayers)
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Players
(PlayerId, PlayerName)
VALUES (@playerID, @playerName)`,
Params: map[string]interface{}{
"playerID": playerID,
"playerName": playerName,
},
})
}
_, err := txn.BatchUpdate(ctx, stmts)
if err != nil {
return err
}
return nil
})
fmt.Fprintf(w, "Inserted players \n")
return nil
}
func insertScores(ctx context.Context, w io.Writer, client *spanner.Client) error {
playerRecordsFound := false
// Create slice for insert statements
stmts := []spanner.Statement{}
// Select all player records
stmt := spanner.Statement{SQL: `SELECT PlayerId FROM Players`}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
// Insert 4 score records into the Scores table for each player in the Players table
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
playerRecordsFound = true
var playerID int64
if err := row.ColumnByName("PlayerId", &playerID); err != nil {
return err
}
// Initialize values for random score and date
rand.Seed(time.Now().UnixNano())
min := 1000
max := 1000000
for i := 0; i < 4; i++ {
// Generate random score between 1,000 and 1,000,000
score := rand.Intn(max-min) + min
// Generate random day within the past two years
now := time.Now()
endDate := now.Unix()
past := now.AddDate(0, -24, 0)
startDate := past.Unix()
randomDateInSeconds := rand.Int63n(endDate-startDate) + startDate
randomDate := time.Unix(randomDateInSeconds, 0)
// Add insert statement to stmts slice
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Scores
(PlayerId, Score, Timestamp)
VALUES (@playerID, @score, @timestamp)`,
Params: map[string]interface{}{
"playerID": playerID,
"score": score,
"timestamp": randomDate,
},
})
}
}
if !playerRecordsFound {
fmt.Fprintln(w, "No player records currently exist. First insert players then insert scores.")
} else {
_, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
// Commit insert statements for all scores to be inserted as a single transaction
_, err := txn.BatchUpdate(ctx, stmts)
return err
})
if err != nil {
return err
}
fmt.Fprintln(w, "Inserted scores")
}
return nil
}
Then, to make the insert command functional, add the following code to your Application's "run" function below the createdatabase handling statement, replacing the return nil statement :
// insert and query commands
cmdFn := commands[cmd]
if cmdFn == nil {
flag.Usage()
os.Exit(2)
}
err := cmdFn(ctx, w, dataClient)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
Once you're done the run function should look like the following:
func run(ctx context.Context, adminClient *database.DatabaseAdminClient, dataClient *spanner.Client, w io.Writer,
cmd string, db string, timespan int) error {
// createdatabase command
if cmd == "createdatabase" {
err := createDatabase(ctx, w, adminClient, db)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
// insert and query commands
cmdFn := commands[cmd]
if cmdFn == nil {
flag.Usage()
os.Exit(2)
}
err := cmdFn(ctx, w, dataClient)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
The final step to complete adding "insert" functionality to your application is to add help text for the "insertplayers" and "insertscores" commands to the flag.Usage()function. Add the following help text to the flag.Usage()function to include help text for the insert commands:
Add the two commands to the list of possible commands:
Command can be one of: createdatabase, insertplayers, insertscores
And add this additional help text below the help text for the createdatabase command.
leaderboard insertplayers projects/my-project/instances/my-instance/databases/example-db
- Insert 100 sample Player records into the database.
leaderboard insertscores projects/my-project/instances/my-instance/databases/example-db
- Insert sample score data into Scores sample Cloud Spanner database table.
Save the changes you made to the leaderboard.go file by selecting "Save" under the Cloud Shell Editor's "File" menu.
You can use the leaderboard.go file in the golang-samples/spanner/spanner_leaderboard directory to see an example of how your leaderboard.go file should look after you've added the code to enable the insertplayers and insertscores commands.
Now let's build and run the application to confirm that the new insertplayers and insertscores commands are included in the application's list of possible commands. Run the following command to build the application:
go build leaderboard.go
Run the resulting application in the cloud shell by entering the following command:
./leaderboard
You should see the insertplayers and insertscores commands now included in the application's default output:
Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase, insertplayers, insertscores
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
leaderboard insertplayers projects/my-project/instances/my-instance/databases/example-db
- Insert 100 sample Player records into the database.
leaderboard insertscores projects/my-project/instances/my-instance/databases/example-db
- Insert sample score data into Scores sample Cloud Spanner database table.
Now let's run the insertplayers command with the same argument values we used when we called the createdatabase command. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard insertplayers projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
After a couple seconds you should see a response like the following:
Inserted players
Now let's use the Go client library to populate our Scores table with four random scores along with timestamps for each player in the Players table.
The Scores table's Timestamp column was defined as a "commit timestamp" column via the following SQL statement that was executed when we previously ran the create command:
CREATE TABLE Scores(
PlayerId INT64 NOT NULL,
Score INT64 NOT NULL,
Timestamp TIMESTAMP NOT NULL OPTIONS(allow_commit_timestamp=true)
) PRIMARY KEY(PlayerId, Timestamp),
INTERLEAVE IN PARENT Players ON DELETE NO ACTION
Notice the OPTIONS(allow_commit_timestamp=true) attribute. This makes Timestamp a "commit timestamp" column and enables it to be auto-populated with the exact transaction timestamp for INSERT and UPDATE operations on a given table row.
You can also insert your own timestamp values into a "commit timestamp" column as long as you insert a timestamp with a value that is in the past, which is what we will do for the purpose of this codelab.
Now let's run the insertscores command with the same argument values we used when we called the insertplayers command. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard insertscores projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
After a couple seconds you should see a response like the following:
Inserted scores
Running the insertScores function uses the following code snippet to insert a randomly generated timestamp with a date-time occurring in the past:
now := time.Now()
endDate := now.Unix()
past := now.AddDate(0, -24, 0)
startDate := past.Unix()
randomDateInSeconds := rand.Int63n(endDate-startDate) + startDate
randomDate := time.Unix(randomDateInSeconds, 0)
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Scores
(PlayerId, Score, Timestamp)
VALUES (@playerID, @score, @timestamp)`,
Params: map[string]interface{}{
"playerID": playerID,
"score": score,
"timestamp": randomDate,
},
})
To auto-populate the Timestamp column with the timestamp of exactly when the "Insert" transaction takes place, you can instead insert the Go constant spanner.CommitTimestamp like in the following code snippet:
...
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Scores
(PlayerId, Score, Timestamp)
VALUES (@playerID, @score, @timestamp)`,
Params: map[string]interface{}{
"playerID": playerID,
"score": score,
"timestamp": spanner.CommitTimestamp,
},
})
Now that we've completed data loading, let's verify the values we just wrote to our new tables in the Cloud Spanner section of the Cloud Console. First select the leaderboard database and then select the Players table. Click the Data tab. You should see that you have data in the table's PlayerId and PlayerName columns.
Next let's verify the Scores table also has data by clicking the Scores table and selecting the Data tab. You should see that you have data in the table's PlayerId, Timestamp, and Score columns.
Well done! Let's update our app to run some queries that we can use to create a gaming leaderboard.
6. Run leaderboard queries
Now that we've set up our database and loaded information into our tables, let's create a leaderboard using this data. To do so we need to answer the following four questions:
- Which Players are the "Top Ten" of all time?
- Which Players are the "Top Ten" of the year?
- Which Players are the "Top Ten" of the month?
- Which Players are the "Top Ten" of the week?
Let's update our application to run the SQL queries that will answer these questions.
We'll add a query command and a queryWithTimespan command that will provide a way to run the queries to answer the questions that will produce the information required for our leaderboard.
Edit the leaderboard.go file in the Cloud Shell Editor to update the application to add a query command and a queryWithTimespan command. We'll also add a formatWithCommas helper function to format our scores with commas.
First update the imports section at the top of the leaderboard.go file, replacing what's currently there so that once you're done it should look like the following:
import (
"bytes"
"context"
"flag"
"fmt"
"io"
"log"
"math/rand"
"os"
"regexp"
"strconv"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
"google.golang.org/api/iterator"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
Next add the following two functions and the helper function below the existing insertScores method:
func query(ctx context.Context, w io.Writer, client *spanner.Client) error {
stmt := spanner.Statement{
SQL: `SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp
FROM Players p
JOIN Scores s ON p.PlayerId = s.PlayerId
ORDER BY s.Score DESC LIMIT 10`}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var playerID, score int64
var playerName string
var timestamp time.Time
if err := row.Columns(&playerID, &playerName, &score, ×tamp); err != nil {
return err
}
fmt.Fprintf(w, "PlayerId: %d PlayerName: %s Score: %s Timestamp: %s\n",
playerID, playerName, formatWithCommas(score), timestamp.String()[0:10])
}
}
func queryWithTimespan(ctx context.Context, w io.Writer, client *spanner.Client, timespan int) error {
stmt := spanner.Statement{
SQL: `SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp
FROM Players p
JOIN Scores s ON p.PlayerId = s.PlayerId
WHERE s.Timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @Timespan HOUR)
ORDER BY s.Score DESC LIMIT 10`,
Params: map[string]interface{}{"Timespan": timespan},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var playerID, score int64
var playerName string
var timestamp time.Time
if err := row.Columns(&playerID, &playerName, &score, ×tamp); err != nil {
return err
}
fmt.Fprintf(w, "PlayerId: %d PlayerName: %s Score: %s Timestamp: %s\n",
playerID, playerName, formatWithCommas(score), timestamp.String()[0:10])
}
}
func formatWithCommas(n int64) string {
numberAsString := strconv.FormatInt(n, 10)
numberLength := len(numberAsString)
if numberLength < 4 {
return numberAsString
}
var buffer bytes.Buffer
comma := []rune(",")
bufferPosition := numberLength % 3
if (bufferPosition) > 0 {
bufferPosition = 3 - bufferPosition
}
for i := 0; i < numberLength; i++ {
if bufferPosition == 3 {
buffer.WriteRune(comma[0])
bufferPosition = 0
}
bufferPosition++
buffer.WriteByte(numberAsString[i])
}
return buffer.String()
}
Next at the top of the leaderboard.go file add "query" as one command options in the commands variable, just below the "insertscores": insertScores option so that the commands variable looks like this:
var (
commands = map[string]command{
"insertplayers": insertPlayers,
"insertscores": insertScores,
"query": query,
}
)
Next add "queryWithTimespan" as a command option within the run function, below the "createdatabase" command section and above the "insert and query" commands handling section:
// querywithtimespan command
if cmd == "querywithtimespan" {
err := queryWithTimespan(ctx, w, dataClient, timespan)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
Once you're done the run function should look like the following:
func run(ctx context.Context, adminClient *database.DatabaseAdminClient, dataClient *spanner.Client, w io.Writer,
cmd string, db string, timespan int) error {
// createdatabase command
if cmd == "createdatabase" {
err := createDatabase(ctx, w, adminClient, db)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
// querywithtimespan command
if cmd == "querywithtimespan" {
if timespan == 0 {
flag.Usage()
os.Exit(2)
}
err := queryWithTimespan(ctx, w, dataClient, timespan)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
// insert and query commands
cmdFn := commands[cmd]
if cmdFn == nil {
flag.Usage()
os.Exit(2)
}
err := cmdFn(ctx, w, dataClient)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
Then, to make the queryWithTimespan command functional, update the flag.Parse() code block in your application's "main" method so that it looks like the following:
flag.Parse()
flagCount := len(flag.Args())
if flagCount < 2 || flagCount > 3 {
flag.Usage()
os.Exit(2)
}
cmd, db := flag.Arg(0), flag.Arg(1)
// If query timespan flag is specified, parse to int
var timespan int = 0
if flagCount == 3 {
parsedTimespan, err := strconv.Atoi(flag.Arg(2))
if err != nil {
fmt.Println(err)
os.Exit(2)
}
timespan = parsedTimespan
}
ctx, cancel := context.WithTimeout(context.Background(), 1*time.Minute)
defer cancel()
adminClient, dataClient := createClients(ctx, db)
if err := run(ctx, adminClient, dataClient, os.Stdout, cmd, db, timespan); err != nil {
os.Exit(1)
}
The final step to complete adding "query" functionality to your application is to add help text for the "query" and "querywithtimespan" commands to the flag.Usage()function. Add the following lines of code to the flag.Usage()function to include help text for the query commands:
Add the two "query" commands to the list of possible commands:
Command can be one of: createdatabase, insertplayers, insertscores, query, querywithtimespan
And add this additional help text below the help text for the insertscores command.
leaderboard query projects/my-project/instances/my-instance/databases/example-db
- Query players with top ten scores of all time.
leaderboard querywithtimespan projects/my-project/instances/my-instance/databases/example-db 168
- Query players with top ten scores within a timespan specified in hours.
Save the changes you made to the leaderboard.go file by selecting "Save" under the Cloud Shell Editor's "File" menu.
You can use the leaderboard.go file in the golang-samples/spanner/spanner_leaderboard directory to see an example of how your leaderboard.go file should look after you've added the code to enable the query and querywithtimespan commands.
Now let's build and run the application to confirm that the new query and querywithtimespan commands are included in the application's list of possible commands.
Run the following command in the Cloud Shell to build the application:
go build leaderboard.go
Run the resulting application in the cloud shell by entering the following command:
./leaderboard
You should see the query and querywithtimespan commands now included in the app's default output as a new command option:
Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase, insertplayers, insertscores, query, querywithtimespan
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
leaderboard insertplayers projects/my-project/instances/my-instance/databases/example-db
- Insert 100 sample Player records into the database.
leaderboard insertscores projects/my-project/instances/my-instance/databases/example-db
- Insert sample score data into Scores sample Cloud Spanner database table.
leaderboard query projects/my-project/instances/my-instance/databases/example-db
- Query players with top ten scores of all time.
leaderboard querywithtimespan projects/my-project/instances/my-instance/databases/example-db 168
- Query players with top ten scores within a timespan specified in hours.
You can see from the response that we can use the query command to get a list of our "Top Ten" players of all time. We can also see that the querywithtimespan command allows us to specify a timespan in number of hours to use for filtering records based on their value in the Scores table's Timestamp column.
Let's run the query command using the same argument values we used when we ran the create command. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard query projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
You should see a response that includes the "Top Ten" players of all time like the following:
PlayerId: 4018687297 PlayerName: Player 83 Score: 999,618 Timestamp: 2017-07-01
PlayerId: 4018687297 PlayerName: Player 83 Score: 998,956 Timestamp: 2017-09-02
PlayerId: 4285713246 PlayerName: Player 51 Score: 998,648 Timestamp: 2017-12-01
PlayerId: 5267931774 PlayerName: Player 49 Score: 997,733 Timestamp: 2017-11-09
PlayerId: 1981654448 PlayerName: Player 35 Score: 997,480 Timestamp: 2018-12-06
PlayerId: 4953940705 PlayerName: Player 87 Score: 995,184 Timestamp: 2018-09-14
PlayerId: 2456736905 PlayerName: Player 84 Score: 992,881 Timestamp: 2017-04-14
PlayerId: 8234617611 PlayerName: Player 19 Score: 992,399 Timestamp: 2017-12-27
PlayerId: 1788051688 PlayerName: Player 76 Score: 992,265 Timestamp: 2018-11-22
PlayerId: 7127686505 PlayerName: Player 97 Score: 992,038 Timestamp: 2017-12-02
Now let's run the querywithtimespan command with the necessary arguments to query the "Top Ten" players of the year by specifying a "timespan" equal to the number of hours in a year which is 8760. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 8760
You should see a response that includes the "Top Ten" players of the year like the following:
PlayerId: 1981654448 PlayerName: Player 35 Score: 997,480 Timestamp: 2018-12-06
PlayerId: 4953940705 PlayerName: Player 87 Score: 995,184 Timestamp: 2018-09-14
PlayerId: 1788051688 PlayerName: Player 76 Score: 992,265 Timestamp: 2018-11-22
PlayerId: 6862349579 PlayerName: Player 30 Score: 990,877 Timestamp: 2018-09-14
PlayerId: 5529627211 PlayerName: Player 16 Score: 989,142 Timestamp: 2018-03-30
PlayerId: 9743904155 PlayerName: Player 1 Score: 988,765 Timestamp: 2018-05-30
PlayerId: 6809119884 PlayerName: Player 7 Score: 986,673 Timestamp: 2018-05-16
PlayerId: 2132710638 PlayerName: Player 54 Score: 983,108 Timestamp: 2018-09-11
PlayerId: 2320093590 PlayerName: Player 79 Score: 981,373 Timestamp: 2018-05-07
PlayerId: 9554181430 PlayerName: Player 80 Score: 981,087 Timestamp: 2018-06-21
Now let's run the querywithtimespan command to query the "Top Ten" players of the month by specifying a "timespan" equal to the number of hours in a month which is 730. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 730
You should see a response that includes the "Top Ten" players of the month like the following:
PlayerId: 3869829195 PlayerName: Player 69 Score: 949,686 Timestamp: 2019-02-19
PlayerId: 7448359883 PlayerName: Player 20 Score: 938,998 Timestamp: 2019-02-07
PlayerId: 1981654448 PlayerName: Player 35 Score: 929,003 Timestamp: 2019-02-22
PlayerId: 9336678658 PlayerName: Player 44 Score: 914,106 Timestamp: 2019-01-27
PlayerId: 6968576389 PlayerName: Player 40 Score: 898,041 Timestamp: 2019-02-21
PlayerId: 5529627211 PlayerName: Player 16 Score: 896,433 Timestamp: 2019-01-29
PlayerId: 9395039625 PlayerName: Player 59 Score: 879,495 Timestamp: 2019-02-09
PlayerId: 2094604854 PlayerName: Player 39 Score: 860,434 Timestamp: 2019-02-01
PlayerId: 9395039625 PlayerName: Player 59 Score: 849,955 Timestamp: 2019-02-21
PlayerId: 4285713246 PlayerName: Player 51 Score: 805,654 Timestamp: 2019-02-02
Now let's run the querywithtimespan command to query the "Top Ten" players of the week by specifying a "timespan" equal to the number of hours in a week which is 168. Make sure you replace my-project with the Project ID you created at the beginning of this codelab.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 168
You should see a response that includes the "Top Ten" players of the week like the following:
PlayerId: 3869829195 PlayerName: Player 69 Score: 949,686 Timestamp: 2019-02-19
PlayerId: 1981654448 PlayerName: Player 35 Score: 929,003 Timestamp: 2019-02-22
PlayerId: 6968576389 PlayerName: Player 40 Score: 898,041 Timestamp: 2019-02-21
PlayerId: 9395039625 PlayerName: Player 59 Score: 849,955 Timestamp: 2019-02-21
PlayerId: 5954045812 PlayerName: Player 8 Score: 795,639 Timestamp: 2019-02-22
PlayerId: 3889939638 PlayerName: Player 71 Score: 775,252 Timestamp: 2019-02-21
PlayerId: 5529627211 PlayerName: Player 16 Score: 604,695 Timestamp: 2019-02-19
PlayerId: 9006728426 PlayerName: Player 3 Score: 457,208 Timestamp: 2019-02-22
PlayerId: 8289497066 PlayerName: Player 58 Score: 227,697 Timestamp: 2019-02-20
PlayerId: 8065482904 PlayerName: Player 99 Score: 198,429 Timestamp: 2019-02-24
Excellent work!
Now as you add records Cloud Spanner will scale your database to however large you need it to be. No matter how much your database grows, your game's leaderboard can continue to scale with accuracy with Cloud Spanner and its Truetime technology.
7. Cleanup
After all the fun playing with Spanner we need to cleanup our playground, saving precious resources and money. Luckily this is an easy step, just go into the Cloud Spanner section of the Cloud Console and delete the instance we created in the codelab step named "Setup a Cloud Spanner Instance".
8. Congratulations!
What we've covered:
- Google Cloud Spanner Instances, Databases and Table Schema for a leaderboard
- How to create Go console application
- How to create a Spanner Database and Tables using the Go client library
- How to load data into a Spanner Database using the Go client library
- How to query "Top Ten" results from your data using Spanner commit timestamps and the Go client library
Next Steps:
- Read the Spanner CAP Whitepaper
- Learn about Schema Design and Query best practices
- Learn more about Cloud Spanner commit timestamps
Give us your feedback
- Please take a moment to complete our very short survey