Cloud Spanner: Create a gaming leaderboard with Java

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.

What you'll need

How will you use this tutorial?

Read it through only Read it and complete the exercises

How would 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 gcLMt5IuEcJJNnMId-Bcz3sxCd0rZn7IzT_r95C8UZeqML68Y1efBG_B0VRp7hc7qiZTLAF-TXD7SsOadxn8uadgHhaLeASnVS3ZHK39eOlKJOgj9SJua_oeGhMxRrbOg3qigddS2A (it should only take a few moments to provision and connect to the environment).

JjEuRXGg0AYYIY6QZ8d-66gx_Mtc-_jDE9ijmbXLJSAXFvJt-qUpNtsBsYjNpv2W6BQSrDc1D-ARINNQ-1EkwUhz-iUK-FUCZhJ-NtjvIEx9pIkE-246DomWuCfiGHK78DgoeWkHRw

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:

158fNPfwSxsFqz9YbtJVZes8viTS3d1bV4CVhij3XPxuzVFOtTObnwsphlm6lYGmgdMFwBJtc-FaLrZU7XHAg_ZYoCrgombMRR3h-eolLPcvO351c5iBv506B3ZwghZoiRg6cz23Qw

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 95269e75bc8c3e4d.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 Java 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 Java 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 Java console application in Cloud Shell.

First clone the sample code for this codelab from Github by typing the following command in Cloud Shell:

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

Then change directory to the "applications" directory where you will create your application.

cd java-docs-samples/spanner/leaderboard

All the code required for this codelab is located in the existing java-docs-samples/spanner/leaderboard/complete directory as a runnable C# 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 $_

Create a new basic Java application named "Leaderboard" using the following Maven (mvn) command:

mvn -B archetype:generate -DarchetypeGroupId=org.apache.maven.archetypes -DgroupId=com.google.codelabs -DartifactId=leaderboard -DarchetypeVersion=1.4

This command creates a simple console application consisting of two files primary files, the Maven app configuration file pom.xml and the Java app file App.java.

Next, change directory into the leaderboard directory that was just created and list its contents:

cd leaderboard && ls

You should see the pom.xml file and the src directory listed:

pom.xml  src

Now let's update this console app by editing App.java to use the Java 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 icon highlighted below:

73cf70e05f653ca.png

Open the pom.xml under the leaderboard folder. Open the pom.xml file that is located in the java-docs-samples\ spanner\leaderboard\codelab\leaderboard folder.This file configures the maven build system to build our application into a jar, including all of our dependencies.

Add the following 1 new dependency management section right under the existing </properties> element:

<dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>google-cloud-bom</artifactId>
        <version>0.83.0-alpha</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

Also add 1 new dependency in the existing <dependencies> section, which will add the Cloud Spanner Java client library to the application.

    <dependency>
      <!-- Version auto-managed by BOM -->
      <groupId>com.google.cloud</groupId>
      <artifactId>google-cloud-spanner</artifactId>
    </dependency>  

Then replace the pom.xml file's existing <build> section with the following <build> section:

 <build>
    <plugins>
      <plugin>
        <artifactId>maven-assembly-plugin</artifactId>
        <version>2.5.5</version>
        <configuration>
          <finalName>leaderboard</finalName>
          <descriptorRefs>
            <descriptorRef>jar-with-dependencies</descriptorRef>
          </descriptorRefs>
          <archive>
            <manifest>
              <mainClass>com.google.codelabs.App</mainClass>
            </manifest>
          </archive>
          <appendAssemblyId>false</appendAssemblyId>
          <attach>false</attach>
        </configuration>
        <executions>
          <execution>
            <id>make-assembly</id>
            <phase>package</phase>
            <goals>
              <goal>single</goal>
            </goals>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-failsafe-plugin</artifactId>
        <version>3.0.0-M3</version>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-surefire-plugin</artifactId>
        <version>3.0.0-M3</version>
        <configuration>
            <useSystemClassLoader>false</useSystemClassLoader>
        </configuration>
      </plugin>  
    </plugins>
  </build>

