C# での BigQuery の使用

1. 概要

BigQuery は、Google が提供するペタバイト規模の低料金フルマネージドアナリティクスデータウェアハウスです。BigQuery は NoOps（管理するインフラストラクチャが存在せず、データベース管理者が必要ない）であるため、使い慣れた SQL を使用してデータから有用な情報を見つけ出す作業に集中できます。また、このサービスは使った分だけ課金される従量制です。

この Codelab では、.NET 用の Google Cloud クライアントライブラリを使用して、C# で BigQuery の一般公開データセットに対してクエリを実行します。

学習内容

Cloud Shell を使用する方法
BigQuery API を有効にする方法
API リクエストを認証する方法
C# 用 Google Cloud クライアントライブラリをインストールする方法
シェイクスピア作品のクエリ方法
GitHub データセットに対するクエリ方法
キャッシュ保存と統計情報の表示を調整する方法

必要なもの

Google Cloud Platform プロジェクト
Chrome や Firefox などのブラウザ
C# の使用経験

アンケート

このチュートリアルをどのように使用されますか？

通読するのみ

通読し、演習を行う

C# のご利用経験はどの程度ありますか？

初心者

中級者

上級者

Google Cloud Platform サービスのご利用経験についてどのように評価されますか？

初心者

中級者

上級者

2. 設定と要件

セルフペース型の環境設定

Google Cloud Console にログインして、プロジェクトを新規作成するか、既存のプロジェクトを再利用します。Gmail アカウントも Google Workspace アカウントもまだお持ちでない場合は、アカウントを作成してください。

プロジェクト名は、このプロジェクトの参加者に表示される名称です。Google API では使用されない文字列です。いつでも更新できます。
プロジェクト ID は、すべての Google Cloud プロジェクトにおいて一意でなければならず、不変です（設定後は変更できません）。Cloud コンソールでは一意の文字列が自動生成されます。通常は、この内容を意識する必要はありません。ほとんどの Codelab では、プロジェクト ID（通常は PROJECT_ID と識別されます）を参照する必要があります。生成された ID が好みではない場合は、ランダムに別の ID を生成できます。または、ご自身で試して、利用可能かどうかを確認することもできます。このステップ以降は変更できず、プロジェクトを通して同じ ID になります。
なお、3 つ目の値として、一部の API が使用するプロジェクト番号があります。これら 3 つの値について詳しくは、こちらのドキュメントをご覧ください。

次に、Cloud のリソースや API を使用するために、Cloud コンソールで課金を有効にする必要があります。この Codelab の操作をすべて行って、費用が生じたとしても、少額です。このチュートリアルの終了後に請求が発生しないようにリソースをシャットダウンするには、作成したリソースを削除するか、プロジェクトを削除します。Google Cloud の新規ユーザーは、300 米ドル分の無料トライアルプログラムをご利用いただけます。

Cloud Shell を起動する

Google Cloud はノートパソコンからリモートで操作できますが、この Codelab では、Google Cloud Shell（Cloud 上で動作するコマンドライン環境）を使用します。

Cloud Shell をアクティブにする

Cloud Console で、[Cloud Shell をアクティブにする] をクリックします。

Cloud Shell を初めて起動する場合は、その内容を説明する中間画面が表示されます。中間画面が表示された場合は、[続行] をクリックします。

すぐにプロビジョニングが実行され、Cloud Shell に接続されます。

この仮想マシンには、必要な開発ツールがすべて用意されています。仮想マシンは Google Cloud で稼働し、永続的なホームディレクトリが 5 GB 用意されているため、ネットワークのパフォーマンスと認証が大幅に向上しています。このコードラボで行う作業のほとんどはブラウザから実行できます。

Cloud Shell に接続すると、認証が完了しており、プロジェクトに各自のプロジェクト ID が設定されていることがわかります。

Cloud Shell で次のコマンドを実行して、認証されたことを確認します。

gcloud auth list

コマンド出力

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Cloud Shell で次のコマンドを実行して、gcloud コマンドがプロジェクトを認識していることを確認します。

