C#エンジニアのためのBigQuery入門（3）
LINQ to BigQueryによるクエリ実行
C#プログラマーにおなじみのLINQでBigQueryのクエリを実行するライブラリを紹介。
よりC#らしいクエリの実行
前回の記事では、グーグルが提供しているBigQueryのClient Libraryを用いたクエリ実行について説明した。しかし記事中に書いたように、このライブラリのクエリ結果の1レコードはobjectの配列（1オブジェクトが1カラムに対応）と非常にプリミティブであり、クエリ結果をさらにプログラムで処理するようなケースでは扱いづらく感じることがあるだろう。
そこで今回はLINQ to BigQueryというライブラリを紹介する。作者はneueccさんであり、自身のブログでも紹介しているため説明が重なる部分もあるが、この記事も併せて読んでぜひ利用してもらいたい。
LINQ to BigQueryをはじめよう
準備
それではさっそく使ってみよう。この記事では、Visual Studio 2015でコンソールプロジェクトを作成し、そこからクエリを実行してみたい。プロジェクトを作成したら、NuGetからLINQ to BigQueryをインストールする（リスト1）。バージョンは執筆時点での最新版 0.63を利用した。
|
PM> Install-Package LINQ-to-BigQuery -Version 0.6.3
|
追加したらまずは認証情報を設定する。前々回と前回で説明した通り、Google Developers Consoleで認証用のアカウントを作成する。
下に載せたコードはサービスアカウントによる認証であるが、JSONによる認証でも問題ない。まずはBigqueryServiceクラス（Google.Apis.Bigquery.v2名前空間）のインスタンスを作成する（リスト1）。
|
using BigQuery.Linq;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Bigquery.v2;
using Google.Apis.Services;
using System.Security.Cryptography.X509Certificates;
……省略……
static BigqueryService CreateClient()
{
var certificate = new X509Certificate2(@"<p12証明書ファイルのパス>", "<証明書のパスワード>", X509KeyStorageFlags.Exportable);
var credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer("<サービスアカウントのメールアドレス>")
{
Scopes = new[]
{
BigqueryService.Scope.Bigquery
}
}.FromCertificate(certificate));
return new BigqueryService(new BaseClientService.Initializer
{
ApplicationName = "API Sample",
HttpClientInitializer = credential
});
}
|
.p12ファイルのパスとパスワード、およびサービスアカウントのメールアドレス（＝［認証情報］のページで取得できる）は各自の環境に合わせてほしい。
そして、LINQ to BigQueryの起点となるBigQueryContextクラス（BigQuery.Linq名前空間）のインスタンスを生成する（リスト2）。その際、引数に先ほどのBigqueryServiceのインスタンスとプロジェクトIDを与える。プロジェクト名は、認証アカウントを作成したプロジェクトIDを指定する。この後のクエリの実行で、publicdataというプロジェクトIDに属するsampleデータセットに対してクエリを実行するが、第三者のプロジェクトに対してクエリを実行する場合でも、このプロジェクトIDは認証情報を作成したプロジェクトのものを指定する。
|
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
|
※プロジェクトIDはGoogle Developers Consoleのトップページなどで確認できる。
クエリを実行する前に、クエリ対象のテーブルをC#のクラスにマッピングするためのクラス定義を出力しよう。クラス定義を使うことで、BigQueryのクエリをLINQで記述できる。LINQ to BigQueryの最新版では、データセットを指定することで、そのデータセットに属するテーブルに対応したC#のクラス定義を出力するメソッドが用意されている。これを使ってpublicdata:samplesデータセットに属するテーブルを出力しよう。
出力されるクラス定義が長いため、以下のコードでは一時ファイルに書き出し、書き出したファイル名をコンソールに出力している。
|
using System.IO;
……省略……
static void DumpCSharpClass(BigQueryContext context, string dataset)
{
var clazz = context.BuildCSharpClass(dataset);
var file = Path.GetTempFileName();
using (var writer = new StreamWriter(file))
{
foreach (var c in clazz)
{
writer.Write(c);
}
}
Console.WriteLine(file);
Console.ReadLine();
}
|
リスト3のコードの後にDumpCSharpClass(context, "publicdata:samples");のような1行を追加してプログラムを実行し、実際に一時ファイルが出力されたら、そのクラス定義をプロジェクト内の適当な.csファイルとして保存する（※ファイル冒頭にusing BigQuery.Linq;とusing System;を追記する必要がある）。
これで準備ができた。前回のAPIによるクエリの実行と同じクエリをLINQ to BigQueryで実行してみよう（リスト5）。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var res = await context.From<shakespeare>()
.Select(x => new
{
word = BqFunc.Top(x.word, 50),
count = BqFunc.Count()
})
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.word}, {row.count}");
}
}
|
もし上のクエリをコピー＆ペーストして実行された方がいたら、ぜひもう一度Visual Studioで同じコードを入力してほしい。LINQ to BigQueryのAPIがIntelliSenseと親和性がよいことが分かるだろう。まず、contextの後でIntelliSenseを開くと、From<T>を含めたFromXXXなメソッドが表示される。これは、クエリ対象のテーブルを決めるためのメソッドである。例えばFromRangeはテーブルレンジクエリのためのメソッドである（次回説明）。これらメソッドの型引数のTには、BigQueryクエリ構文におけるFROM節で指定するテーブルに対応したC#のクラス名を指定する。
次にFromメソッドでテーブル指定した後にIntelliSenseを出すと、WhereやSelectもしくはWithSnapshotなどのメソッドが表示される。WhereやSelectは、BigQueryのWHERE節、SELECT節に対応したもので、WithSnapshotはある時点でのテーブルのスナップショットをクエリの対象に指定するメソッドだ。
Selectメソッドの引数は、匿名オブジェクトを使って、プロパティにSELECT節で指定するフィールドを定義するのが簡単だろう。TOP()やCount(*)といったBigQueryの組み込み関数は、BqFuncクラス（BigQuery.Linq名前空間）に定義されているものを使う。Selectメソッドの後でIntelliSenseを出すと、今度はGroupByなどが表示されていることが分かるだろう。これは、GROUP BY節で指定するフィールドは事前にSELECT節で指定していないといけないためである。このように、LINQ to BigQueryのIntelliSenseは正しいクエリを記述しやすくなっている。
最後にRunメソッドもしくはRunAsyncメソッドを実行してクエリを発行し、結果を取得する。今回のコードを実行すると、下のようなコンソールの表示になるだろう。
クエリの結果から、発行されたSQL文やスキャンしたデータ量などを取得できる。もし、クエリが実行可能かどうかやスキャンするデータ量を事前に知りたいなどの場合は、RunDryメソッドでDry実行ができる。また、クエリのみ取得したい場合は、RunAsyncやRunDryの代わりにToStringを呼べばクエリ文字列を取得できる。
なお、LINQ to BigQueryでのクエリの実行は、前回説明した同期的なAPI実行である。そのため、最近のBigQueryのUpdateで導入されたUDF（ユーザー定義関数）は実行できない。最近のUpdateに関しては、本稿の最後の節に別途まとめている。
時間の扱い
BigQueryを扱うときの使いづらいと感じるところに、格納された時間（timestamp型）が UTCになってしまう点がある（データの挿入については次回以降で説明するが、UTC以外のタイムゾーンの時間を挿入しても、UTCとして保存されてしまう）。LINQ to BigQueryを使うと、手元のローカルタイムで検索および表示ができるため便利である。試してみよう。
今回、timestamp型のカラムを持つサンプルデータを扱うため、githubarchive:day.yesterdayというビューを対象にする（※なお、このビューはGitHub Archiveというパブリックなデータセットに含まれている。第1回の最後でも説明しているが、これを使うには、データセットのURL「https://bigquery.cloud.google.com/table/githubarchive:day.events_20150101にアクセスすればよい。これだけで、Google BigQueryの管理ページにgithubarchive:day.yesterdayビューが追加される。そして、これをLINQ to BigQueryで使うために、リスト4で説明したようにDumpCSharpClass(context, "githubarchive:day");という1行を実行して定義クラスのファイルを出力し、それを.csファイルとしてプロジェクトに追加する必要がある。ビルドをするとクラス名の重複のエラーが出る場合は、取りあえずそのクラス名を変更して回避する）。
対象のyesterdayというのは、GitHubでのイベント（リポジトリの作成など）を日ごとにまとめているテーブルのうち、昨日のものを指すテーブルへのビューである。このビューに対して、ローカルタイムゾーンの9～18時に発生したイベント数の上位50のリポジトリを取得してみよう（リスト6）。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var yest = DateTime.Today.AddDays(-1);
var start = yest.AddHours(9);
var end = yest.AddHours(18);
var res = await context.From<yesterday>()
.Where(x => x.created_at >= start && x.created_at < end)
.Select(x => new
{
repo = BqFunc.Top(x.repo.name, 50),
count = BqFunc.Count()
})
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.repo}, {row.count}");
}
}
|
デフォルトでは、実行している環境のタイムゾーンにおける9時～18時の間で検索する。
時間の比較をするのに、DateTimeオブジェクトを生成し、そのままWhereメソッドの比較条件に渡している。BigQueryではUTC表記でリテラルとして表現したり、関数を経由したりする必要があるので、DateTimeで記述できるLINQ to BigQueryは便利である。
クエリの具体例
ここからは、グーグルのQuery Referenceのページ原文・日本語訳にあるサンプルを引用して、LINQ to BigQueryでの具体的な書き方を紹介したい。「BigQueryのクエリ構文による書き方」「LINQ to BigQueryによる書き方」「実行結果」「説明」という順序で、サンプル項目別に説明していく。
サブクエリ
|
SELECT corpus, count_corpus_words
FROM
(SELECT corpus, count(word) AS count_corpus_words
FROM publicdata:samples.shakespeare
GROUP BY corpus) AS sub_shakespeare
WHERE count_corpus_words > 4000
|
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var res = await context.From<shakespeare>()
.Select(x => new
{
x.corpus,
CountCorpusWords = BqFunc.Count(x.word)
})
.GroupBy(x => x.corpus)
.Into()
.Where(y => y.CountCorpusWords > 4000)
.Select(y => new
{
y.corpus,
y.CountCorpusWords
})
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.corpus}, {row.CountCorpusWords}");
}
}
|
サブクエリをLINQ to BigQueryで記述する場合、まずサブクエリを先に記述し、Intoメソッドで記述したクエリをサブクエリとして扱うようにする。その後はまたFromでテーブルを指定したときと同じようにクエリを記述すればよい。
集計値を使ったクエリの結果のフィルタリング
HAVING節を使った、集計関数の結果をもとに結果をフィルターするクエリである。
|
SELECT
state,
IF (is_male, 'Male', 'Female') AS sex,
COUNT(*) AS cnt
FROM
[publicdata:samples.natality]
WHERE
state != ''
GROUP BY
state, sex
HAVING
cnt > 3000000
ORDER BY
cnt DESC
|
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var res = await context.From<natality>()
.Where(x => x.state != null)
.Select(x => new
{
x.state,
x.is_male,
cnt = BqFunc.Count()
})
.GroupBy(x => new { x.state, x.is_male})
.Having(x => x.cnt > 3000000)
.OrderByDescending(x => x.cnt)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows.Select(r => new
{
r.state,
sex = r.is_male ? "Male" : "Female",
r.cnt
}))
{
Console.WriteLine($"{row.state}, {row.sex}, {row.cnt}");
}
}
|
これもIntelliSenseで確かめてほしいが、Havingメソッドを呼び出せるのはGroupByメソッドの後となっている。また、元のクエリではSELECT節の中でbool型のis_maleカラムの値を文字列に変換しているが、LINQ to BigQueryのサンプルではクエリを実行した後、.NET Framework側のLINQで変換を行っている。クエリのみで処理する場合には、このような見た目を整える処理もクエリの中で行う必要があるが、LINQ to BigQueryの場合は、クエリを実行した後、適宜、クライアントサイドの処理として記述することも容易である。
TimeSliceによる集計
ある一定の時間間隔ごとに集計を行うクエリである。TimeSliceによる集計を行うときのポイントは、時間間隔（ここでは月）を示す値を文字列として出力するフィールドを定義し、そのフィールドでGROUP BY集計を行う点である。timestamp型ではGROUP BYができないため、このような対応をしている。このケースでは、UTC_USEC_TO_MONTH関数でtimestampをその月の最初の時間に変換し、FORMAT_UTC_USEC関数で文字列に変換した最初の7文字をとることで、「2006-08」といった月表示の文字列を取得している。
|
SELECT
contributor_username,
/* Return the timestamp shifted to the
* start of the month, formatted in
* a human-readable format. Uses the
* 'LEFT()' string function to return only
* the first 7 characters of the formatted timestamp.
*/
LEFT (FORMAT_UTC_USEC(
UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
AS month,
SUM(LENGTH(comment)) as total_chars_used
FROM
[publicdata:samples.wikipedia]
WHERE
(contributor_username != '' AND
contributor_username IS NOT NULL)
AND timestamp > 1133395200
AND timestamp < 1157068800
GROUP BY
contributor_username, month
ORDER BY
total_chars_used DESC;
|
このクエリで出てくるtimestampはint型の秒単位のUNIX時間である。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var res = await context.From<wikipedia>()
.Where(x => x.contributor_username != null &&
x.contributor_username != "" &&
x.timestamp > 1133395200 &&
x.timestamp < 1157068800)
.Select(x => new
{
x.contributor_username,
month = BqFunc.StrftimeUtcUsec(BqFunc.UtcUsecToMonth(x.timestamp * 1000000), "%Y-%m"),
TotalCharsUsed = BqFunc.Sum(BqFunc.Length(x.comment))
})
.GroupBy(x => new { x.contributor_username, x.month })
.OrderByDescending(x => x.TotalCharsUsed)
.Limit(10)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.contributor_username}, {row.month}, {row.TotalCharsUsed}");
}
}
|
なお、図5に示す通り、16GBytesほどデータをスキャンするため、実行する場合は課金額に注意されたい。
LINQ to BigQueryでも同様のアプローチをとるが、こちらではSTRFTIME_UTC_USEC関数に対応する、StrftimeUtcUsecメソッドを使っており、書式を指定している。
Window関数
結果セットの中の特定のパーティションだけを対象として操作する処理である。BigQueryのクエリ構文では、Window関数（ウィンドウ関数、窓関数）として、CUME_DIST()やRANK()など多数の関数が用意されている。
|
SELECT
word,
word_count,
CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank
FROM
[publicdata:samples.shakespeare]
WHERE
corpus='othello' and length(word) > 10
ORDER BY
word_count DESC
|
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var res = await context.From<shakespeare>()
.Where(x => x.corpus == "othello" && BqFunc.Length(x.word) > 10)
.Select(x => new
{
x.word,
x.word_count,
CumeDist = BqFunc.CumulativeDistribution(x)
.PartitionBy(y => y.corpus)
.OrderByDescending(y => y.word_count)
.Value,
Rank = BqFunc.Rank(x)
.PartitionBy(y => y.corpus)
.OrderByDescending(y => y.word_count)
.Value,
})
.OrderByDescending(x => x.word_count)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.word}, {row.word_count}, {row.CumeDist}");
}
}
|
Window関数は、Selectメソッドの引数の匿名オブジェクトのプロパティを定義するときに使っている。Window関数もBqFuncクラスに定義されているが、そのWindow関数（この例ではCumulativeDistribution／Rankメソッド）を呼び出し、さらにPartitionByメソッドやOrderByメソッドを呼び出した後、最後のValueプロパティを取得する必要がある。
結果セットのバケットを使った分類
クエリの結果セットをBigQuery以外で定義されているバケットを使って分類する例である。リスト15では、stateごとにあらかじめ定義した対応するregionを表示するようにしている。
|
SELECT
CASE
WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
THEN 'West'
WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
'MD', 'DC', 'DE')
THEN 'South'
WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
THEN 'Midwest'
WHEN state IN ('NY', 'PA', 'NJ', 'CT',
'RI', 'MA', 'VT', 'NH', 'ME')
THEN 'Northeast'
ELSE 'None'
END as region,
average_mother_age,
average_father_age,
state, year
FROM
(SELECT
year, state,
SUM(mother_age)/COUNT(mother_age) as average_mother_age,
SUM(father_age)/COUNT(father_age) as average_father_age
FROM
publicdata:samples.natality
WHERE
father_age < 99
GROUP BY
year, state)
ORDER BY
year
LIMIT 5;
|
クエリだけで完結させるためにCASE構文を使っているが、LINQ to BigQueryの場合、クエリを実行した後の結果セットに対し、クライアントサイドでデータ加工を行うことができる。今回はサンプルのため、メソッド内でstateとregionの対応を定義しているが、これが他のデータストアに定義されている場合でも同様に利用できるだろう。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var res = await context.From<natality>()
.Where(x => x.father_age < 99)
.Select(x => new
{
x.year,
x.state,
AverageMotherAge = BqFunc.Sum(x.mother_age) / BqFunc.Count(x.mother_age),
AverageFatherAge = BqFunc.Sum(x.father_age) / BqFunc.Count(x.father_age)
})
.GroupBy(x => new { x.year, x.state })
.Limit(5)
.RunAsync();
var dict = new Dictionary<string, string>();
var west = new[] { "WA", "OR", "CA", "AK", "HI", "ID", "MT", "WY", "NV", "UT", "CO", "AZ", "NM" };
var south = new[] { "OK", "TX", "AR", "LA", "TN", "MS", "AL", "KY", "GA", "FL", "SC", "NC", "VA", "WV", "MD", "DC", "DE" };
var mideast = new[] { "ND", "SD", "NE", "KS", "MN", "IA", "MO", "WI", "IL", "IN", "MI", "OH" };
var northeast = new[] { "NY", "PA", "NJ", "CT", "RI", "MA", "VT", "NH", "ME" };
foreach (var e in west)
dict[e] = "West";
foreach (var e in south)
dict[e] = "South";
foreach (var e in mideast)
dict[e] = "Midwest";
foreach (var e in northeast)
dict[e] = "Northeast";
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
var region = dict.ContainsKey(row.state) ? dict[row.state] : "None";
Console.WriteLine($"{region}, {row.AverageMotherAge}, {row.AverageFatherAge}, {row.state}, {row.year}");
}
}
|
リスト15のように分類処理がトップレベルのクエリであれば、リスト16のようにLINQ to BigQueryの結果をクライアント側の処理で分類することができた。しかし場合によってはサブクエリ内で分類処理を行い、処理結果をさらにクエリで処理したいケースもあるだろう。その場合はCASE文をLINQ to BigQueryで記述する必要がでてくる。その際はリスト16-Bのように参考演算子で記述すればよい。（※2015/11/17追記）
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var west = new[] { "WA", "OR", "CA", "AK", "HI", "ID", "MT", "WY", "NV", "UT", "CO", "AZ", "NM" };
var south = new[] { "OK", "TX", "AR", "LA", "TN", "MS", "AL", "KY", "GA", "FL", "SC", "NC", "VA", "WV", "MD", "DC", "DE" };
var mideast = new[] { "ND", "SD", "NE", "KS", "MN", "IA", "MO", "WI", "IL", "IN", "MI", "OH" };
var northeast = new[] { "NY", "PA", "NJ", "CT", "RI", "MA", "VT", "NH", "ME" };
var res = await context.From<natality>()
.Where(x => x.father_age < 99)
.Select(x => new
{
x.year,
x.state,
AverageMotherAge = BqFunc.Sum(x.mother_age) / BqFunc.Count(x.mother_age),
AverageFatherAge = BqFunc.Sum(x.father_age) / BqFunc.Count(x.father_age),
})
.GroupBy(x => new { x.year, x.state })
.Into()
.Select(x => new
{
Region = BqFunc.In(x.state, west) ? "West" :
BqFunc.In(x.state, south) ? "South" :
BqFunc.In(x.state, mideast) ? "MidEast" :
BqFunc.In(x.state, northeast) ? "Northeast" : "NONE",
x.year,
x.state,
x.AverageMotherAge,
x.AverageFatherAge
})
.OrderBy(x => x.year)
.Limit(5)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.Region}, {row.AverageMotherAge}, {row.AverageFatherAge}, {row.state}, {row.year}");
}
}
|
最近のBigQueryのUpdate
今回のテーマとは少し離れるが、最近、BigQueryに大きなUpdateがあったので紹介しておきたい。
UDF（ユーザー定義関数）
JavaScriptでユーザー定義関数を定義し、クエリから参照できるようになった。前述している通り、非同期なクエリ実行APIでしか現状サポートしていない。グーグルのドキュメントにあるサンプルをC#から実行すると、以下のようになる。
|
using Google.Apis.Bigquery.v2.Data;
……省略……
private static async Task ExecuteUDFAsync()
{
var bigquery = CreateClient();
var project = "<プロジェクトID>";
var res = await bigquery.Jobs.Insert(new Job
{
Configuration = new JobConfiguration
{
Query = new JobConfigurationQuery
{
UserDefinedFunctionResources = new List<Google.Apis.Bigquery.v2.Data.UserDefinedFunctionResource>
{
new UserDefinedFunctionResource()
{
InlineCode = @"
// The UDF
function urlDecode(row, emit) {
emit({title: decodeHelper(row.title),
requests: row.num_requests});
}
// Helper function for error handling
function decodeHelper(s) {
try {
return decodeURI(s);
} catch (ex) {
return s;
}
}
// UDF registration
bigquery.defineFunction(
'urlDecode', // Name used to call the function from SQL
['title', 'num_requests'], // Input column names
// JSON representation of the output schema
[{name: 'title', type: 'string'},
{name: 'requests', type: 'integer'}],
urlDecode // The function reference
);
"
}
},
Query = @"
SELECT requests, title
FROM
urlDecode(
SELECT
title, sum(requests) AS num_requests
FROM
[fh-bigquery:wikipedia.pagecounts_201504]
WHERE language = 'fr'
GROUP EACH BY title
)
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100
"
}
}
}, project).ExecuteAsync();
Job job;
do
{
job = await bigquery.Jobs.Get(project, res.JobReference.JobId).ExecuteAsync();
Console.WriteLine(job.Status.State);
await Task.Delay(TimeSpan.FromSeconds(1));
} while (job.Status.State != "DONE");
var queryResult = await bigquery.Jobs.GetQueryResults(project, job.JobReference.JobId).ExecuteAsync();
foreach (var row in queryResult.Rows)
{
foreach (var f in row.F)
{
Console.WriteLine(f.V);
}
}
}
|
ジョブのキャンセルをサポート
例えば、上のUDFを利用するサンプルコードで、ジョブの完了を待機している箇所で、時間がかかりすぎている場合、APIからCancelメソッドを呼び出してキャンセルできるようになった。
Streaming Insertの課金体系の変更、Federated DataSource、Streaming Inserts用のstreaming buffer statisticsの導入
これらはデータ投入に関連するため、次回連載記事で説明する予定である。巨大なデータセットを扱う場合に追加課金が発生するようになっている。
Web UIの改善
新機能のUDFを扱うためのエディター機能が追加されたのに加え、テーブルの末尾に日付を入れて日ごとにテーブルを分けて扱っている場合に、まとめて表示する機能が追加された（図8）。今までは日付分、左側のペインにテーブルが並んでいて非常に見づらい状況であった。
JOINや GROUP BYでEACH不要
巨大なデータセットをJOINやGROUP BYする場合にはEACHを指定する必要があったが、パフォーマンス改善により、EACHがなくても最適化して実行するようになった。
Billing Alerts機能
上で挙げてきたUpdateとはタイミングは違うが、Google Cloud Platform全体での課金額に対して閾値（いきち、上限）を設定し、その上限に近づいたときや超えたときに通知してくれるBilling Alerts機能が追加された。BigQueryは有料のサービスであるため、これを設定しておくと知らない間に使いすぎることを防ぐことができるだろう。
まとめ
今回はLINQ to BigQueryを使ったクエリの実行を、さまざまな具体例で説明した。C#エンジニアにとってはLINQという使い慣れたコンテキストで、BigQueryのクエリを記述できるのがメリットである。次回は、今回の記事にも出てきたテーブルデコレーターの説明と、LINQ to BigQueryを使ってテーブルデコレーターを利用する方法を説明する。
※以下では、本稿の前後を合わせて5回分（第1回～第5回）のみ表示しています。
連載の全タイトルを参照するには、［この記事の連載 INDEX］を参照してください。
1. 誰でも簡単に超高速なクエリができるBigQueryとは？
知らないと損！ 使わないと損！ これからのデータ解析に必須のBigQueryの概要を紹介。また、Webコンソールからのクエリ実行の基礎を解説する。
2. Google API Client Library for .NETの使い方
BigQueryをはじめ、GoogleのほとんどのサービスはAPIが提供されている。これを.NETから利用するためのライブラリの基本的な使用方法を解説する。
4. 【現在、表示中】≫ LINQ to BigQueryによるクエリ実行
C#プログラマーにおなじみのLINQでBigQueryのクエリを実行するライブラリを紹介。
5. LINQでBigQuery： データスキャン量を抑えたクエリの実行方法
膨大なデータへのクエリで、スキャン量を減らしてクエリの課金額を抑えるには？ テーブルワイルドカード関数とテーブルデコレーターを説明する。