Save the changes you made to the pom.xml file by selecting "Save" under the Cloud Shell Editor's "File" menu or by pressing the "Ctrl" and "S" keyboard keys together.

Next, open the App.java file in the Cloud Shell Editor located in the src/main/java/com/google/codelabs/ folder. Replace the file's existing code with the code required to create the leaderboard database and the Players and Scores tables by pasting the following Java code into the App.java file:

package com.google.codelabs;

import com.google.api.gax.longrunning.OperationFuture;
import com.google.cloud.spanner.Database;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerException;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.spanner.admin.database.v1.CreateDatabaseMetadata;
import java.util.Arrays;
import java.util.concurrent.ExecutionException;

/**
 * Example code for using the Cloud Spanner API with the Google Cloud Java client library
 * to create a simple leaderboard.
 * 
 * This example demonstrates:
 *
 * <p>
 *
 * <ul>
 *   <li>Creating a Cloud Spanner database.
 * </ul>
 */
public class App {

  static void create(DatabaseAdminClient dbAdminClient, DatabaseId db) {
    OperationFuture<Database, CreateDatabaseMetadata> op =
        dbAdminClient.createDatabase(
            db.getInstanceId().getInstance(),
            db.getDatabase(),
            Arrays.asList(
                "CREATE TABLE Players(\n"
                    + "  PlayerId INT64 NOT NULL,\n"
                    + "  PlayerName STRING(2048) NOT NULL\n"
                    + ") PRIMARY KEY(PlayerId)",
                "CREATE TABLE Scores(\n"
                    + "  PlayerId INT64 NOT NULL,\n"
                    + "  Score INT64 NOT NULL,\n"
                    + "  Timestamp TIMESTAMP NOT NULL\n"
                    + "  OPTIONS(allow_commit_timestamp=true)\n"
                    + ") PRIMARY KEY(PlayerId, Timestamp),\n"
                    + "INTERLEAVE IN PARENT Players ON DELETE NO ACTION"));
    try {
      // Initiate the request which returns an OperationFuture.
      Database dbOperation = op.get();
      System.out.println("Created database [" + dbOperation.getId() + "]");
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw (SpannerException) e.getCause();
    } catch (InterruptedException e) {
      // Throw when a thread is waiting, sleeping, or otherwise occupied,
      // and the thread is interrupted, either before or during the activity.
      throw SpannerExceptionFactory.propagateInterrupt(e);
    }
  }

  static void printUsageAndExit() {
    System.out.println("Leaderboard 1.0.0");
    System.out.println("Usage:");
    System.out.println("  java -jar leaderboard.jar "
        + "<command> <instance_id> <database_id> [command_option]");
    System.out.println("");
    System.out.println("Examples:");
    System.out.println("  java -jar leaderboard.jar create my-instance example-db");
    System.out.println("      - Create a sample Cloud Spanner database along with "
        + "sample tables in your project.\n");
    System.exit(1);
  }

  public static void main(String[] args) throws Exception {
    if (!(args.length == 3 || args.length == 4)) {
      printUsageAndExit();
    }
    SpannerOptions options = SpannerOptions.newBuilder().build();
    Spanner spanner = options.getService();
    try {
      String command = args[0];
      DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
      DatabaseClient dbClient = spanner.getDatabaseClient(db);
      DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
      switch (command) {
        case "create":
          create(dbAdminClient, db);
          break;
        default:
          printUsageAndExit();
      }
    } finally {
      spanner.close();
    }
    System.out.println("Closed client");
  }
}

Save the changes you made to the App.java file by selecting "Save" under the Cloud Shell Editor's "File" menu.

You can use the App.java file in the java-docs-samples/spanner/leaderboard/step4/src directory to see an example of how your App.java file should look after you've added the code to enable the create command.

