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. Helpful information about how Cloud Spanner is different will be provided along the way, as well as how to get the most out of this powerful database. You'll conclude the lab running some sample queries and tearing down the instance.

What you'll learn

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

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 ‘+' icon on 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 Cloud 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 on the 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 Google Cloud Shell, from the developer console simply click the button on the top right-hand side (it should only take a few moments to provision and connect to the environment):

Then accept the terms of service and click the "Start Cloud Shell" link:

Once connected to the 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 console dashboard:

IMPORTANT: Finally, set the default zone and project configuration:

gcloud config set compute/zone us-central1-f

You can choose a variety of different zones. Learn more in the Regions & Zones documentation.

To run the sample application, the following dependencies are required:

All these dependencies have been pre-installed for you on Google Cloud Shell which we will use for this codelab.

If you don't have a Cloud Shell window open already, open one by clicking Cloud Shell symbol in the upper right corner. All following command line instructions are executed in the Cloud Shell session.

You can open additional Cloud Shell sessions by clicking on "+" in the Cloud Shell sessions tab bar .

So you're ready to get started!

Summary

In this step, you setup your environment.

Next up

Next, you will 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 choosing a name for your instance, 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 create a database and schema in our new instance.

In this step we're going to create our sample database and schema.

Click on "Create database" on the Instance Details screen that followed the creation of the instance or select your instance from the instance list when you select the Spanner menu item.

Follow the guided dialog to create the database and schema.

After clicking "Continue" we will create a schema with a DDL. For this step, switch the toggle "Edit as text" and paste the following DDL and click "Create".

CREATE TABLE Songs (
   SongId  STRING(36) NOT NULL,
   Title   STRING(100), 
   year    INT64, 
   peak    INT64,
) PRIMARY KEY(SongId);

After a couple seconds you will see your new database and table coming up in the left hand-side menu.

In the next step we will use the Java client library to load some data into your new database.

We now have a database called demo containing a table called songs. Now let's use the Java client library to populate our table of songs with columns for each song name, year of release in the US, and peak chart position. Before you can do reads or writes, we must create a DatabaseClient, which encapsulates a Cloud Spanner database connection.

Open the Cloud Shell, by clicking on the icon highlighted below:

Create an empty Maven project:

$ mvn -B archetype:generate \
  -DarchetypeGroupId=org.apache.maven.archetypes \
  -DgroupId=com.google.codelabs \
  -DartifactId=cloudspanner101

Next, move into the folder cloudspanner101 and list its contents:

$ cd cloudspanner101 && ls
pom.xml  src

Next launch the code editor by clicking on the most left icon on the right side of the cloud shell bar.

Open the pom.xml under the cloudspanner101 folder and add the following 3 dependencies.

This file configures the maven build system to build our application into a jar, including all of our dependencies.

<dependency>
   <groupId>com.google.cloud</groupId>
   <artifactId>google-cloud-spanner</artifactId>
   <version>0.35.0-beta</version>
</dependency>
<dependency>
   <groupId>com.google.guava</groupId>
   <artifactId>guava</artifactId>
   <version>23.0</version>
</dependency>
<dependency>
   <groupId>com.google.auth</groupId>
   <artifactId>google-auth-library-oauth2-http</artifactId>
   <version>0.8.0</version>
</dependency>

Then add the following <build> section above the <dependencies> section:

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
        </configuration>
      </plugin>
      <plugin>
            <artifactId>maven-assembly-plugin</artifactId>
            <executions>
              <execution>
                <phase>package</phase>
                <goals>
                  <goal>single</goal>
                </goals>
              </execution>
            </executions>
            <configuration>
              <archive>
                <manifest>
                  <addClasspath>true</addClasspath>
                  <mainClass>com.google.codelabs.App</mainClass>
                </manifest>
              </archive>
              <descriptorRefs>
                <descriptorRef>jar-with-dependencies</descriptorRef>
              </descriptorRefs>
            </configuration>
          </plugin> 
    </plugins>
  </build>

