1. 總覽
Google Cloud Spanner 是全代管且遍及全球的關聯資料庫服務，提供 ACID 交易和 SQL 語意，但不會犧牲效能及高可用性。
在本研究室中，您將瞭解如何設定 Cloud Spanner 執行個體。並逐步建立可用於遊戲排行榜的資料庫和結構定義。第一步是建立用於儲存玩家資訊的「玩家」表格，以及用來儲存玩家得分的「得分」表格。
接下來要將範例資料填入資料表。接著，您將於課程結束之前執行一些前十大範例查詢，最後刪除執行個體來釋出資源。
課程內容
- 如何設定 Cloud Spanner 執行個體。
- 如何建立資料庫和資料表。
- 如何使用修訂時間戳記欄。
- 如何將含有時間戳記的資料載入 Cloud Spanner 資料庫資料表。
- 如何查詢 Cloud Spanner 資料庫。
- 如何刪除 Cloud Spanner 執行個體。
需求條件
您會如何使用這個教學課程？
您對 Google Cloud Platform 的使用體驗有何評價？
2. 設定和需求
自修環境設定
如果您還沒有 Google 帳戶 (Gmail 或 Google Apps)，請先建立帳戶。登入 Google Cloud Platform 控制台 ( console.cloud.google.com)，並建立新專案。
如果您已有專案，請按一下控制台左上方的專案選取下拉式選單：
並點選 [新增專案]按鈕，用於建立新專案：
如果您還沒有專案，系統會顯示如下的對話方塊，讓您建立第一個專案：
後續的專案建立對話方塊可讓您輸入新專案的詳細資料：
請記住，專案 ID 在所有的 Google Cloud 專案中是不重複的名稱 (已經有人使用上述名稱，目前無法為您解決問題！)。稍後在本程式碼研究室中會稱為 PROJECT_ID。
接下來，如果您尚未在 Developers Console 中啟用計費功能，必須完成此步驟，才能使用 Google Cloud 資源並啟用 Cloud Spanner API。
執行本程式碼研究室所需的費用不應超過數美元，但如果您決定使用更多資源，或讓這些資源繼續運作，費用會增加 (請參閱本文件結尾的「清理」一節)。如需 Google Cloud Spanner 的定價資訊，請參閱這裡。
Google Cloud Platform 的新使用者符合 $300 美元的免費試用資格，應該可以免費使用本程式碼研究室。
Google Cloud Shell 設定
雖然 Google Cloud 和 Spanner 可以在筆記型電腦上遠端運作，但在本程式碼研究室中，我們會使用 Google Cloud Shell，這是一種在 Cloud 中執行的指令列環境。
這種以 Debian 為基礎的虛擬機器，搭載各種您需要的開發工具。提供永久的 5 GB 主目錄，而且在 Google Cloud 中運作，大幅提高網路效能和驗證能力。換言之，本程式碼研究室只需要在 Chromebook 上運作即可。
- 如要透過 Cloud 控制台啟用 Cloud Shell，只要點選「啟用 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 控制台資訊主頁查詢：
根據預設，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 服務水準協議的資格，您必須在 Cloud Spanner 執行個體中執行 3 個以上的節點。
最後，點選 [建立]而且您能在幾秒內使用 Cloud Spanner 執行個體
在下一個步驟中，我們會使用 C# 用戶端程式庫，在新的執行個體中建立資料庫和結構定義。
4. 建立資料庫和結構定義
在這個步驟中，我們會建立範例資料庫和結構定義。
讓我們使用 C# 用戶端程式庫建立兩個資料表：玩家資訊表以及儲存玩家分數的得分錶格。因此，我們會逐步講解在 Cloud Shell 中建立 C# 控制台應用程式的步驟。
請先在 Cloud Shell 輸入下列指令，從 GitHub 複製本程式碼研究室的程式碼範例：
git clone https://github.com/GoogleCloudPlatform/dotnet-docs-samples.git
然後，將目錄變更為「applications」，以便建立應用程式。
cd dotnet-docs-samples/applications/
本程式碼研究室所需的所有程式碼都位於現有的 dotnet-docs-samples/applications/leaderboard 目錄中，做為名為 Leaderboard 的可執行 C# 應用程式，可讓您在參與本程式碼研究室的過程中做為參考。我們將分階段建立新的目錄，並分階段建立排行榜應用程式副本。
建立名為「codelab」的新目錄，並使用下列指令將目錄變更為該目錄：
mkdir codelab && cd $_
建立名為「Leaderboard」的新 .NET C# 控制台應用程式使用以下指令：
dotnet new console -n Leaderboard
這個指令會建立一個簡易的主控台應用程式，包含兩個檔案主要檔案：專案檔案 Leaderboard.csproj 和程式檔案 Program.cs。
開始執行。將目錄變更為新建立的排行榜目錄，位於應用程式所在的位置：
cd Leaderboard
然後輸入下列指令來執行該指標。
dotnet run
您應該會看到應用程式輸出「Hello World!」。
現在讓我們透過編輯 Program.cs 來使用 C# Spanner 用戶端程式庫，建立由兩張表格玩家和分數組成的排行榜，藉此更新主控台應用程式。您可以直接在 Cloud Shell 編輯器中執行這項操作：
請點選下方醒目顯示的圖示，開啟 Cloud Shell 編輯器：
接著，在 Cloud Shell 編輯器中開啟 Program.cs 檔案，然後將檔案現有的程式碼替換為建立 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);
}
}
}
為了提供清楚的計畫程式碼資訊，以下是計畫的圖表，以及其主要部分：
您可以在 dotnet-docs-samples/applications/leaderboard/step4 目錄中使用 Program.cs 檔案，查看啟用 create 指令的程式碼後，Program.cs 檔案應如何呈現。
接著，使用 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>
這項變更已新增需要與 Cloud Spanner API 互動的 C# Spanner Nuget 套件 Google.Cloud.Spanner.Data 的參照。這項變更也會新增 CommandLineUtil 專案的參照，該專案屬於 dotnet-doc-samples GitHub 存放區的一環，並提供實用的「verbmap」開放原始碼 CommandLineParser 擴充功能；這個實用的程式庫可以處理主控台應用程式的指令列輸入內容。
您可以在 dotnet-docs-samples/applications/leaderboard/step4 目錄中使用 Leaderboard.csproj 檔案，查看啟用 create 指令的程式碼後，Leaderboard.csproj 檔案應如何呈現。
現在您可以執行更新後的樣本。輸入以下內容，查看更新後應用程式的預設回應：
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 指令的預期引數為專案 ID、執行個體 ID 和資料庫 ID。
接著執行下列指令。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 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 控制台的 Cloud Spanner 部分，您應該會在左側選單中看到新的資料庫和資料表。
在下一個步驟中，我們會更新應用程式，將一些資料載入新的資料庫。
5. 載入資料
我們現在有一個名為 leaderboard 的資料庫，內含兩份資料表；Players和Scores。現在，我們要使用 C# 用戶端程式庫，在 Players 資料表中填入玩家，並在 Scores 資料表中填入每位玩家的隨機分數。
請點選下方醒目顯示的圖示，開啟 Cloud Shell 編輯器：
接著，在 Cloud Shell 編輯器中編輯 Program.cs 檔案，新增 insert 指令，該指令可用於在 Players 資料表中插入 100 位玩家，也可以用該指令在 Players 資料表中為每位玩家插入 4 個隨機分數。Scores
首先，在「Verbmap」中新增 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; }
}
接下來，在現有 CreateAsync 方法下方新增下列 Insert、InsertPlayersAsync 和 InsertScoresAsync 方法：
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))
您可以在 dotnet-docs-samples/applications/leaderboard/step5 目錄中使用 Program.cs 檔案，查看啟用 insert 指令的程式碼後，Program.cs 檔案應如何呈現。
現在請執行程式，確認新的 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'.
您可以在回應中看到，除了專案 ID、執行個體 ID 和資料庫 ID 之外，還有另一個引數 value pos. 3 應該是「insert type」(插入類型)待執行。這個引數的值可以是「players」或「分數」
現在，使用我們呼叫 create 指令時採用的相同引數值執行 insert 指令，新增「players」做為額外的「插入類型」引數。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 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 資料表中每位玩家的時間戳記。
Scores 資料表的「Timestamp」欄定義為「修訂時間戳記」如下所示，該陳述式為在我們之前執行 create 指令時執行的下列 SQL 陳述式：
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 作業的確切交易時間戳記。
您也可以將自己的時間戳記值插入「修訂時間戳記」欄中，只要您插入時間戳記值在過去的時間戳記，我們就會對此程式碼研究室進行操作。
現在，使用我們呼叫 create 指令新增「scores」時使用的相同引數值執行 insert 指令做為額外的「插入類型」引數。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 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. 執行排行榜查詢
資料庫設定完成，並將資訊載入表格後，我們就來使用這些資料建立排行榜吧。為此，請回答下列四個問題：
- 哪些玩家是「前十名」而非時間
- 哪些玩家是「前十名」該怎麼辦？
- 哪些玩家是「前十名」每月？
- 哪些玩家是「前十名」一週？
請更新程式來執行 SQL 查詢來回答這些問題。
我們將新增 query 指令，藉此執行查詢，找出會產生排行榜所需資訊的問題。
在 Cloud Shell 編輯器中編輯 Program.cs 檔案，更新程式以新增 query 指令。
首先，在「Verbmap」中新增 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; }
}
接下來，在現有 InsertScoresAsync 方法下方新增下列 Query 和 QueryAsync 方法：
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))
您可以在 dotnet-docs-samples/applications/leaderboard/step6 目錄中使用 Program.cs 檔案，查看啟用 query 指令的程式碼後，Program.cs 檔案應如何呈現。
現在請執行程式，確認新的 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.
從回應中可以發現，除了專案 ID、執行個體 ID 和資料庫 ID 之外，還有另一個引數 value pos. 3。這可讓我們根據 Scores 表格的 Timestamp 欄中的值，指定要用於篩選記錄的時間範圍 (以小時為單位)。這個引數的預設值是 0，表示沒有任何記錄依時間戳記篩選。所以可以在沒有「時間範圍」的情況下使用 query 指令值，即可取得「前十大」名單隨時都有可能獲得玩家青睞
讓我們使用執行 create 指令時採用的相同引數值，在不指定「時距」的情況下執行 query 指令。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 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 指令，查詢「前十名」方法是指定「時間範圍」等於一年中的時數為 8760。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 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 指令來查詢「前十名」方法是指定「時間範圍」等於當月的小時數 (730)。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 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 指令來查詢「前十名」方法是指定「時間範圍」等於一週的小時數 (168)。請確認您將 PROJECT_ID 替換成您在本程式碼研究室一開始建立的專案 ID。
dotnet run query PROJECT_ID cloudspanner-leaderboard leaderboard 168
您應該會看到一則包含「前十大」的回覆每週的玩家，如下所示：
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 就會視需求擴充資料庫的資源配置。
無論資料庫數量增加，遊戲排行榜都能透過 Spanner 和 Truetime 技術，持續擴充準確率。
7. 清除
享受到 Spanner 帶來的樂趣後，我們需要清理 Playground，節省寶貴的資源和費用。幸好這個步驟很簡單，只要前往 Play 管理中心，刪除我們在「設定 Cloud Spanner 執行個體」程式碼研究室步驟中建立的執行個體即可。
8. 恭喜！
本文涵蓋內容：
- 排行榜的 Google Cloud Spanner 執行個體、資料庫和資料表結構定義
- 如何建立 .NET Core C# 主控台應用程式
- 如何使用 C# 用戶端程式庫建立 Spanner 資料庫和資料表
- 如何使用 C# 用戶端程式庫將資料載入 Spanner 資料庫
- 如何查詢「前十名」資料來源：使用 Spanner 修訂時間戳記和 C# 用戶端程式庫
後續步驟：
- 閱讀 Spanner CAP 白皮書
- 瞭解結構定義設計和查詢的最佳做法
- 進一步瞭解 Cloud Spanner 修訂時間戳記
請提供您寶貴的意見
- 請花點時間完成簡短的問卷調查