To build your app run mvn package from the directory where your pom.xml is located:

mvn package

Once your Java jar file is successfully built, run the resulting application in the cloud shell by entering the following command:

java -jar target/leaderboard.jar

You should see output like the following:

Leaderboard 1.0.0
Usage:
  java -jar leaderboard.jar <command> <instance_id> <database_id> [command_option]

Examples:
  java -jar leaderboard.jar create my-instance 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: create. We can see that the create command's expected arguments are Instance ID and Database ID.

Now run the following command.

java -jar target/leaderboard.jar create cloudspanner-leaderboard leaderboard

After a couple seconds you should see a response like the following:

Created database [projects/your-project/instances/cloudspanner-leaderboard/databases/leaderboard] 

In the Cloud Spanner section of the Cloud Console you should see your new database and tables coming up in the left hand-side menu.

ba9008bb84cb90b0.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 Java 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:

ef49fcbaaed19024.png

Next, edit the App.java file in the Cloud Shell Editor to add an insert command that can be used to insert 100 players into the Players table or it 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 app file, replacing what's currently there so that once you're done it should look like the following:

package com.google.codelabs;

import static com.google.cloud.spanner.TransactionRunner.TransactionCallable;

import com.google.api.gax.longrunning.OperationFuture;
import com.google.cloud.spanner.Database;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Mutation;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerException;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.TransactionContext;
import com.google.spanner.admin.database.v1.CreateDatabaseMetadata;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ThreadLocalRandom;

Next add the following insert, insertPlayers, and insertScores methods below the existing create() method and above the existing printUsageAndExit() method:

  static void insert(DatabaseClient dbClient, String insertType) {
    try {
      insertType = insertType.toLowerCase();
    } catch (Exception e) {
      // Invalid input received, set insertType to empty string.
      insertType = "";
    }
    if (insertType.equals("players")) {
      // Insert players.
      insertPlayers(dbClient);
    } else if (insertType.equals("scores")) {
      // Insert scores.
      insertScores(dbClient);
    } else {
      // Invalid input.
      System.out.println("Invalid value for 'type of insert'. "
          + "Specify a valid value: 'players' or 'scores'.");
      System.exit(1);
    }
  }

  static void insertPlayers(DatabaseClient dbClient) {
    dbClient
        .readWriteTransaction()
        .run(
            new TransactionCallable<Void>() {
              @Override
              public Void run(TransactionContext transaction) throws Exception {
                // Get the number of players.
                String sql = "SELECT Count(PlayerId) as PlayerCount FROM Players";
                ResultSet resultSet = transaction.executeQuery(Statement.of(sql));
                long numberOfPlayers = 0;
                if (resultSet.next()) {
                  numberOfPlayers = resultSet.getLong("PlayerCount");
                }
                // Insert 100 player records into the Players table.
                List<Statement> stmts = new ArrayList<Statement>();
                long randomId;
                for (int x = 1; x <= 100; x++) {
                  numberOfPlayers++;
                  randomId = (long) Math.floor(Math.random() * 9_000_000_000L) + 1_000_000_000L;
                  Statement statement =
                      Statement
                        .newBuilder(
                            "INSERT INTO Players (PlayerId, PlayerName) "
                            + "VALUES (@PlayerId, @PlayerName) ")
                        .bind("PlayerId")
                        .to(randomId)
                        .bind("PlayerName")
                        .to("Player " + numberOfPlayers)
                        .build();
                  stmts.add(statement);
                }
                transaction.batchUpdate(stmts);
                return null;
              }
            });
    System.out.println("Done inserting player records...");
  }

  static void insertScores(DatabaseClient dbClient) {
    boolean playerRecordsFound = false;
    ResultSet resultSet =
        dbClient
            .singleUse()
            .executeQuery(Statement.of("SELECT * FROM Players"));
    while (resultSet.next()) {
      playerRecordsFound = true;
      final long playerId = resultSet.getLong("PlayerId");
      dbClient
          .readWriteTransaction()
          .run(
              new TransactionCallable<Void>() {
                @Override
                public Void run(TransactionContext transaction) throws Exception {
                  // Initialize objects for random Score and random Timestamp.
                  LocalDate endDate = LocalDate.now();
                  long end = endDate.toEpochDay();
                  int startYear = endDate.getYear() - 2;
                  int startMonth = endDate.getMonthValue();
                  int startDay = endDate.getDayOfMonth();
                  LocalDate startDate = LocalDate.of(startYear, startMonth, startDay);
                  long start = startDate.toEpochDay();
                  Random r = new Random();
                  List<Statement> stmts = new ArrayList<Statement>();
                  // Insert 4 score records into the Scores table 
                  // for each player in the Players table.
                  for (int x = 1; x <= 4; x++) {
                    // Generate random score between 1,000,000 and 1,000
                    long randomScore = r.nextInt(1000000 - 1000) + 1000;
                    // Get random day within the past two years.
                    long randomDay = ThreadLocalRandom.current().nextLong(start, end);
                    LocalDate randomDayDate = LocalDate.ofEpochDay(randomDay);
                    LocalTime randomTime = LocalTime.of(
                        r.nextInt(23), r.nextInt(59), r.nextInt(59), r.nextInt(9999));
                    LocalDateTime randomDate = LocalDateTime.of(randomDayDate, randomTime);
                    Instant randomInstant = randomDate.toInstant(ZoneOffset.UTC);
                    Statement statement =
                        Statement
                        .newBuilder(
                          "INSERT INTO Scores (PlayerId, Score, Timestamp) "
                          + "VALUES (@PlayerId, @Score, @Timestamp) ")
                        .bind("PlayerId")
                        .to(playerId)
                        .bind("Score")
                        .to(randomScore)
                        .bind("Timestamp")
                        .to(randomInstant.toString())
                        .build();
                    stmts.add(statement);
                  }
                  transaction.batchUpdate(stmts);
                  return null;
                }
              });

    }
    if (!playerRecordsFound) {
      System.out.println("Parameter 'scores' is invalid since "
          + "no player records currently exist. First insert players "
          + "then insert scores.");
      System.exit(1);
    } else {
      System.out.println("Done inserting score records...");
    }
  }