Next open the App.java with the code editor. It is located in the src/main/java/com/google/codelabs directory. Replace the contents of the file with the following code:

/*
 * Copyright (C) 2018 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package com.google.codelabs;

import com.google.auth.oauth2.GoogleCredentials;
import com.google.cloud.spanner.*;
import org.apache.commons.codec.binary.Hex;

import java.io.IOException;
import java.nio.ByteBuffer;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;

public class App {

    /**
     * Class to contain Song data.
     */
    static class Song {

        final String songId;
        final String title;
        final int year;
        final int peak;

        Song(String songId, String title, int year, int peak) {
            this.songId = songId;
            this.title = title;
            this.year = year;
            this.peak = peak;
        }
    }

    static final List<Song> SONGS =
        Arrays.asList(
            new Song(getUUID(), "My Bonnie", 1961, 26),
            new Song(getUUID(), "Love Me Do", 1962, 1),
            new Song(getUUID(), "From Me to You", 1963, 116),
            new Song(getUUID(), "She Loves You", 1963, 1),
            new Song(getUUID(), "Roll Over Beethoven", 1963, 68),
            new Song(getUUID(), "I Want to Hold Your Hand", 1963, 1),
            new Song(getUUID(), "Please Please Me", 1964, 3),
            new Song(getUUID(), "All My Loving", 1964, 45),
            new Song(getUUID(), "Why", 1964, 88),
            new Song(getUUID(), "Twist and Shout", 1964, 2),
            new Song(getUUID(), "Can't Buy Me Love", 1964, 1),
            new Song(getUUID(), "Do You Want to Know a Secret", 1964, 2),
            new Song(getUUID(), "Ain't She Sweet", 1964, 19),
            new Song(getUUID(), "A Hard Day's Night", 1964, 1),
            new Song(getUUID(), "I'll Cry Instead", 1964, 25),
            new Song(getUUID(), "And I Love Her", 1964, 12),
            new Song(getUUID(), "Matchbox", 1964, 17),
            new Song(getUUID(), "I Feel Fine", 1964, 1),
            new Song(getUUID(), "Eight Days a Week", 1965, 1),
            new Song(getUUID(), "Ticket to Ride", 1965, 1),
            new Song(getUUID(), "Help", 1965, 1),
            new Song(getUUID(), "Yesterday", 1965, 1),
            new Song(getUUID(), "Boys", 1965, 102),
            new Song(getUUID(), "We Can Work It Out", 1965, 1),
            new Song(getUUID(), "Nowhere Man", 1966, 3),
            new Song(getUUID(), "Paperback Writer", 1966, 1),
            new Song(getUUID(), "Yellow Submarine", 1966, 2),
            new Song(getUUID(), "Penny Lane", 1967, 1),
            new Song(getUUID(), "All You Need Is Love", 1967, 1),
            new Song(getUUID(), "Hello Goodbye", 1967, 1),
            new Song(getUUID(), "Lady Madonna", 1968, 4),
            new Song(getUUID(), "Hey Jude", 1968, 1),
            new Song(getUUID(), "Get Back", 1969, 1),
            new Song(getUUID(), "The Ballad of John and Yoko", 1969, 8),
            new Song(getUUID(), "Something", 1969, 3),
            new Song(getUUID(), "Let It Be", 1970, 1),
            new Song(getUUID(), "The Long and Winding Road", 1970, 1),
            new Song(getUUID(), "Got to Get You into My Life", 1976, 7),
            new Song(getUUID(), "Ob-La-Di, Ob-La-Da", 1976, 49),
            new Song(getUUID(), "Sgt. Pepper's Lonely Hearts Club Band", 1978, 71),
            new Song(getUUID(), "The Beatles Movie Medley", 1982, 12),
            new Song(getUUID(), "Baby It's You", 1995, 67),
            new Song(getUUID(), "Free as a Bird", 1995, 6),
            new Song(getUUID(), "Real Love", 1996, 11));

    static void writeData(DatabaseClient dbClient) {
        List<Mutation> mutations = new ArrayList<>();
        for (Song song : SONGS) {
            mutations.add(
                Mutation.newInsertBuilder("Songs")
                    .set("SongId")
                    .to(song.songId)
                    .set("Title")
                    .to(song.title)
                    .set("Year")
                    .to(song.year)
                    .set("Peak")
                    .to(song.peak)
                    .build());
        }
        dbClient.write(mutations);
    }

    public static void main(String[] args) throws Exception {
        // Authenticating with Google Application Default Credentials
        try {
            GoogleCredentials.getApplicationDefault();
        } catch (IOException e) {
            // Panic and leave ;)
            e.printStackTrace();
            return;
        }
        SpannerOptions options = SpannerOptions.newBuilder().build();
        Spanner spanner = options.getService();
        String instanceId = "cloudspanner-codelab";
        String databaseId = "demo";

        try {
            DatabaseClient dbClient = spanner.getDatabaseClient(DatabaseId.of(options.getProjectId(), instanceId, databaseId));
            writeData(dbClient);
        } finally {
            spanner.close();
        }
    }

    public static String getUUID() {
        return java.util.UUID.randomUUID().toString();
    }
}

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