gcloud config list project

コマンド出力

[core]
project = <PROJECT_ID>

上記のようになっていない場合は、次のコマンドで設定できます。

gcloud config set project <PROJECT_ID>

コマンド出力

Updated property [core/project].

3. BigQuery API を有効にする

BigQuery API は、すべての Google Cloud プロジェクトでデフォルトで有効になっています。Cloud Shell で次のコマンドを実行して、これが true かどうかを確認できます。BigQuery が一覧表示されます。

gcloud services list

BigQuery が表示されます。

NAME                              TITLE
bigquery-json.googleapis.com      BigQuery API
...

BigQuery API が有効になっていない場合は、Cloud Shell で次のコマンドを使用して有効にできます。

gcloud services enable bigquery-json.googleapis.com

4. C# 用の BigQuery クライアントライブラリをインストールする

まず、BigQuery API サンプルの実行に使用する簡単な C# コンソールアプリケーションを作成します。

dotnet new console -n BigQueryDemo

アプリケーションが作成され、依存関係が解決されたことがわかります。

The template "Console Application" was created successfully.
Processing post-creation actions...
...
Restore succeeded.

次に、BigQueryDemo フォルダに移動します。

cd BigQueryDemo

また、Google.Cloud.BigQuery.V2 NuGet パッケージをプロジェクトに追加します。

dotnet add package Google.Cloud.BigQuery.V2

info : Adding PackageReference for package 'Google.Cloud.BigQuery.V2' into project '/home/atameldev/BigQueryDemo/BigQueryDemo.csproj'.
log  : Restoring packages for /home/atameldev/BigQueryDemo/BigQueryDemo.csproj...
...
info : PackageReference for package 'Google.Cloud.BigQuery.V2' version '1.2.0' added to file '/home/atameldev/BigQueryDemo/BigQueryDemo.csproj'.

これで、BigQuery API を使用する準備が整いました。

5. シェイクスピア作品のクエリ

一般公開データセットは、BigQuery に保存され、一般に公開されるデータセットです。照会が可能な一般公開データセットは、他にも数多くあり、一部は Google でもホストされますが、多くはサードパーティでホストされます。詳細については、一般公開データセットのページをご覧ください。

BigQuery には、一般公開データセットのほかにも、クエリを実行できるサンプルテーブルがあります。これらのテーブルは bigquery-public-data:samples dataset に含まれています。これらのテーブルの 1 つは shakespeare. と呼ばれます。このテーブルには、シェイクスピア作品の単語の索引が含まれていて、それぞれのコーパスで各単語が出現する回数を示しています。

このステップでは、Shakespeare テーブルに対してクエリを実行します。

まず、Cloud Shell の右上にあるコードエディタを開きます。

BigQueryDemo フォルダ内の Program.cs ファイルに移動し、コードを次のコードに置き換えます。projectId は実際のプロジェクト ID に置き換えてください。

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "samples", "shakespeare");
            var sql = $"SELECT corpus AS title, COUNT(word) AS unique_words FROM {table} GROUP BY title ORDER BY unique_words DESC LIMIT 10";

            var results = client.ExecuteQuery(sql, parameters: null);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
            }
        }
    }
}

コードを 1 ～ 2 分ほど確認して、テーブルがどのようにクエリされているかを確認します。

Cloud Shell に戻ってアプリを実行します。

dotnet run

単語とその出現回数のリストが表示されます。

hamlet: 5318
kinghenryv: 5104
cymbeline: 4875
troilusandcressida: 4795
kinglear: 4784
kingrichardiii: 4713
2kinghenryvi: 4683
...

注: この C# コードが機能しない場合は、API リクエストを認証するの手順で実行した手順を確認してください。

次のコマンドを使用して、GOOGLE_APPLICATION_CREDENTIALS 環境変数の値を確認します。

echo GOOGLE_APPLICATION_CREDENTIALS

値 "~/key.json". を出力する必要があります。