Then, to make the insert command functional, add the following code to your app's "main" method within the switch (command) statement :

        case "insert":
          String insertType;
          try {
            insertType = args[3];
          } catch (ArrayIndexOutOfBoundsException exception) {
            insertType = "";
          }
          insert(dbClient, insertType);
          break;

Once you're done the switch (command) statement should look like the following:

      switch (command) {
        case "create":
          create(dbAdminClient, db);
          break;
        case "insert":
          String insertType;
          try {
            insertType = args[3];
          } catch (ArrayIndexOutOfBoundsException exception) {
            insertType = "";
          }
          insert(dbClient, insertType);
          break;
        default:
          printUsageAndExit();
      }

The final step to complete adding "insert" functionality to your app is to add help text for the "insert" command to the printUsageAndExit()method. Add the following lines of code to the printUsageAndExit()method to include help text for the insert command:

    System.out.println("  java -jar leaderboard.jar insert my-instance example-db players");
    System.out.println("      - Insert 100 sample Player records into the database.\n");
    System.out.println("  java -jar leaderboard.jar insert my-instance example-db scores");
    System.out.println("      - Insert sample score data into Scores sample Cloud Spanner "
        + "database table.\n");

Save the changes you made to the App.java file by selecting "Save" under the Cloud Shell Editor's "File" menu.

You can use the App.java file in the java-docs-samples/spanner/leaderboard/step5/src directory to see an example of how your App.java file should look after you've added the code to enable the insert command.

Now let's rebuild and run the app to confirm that the new insert command is included in the app's list of possible commands.

To build your app run mvn package from the directory where your pom.xml is located:

