1. Обзор
Google Cloud Spanner — это полностью управляемый, горизонтально масштабируемый, глобально распределенный сервис реляционных баз данных, обеспечивающий ACID-транзакции и семантику SQL без ущерба для производительности и высокой доступности.
В этой лабораторной работе вы научитесь настраивать экземпляр Cloud Spanner. Вы пройдете этапы создания базы данных и схемы, которые можно использовать для игровой таблицы лидеров. Вы начнете с создания таблицы Players для хранения информации об игроках и таблицы Scores для хранения результатов игроков.
Далее вы заполните таблицы примерами данных. Затем завершите лабораторную работу, выполнив несколько примеров запросов из списка «Топ-10», и, наконец, удалите экземпляр, чтобы освободить ресурсы.
Что вы узнаете
- Как настроить экземпляр Cloud Spanner.
- Как создать базу данных и таблицы.
- Как использовать столбец с меткой времени фиксации транзакции.
- Как загрузить данные с временными метками в таблицу базы данных Cloud Spanner.
- Как выполнить запросы к базе данных Cloud Spanner.
- Как удалить свой экземпляр Cloud Spanner.
Что вам понадобится
Как вы будете использовать этот учебный материал?
Как бы вы оценили свой опыт работы с платформой Google Cloud Platform?
2. Настройка и требования
Настройка среды для самостоятельного обучения
Если у вас еще нет учетной записи Google (Gmail или Google Apps), вам необходимо ее создать . Войдите в консоль Google Cloud Platform ( console.cloud.google.com ) и создайте новый проект.
Если у вас уже есть проект, щелкните раскрывающееся меню выбора проекта в левом верхнем углу консоли:
и нажмите кнопку «СОЗДАТЬ ПРОЕКТ» в появившемся диалоговом окне, чтобы создать новый проект:
Если у вас ещё нет проекта, вы увидите диалоговое окно, подобное этому, для создания вашего первого проекта:
В появившемся диалоговом окне создания проекта вы можете ввести подробные сведения о вашем новом проекте:
Запомните идентификатор проекта (Project ID), который является уникальным именем для всех проектов Google Cloud (указанное выше имя уже занято и вам не подойдёт, извините!). В дальнейшем в этом практическом занятии он будет обозначаться как PROJECT_ID .
Далее, если вы еще этого не сделали, вам необходимо включить оплату в консоли разработчика, чтобы использовать ресурсы Google Cloud и активировать API Cloud Spanner .
Выполнение этого практического задания не должно обойтись вам дороже нескольких долларов, но может стоить больше, если вы решите использовать больше ресурсов или оставите их запущенными (см. раздел «очистка» в конце этого документа). Информация о ценах на Google Cloud Spanner приведена здесь .
Новые пользователи Google Cloud Platform могут воспользоваться бесплатной пробной версией стоимостью 300 долларов , что сделает этот практический семинар совершенно бесплатным.
Настройка Google Cloud Shell
Хотя Google Cloud и Spanner можно запускать удаленно с ноутбука, в этом практическом занятии мы будем использовать Google Cloud Shell — среду командной строки, работающую в облаке.
Эта виртуальная машина на базе Debian содержит все необходимые инструменты разработки. Она предоставляет постоянный домашний каталог размером 5 ГБ и работает в облаке Google, что значительно повышает производительность сети и аутентификацию. Это означает, что для выполнения этого практического задания вам понадобится только браузер (да, он работает и на Chromebook).
- Для активации Cloud Shell из консоли Cloud Console просто нажмите «Активировать Cloud Shell».
(На подготовку и подключение к среде должно уйти всего несколько минут).
После подключения к Cloud Shell вы увидите, что ваша аутентификация пройдена и проект уже настроен на ваш PROJECT_ID .
gcloud auth list
вывод команды
Credentialed accounts: - <myaccount>@<mydomain>.com (active)
gcloud config list project
вывод команды
[core] project = <PROJECT_ID>
Если по какой-либо причине проект не создан, просто выполните следующую команду:
gcloud config set project <PROJECT_ID>
Ищете свой PROJECT_ID ? Проверьте, какой ID вы использовали на этапах настройки, или найдите его на панели управления Cloud Console:
Cloud Shell также по умолчанию устанавливает некоторые переменные среды, которые могут быть полезны при выполнении будущих команд.
echo $GOOGLE_CLOUD_PROJECT
вывод команды
<PROJECT_ID>
- Наконец, установите зону по умолчанию и конфигурацию проекта.
gcloud config set compute/zone us-central1-f
Вы можете выбрать различные зоны. Для получения дополнительной информации см. раздел «Регионы и зоны» .
Краткое содержание
На этом этапе вы настраиваете свою среду.
Далее
Далее вам потребуется настроить экземпляр Cloud Spanner.
3. Настройка экземпляра Cloud Spanner
На этом этапе мы настраиваем экземпляр Cloud Spanner для данного практического занятия. Найдите запись Spanner. 
в левом верхнем углу меню гамбургеров 
или найдите «Spanner», нажав клавишу «/» и введя «Spanner».
Далее нажмите на 
Заполните форму, указав имя экземпляра cloudspanner-leaderboard для вашего экземпляра, выбрав конфигурацию (выберите региональный экземпляр) и указав количество узлов. Для этого практического занятия нам понадобится только 1 узел. Для производственных экземпляров и для соответствия требованиям соглашения об уровне обслуживания Cloud Spanner вам потребуется запустить 3 или более узлов в вашем экземпляре Cloud Spanner.
И наконец, нажмите кнопку «Создать», и через несколько секунд у вас будет в распоряжении экземпляр Cloud Spanner.
На следующем шаге мы воспользуемся клиентской библиотекой Go для создания базы данных и схемы в нашем новом экземпляре.
4. Создайте базу данных и схему.
На этом этапе мы создадим нашу тестовую базу данных и схему.
Давайте воспользуемся клиентской библиотекой Go для создания двух таблиц: таблицы Players для информации об игроках и таблицы Scores для хранения результатов игроков. Для этого мы пройдемся по шагам создания консольного приложения Go в Cloud Shell.
Сначала клонируйте пример кода для этого практического занятия с Github, введя следующую команду в Cloud Shell:
export GO111MODULE=auto
go get -u github.com/GoogleCloudPlatform/golang-samples/spanner/...
Затем перейдите в каталог "leaderboard", где вы создадите своё приложение.
cd gopath/src/github.com/GoogleCloudPlatform/golang-samples/spanner/spanner_leaderboard
Весь код, необходимый для этой практической работы, находится в существующей директории golang-samples/spanner/spanner_leaderboard/ в виде исполняемого приложения Go под названием leaderboard , которое будет служить вам справочным материалом по мере прохождения работы. Мы создадим новую директорию и будем поэтапно собирать копию приложения Leaderboard.
Создайте для приложения новую директорию с именем "codelab" и перейдите в неё с помощью следующей команды:
mkdir codelab && cd $_
Теперь давайте создадим простое приложение на Go под названием "Таблица лидеров", которое использует клиентскую библиотеку Spanner для создания таблицы лидеров, состоящей из двух таблиц: "Игроки" и "Результаты". Это можно сделать прямо в редакторе Cloud Shell:
Откройте редактор Cloud Shell, нажав на значок «Открыть редактор», выделенный ниже:
Создайте файл с именем "leaderboard.go" в папке ~/gopath/src/github.com/GoogleCloudPlatform/golang-samples/spanner/codelab.
- Для начала убедитесь, что в списке папок редактора Cloud Shell выбрана папка "codelab".
- Затем выберите «Новый файл» в меню «Файл» редактора Cloud Shell.
- Введите "leaderboard.go" в качестве имени для нового файла.
Это основной файл приложения, который будет содержать код приложения и ссылки на все необходимые зависимости.
Для создания базы данных таблицы leaderboard , а также таблиц Players и Scores , скопируйте (Ctrl + P) и вставьте (Ctrl + V) следующий код Go в файл leaderboard.go :
package main
import (
"context"
"flag"
"fmt"
"io"
"log"
"os"
"regexp"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
type adminCommand func(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error
func createDatabase(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, db string) error {
matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
if matches == nil || len(matches) != 3 {
return fmt.Errorf("Invalid database id %s", db)
}
op, err := adminClient.CreateDatabase(ctx, &adminpb.CreateDatabaseRequest{
Parent: matches[1],
CreateStatement: "CREATE DATABASE `" + matches[2] + "`",
ExtraStatements: []string{
`CREATE TABLE Players(
PlayerId INT64 NOT NULL,
PlayerName STRING(2048) NOT NULL
) PRIMARY KEY(PlayerId)`,
`CREATE TABLE Scores(
PlayerId INT64 NOT NULL,
Score INT64 NOT NULL,
Timestamp TIMESTAMP NOT NULL
OPTIONS(allow_commit_timestamp=true)
) PRIMARY KEY(PlayerId, Timestamp),
INTERLEAVE IN PARENT Players ON DELETE NO ACTION`,
},
})
if err != nil {
return err
}
if _, err := op.Wait(ctx); err != nil {
return err
}
fmt.Fprintf(w, "Created database [%s]\n", db)
return nil
}
func createClients(ctx context.Context, db string) (*database.DatabaseAdminClient, *spanner.Client) {
adminClient, err := database.NewDatabaseAdminClient(ctx)
if err != nil {
log.Fatal(err)
}
dataClient, err := spanner.NewClient(ctx, db)
if err != nil {
log.Fatal(err)
}
return adminClient, dataClient
}
func run(ctx context.Context, adminClient *database.DatabaseAdminClient, dataClient *spanner.Client, w io.Writer,
cmd string, db string, timespan int) error {
// createdatabase command
if cmd == "createdatabase" {
err := createDatabase(ctx, w, adminClient, db)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
return nil
}
func main() {
flag.Usage = func() {
fmt.Fprintf(os.Stderr, `Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
`)
}
flag.Parse()
flagCount := len(flag.Args())
if flagCount != 2 {
flag.Usage()
os.Exit(2)
}
cmd, db := flag.Arg(0), flag.Arg(1)
// Set timespan to zero, as it's not currently being used
var timespan int = 0
ctx, cancel := context.WithTimeout(context.Background(), 1*time.Minute)
defer cancel()
adminClient, dataClient := createClients(ctx, db)
if err := run(ctx, adminClient, dataClient, os.Stdout, cmd, db, timespan); err != nil {
os.Exit(1)
}
}
Сохраните изменения, внесенные в файл leaderboard.go , выбрав пункт «Сохранить» в меню «Файл» редактора Cloud Shell.
Вы можете использовать файл leaderboard.go в каталоге golang-samples/spanner/spanner_leaderboard чтобы увидеть пример того, как должен выглядеть ваш файл leaderboard.go после добавления кода, включающего команду createdatabase .
Чтобы собрать приложение в Cloud Shell, запустите команду "go build" из каталога codelab , где находится файл leaderboard.go :
go build leaderboard.go
После успешной сборки приложения запустите его в Cloud Shell, введя следующую команду:
./leaderboard
В результате вы должны увидеть примерно следующее:
Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
Из этого ответа видно, что это приложение Leaderboard , которое в настоящее время имеет одну возможную команду: createdatabase . Мы видим, что ожидаемым аргументом команды createdatabase является строка, содержащая конкретный идентификатор экземпляра и идентификатор базы данных.
Теперь выполните следующую команду. Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard createdatabase projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
Через пару секунд вы должны увидеть ответ примерно следующего вида:
Created database [projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard]
В разделе обзора баз данных Cloud Spanner в консоли Cloud Console вы должны увидеть свою новую базу данных и таблицы в меню слева.
На следующем этапе мы обновим наше приложение, чтобы загрузить некоторые данные в вашу новую базу данных.
5. Загрузка данных
Теперь у нас есть база данных под названием leaderboard , содержащая две таблицы: Players и Scores . Давайте воспользуемся клиентской библиотекой Go, чтобы заполнить таблицу Players именами игроков, а таблицу Scores — случайными результатами для каждого игрока.
Если редактор Cloud Shell еще не открыт, откройте его, щелкнув по выделенному ниже значку:
Далее отредактируйте файл leaderboard.go в редакторе Cloud Shell, добавив команду insertplayers , которая позволяет вставить 100 игроков в таблицу Players . Мы также добавим команду insertscores , которая позволяет вставить 4 случайных результата в таблицу Scores для каждого игрока из таблицы Players .
Сначала обновите раздел imports в верхней части файла leaderboard.go , заменив существующий код, чтобы после завершения работы файл выглядел следующим образом:
import (
"context"
"flag"
"fmt"
"io"
"log"
"math/rand"
"os"
"regexp"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
"google.golang.org/api/iterator"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
Далее добавьте новый тип команды вместе со списком команд в верхней части файла, сразу под строкой, начинающейся с "type adminCommand ...", так что после завершения работы файл должен выглядеть следующим образом:
type adminCommand func(ctx context.Context, w io.Writer, adminClient *database.DatabaseAdminClient, database string) error
type command func(ctx context.Context, w io.Writer, client *spanner.Client) error
var (
commands = map[string]command{
"insertplayers": insertPlayers,
"insertscores": insertScores,
}
)
Далее добавьте следующие функции insertPlayers и insertScores ниже существующей функции createdatabase() :
func insertPlayers(ctx context.Context, w io.Writer, client *spanner.Client) error {
// Get number of players to use as an incrementing value for each PlayerName to be inserted
stmt := spanner.Statement{
SQL: `SELECT Count(PlayerId) as PlayerCount FROM Players`,
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
row, err := iter.Next()
if err != nil {
return err
}
var numberOfPlayers int64 = 0
if err := row.Columns(&numberOfPlayers); err != nil {
return err
}
// Initialize values for random PlayerId
rand.Seed(time.Now().UnixNano())
min := 1000000000
max := 9000000000
// Insert 100 player records into the Players table
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmts := []spanner.Statement{}
for i := 1; i <= 100; i++ {
numberOfPlayers++
playerID := rand.Intn(max-min) + min
playerName := fmt.Sprintf("Player %d", numberOfPlayers)
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Players
(PlayerId, PlayerName)
VALUES (@playerID, @playerName)`,
Params: map[string]interface{}{
"playerID": playerID,
"playerName": playerName,
},
})
}
_, err := txn.BatchUpdate(ctx, stmts)
if err != nil {
return err
}
return nil
})
fmt.Fprintf(w, "Inserted players \n")
return nil
}
func insertScores(ctx context.Context, w io.Writer, client *spanner.Client) error {
playerRecordsFound := false
// Create slice for insert statements
stmts := []spanner.Statement{}
// Select all player records
stmt := spanner.Statement{SQL: `SELECT PlayerId FROM Players`}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
// Insert 4 score records into the Scores table for each player in the Players table
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
playerRecordsFound = true
var playerID int64
if err := row.ColumnByName("PlayerId", &playerID); err != nil {
return err
}
// Initialize values for random score and date
rand.Seed(time.Now().UnixNano())
min := 1000
max := 1000000
for i := 0; i < 4; i++ {
// Generate random score between 1,000 and 1,000,000
score := rand.Intn(max-min) + min
// Generate random day within the past two years
now := time.Now()
endDate := now.Unix()
past := now.AddDate(0, -24, 0)
startDate := past.Unix()
randomDateInSeconds := rand.Int63n(endDate-startDate) + startDate
randomDate := time.Unix(randomDateInSeconds, 0)
// Add insert statement to stmts slice
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Scores
(PlayerId, Score, Timestamp)
VALUES (@playerID, @score, @timestamp)`,
Params: map[string]interface{}{
"playerID": playerID,
"score": score,
"timestamp": randomDate,
},
})
}
}
if !playerRecordsFound {
fmt.Fprintln(w, "No player records currently exist. First insert players then insert scores.")
} else {
_, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
// Commit insert statements for all scores to be inserted as a single transaction
_, err := txn.BatchUpdate(ctx, stmts)
return err
})
if err != nil {
return err
}
fmt.Fprintln(w, "Inserted scores")
}
return nil
}
Затем, чтобы команда insert заработала, добавьте следующий код в функцию `run` вашего приложения ниже оператора обработки createdatabase , заменив оператор ` return nil :
// insert and query commands
cmdFn := commands[cmd]
if cmdFn == nil {
flag.Usage()
os.Exit(2)
}
err := cmdFn(ctx, w, dataClient)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
После завершения работы функция run должна выглядеть следующим образом:
func run(ctx context.Context, adminClient *database.DatabaseAdminClient, dataClient *spanner.Client, w io.Writer,
cmd string, db string, timespan int) error {
// createdatabase command
if cmd == "createdatabase" {
err := createDatabase(ctx, w, adminClient, db)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
// insert and query commands
cmdFn := commands[cmd]
if cmdFn == nil {
flag.Usage()
os.Exit(2)
}
err := cmdFn(ctx, w, dataClient)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
Последний шаг для добавления функции "вставки" в ваше приложение — это добавление справочного текста для команд "insertplayers" и "insertscores" в функцию flag.Usage() . Добавьте следующий справочный текст в функцию flag.Usage() , чтобы включить справочный текст для команд вставки:
Добавьте эти две команды в список возможных команд:
Command can be one of: createdatabase, insertplayers, insertscores
А ниже текста справки для команды createdatabase добавьте этот дополнительный справочный текст.
leaderboard insertplayers projects/my-project/instances/my-instance/databases/example-db
- Insert 100 sample Player records into the database.
leaderboard insertscores projects/my-project/instances/my-instance/databases/example-db
- Insert sample score data into Scores sample Cloud Spanner database table.
Сохраните изменения, внесенные в файл leaderboard.go , выбрав пункт «Сохранить» в меню «Файл» редактора Cloud Shell.
Вы можете использовать файл leaderboard.go в каталоге golang-samples/spanner/spanner_leaderboard чтобы увидеть пример того, как должен выглядеть ваш файл leaderboard.go после добавления кода, включающего команды insertplayers и insertscores .
Теперь давайте соберем и запустим приложение, чтобы убедиться, что новые команды insertplayers и insertscores включены в список возможных команд приложения. Для сборки приложения выполните следующую команду:
go build leaderboard.go
Запустите полученное приложение в облачной оболочке, введя следующую команду:
./leaderboard
В стандартном выводе приложения теперь должны отображаться команды insertplayers и insertscores :
Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase, insertplayers, insertscores
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
leaderboard insertplayers projects/my-project/instances/my-instance/databases/example-db
- Insert 100 sample Player records into the database.
leaderboard insertscores projects/my-project/instances/my-instance/databases/example-db
- Insert sample score data into Scores sample Cloud Spanner database table.
Теперь давайте запустим команду insertplayers с теми же значениями аргументов, которые мы использовали при вызове команды createdatabase . Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard insertplayers projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
Через пару секунд вы должны увидеть ответ примерно следующего вида:
Inserted players
Теперь воспользуемся клиентской библиотекой Go, чтобы заполнить нашу таблицу Scores четырьмя случайными результатами вместе с временными метками для каждого игрока из таблицы Players .
Столбец Timestamp таблицы Scores был определен как столбец "метки времени подтверждения" с помощью следующего SQL-запроса, который был выполнен при предыдущем запуске команды create :
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
Обратите внимание на атрибут OPTIONS(allow_commit_timestamp=true) . Он превращает столбец Timestamp в столбец с «меткой времени подтверждения» и позволяет автоматически заполнять его точной меткой времени транзакции для операций INSERT и UPDATE в заданной строке таблицы.
Вы также можете вставлять собственные значения временных меток в столбец "временная метка фиксации", если вы вставляете метку времени, значение которой находится в прошлом, что мы и сделаем для целей этого практического занятия.
Теперь давайте запустим команду insertscores с теми же значениями аргументов, которые мы использовали при вызове команды insertplayers . Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard insertscores projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
Через пару секунд вы должны увидеть ответ примерно следующего вида:
Inserted scores
Для запуска функции insertScores используется следующий фрагмент кода, который вставляет случайно сгенерированную метку времени, относящуюся к прошлому:
now := time.Now()
endDate := now.Unix()
past := now.AddDate(0, -24, 0)
startDate := past.Unix()
randomDateInSeconds := rand.Int63n(endDate-startDate) + startDate
randomDate := time.Unix(randomDateInSeconds, 0)
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Scores
(PlayerId, Score, Timestamp)
VALUES (@playerID, @score, @timestamp)`,
Params: map[string]interface{}{
"playerID": playerID,
"score": score,
"timestamp": randomDate,
},
})
Чтобы автоматически заполнить столбец Timestamp меткой времени, точно соответствующей моменту выполнения транзакции "Insert", можно использовать константу Go spanner.CommitTimestamp как показано в следующем фрагменте кода:
...
stmts = append(stmts, spanner.Statement{
SQL: `INSERT INTO Scores
(PlayerId, Score, Timestamp)
VALUES (@playerID, @score, @timestamp)`,
Params: map[string]interface{}{
"playerID": playerID,
"score": score,
"timestamp": spanner.CommitTimestamp,
},
})
Теперь, когда загрузка данных завершена, давайте проверим значения, которые мы только что записали в наши новые таблицы в разделе Cloud Spanner консоли Cloud. Сначала выберите базу данных таблицы leaderboard , а затем таблицу Players . Перейдите на вкладку Data . Вы должны увидеть, что данные есть в столбцах PlayerId и PlayerName этой таблицы.
Теперь давайте проверим, есть ли данные и в таблице «Счета». Для этого щелкните по таблице Scores и выберите вкладку Data . Вы должны увидеть данные в столбцах PlayerId , Timestamp и Score .
Отлично! Давайте обновим наше приложение, чтобы оно выполняло несколько запросов, которые мы сможем использовать для создания игровой таблицы лидеров.
6. Выполните запросы к таблице лидеров.
Теперь, когда мы создали базу данных и загрузили информацию в таблицы, давайте создадим таблицу лидеров, используя эти данные. Для этого нам нужно ответить на следующие четыре вопроса:
- Какие игроки входят в "десятку лучших" всех времен?
- Какие игроки входят в "Топ-10" года?
- Какие игроки вошли в «Топ-10» месяца?
- Какие игроки вошли в «Топ-10» недели?
Давайте обновим наше приложение, чтобы оно выполняло SQL-запросы, которые ответят на эти вопросы.
Мы добавим команду query и команду queryWithTimespan , которые позволят выполнять запросы для ответа на вопросы, необходимые для формирования таблицы лидеров.
Отредактируйте файл leaderboard.go в редакторе Cloud Shell, чтобы обновить приложение и добавить команду query и команду queryWithTimespan . Мы также добавим вспомогательную функцию formatWithCommas для форматирования результатов с использованием запятых.
Сначала обновите раздел imports в верхней части файла leaderboard.go , заменив существующий код, чтобы после завершения работы файл выглядел следующим образом:
import (
"bytes"
"context"
"flag"
"fmt"
"io"
"log"
"math/rand"
"os"
"regexp"
"strconv"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
"google.golang.org/api/iterator"
adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)
Далее добавьте следующие две функции и вспомогательную функцию ниже существующего метода insertScores :
func query(ctx context.Context, w io.Writer, client *spanner.Client) error {
stmt := spanner.Statement{
SQL: `SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp
FROM Players p
JOIN Scores s ON p.PlayerId = s.PlayerId
ORDER BY s.Score DESC LIMIT 10`}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var playerID, score int64
var playerName string
var timestamp time.Time
if err := row.Columns(&playerID, &playerName, &score, ×tamp); err != nil {
return err
}
fmt.Fprintf(w, "PlayerId: %d PlayerName: %s Score: %s Timestamp: %s\n",
playerID, playerName, formatWithCommas(score), timestamp.String()[0:10])
}
}
func queryWithTimespan(ctx context.Context, w io.Writer, client *spanner.Client, timespan int) error {
stmt := spanner.Statement{
SQL: `SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp
FROM Players p
JOIN Scores s ON p.PlayerId = s.PlayerId
WHERE s.Timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL @Timespan HOUR)
ORDER BY s.Score DESC LIMIT 10`,
Params: map[string]interface{}{"Timespan": timespan},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var playerID, score int64
var playerName string
var timestamp time.Time
if err := row.Columns(&playerID, &playerName, &score, ×tamp); err != nil {
return err
}
fmt.Fprintf(w, "PlayerId: %d PlayerName: %s Score: %s Timestamp: %s\n",
playerID, playerName, formatWithCommas(score), timestamp.String()[0:10])
}
}
func formatWithCommas(n int64) string {
numberAsString := strconv.FormatInt(n, 10)
numberLength := len(numberAsString)
if numberLength < 4 {
return numberAsString
}
var buffer bytes.Buffer
comma := []rune(",")
bufferPosition := numberLength % 3
if (bufferPosition) > 0 {
bufferPosition = 3 - bufferPosition
}
for i := 0; i < numberLength; i++ {
if bufferPosition == 3 {
buffer.WriteRune(comma[0])
bufferPosition = 0
}
bufferPosition++
buffer.WriteByte(numberAsString[i])
}
return buffer.String()
}
Далее, в верхней части файла leaderboard.go добавьте параметр "query" в качестве одного из параметров команды в переменную commands , сразу после параметра " insertscores": insertScores , чтобы переменная commands выглядела следующим образом:
var (
commands = map[string]command{
"insertplayers": insertPlayers,
"insertscores": insertScores,
"query": query,
}
)
Далее добавьте параметр команды "queryWithTimespan" в функцию run , ниже раздела команды "createdatabase" и выше раздела обработки команд "insert and query":
// querywithtimespan command
if cmd == "querywithtimespan" {
err := queryWithTimespan(ctx, w, dataClient, timespan)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
После завершения работы функция run должна выглядеть следующим образом:
func run(ctx context.Context, adminClient *database.DatabaseAdminClient, dataClient *spanner.Client, w io.Writer,
cmd string, db string, timespan int) error {
// createdatabase command
if cmd == "createdatabase" {
err := createDatabase(ctx, w, adminClient, db)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
// querywithtimespan command
if cmd == "querywithtimespan" {
if timespan == 0 {
flag.Usage()
os.Exit(2)
}
err := queryWithTimespan(ctx, w, dataClient, timespan)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
// insert and query commands
cmdFn := commands[cmd]
if cmdFn == nil {
flag.Usage()
os.Exit(2)
}
err := cmdFn(ctx, w, dataClient)
if err != nil {
fmt.Fprintf(w, "%s failed with %v", cmd, err)
}
return err
}
Затем, чтобы команда queryWithTimespan работала, обновите блок кода flag.Parse() в методе "main" вашего приложения так, чтобы он выглядел следующим образом:
flag.Parse()
flagCount := len(flag.Args())
if flagCount < 2 || flagCount > 3 {
flag.Usage()
os.Exit(2)
}
cmd, db := flag.Arg(0), flag.Arg(1)
// If query timespan flag is specified, parse to int
var timespan int = 0
if flagCount == 3 {
parsedTimespan, err := strconv.Atoi(flag.Arg(2))
if err != nil {
fmt.Println(err)
os.Exit(2)
}
timespan = parsedTimespan
}
ctx, cancel := context.WithTimeout(context.Background(), 1*time.Minute)
defer cancel()
adminClient, dataClient := createClients(ctx, db)
if err := run(ctx, adminClient, dataClient, os.Stdout, cmd, db, timespan); err != nil {
os.Exit(1)
}
Последний шаг для добавления функциональности «запросов» в ваше приложение — это добавление справочного текста для команд «query» и «querywithtimespan» в функцию flag.Usage() . Добавьте следующие строки кода в функцию flag.Usage() , чтобы включить справочный текст для команд запроса:
Добавьте две команды типа «query» в список возможных команд:
Command can be one of: createdatabase, insertplayers, insertscores, query, querywithtimespan
А ниже текста справки для команды insertscores добавьте этот дополнительный текст справки.
leaderboard query projects/my-project/instances/my-instance/databases/example-db
- Query players with top ten scores of all time.
leaderboard querywithtimespan projects/my-project/instances/my-instance/databases/example-db 168
- Query players with top ten scores within a timespan specified in hours.
Сохраните изменения, внесенные в файл leaderboard.go , выбрав пункт «Сохранить» в меню «Файл» редактора Cloud Shell.
Вы можете использовать файл leaderboard.go в каталоге golang-samples/spanner/spanner_leaderboard чтобы увидеть пример того, как должен выглядеть ваш файл leaderboard.go после добавления кода, включающего команды query и querywithtimespan .
Теперь давайте скомпилируем и запустим приложение, чтобы убедиться, что новые команды query и querywithtimespan включены в список возможных команд приложения.
Для сборки приложения выполните следующую команду в Cloud Shell:
go build leaderboard.go
Запустите полученное приложение в облачной оболочке, введя следующую команду:
./leaderboard
В стандартном выводе приложения теперь должны отображаться новые параметры команд: команды query и querywithtimespan
Usage: leaderboard <command> <database_name> [command_option]
Command can be one of: createdatabase, insertplayers, insertscores, query, querywithtimespan
Examples:
leaderboard createdatabase projects/my-project/instances/my-instance/databases/example-db
- Create a sample Cloud Spanner database along with sample tables in your project.
leaderboard insertplayers projects/my-project/instances/my-instance/databases/example-db
- Insert 100 sample Player records into the database.
leaderboard insertscores projects/my-project/instances/my-instance/databases/example-db
- Insert sample score data into Scores sample Cloud Spanner database table.
leaderboard query projects/my-project/instances/my-instance/databases/example-db
- Query players with top ten scores of all time.
leaderboard querywithtimespan projects/my-project/instances/my-instance/databases/example-db 168
- Query players with top ten scores within a timespan specified in hours.
Как видно из ответа, мы можем использовать команду query , чтобы получить список наших «Топ-10» игроков всех времен. Мы также видим, что команда querywithtimespan позволяет указать временной интервал в часах для фильтрации записей на основе их значения в столбце Timestamp таблицы « Scores .
Давайте выполним команду query , используя те же значения аргументов, что и при выполнении команды create . Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard query projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard
В ответ вы должны увидеть список десяти лучших игроков всех времен, примерно такой:
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
Теперь давайте запустим команду querywithtimespan с необходимыми аргументами, чтобы получить список «Топ-10» игроков года, указав «timespan», равный количеству часов в году, то есть 8760. Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 8760
В ответ вы должны увидеть список десяти лучших игроков года, например, такой:
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
Теперь давайте выполним команду querywithtimespan , чтобы получить список «Топ-10» игроков месяца, указав значение "timespan", равное количеству часов в месяце, то есть 730. Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 730
В ответ вы должны увидеть список из десяти лучших игроков месяца, примерно такой:
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
Теперь давайте выполним команду querywithtimespan , чтобы получить список «Топ-10» игроков недели, указав значение "timespan", равное количеству часов в неделе, то есть 168. Убедитесь, что вы заменили my-project на идентификатор проекта, который вы создали в начале этого практического занятия.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 168
Вы должны увидеть ответ, включающий «Топ-10» игроков недели, примерно следующего вида:
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
Отличная работа!
Теперь, по мере добавления записей, Cloud Spanner будет масштабировать вашу базу данных до необходимого вам размера. Независимо от того, насколько вырастет ваша база данных, таблица лидеров вашей игры сможет продолжать масштабироваться с высокой точностью благодаря Cloud Spanner и его технологии Truetime.
7. Уборка
После всех развлечений со Spanner нам нужно навести порядок на нашей площадке, сэкономив ценные ресурсы и деньги. К счастью, это простой шаг: просто зайдите в раздел Cloud Spanner в Cloud Console и удалите экземпляр, созданный нами на шаге практического задания под названием «Настройка экземпляра Cloud Spanner».
8. Поздравляем!
Что мы рассмотрели:
- Экземпляры Google Cloud Spanner, базы данных и схема таблиц для таблицы лидеров.
- Как создать консольное приложение на Go
- Как создать базу данных и таблицы Spanner с помощью клиентской библиотеки Go.
- Как загрузить данные в базу данных Spanner с помощью клиентской библиотеки Go.
- Как получить список из "Топ-10" результатов из ваших данных, используя временные метки коммитов Spanner и клиентскую библиотеку Go.
Следующие шаги:
- Ознакомьтесь с официальным документом Spanner CAP.
- Узнайте о проектировании схем и передовых методах работы с запросами.
- Узнайте больше о временных метках коммитов Cloud Spanner.
Оставьте свой отзыв.
- Пожалуйста, уделите несколько минут, чтобы пройти наш очень короткий опрос.