1. Обзор
Google Cloud Spanner — это полностью управляемая, горизонтально масштабируемая, глобально распределенная служба реляционных баз данных, которая обеспечивает транзакции ACID и семантику SQL без ущерба для производительности и высокой доступности.
В ходе этой лабораторной работы вы узнаете, как настроить экземпляр Cloud Spanner. Вы пройдете этапы создания базы данных и схемы, которые можно использовать для игровой таблицы лидеров. Вы начнете с создания таблицы Players для хранения информации об игроках и таблицы Scores для хранения результатов игроков.
Далее вы заполните таблицы примерами данных. Затем вы завершите лабораторную работу, выполнив несколько примеров запросов из первой десятки и, наконец, удалив экземпляр, чтобы освободить ресурсы.
Что вы узнаете
- Как настроить экземпляр Cloud Spanner.
- Как создать базу данных и таблицы.
- Как использовать столбец отметки времени фиксации.
- Как загрузить данные в таблицу базы данных Cloud Spanner с метками времени.
- Как запросить вашу базу данных Cloud Spanner.
- Как удалить экземпляр Cloud Spanner.
Что вам понадобится
Как вы будете использовать этот урок?
Как бы вы оценили свой опыт работы с Google Cloud Platform?
2. Настройка и требования
Самостоятельная настройка среды
Если у вас еще нет учетной записи Google (Gmail или Google Apps), вам необходимо ее создать . Войдите в консоль Google Cloud Platform ( console.cloud.google.com ) и создайте новый проект.
Если у вас уже есть проект, щелкните раскрывающееся меню выбора проекта в левом верхнем углу консоли:
и нажмите кнопку «НОВЫЙ ПРОЕКТ» в появившемся диалоговом окне, чтобы создать новый проект:
Если у вас еще нет проекта, вы должны увидеть подобное диалоговое окно, чтобы создать свой первый:
Последующий диалог создания проекта позволяет вам ввести детали вашего нового проекта:
Запомните идентификатор проекта, который является уникальным именем для всех проектов Google Cloud (имя, указанное выше, уже занято и не подойдет вам, извините!). Позже в этой лаборатории он будет называться PROJECT_ID .
Далее, если вы еще этого не сделали, вам необходимо включить выставление счетов в консоли разработчика, чтобы использовать ресурсы Google Cloud и включить Cloud Spanner API .
Выполнение этой кодовой лаборатории не должно стоить вам больше нескольких долларов, но может стоить больше, если вы решите использовать больше ресурсов или оставите их включенными (см. раздел «Очистка» в конце этого документа). Цены на Google Cloud Spanner указаны здесь .
Новые пользователи Google Cloud Platform имеют право на бесплатную пробную версию стоимостью 300 долларов США , что делает эту лабораторию кода совершенно бесплатной.
Настройка Google Cloud Shell
Хотя Google Cloud и Spanner можно управлять удаленно с вашего ноутбука, в этой лаборатории мы будем использовать Google Cloud Shell , среду командной строки, работающую в облаке.
Эта виртуальная машина на базе Debian оснащена всеми необходимыми инструментами разработки. Он предлагает постоянный домашний каталог объемом 5 ГБ и работает в Google Cloud, что значительно повышает производительность сети и аутентификацию. Это означает, что все, что вам понадобится для этой лаборатории кода, — это браузер (да, он работает на 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 ? Узнайте, какой идентификатор вы использовали на этапах настройки, или найдите его на панели управления 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 для этой лаборатории кода. Найдите запись о гаечном ключе 
в левом верхнем меню гамбургеров 
или найдите «Гаечный ключ», нажав «/» и введите «Гаечный ключ».
Далее нажмите 
и заполните форму, введя имя экземпляра Cloudpanner-leaderboard для вашего экземпляра, выбрав конфигурацию (выберите региональный экземпляр) и задав количество узлов, для этой кодовой лаборатории нам понадобится только 1 узел. Для рабочих экземпляров и для того, чтобы претендовать на соглашение об уровне обслуживания Cloud Spanner, вам потребуется запустить 3 или более узлов в вашем экземпляре Cloud Spanner.
И последнее, но не менее важное: нажмите «Создать», и через несколько секунд в вашем распоряжении будет экземпляр Cloud Spanner.
На следующем шаге мы собираемся использовать клиентскую библиотеку Go для создания базы данных и схемы в нашем новом экземпляре.
4. Создайте базу данных и схему.
На этом этапе мы создадим образец базы данных и схемы.
Давайте воспользуемся клиентской библиотекой Go для создания двух таблиц; таблица «Игроки» для информации об игроках и таблица «Счет» для хранения результатов игроков. Для этого мы рассмотрим этапы создания консольного приложения Go в Cloud Shell.
Сначала клонируйте пример кода для этой лаборатории кода из Github, введя следующую команду в Cloud Shell:
export GO111MODULE=auto
go get -u github.com/GoogleCloudPlatform/golang-samples/spanner/...
Затем измените каталог на каталог «таблица лидеров», где вы создадите свое приложение.
cd gopath/src/github.com/GoogleCloudPlatform/golang-samples/spanner/spanner_leaderboard
Весь код, необходимый для этой лаборатории кода, находится в существующем каталоге golang-samples/spanner/spanner_leaderboard/ в виде запускаемого приложения Go с именем leaderboard , которое будет служить справочной информацией по мере прохождения лаборатории кода. Мы создадим новый каталог и поэтапно создадим копию приложения Leaderboard.
Создайте для приложения новый каталог с именем «codelab» и перейдите в него с помощью следующей команды:
mkdir codelab && cd $_
Теперь давайте создадим базовое приложение Go под названием «Leaderboard», которое использует клиентскую библиотеку 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 меткой времени, когда именно происходит транзакция «Вставка», вы можете вместо этого вставить константу 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 облачной консоли. Сначала выберите базу данных leaderboard , а затем выберите таблицу Players . Откройте вкладку Data . Вы должны увидеть данные в столбцах PlayerId и PlayerName таблицы.
Далее давайте проверим, что таблица «Оценки» также содержит данные, щелкнув таблицу Scores и выбрав вкладку Data . Вы должны увидеть данные в столбцах PlayerId , Timestamp и Score таблицы.
Отличная работа! Давайте обновим наше приложение, чтобы оно выполняло несколько запросов, которые мы можем использовать для создания игровой таблицы лидеров.
6. Запускайте запросы к таблице лидеров
Теперь, когда мы настроили нашу базу данных и загрузили информацию в наши таблицы, давайте создадим таблицу лидеров, используя эти данные. Для этого нам необходимо ответить на следующие четыре вопроса:
- Какие игроки входят в «десятку лучших» всех времен?
- Какие игроки входят в «десятку лучших» года?
- Какие игроки входят в «десятку лучших» месяца?
- Какие игроки входят в «десятку лучших» недели?
Давайте обновим наше приложение для выполнения 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 под разделом команд «создать базу данных» и над разделом обработки команд «вставка и запрос»:
// 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() в «основном» методе вашего приложения, чтобы он выглядел следующим образом:
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() , чтобы включить текст справки для команд запроса:
Добавьте две команды запроса в список возможных команд:
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 , чтобы получить список наших «десяти лучших» игроков всех времен. Мы также видим, что команда 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 с необходимыми аргументами, чтобы запросить «Десять лучших» игроков года, указав «промежуток времени», равный количеству часов в году, равному 8760. Обязательно замените my-project на 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 , чтобы запросить «Десять лучших» игроков месяца, указав «промежуток времени», равный количеству часов в месяце, то есть 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 , чтобы запросить «Десять лучших» игроков недели, указав «промежуток времени», равный количеству часов в неделе, которое равно 168. Обязательно замените my-project идентификатором проекта, который вы создали в начало этой кодовой лаборатории.
./leaderboard querywithtimespan projects/my-project/instances/cloudspanner-leaderboard/databases/leaderboard 168
Вы должны увидеть ответ, включающий «десятку лучших» игроков недели, например:
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. Очистка
После всего удовольствия от игры со Спаннером нам нужно навести порядок на нашей игровой площадке, сэкономив драгоценные ресурсы и деньги. К счастью, это простой шаг: просто зайдите в раздел Cloud Spanner облачной консоли и удалите экземпляр, который мы создали на этапе лаборатории кода под названием «Настройка экземпляра Cloud Spanner».
8. Поздравляем!
Что мы рассмотрели:
- Экземпляры Google Cloud Spanner, базы данных и схема таблиц для таблицы лидеров
- Как создать консольное приложение Go
- Как создать базу данных и таблицы Spanner с помощью клиентской библиотеки Go
- Как загрузить данные в базу данных Spanner с помощью клиентской библиотеки Go
- Как запросить результаты «Десять лучших» из ваших данных, используя временные метки фиксации Spanner и клиентскую библиотеку Go
Следующие шаги:
- Прочтите технический документ Spanner CAP
- Узнайте о лучших практиках проектирования схем и запросов.
- Подробнее о временных метках фиксации Cloud Spanner…
Оставьте нам свой отзыв
- Пожалуйста, уделите немного времени и заполните наш очень короткий опрос.