mvn package

Once your Java jar file is successfully built, run the following command:

java -jar target/leaderboard.jar

You should see the insert command now included in the app's default output:

Leaderboard 1.0.0
Usage:
  java -jar leaderboard.jar <command> <instance_id> <database_id> [command_option]

Examples:
  java -jar leaderboard.jar create my-instance example-db
      - Create a sample Cloud Spanner database along with sample tables in your project.

  java -jar leaderboard.jar insert my-instance example-db players
      - Insert 100 sample Player records into the database.

  java -jar leaderboard.jar insert my-instance example-db scores
      - Insert sample score data into Scores sample Cloud Spanner database table.

You can see from the response that in addition to the Instance ID and Database ID there's another argument that can have a value of ‘players' or ‘scores'.

Now let's run the insert command with the same argument values we used when we called the create command, adding "players" as the additional "type of insert" argument.

java -jar target/leaderboard.jar insert cloudspanner-leaderboard leaderboard players

After a couple seconds you should see a response like the following:

Done inserting player records...

Now let's use the Java 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 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 insert command with the same argument values we used when we called the create command adding "scores" as the additional "type of insert" argument.

java -jar target/leaderboard.jar insert cloudspanner-leaderboard leaderboard scores

After a couple seconds you should see a response like the following:

Done inserting score records...

Running insert with the "type of insert" specified as scores calls the insertScores method which uses the following code snippets to insert a randomly generated timestamp with a date-time occurring in the past:

          LocalDate endDate = LocalDate.now();
          long end = endDate.toEpochDay();
          int startYear = endDate.getYear() - 2;
          int startMonth = endDate.getMonthValue();
          int startDay = endDate.getDayOfMonth();
          LocalDate startDate = LocalDate.of(startYear, startMonth, startDay);
          long start = startDate.toEpochDay();
...
            long randomDay = ThreadLocalRandom.current().nextLong(start, end);
            LocalDate randomDayDate = LocalDate.ofEpochDay(randomDay);
            LocalTime randomTime = LocalTime.of(
                        r.nextInt(23), r.nextInt(59), r.nextInt(59), r.nextInt(9999));
            LocalDateTime randomDate = LocalDateTime.of(randomDayDate, randomTime);
            Instant randomInstant = randomDate.toInstant(ZoneOffset.UTC);

...
               .bind("Timestamp")
               .to(randomInstant.toString())

To auto-populate the Timestamp column with the timestamp of exactly when the "Insert" transaction takes place, you can instead insert the Java constant Value.COMMIT_TIMESTAMP like in the following code snippet:

               .bind("Timestamp")
               .to(Value.COMMIT_TIMESTAMP)

Now that we've completed data loading, let's verify the values we just wrote to our new tables. 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.

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

d8a4ee4f13244c19.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 app to run the SQL queries that will answer these questions.

We'll add a query 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 App.java file in the Cloud Shell Editor to update the app to add a query command. The query command is comprised of two query methods, one which takes only a DatabaseClient argument and one which takes an additional timespan argument to facilitate the filtering of results by a timespan specified in hours.

Add the following two query methods below the existing insertScores() method and above the existing printUsageAndExit() method:

  static void query(DatabaseClient dbClient) {
    String scoreDate;
    String score;
    ResultSet resultSet =
        dbClient
            .singleUse()
            .executeQuery(
                Statement.of(
                    "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"));
    while (resultSet.next()) {
      scoreDate = String.valueOf(resultSet.getTimestamp("Timestamp"));
      score = String.format("%,d", resultSet.getLong("Score"));
      System.out.printf(
          "PlayerId: %d  PlayerName: %s  Score: %s  Timestamp: %s\n",
          resultSet.getLong("PlayerId"), resultSet.getString("PlayerName"), score,
          scoreDate.substring(0,10));
    }
  }

  static void query(DatabaseClient dbClient, int timespan) {
    String scoreDate;
    String score;
    Statement statement =
        Statement
            .newBuilder(
              "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")
            .bind("Timespan")
            .to(timespan)
            .build();
    ResultSet resultSet =
        dbClient
            .singleUse()
            .executeQuery(statement);
    while (resultSet.next()) {
      scoreDate = String.valueOf(resultSet.getTimestamp("Timestamp"));
      score = String.format("%,d", resultSet.getLong("Score"));
      System.out.printf(
          "PlayerId: %d  PlayerName: %s  Score: %s  Timestamp: %s\n",
          resultSet.getLong("PlayerId"), resultSet.getString("PlayerName"), score,
          scoreDate.substring(0,10));
    }
  }

