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?
How would 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 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:
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.
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:
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.
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 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:
- 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