作成されている場合は、次のコマンドを使用して、サービスアカウントが作成され、"~/key.json" に配置されていることを確認します。

cat "~/key.json"

次のように表示されます。

{

"type": "service_account",

"project_id": "PROJECT_ID",

"private_key_id": "ff31939192529e07f42e4535fb20bb029def1276",

"Private_key":...

そうでない場合は、API リクエストを認証するの手順をもう一度確認してください。

6. GitHub データセットに対するクエリ

BigQuery の理解を深めるため、GitHub 一般公開データセットに対してクエリを発行します。最も一般的なコミットメッセージは GitHub で確認できます。また、BigQuery のウェブコンソールを使用して、アドホッククエリをプレビューして実行します。

データがどのように表示されるかを確認するには、BigQuery ウェブ UI で GitHub データセットを開きます。

https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=github_repos&t=commits&page=table

[プレビュー] ボタンを使用して、データの外観を簡単にプレビューします。

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "github_repos", "commits");
            
            var sql = $"SELECT subject AS subject, COUNT(*) AS num_duplicates FROM {table} GROUP BY subject ORDER BY num_duplicates DESC LIMIT 10";

            var results = client.ExecuteQuery(sql, parameters: null);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["subject"]}: {row["num_duplicates"]}");
            }
        }
    }
}

コードを 1 ～ 2 分ほど確認して、最も一般的なコミットメッセージについてテーブルがどのようにクエリされているかを確認します。

Cloud Shell に戻ってアプリを実行します。

dotnet run

コミットメッセージとその発生回数のリストが表示されます。

Update README.md: 2509242
: 1971725
Initial commit: 1942149
Mirroring from Micro.blog.: 838586
update: 575188
Update data.json: 548651
Update data.js: 548339
Add files via upload: 379941
*** empty log message ***: 358528
Can't you see I'm updating the time?: 286863

7. キャッシュ保存と統計情報

最初のクエリの後、BigQuery は結果をキャッシュに保存します。その結果、後続のクエリの所要時間が大幅に短縮されます。クエリオプションを使用してキャッシュ保存を無効にすることもできます。BigQuery は、作成時間、終了時間、処理された合計バイト数など、クエリに関する統計情報も追跡します。

このステップでは、キャッシュ保存を無効にして、クエリに関する統計情報を表示します。

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "github_repos", "commits");
            
            var sql = $"SELECT subject AS subject, COUNT(*) AS num_duplicates FROM {table} GROUP BY subject ORDER BY num_duplicates DESC LIMIT 10";
            var queryOptions = new QueryOptions {
                UseQueryCache = false
            };

            var results = client.ExecuteQuery(sql, parameters: null, queryOptions: queryOptions);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["subject"]}: {row["num_duplicates"]}");
            }

            var job = client.GetJob(results.JobReference);
            var stats = job.Statistics;
            Console.WriteLine("----------");
            Console.WriteLine($"Creation time: {stats.CreationTime}");
            Console.WriteLine($"End time: {stats.EndTime}");
            Console.WriteLine($"Total bytes processed: {stats.TotalBytesProcessed}");
        }
    }
}

コードについて、いくつか注意点があります。まず、クエリオプションを導入し、UseQueryCache を false に設定して、キャッシュ保存を無効にします。次に、ジョブオブジェクトからクエリに関する統計情報にアクセスしました。

Cloud Shell に戻ってアプリを実行します。

dotnet run

以前と同様に、コミットメッセージとその発生回数の一覧が表示されます。また、最後にクエリに関する統計情報も表示されます。

Update README.md: 2509242
: 1971725
Initial commit: 1942149
Mirroring from Micro.blog.: 838586
update: 575188
Update data.json: 548651
Update data.js: 548339
Add files via upload: 379941
*** empty log message ***: 358528
Can't you see I'm updating the time?: 286863
----------
Creation time: 1533052057398
End time: 1533052066961
Total bytes processed: 9944197093

8. BigQuery へのデータの読み込み