Then, to make the query command functional, add the following code to the switch(command) statement in your app's "main" method:

        case "query":
          if (args.length == 4) {
            int timespan = 0;
            try {
              timespan = Integer.parseInt(args[3]);
            } catch (NumberFormatException e) {
              System.err.println("query command's 'timespan' parameter must be a valid integer.");
              System.exit(1);
            }
            query(dbClient, timespan);
          } else {
            query(dbClient);
          }
          break;

The final step to complete adding "query" functionality to your app is to add help text for the "query" command to the printUsageAndExit()method. Add the following lines of code to the printUsageAndExit()method to include help text for the "query" command:

    System.out.println("  java -jar leaderboard.jar query my-instance example-db");
    System.out.println("      - Query players with top ten scores of all time.\n");
    System.out.println("  java -jar leaderboard.jar query my-instance example-db 168");
    System.out.println("      - Query players with top ten scores within a timespan "
        + "specified in hours.\n");

Save the changes you made to the App.java file by selecting "Save" under the Cloud Shell Editor's "File" menu.

You can use the App.java file in the dotnet-docs-samples/applications/leaderboard/step6/src directory to see an example of how your App.java file should look after you've added the code to enable the query command.

To build your app run mvn package from the directory where your pom.xml is located:

mvn package

Now let's run the app to confirm that the new query command is included in the app's list of possible commands. Run the following command:

java -jar target/leaderboard.jar

You should see the query command now included in the app's default output as a new command option:

Leaderboard 1.0.0
Usage:
  java -jar leaderboard.jar <command> <instance_id> <database_id> [command_option]

Examples:
  java -jar leaderboard.jar create my-instance example-db
      - Create a sample Cloud Spanner database along with sample tables in your project.

  java -jar leaderboard.jar insert my-instance example-db players
      - Insert 100 sample Player records into the database.

  java -jar leaderboard.jar insert my-instance example-db scores
      - Insert sample score data into Scores sample Cloud Spanner database table.

  java -jar leaderboard.jar query my-instance example-db
      - Query players with top ten scores of all time.

  java -jar leaderboard.jar query my-instance example-db 168
      - Query players with top ten scores within a timespan specified in hours.

You can see from the response that in addition to the Instance ID and Database ID arguments the query command allows us to specify an optional timespan in number of hours to use for filtering records based on their value in the Scores table's Timestamp column. Since the timespan argument in optional it means that if that a timespan argument is not included then no records will be filtered by timestamps. So we can use the query command without a "timespan" value to get a list of our "Top Ten" players of all time.

Let's run the query command without specifying a "timespan", using the same argument values we used when we ran the create command.

java -jar target/leaderboard.jar query cloudspanner-leaderboard 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 query 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.

java -jar target/leaderboard.jar query cloudspanner-leaderboard 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 query 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.

java -jar target/leaderboard.jar query cloudspanner-leaderboard 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 query 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.

java -jar target/leaderboard.jar query cloudspanner-leaderboard 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 Java console application
  • How to create a Spanner Database and Tables using the Java client library
  • How to load data into a Spanner Database using the Java client library
  • How to query "Top Ten" results from your data using Spanner commit timestamps and the Java client library

Next Steps:

Give us your feedback

  • Please take a moment to complete our very short survey