$ mvn package

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

$ java -jar target/cloudspanner101-1.0-SNAPSHOT-jar-with-dependencies.jar

This may take a little while but you can monitor your progress by watching the database contents on the Cloud Console. You can click on the Songs table and on the data tab as shown below:

Now that we've set up our database and loaded information into a table, let's run some simple queries. First, let's verify the values we just wrote to our new table. Select the demo database and then the query tool:

Then enter the SQL query SELECT * FROM Songs; and click the "Run query" button.

This should show all records we wrote in our simple app.

Now imagine we want to ask the following three questions:

  1. How many singles did the Beatles release in the US?
  2. How many number one singles did the Beatles release in the US? And what were the titles of those top sellers?
  3. In which year did the Beatles release the most US singles and the most number one singles?

We'll answer each of these questions using SQL queries submitted via the Cloud Spanner query tool on the Cloud Console.

To determine the total number of singles released by The Beatles in the US, we simply need to count the number of rows in the songs table (since all the songs we wrote into the database are from the Beatles).

Then paste and run this query into the query tool:

SELECT count(*) FROM Songs;

This query tells us The Beatles released 44 singles in the US.

For our next query, we'll count the number of singles released in the US, which rose to the top position on the charts. This is easy to do by extending the query with a "where" clause, like this:

Paste and run this query into the query tool: SELECT COUNT(*) FROM Songs WHERE peak = 1;

This query reveals 19 singles rose to the top position on the US charts. If we wanted to identify those top selling singles, we need only replace "COUNT(*)" with "Title,year", like this:

Finally, let's determine the year in which The Beatles released the most singles in the US:

SELECT year, count(*) AS releases

FROM Songs

GROUP BY year

ORDER BY COUNT(*) DESC

LIMIT 1;

This shows us that 1964 was a very productive year for The Beatles, as they released 12 singles into the US market, the most of any year in their career. If we limit the query to include only number one hits and remove the limit clause to display all results (not just the top result), we get:

SELECT year, count(*) AS hits

FROM Songs

WHERE peak = 1

GROUP BY year

ORDER BY COUNT(*) DESC;

This query shows us that 1965 was the year in which The Beatles enjoyed their most chart toppers (5), along with the number of top hits for all the other years in which they released singles in the United States.

After all the fun playing with Cloud Spanner we need to cleanup our playground, saving precious resources and money. Luckily this is an easy step, just go into the developer Console and delete the instance we created in the 4th step.

What we've covered:

Next Steps: