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.
На следующем шаге мы воспользуемся клиентской библиотекой C# для создания базы данных и схемы в нашем новом экземпляре.
4. Создайте базу данных и схему.
На этом этапе мы создадим нашу тестовую базу данных и схему.
Давайте воспользуемся клиентской библиотекой C# для создания двух таблиц: таблицы Players для информации об игроках и таблицы Scores для хранения результатов игроков. Для этого мы пройдемся по шагам создания консольного приложения C# в Cloud Shell.
Сначала клонируйте пример кода для этого практического занятия с Github, введя следующую команду в Cloud Shell:
git clone https://github.com/GoogleCloudPlatform/dotnet-docs-samples.git
Затем перейдите в каталог "applications", где вы создадите своё приложение.
cd dotnet-docs-samples/applications/
Весь код, необходимый для этой практической работы, находится в существующей директории dotnet-docs-samples/applications/leaderboard в виде исполняемого приложения C# под названием Leaderboard , которое будет служить вам справочным материалом по мере прохождения работы. Мы создадим новую директорию и поэтапно скомпилируем копию приложения Leaderboard.
Создайте для приложения новую директорию с именем "codelab" и перейдите в неё с помощью следующей команды:
mkdir codelab && cd $_
Создайте новое консольное приложение .NET C# с именем "Leaderboard", используя следующую команду:
dotnet new console -n Leaderboard
Эта команда создает простое консольное приложение, состоящее из двух основных файлов: файла проекта Leaderboard.csproj и файла программы Program.cs .
Давайте запустим его. Перейдите в только что созданную директорию Leaderboard, где находится приложение:
cd Leaderboard
Затем введите следующую команду для запуска.
dotnet run
Вы должны увидеть вывод приложения "Hello World!".
Теперь давайте обновим наше консольное приложение, отредактировав файл Program.cs , чтобы использовать клиентскую библиотеку C# Spanner для создания таблицы лидеров, состоящей из двух таблиц: Players и Scores. Это можно сделать прямо в редакторе Cloud Shell:
Откройте редактор Cloud Shell, щелкнув по выделенному ниже значку:
Далее откройте файл Program.cs в редакторе Cloud Shell и замените существующий код файла кодом, необходимым для создания базы данных таблицы leaderboard , а также таблиц Players и Scores , вставив следующий код приложения C# в файл Program.cs :
using System;
using System.Threading.Tasks;
using Google.Cloud.Spanner.Data;
using CommandLine;
namespace GoogleCloudSamples.Leaderboard
{
[Verb("create", HelpText = "Create a sample Cloud Spanner database "
+ "along with sample 'Players' and 'Scores' tables in your project.")]
class CreateOptions
{
[Value(0, HelpText = "The project ID of the project to use "
+ "when creating Cloud Spanner resources.", Required = true)]
public string projectId { get; set; }
[Value(1, HelpText = "The ID of the instance where the sample database "
+ "will be created.", Required = true)]
public string instanceId { get; set; }
[Value(2, HelpText = "The ID of the sample database to create.",
Required = true)]
public string databaseId { get; set; }
}
public class Program
{
enum ExitCode : int
{
Success = 0,
InvalidParameter = 1,
}
public static object Create(string projectId,
string instanceId, string databaseId)
{
var response =
CreateAsync(projectId, instanceId, databaseId);
Console.WriteLine("Waiting for operation to complete...");
response.Wait();
Console.WriteLine($"Operation status: {response.Status}");
Console.WriteLine($"Created sample database {databaseId} on "
+ $"instance {instanceId}");
return ExitCode.Success;
}
public static async Task CreateAsync(
string projectId, string instanceId, string databaseId)
{
// Initialize request connection string for database creation.
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}";
using (var connection = new SpannerConnection(connectionString))
{
string createStatement = $"CREATE DATABASE `{databaseId}`";
string[] createTableStatements = new string[] {
// Define create table statement for Players table.
@"CREATE TABLE Players(
PlayerId INT64 NOT NULL,
PlayerName STRING(2048) NOT NULL
) PRIMARY KEY(PlayerId)",
// Define create table statement for Scores table.
@"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" };
// Make the request.
var cmd = connection.CreateDdlCommand(
createStatement, createTableStatements);
try
{
await cmd.ExecuteNonQueryAsync();
}
catch (SpannerException e) when
(e.ErrorCode == ErrorCode.AlreadyExists)
{
// OK.
}
}
}
public static int Main(string[] args)
{
var verbMap = new VerbMap<object>();
verbMap
.Add((CreateOptions opts) => Create(
opts.projectId, opts.instanceId, opts.databaseId))
.NotParsedFunc = (err) => 1;
return (int)verbMap.Run(args);
}
}
}
Для более наглядного представления кода программы, ниже приведена диаграмма программы с обозначением её основных компонентов:
Вы можете использовать файл Program.cs в каталоге dotnet-docs-samples/applications/leaderboard/step4 чтобы увидеть пример того, как должен выглядеть ваш файл Program.cs после добавления кода, включающего команду create .
Далее, используя редактор Cloud Shell, откройте и отредактируйте файл проекта программы Leaderboard.csproj , обновив его до следующего кода. Обязательно сохраните все изменения, используя меню «Файл» редактора Cloud Shell.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Google.Cloud.Spanner.Data" Version="3.3.0" />
</ItemGroup>
<ItemGroup>
<ProjectReference Include="..\..\..\commandlineutil\Lib\CommandLineUtil.csproj" />
</ItemGroup>
</Project>
Это изменение добавило ссылку на пакет NuGet C# Spanner Google.Cloud.Spanner.Data , необходимый для взаимодействия с API Cloud Spanner. Также добавлена ссылка на проект CommandLineUtil , входящий в репозиторий dotnet-doc-samples на Github, который предоставляет полезное расширение "verbmap" для библиотеки CommandLineParser с открытым исходным кодом — удобной библиотеки для обработки ввода командной строки в консольных приложениях.
Вы можете использовать файл Leaderboard.csproj расположенный в каталоге dotnet-docs-samples/applications/leaderboard/step4 чтобы увидеть пример того, как должен выглядеть ваш файл Leaderboard.csproj после добавления кода, включающего команду create .
Теперь вы готовы запустить обновленный пример. Введите следующее, чтобы увидеть стандартный ответ вашего обновленного приложения:
dotnet run
В результате вы должны увидеть примерно следующее:
Leaderboard 1.0.0 Copyright (C) 2018 Leaderboard ERROR(S): No verb selected. create Create a sample Cloud Spanner database along with sample 'Players' and 'Scores' tables in your project. help Display more information on a specific command. version Display version information.
Из этого ответа видно, что это приложение Leaderboard , которое можно запустить с помощью одной из трех возможных команд: create , help и version .
Давайте попробуем использовать команду create для создания базы данных и таблиц Spanner. Запустите команду без аргументов, чтобы увидеть ожидаемые аргументы команды.
dotnet run create
Вы должны увидеть ответ примерно следующего вида:
Leaderboard 1.0.0 Copyright (C) 2018 Leaderboard ERROR(S): A required value not bound to option name is missing. --help Display this help screen. --version Display version information. value pos. 0 Required. The project ID of the project to use when creating Cloud Spanner resources. value pos. 1 Required. The ID of the instance where the sample database will be created. value pos. 2 Required. The ID of the sample database to create.
Здесь мы видим, что ожидаемыми аргументами команды create являются идентификатор проекта, идентификатор экземпляра и идентификатор базы данных.
Теперь выполните следующую команду. Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run create PROJECT_ID cloudspanner-leaderboard leaderboard
Через пару секунд вы должны увидеть ответ примерно следующего вида:
Waiting for operation to complete... Operation status: RanToCompletion Created sample database leaderboard on instance cloudspanner-leaderboard
В разделе Cloud Spanner консоли Cloud Console вы должны увидеть свою новую базу данных и таблицы в меню слева.
На следующем этапе мы обновим наше приложение, чтобы загрузить некоторые данные в вашу новую базу данных.
5. Загрузка данных
Теперь у нас есть база данных под названием leaderboard , содержащая две таблицы: Players и Scores . Давайте воспользуемся клиентской библиотекой C#, чтобы заполнить таблицу Players именами игроков, а таблицу Scores — случайными результатами для каждого игрока.
Откройте редактор Cloud Shell, щелкнув по выделенному ниже значку:
Далее отредактируйте файл Program.cs в редакторе Cloud Shell, чтобы добавить команду insert , которая может использоваться для добавления 100 игроков в таблицу Players или для добавления 4 случайных результатов в таблицу Scores для каждого игрока в таблице Players .
Сначала добавьте новый блок команд insert в "Карту глаголов" в верхней части программы, под существующим блоком команд create :
[Verb("insert", HelpText = "Insert sample 'players' records or 'scores' records "
+ "into the database.")]
class InsertOptions
{
[Value(0, HelpText = "The project ID of the project to use "
+ "when managing Cloud Spanner resources.", Required = true)]
public string projectId { get; set; }
[Value(1, HelpText = "The ID of the instance where the sample database resides.",
Required = true)]
public string instanceId { get; set; }
[Value(2, HelpText = "The ID of the database where the sample database resides.",
Required = true)]
public string databaseId { get; set; }
[Value(3, HelpText = "The type of insert to perform, 'players' or 'scores'.",
Required = true)]
public string insertType { get; set; }
}
Далее добавьте следующие методы Insert , InsertPlayersAsync и InsertScoresAsync ниже существующего метода CreateAsync :
public static object Insert(string projectId,
string instanceId, string databaseId, string insertType)
{
if (insertType.ToLower() == "players")
{
var responseTask =
InsertPlayersAsync(projectId, instanceId, databaseId);
Console.WriteLine("Waiting for insert players operation to complete...");
responseTask.Wait();
Console.WriteLine($"Operation status: {responseTask.Status}");
}
else if (insertType.ToLower() == "scores")
{
var responseTask =
InsertScoresAsync(projectId, instanceId, databaseId);
Console.WriteLine("Waiting for insert scores operation to complete...");
responseTask.Wait();
Console.WriteLine($"Operation status: {responseTask.Status}");
}
else
{
Console.WriteLine("Invalid value for 'type of insert'. "
+ "Specify 'players' or 'scores'.");
return ExitCode.InvalidParameter;
}
Console.WriteLine($"Inserted {insertType} into sample database "
+ $"{databaseId} on instance {instanceId}");
return ExitCode.Success;
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
// Execute a SQL statement to get current number of records
// in the Players table to use as an incrementing value
// for each PlayerName to be inserted.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
numberOfPlayers = await cmd.ExecuteScalarAsync<long>();
// Insert 100 player records into the Players table.
SpannerBatchCommand cmdBatch = connection.CreateBatchDmlCommand();
for (int i = 0; i < 100; i++)
{
numberOfPlayers++;
SpannerCommand cmdInsert = connection.CreateDmlCommand(
"INSERT INTO Players "
+ "(PlayerId, PlayerName) "
+ "VALUES (@PlayerId, @PlayerName)",
new SpannerParameterCollection {
{"PlayerId", SpannerDbType.Int64},
{"PlayerName", SpannerDbType.String}});
cmdInsert.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmdInsert.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmdBatch.Add(cmdInsert);
}
await cmdBatch.ExecuteNonQueryAsync();
});
}
Console.WriteLine("Done inserting player records...");
}
public static async Task InsertScoresAsync(
string projectId, string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Insert 4 score records into the Scores table for each player
// in the Players table.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
Random r = new Random();
bool playerRecordsFound = false;
SpannerBatchCommand cmdBatch =
connection.CreateBatchDmlCommand();
var cmdLookup =
connection.CreateSelectCommand("SELECT * FROM Players");
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerRecordsFound = true;
for (int i = 0; i < 4; i++)
{
DateTime randomTimestamp = DateTime.Now
.AddYears(r.Next(-2, 1))
.AddMonths(r.Next(-12, 1))
.AddDays(r.Next(-28, 0))
.AddHours(r.Next(-24, 0))
.AddSeconds(r.Next(-60, 0))
.AddMilliseconds(r.Next(-100000, 0));
SpannerCommand cmdInsert =
connection.CreateDmlCommand(
"INSERT INTO Scores "
+ "(PlayerId, Score, Timestamp) "
+ "VALUES (@PlayerId, @Score, @Timestamp)",
new SpannerParameterCollection {
{"PlayerId", SpannerDbType.Int64},
{"Score", SpannerDbType.Int64},
{"Timestamp",
SpannerDbType.Timestamp}});
cmdInsert.Parameters["PlayerId"].Value =
reader.GetFieldValue<int>("PlayerId");
cmdInsert.Parameters["Score"].Value =
r.Next(1000, 1000001);
cmdInsert.Parameters["Timestamp"].Value =
randomTimestamp.ToString("o");
cmdBatch.Add(cmdInsert);
}
}
if (!playerRecordsFound)
{
Console.WriteLine("Parameter 'scores' is invalid "
+ "since no player records currently exist. First "
+ "insert players then insert scores.");
Environment.Exit((int)ExitCode.InvalidParameter);
}
else
{
await cmdBatch.ExecuteNonQueryAsync();
Console.WriteLine(
"Done inserting score records..."
);
}
}
});
}
}
Затем, чтобы команда insert заработала, добавьте следующий код в метод «Main» вашей программы:
.Add((InsertOptions opts) => Insert(
opts.projectId, opts.instanceId, opts.databaseId, opts.insertType))
Вы можете использовать файл Program.cs в каталоге dotnet-docs-samples/applications/leaderboard/step5 чтобы увидеть пример того, как должен выглядеть ваш файл Program.cs после добавления кода, включающего команду insert .
Теперь запустим программу, чтобы убедиться, что новая команда insert включена в список возможных команд программы. Выполните следующую команду:
dotnet run
Теперь в стандартный вывод программы должна отображаться команда insert :
Leaderboard 1.0.0 Copyright (C) 2018 Leaderboard ERROR(S): No verb selected. create Create a sample Cloud Spanner database along with sample 'Players' and 'Scores' tables in your project. insert Insert sample 'players' records or 'scores' records into the database. help Display more information on a specific command. version Display version information.
Теперь давайте выполним команду insert , чтобы увидеть ее входные аргументы. Введите следующую команду.
dotnet run insert
В результате должна быть получена следующая информация:
Leaderboard 1.0.0 Copyright (C) 2018 Leaderboard ERROR(S): A required value not bound to option name is missing. --help Display this help screen. --version Display version information. value pos. 0 Required. The project ID of the project to use when managing Cloud Spanner resources. value pos. 1 Required. The ID of the instance where the sample database resides. value pos. 2 Required. The ID of the database where the sample database resides. value pos. 3 Required. The type of insert to perform, 'players' or 'scores'.
Как видно из ответа, помимо идентификатора проекта, идентификатора экземпляра и идентификатора базы данных, ожидается еще одно value pos. 3 , которое представляет собой «тип вставки» для выполнения. Этот аргумент может принимать значения «players» или «scores».
Теперь выполним команду insert с теми же значениями аргументов, которые мы использовали при вызове команды create , добавив "players" в качестве дополнительного аргумента "тип вставки". Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run insert PROJECT_ID cloudspanner-leaderboard leaderboard players
Через пару секунд вы должны увидеть ответ примерно следующего вида:
Waiting for insert players operation to complete... Done inserting player records... Operation status: RanToCompletion Inserted players into sample database leaderboard on instance cloudspanner-leaderboard
Теперь воспользуемся клиентской библиотекой C#, чтобы заполнить нашу таблицу 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 в заданной строке таблицы.
Вы также можете вставлять собственные значения временных меток в столбец "временная метка фиксации", если вставляете метку времени, значение которой находится в прошлом, что мы и сделаем в рамках этого практического занятия.
Теперь давайте выполним команду insert с теми же значениями аргументов, которые мы использовали при вызове команды create добавив "scores" в качестве дополнительного аргумента "тип вставки". Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run insert PROJECT_ID cloudspanner-leaderboard leaderboard scores
Через пару секунд вы должны увидеть ответ примерно следующего вида:
Waiting for insert players operation to complete... Done inserting player records... Operation status: RanToCompletion Inserted players into sample database leaderboard on instance cloudspanner-leaderboard
При выполнении insert с указанием типа вставки " scores " вызывается метод InsertScoresAsync , который использует следующие фрагменты кода для вставки случайно сгенерированной метки времени, относящейся к прошлому:
DateTime randomTimestamp = DateTime.Now
.AddYears(r.Next(-2, 1))
.AddMonths(r.Next(-12, 1))
.AddDays(r.Next(-28, 0))
.AddHours(r.Next(-24, 0))
.AddSeconds(r.Next(-60, 0))
.AddMilliseconds(r.Next(-100000, 0));
...
cmdInsert.Parameters["Timestamp"].Value = randomTimestamp.ToString("o");
Чтобы автоматически заполнить столбец Timestamp меткой времени, точно соответствующей моменту выполнения транзакции "Insert", можно использовать константу C# SpannerParameter.CommitTimestamp как показано в следующем фрагменте кода:
cmd.Parameters["Timestamp"].Value = SpannerParameter.CommitTimestamp;
Теперь, когда загрузка данных завершена, давайте проверим значения, которые мы только что записали в наши новые таблицы. Сначала выберите базу данных leaderboard , а затем таблицу Players . Перейдите на вкладку Data . Вы должны увидеть, что данные есть в столбцах PlayerId и PlayerName этой таблицы.
Теперь давайте проверим, есть ли данные и в таблице «Счета». Для этого щелкните по таблице Scores и выберите вкладку Data . Вы должны увидеть данные в столбцах PlayerId , Timestamp и Score .
Отлично! Давайте обновим нашу программу, чтобы она выполняла несколько запросов, которые мы сможем использовать для создания игровой таблицы лидеров.
6. Выполните запросы к таблице лидеров.
Теперь, когда мы создали базу данных и загрузили информацию в таблицы, давайте создадим таблицу лидеров, используя эти данные. Для этого нам нужно ответить на следующие четыре вопроса:
- Какие игроки входят в "десятку лучших" всех времен?
- Какие игроки входят в "Топ-10" года?
- Какие игроки вошли в «Топ-10» месяца?
- Какие игроки вошли в «Топ-10» недели?
Давайте обновим нашу программу, чтобы она выполняла SQL-запросы, которые ответят на эти вопросы.
Мы добавим команду query , которая позволит выполнять запросы для ответа на вопросы, необходимые для формирования таблицы лидеров.
Отредактируйте файл Program.cs в редакторе Cloud Shell, чтобы обновить программу и добавить команду query .
Сначала добавьте новый блок команд query в "Карту глаголов" в верхней части программы, под существующим блоком команд insert :
[Verb("query", HelpText = "Query players with 'Top Ten' scores within a specific timespan "
+ "from sample Cloud Spanner database table.")]
class QueryOptions
{
[Value(0, HelpText = "The project ID of the project to use "
+ "when managing Cloud Spanner resources.", Required = true)]
public string projectId { get; set; }
[Value(1, HelpText = "The ID of the instance where the sample data resides.",
Required = true)]
public string instanceId { get; set; }
[Value(2, HelpText = "The ID of the database where the sample data resides.",
Required = true)]
public string databaseId { get; set; }
[Value(3, Default = 0, HelpText = "The timespan in hours that will be used to filter the "
+ "results based on a record's timestamp. The default will return the "
+ "'Top Ten' scores of all time.")]
public int timespan { get; set; }
}
Далее добавьте следующие методы Query и QueryAsync ниже существующего метода InsertScoresAsync :
public static object Query(string projectId,
string instanceId, string databaseId, int timespan)
{
var response = QueryAsync(
projectId, instanceId, databaseId, timespan);
response.Wait();
return ExitCode.Success;
}
public static async Task QueryAsync(
string projectId, string instanceId, string databaseId, int timespan)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
string sqlCommand;
if (timespan == 0)
{
// No timespan specified. Query Top Ten scores of all time.
sqlCommand =
@"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";
}
else
{
// Query Top Ten scores filtered by the timepan specified.
sqlCommand =
$@"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.ToString()} HOUR)
ORDER BY s.Score DESC LIMIT 10";
}
var cmd = connection.CreateSelectCommand(sqlCommand);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine("PlayerId : "
+ reader.GetFieldValue<string>("PlayerId")
+ " PlayerName : "
+ reader.GetFieldValue<string>("PlayerName")
+ " Score : "
+ string.Format("{0:n0}",
Int64.Parse(reader.GetFieldValue<string>("Score")))
+ " Timestamp : "
+ reader.GetFieldValue<string>("Timestamp").Substring(0, 10));
}
}
}
}
Затем, чтобы команда query заработала, добавьте следующий код в метод «Main» вашей программы:
.Add((QueryOptions opts) => Query(
opts.projectId, opts.instanceId, opts.databaseId, opts.timespan))
Вы можете использовать файл Program.cs в каталоге dotnet-docs-samples/applications/leaderboard/step6 чтобы увидеть пример того, как должен выглядеть ваш файл Program.cs после добавления кода, включающего команду query .
Теперь запустим программу, чтобы убедиться, что новая команда query включена в список возможных команд программы. Выполните следующую команду:
dotnet run
Теперь в стандартный вывод программы в качестве новой опции должна отображаться команда query :
Leaderboard 1.0.0 Copyright (C) 2018 Leaderboard ERROR(S): No verb selected. create Create a sample Cloud Spanner database along with sample 'Players' and 'Scores' tables in your project. insert Insert sample 'players' records or 'scores' records into the database. query Query players with 'Top Ten' scores within a specific timespan from sample Cloud Spanner database table. help Display more information on a specific command. version Display version information.
Теперь давайте выполним команду query , чтобы увидеть ее входные аргументы. Введите следующую команду:
dotnet run query
В результате будет получен следующий ответ:
Leaderboard 1.0.0 Copyright (C) 2018 Leaderboard ERROR(S): A required value not bound to option name is missing. --help Display this help screen. --version Display version information. value pos. 0 Required. The project ID of the project to use when managing Cloud Spanner resources. value pos. 1 Required. The ID of the instance where the sample data resides. value pos. 2 Required. The ID of the database where the sample data resides. value pos. 3 (Default: 0) The timespan in hours that will be used to filter the results based on a record's timestamp. The default will return the 'Top Ten' scores of all time.
Как видно из ответа, помимо идентификатора проекта, идентификатора экземпляра и идентификатора базы данных, ожидается еще одно value pos. 3 , которое позволяет указать временной интервал в часах для фильтрации записей на основе их значений в столбце Timestamp таблицы Scores . Значение этого аргумента по умолчанию равно 0, что означает, что никакие записи не будут фильтроваться по временным меткам. Таким образом, мы можем использовать команду query без значения «временной интервал», чтобы получить список наших «Топ-10» игроков всех времен.
Давайте выполним команду query , не указывая "временной интервал", используя те же значения аргументов, что и при выполнении команды create . Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run query PROJECT_ID cloudspanner-leaderboard leaderboard
В ответ вы должны увидеть список десяти лучших игроков всех времен, примерно такой:
PlayerId : 1843159180 PlayerName : Player 87 Score : 998,955 Timestamp : 2016-03-23
PlayerId : 61891198 PlayerName : Player 19 Score : 998,720 Timestamp : 2016-03-26
PlayerId : 340906298 PlayerName : Player 48 Score : 993,302 Timestamp : 2015-08-27
PlayerId : 541473117 PlayerName : Player 22 Score : 991,368 Timestamp : 2018-04-30
PlayerId : 857460496 PlayerName : Player 68 Score : 988,010 Timestamp : 2015-05-25
PlayerId : 1826646419 PlayerName : Player 91 Score : 984,022 Timestamp : 2016-11-26
PlayerId : 1002199735 PlayerName : Player 35 Score : 982,933 Timestamp : 2015-09-26
PlayerId : 2002563755 PlayerName : Player 23 Score : 979,041 Timestamp : 2016-10-25
PlayerId : 1377548191 PlayerName : Player 2 Score : 978,632 Timestamp : 2016-05-02
PlayerId : 1358098565 PlayerName : Player 65 Score : 973,257 Timestamp : 2016-10-30
Теперь выполним команду query с необходимыми аргументами, чтобы получить список «Топ-10» игроков года, указав «временной интервал», равный количеству часов в году, то есть 8760. Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run query PROJECT_ID cloudspanner-leaderboard leaderboard 8760
В ответ вы должны увидеть список десяти лучших игроков года, например, такой:
PlayerId : 541473117 PlayerName : Player 22 Score : 991,368 Timestamp : 2018-04-30
PlayerId : 228469898 PlayerName : Player 82 Score : 967,177 Timestamp : 2018-01-26
PlayerId : 1131343000 PlayerName : Player 26 Score : 944,725 Timestamp : 2017-05-26
PlayerId : 396780730 PlayerName : Player 41 Score : 929,455 Timestamp : 2017-09-26
PlayerId : 61891198 PlayerName : Player 19 Score : 921,251 Timestamp : 2018-05-01
PlayerId : 634269851 PlayerName : Player 54 Score : 909,379 Timestamp : 2017-07-24
PlayerId : 821111159 PlayerName : Player 55 Score : 908,402 Timestamp : 2017-05-25
PlayerId : 228469898 PlayerName : Player 82 Score : 889,040 Timestamp : 2017-12-26
PlayerId : 1408782275 PlayerName : Player 27 Score : 874,124 Timestamp : 2017-09-24
PlayerId : 1002199735 PlayerName : Player 35 Score : 864,758 Timestamp : 2018-04-24
Теперь выполним команду query , чтобы получить список «Топ-10» игроков месяца, указав параметр «timespan», равный количеству часов в месяце, то есть 730. Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run query PROJECT_ID cloudspanner-leaderboard leaderboard 730
В ответ вы должны увидеть список из десяти лучших игроков месяца, примерно такой:
PlayerId : 541473117 PlayerName : Player 22 Score : 991,368 Timestamp : 2018-04-30
PlayerId : 61891198 PlayerName : Player 19 Score : 921,251 Timestamp : 2018-05-01
PlayerId : 1002199735 PlayerName : Player 35 Score : 864,758 Timestamp : 2018-04-24
PlayerId : 1228490432 PlayerName : Player 11 Score : 682,033 Timestamp : 2018-04-26
PlayerId : 648239230 PlayerName : Player 92 Score : 653,895 Timestamp : 2018-05-02
PlayerId : 70762849 PlayerName : Player 77 Score : 598,074 Timestamp : 2018-04-22
PlayerId : 1671215342 PlayerName : Player 62 Score : 506,770 Timestamp : 2018-04-28
PlayerId : 1208850523 PlayerName : Player 21 Score : 216,008 Timestamp : 2018-04-30
PlayerId : 1587692674 PlayerName : Player 63 Score : 188,157 Timestamp : 2018-04-25
PlayerId : 992391797 PlayerName : Player 37 Score : 167,175 Timestamp : 2018-04-30
Теперь выполним команду query , чтобы получить список «Топ-10» игроков недели, указав параметр «timespan», равный количеству часов в неделе, то есть 168. Убедитесь, что вы заменили PROJECT_ID на идентификатор проекта, который вы создали в начале этого практического занятия.
dotnet run query PROJECT_ID cloudspanner-leaderboard leaderboard 168
Вы должны увидеть ответ, включающий «Топ-10» игроков недели, примерно следующего вида:
PlayerId : 541473117 PlayerName : Player 22 Score : 991,368 Timestamp : 2018-04-30
PlayerId : 61891198 PlayerName : Player 19 Score : 921,251 Timestamp : 2018-05-01
PlayerId : 228469898 PlayerName : Player 82 Score : 853,602 Timestamp : 2018-04-28
PlayerId : 1131343000 PlayerName : Player 26 Score : 695,318 Timestamp : 2018-04-30
PlayerId : 1228490432 PlayerName : Player 11 Score : 682,033 Timestamp : 2018-04-26
PlayerId : 1408782275 PlayerName : Player 27 Score : 671,827 Timestamp : 2018-04-27
PlayerId : 648239230 PlayerName : Player 92 Score : 653,895 Timestamp : 2018-05-02
PlayerId : 816861444 PlayerName : Player 83 Score : 622,277 Timestamp : 2018-04-27
PlayerId : 162043954 PlayerName : Player 75 Score : 572,634 Timestamp : 2018-05-02
PlayerId : 1671215342 PlayerName : Player 62 Score : 506,770 Timestamp : 2018-04-28
Отличная работа!
Теперь, по мере добавления записей, Spanner будет масштабировать вашу базу данных до необходимого вам размера.
Независимо от того, насколько разрастется ваша база данных, таблица лидеров вашей игры сможет продолжать масштабироваться с высокой точностью благодаря технологии Truetime от Spanner.
7. Уборка
После всех развлечений со Spanner нам нужно навести порядок на нашей площадке, сэкономив ценные ресурсы и деньги. К счастью, это простой шаг: просто зайдите в консоль разработчика и удалите экземпляр, созданный нами на шаге практического задания под названием «Настройка облачного экземпляра Spanner».
8. Поздравляем!
Что мы рассмотрели:
- Экземпляры Google Cloud Spanner, базы данных и схема таблиц для таблицы лидеров.
- Как создать консольное приложение на C# с использованием .NET Core
- Как создать базу данных и таблицы Spanner с помощью клиентской библиотеки C#
- Как загрузить данные в базу данных Spanner с помощью клиентской библиотеки C#
- Как получить список из "Топ-10" результатов из ваших данных, используя временные метки коммитов Spanner и клиентскую библиотеку C#.
Следующие шаги:
- Ознакомьтесь с официальным документом Spanner CAP.
- Узнайте о проектировании схем и передовых методах работы с запросами.
- Узнайте больше о временных метках коммитов Cloud Spanner.
Оставьте свой отзыв.
- Пожалуйста, уделите несколько минут, чтобы пройти наш очень короткий опрос.