独自のデータをクエリする場合は、まずデータを BigQuery に読み込む必要があります。BigQuery は、Google Cloud Storage、他の Google サービス、読み取り可能なソースなど、さまざまなソースからのデータの読み込みをサポートしています。ストリーミング挿入を使用してデータをストリーミングすることもできます。詳細については、BigQuery へのデータの読み込みをご覧ください。

このステップでは、Google Cloud Storage に保存されている JSON ファイルを BigQuery テーブルに読み込みます。JSON ファイルは gs://cloud-samples-data/bigquery/us-states/us-states.json にあります。

JSON ファイルの内容を確認するには、Cloud Shell で gsutil コマンドラインツールを使用してダウンロードします。

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

Copying gs://cloud-samples-data/bigquery/us-states/us-states.json...
/ [1 files][  2.0 KiB/  2.0 KiB]                                                
Operation completed over 1 objects/2.0 KiB.

米国の州のリストが含まれており、各州が別の行の JSON ドキュメントになっていることがわかります。

less us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

この JSON ファイルを BigQuery に読み込むには、BigQueryDemo フォルダ内の Program.cs ファイルに移動し、コードを次のコードに置き換えます。projectId は実際のプロジェクト ID に置き換えてください。

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var gcsUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
            var client = BigQueryClient.Create("projectId");
            var dataset = client.GetOrCreateDataset("us_states_dataset");

            var schema = new TableSchemaBuilder 
            {
                { "name", BigQueryDbType.String },
                { "post_abbr", BigQueryDbType.String }
            }.Build();

            var jobOptions = new CreateLoadJobOptions
            {
                SourceFormat = FileFormat.NewlineDelimitedJson
            };

            var table = dataset.GetTableReference("us_states_table");
            var loadJob = client.CreateLoadJob(gcsUri, table, schema, jobOptions);

            loadJob.PollUntilCompleted();
            loadJob.ThrowOnAnyError();
            Console.WriteLine("Json file loaded to BigQuery");
        }
    }
}

コードが JSON ファイルを読み込み、データセットの下にスキーマを含むテーブルを作成する方法を 1 ～ 2 分ほどで確認します。

Cloud Shell に戻ってアプリを実行します。

dotnet run

BigQuery にデータセットとテーブルが作成される

Json file loaded to BigQuery

データセットが実際に作成されたことを確認するには、BigQuery コンソールに移動します。新しいデータセットとテーブルが作成されていることを確認します。テーブルのプレビュータブに切り替えると、実際のデータを確認できます。

9. 完了

C# を使用して BigQuery を使用する方法を学びました。

クリーンアップ

このクイックスタートで使用するリソースに対して Google Cloud Platform アカウントに課金されないようにするには:

Cloud Platform Console に移動します。
シャットダウンするプロジェクトを選択し、上部の [削除] をクリックします。これにより、プロジェクトの削除がスケジュールされます。

詳細

Google BigQuery: https://cloud.google.com/bigquery/docs/
Google Cloud Platform での C#/.NET: https://cloud.google.com/dotnet/
Google Cloud .NET クライアント: https://googlecloudplatform.github.io/google-cloud-dotnet/

ライセンス

この作業はクリエイティブ・コモンズの表示 2.0 汎用ライセンスにより使用許諾されています。

C# での BigQuery の使用

1. 概要

学習内容

必要なもの

アンケート

このチュートリアルをどのように使用されますか？

C# のご利用経験はどの程度ありますか？

Google Cloud Platform サービスのご利用経験についてどのように評価されますか？

2. 設定と要件

セルフペース型の環境設定

Cloud Shell を起動する

Cloud Shell をアクティブにする

3. BigQuery API を有効にする

4. C# 用の BigQuery クライアント ライブラリをインストールする

5. シェイクスピア作品のクエリ

6. GitHub データセットに対するクエリ

7. キャッシュ保存と統計情報

8. BigQuery へのデータの読み込み

9. 完了

クリーンアップ

詳細

ライセンス

4. C# 用の BigQuery クライアントライブラリをインストールする