Cloud Spanner: Create a gaming leaderboard with Go

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?

Read it through only Read it and complete the exercises

How would you rate your experience with Google Cloud Platform?

Novice Intermediate Proficient

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:

6c9406d9b014760.png

and click the ‘NEW PROJECT' button in the resulting dialog to create a new project:

f708315ae07353d0.png

If you don't already have a project, you should see a dialog like this to create your first one:

870a3cbd6541ee86.png

The subsequent project creation dialog allows you to enter the details of your new project:

6a92c57d3250a4b3.png

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.

15d0ef27a8fbab27.png

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

  1. To activate Cloud Shell from the Cloud Console, simply click Activate Cloud Shell a8460e837e9f5fda.png (it should only take a few moments to provision and connect to the environment).

b532b2f19ab85dda.png

Screen Shot 2017-06-14 at 10.13.43 PM.png

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:

2485e00c1223af09.png

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>
  1. 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 1a6580bd3d3e6783.pngin the left top Hamburger Menu 3129589f7bc9e5ce.png or search for Spanner by pressing "/" and type "Spanner"

36e52f8df8e13b99.png

Next, click on 19bb9864067757cb.png 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.

dceb68e9ed3801e8.png

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:

7519d016b96ca51b.png

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.

a12fa65e352836b1.png

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:

7519d016b96ca51b.png

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.

86dc5b927809a4ec.png

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.

87c8610c92d3c612.png

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:

  1. Which Players are the "Top Ten" of all time?
  2. Which Players are the "Top Ten" of the year?
  3. Which Players are the "Top Ten" of the month?
  4. 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, &timestamp); 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, &timestamp); 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:

Give us your feedback

  • Please take a moment to complete our very short survey