C#エンジニアのためのBigQuery入門（4）
LINQでBigQuery： データスキャン量を抑えたクエリの実行方法
膨大なデータへのクエリで、スキャン量を減らしてクエリの課金額を抑えるには？ テーブルワイルドカード関数とテーブルデコレーターを説明する。
BigQuery特有のクエリ構文
前回の記事では、LINQ to BigQueryを使ったクエリの実行について説明した。今回は前回説明しきれなかった、テーブルワイルドカード関数およびテーブルデコレーターについて説明したい。
データスキャン量を抑えるための機能
テーブルワイルドカード関数やテーブルデコレーターといった機能はBigQuery独特の機能であるが、この機能が活用される背景には、BigQueryのテーブルはレコードの追記のみが可能で、更新や削除ができないことがある。
日々レコードが追加されていくテーブルを、同じテーブルのまま利用していくと仮定しよう。まずレコード数が増えるため、同じクエリであってもデータスキャン量が増える。そのため、クエリの課金額も増える、という問題が生じるだろう。また、古くなったデータを削除することになっても、レコードの削除ができない。もちろん、新規に同じスキーマの別名テーブルに対し、必要なレコードを抽出するWHERE条件を付けてSELECTした結果データを新テーブル側に挿入し、元のテーブルを削除して、別名テーブルを元のテーブルにリネームする、といった運用もできなくはないが、非常に煩雑である。
そこで一般的に活用されているのが、ポストフィクスを付けたテーブルである。
ポストフィクスは日付を利用することが多い。例えば、アクセスログを保存する場合、AccessLogという名前の後ろに日付をポストフィクスとして追加する。11/1のレコードであれば AccessLog_20151101、11/2のレコードあればAccessLog_20151102といった具合である。必ずしも1日おきにテーブルを作る必要はなく、データ量に応じて月ごとや年ごとにテーブルを作ればいいだろう。
このような運用をすれば、テーブルのサイズが増え続けることもなく、古いレコードを削除したい場合はテーブルごと削除すればよい。
この運用をすると、複数の日にまたがったレコードをクエリの対象とする場合、複数のテーブルをクエリの対象にする必要がある。テーブルワイルドカード関数を使うことによって、あらかじめ指定した条件に一致するテーブルのみを対象とすることができる。
一方、テーブルデコレーターは常にレコードが挿入されているようなテーブルで、通常のクエリではスキャン量が多くなってしまうような場合に、レコードの挿入時刻でスキャン対象をフィルターする機能である。つまりどちらも、データスキャン量を抑えることのできる機能である。
テーブルワイルドカード関数
TABLE_DATE_RANGE
今回は日付付きのポストフィクステーブルをクエリの対象とするため、githubarchive:day.events_YYYYMMDD（YYYYMMDDは日付で、執筆時点では2015年1月1日以降毎日作成されている）に公開されているテーブルを使用する。まず、BigQueryのクエリを使って5日前～3日前のテーブルを対象にしたクエリを紹介する。
|
SELECT
[type],
COUNT(*) AS [count]
FROM
TABLE_DATE_RANGE([githubarchive:day.events_], TIMESTAMP('2015-10-29'), TIMESTAMP('2015-10-31'))
GROUP BY
[type]
|
リスト1のように、FROM句にTABLE_DATE_RANGE関数の引数には、テーブルのポストフィクスを除いた共通の部分と、TIMESTAMPを指定する。では、これをLINQ to BigQueryで書いてみよう。Visual Studioでのプロジェクトの作り方、NuGetでのプロジェクトの追加、認証しBigQueryContextを生成する処理などは、前回の記事を参考にしてほしい。
まず、テーブルに対応したC#クラスの定義で、TablePrefix属性に変更し、日付を除いた部分を属性の引数に指定する（リスト2）。
|
[TablePrefix("[githubarchive:day.events_]")]
public class events
{
public string type { get; set; }
public bool @public { get; set; }
public string payload { get; set; }
public repo repo { get; set; }
public actor actor { get; set; }
public org org { get; set; }
public DateTimeOffset created_at { get; set; }
public string id { get; set; }
}
|
クエリを実行するコードはリスト3となる。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var start = DateTime.Today.AddDays(-5);
var end = DateTime.Today.AddDays(-3);
var res = await context
.FromDateRange<events>(start, end)
.Select(x => new
{
x.type,
count = BqFunc.Count()
})
.GroupBy(x => x.type)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.type}, {row.count}");
}
}
|
なお、このコードで実行されるクエリ内容はリスト1と同じである。
Fromメソッドの代わりにFromDateRangeメソッドを使い、その引数にDateTimeOffset（＝リスト3ではDateTimeオブジェクトを指定し、DateTimeOffset型への暗黙的な変換を利用している）を指定し、開始日と終了日を指定している。FromDateRangeメソッドにはいくつかオーバーロードが用意されているが、引数を指定せずに今日（＝DateTime.Today）のテーブルを対象にするものや、開始日のみを指定して開始日から今日までのテーブルを対象にするものをよく使うだろう。
TABLE_DATE_RANGE_STRICT
さて、TABLE_DATE_RANGE関数は指定期間に欠けている日付がある場合、その日付以外のテーブルをクエリの対象とする。例えば、2日前～2日後の日付を対象にしてリスト2と同様のコードを実行すると、2日前～今日もしくは1日前の日付を対象にした結果と変わらないはずだ（※今日までか1日前までかのどちらになるかは、その日のテーブルが作成されるタイミングによる）。それに対し、テーブルが存在しない日付を対象にした場合エラーを返すのが、TABLE_DATE_RANGE_STRICT関数である。LINQ to BigQueryではリスト4のようにFromDataRangeStrictメソッドに対応する。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var start = DateTime.Today.AddDays(-2);
var end = DateTime.Today.AddDays(2);
var res = await context
.FromDateRangeStrict<events>(start, end)
.Select(x => new
{
x.type,
count = BqFunc.Count()
})
.GroupBy(x => x.type)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.type}, {row.count}");
}
}
|
なお、このコードは実行時エラーになる。
LINQ to BigQueryでは、指定した範囲の中に存在しないテーブルが存在する場合、実行時エラーとなる。例外の型はGoogleApiExceptionであるが、Google API関連が原因の例外は、全てこの型になる。このため、より詳細な例外処理をするためには、メッセージに特定の文字列が含まれているか、などで処理する必要があるだろう。例えばリスト4のコードを実行すると「Not found: Table githubarchive:day.events_20151116」といった文字列が含まれているため、「Not found: Table」と後続のテーブル名が手掛かりになるだろう。
なお、メッセージの形式は仕様として定まっていないため、変更される可能性があるので注意されたい。過去実際に、メッセージの大文字・小文字が変更されたこともある。
TABLE_QUERY
ここまでのテーブルワイルドカード関数は、毎日テーブルが作られることを前提として、開始日と終了日の範囲指定で対象のテーブルを指定できた。より汎用（はんよう）的に、テーブル名を文字列として扱い、条件式に一致するテーブルをクエリ対象にできるのが、TABLE_QUERY関数である。例えば正規表現を使って各月下旬（＝20日以降）のテーブルを対象にするクエリは、リスト5のように書ける。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var start = DateTime.Today.AddDays(-2);
var end = DateTime.Today.AddDays(2);
var res = await context
.FromTableQuery<events>("githubarchive:day", t => BqFunc.RegexpMatch(t.table_id, @"2015\d{2}(2|3)\d"))
.Select(x => new
{
x.type,
count = BqFunc.Count()
})
.GroupBy(x => x.type)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.type}, {row.count}");
}
}
|
なお、このクエリは800MBytes以上スキャンするため、実行するときは課金額に注意してほしい。
正規表現に限らず、BigQueryの式で記述できれば利用できるため、リスト6のようにテーブル名の先頭から「events_」の文字列を取り除いた日付部分をパースし、日にち部分を比較することでフィルタリングするテーブルクエリも実行可能である。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var start = DateTime.Today.AddDays(-2);
var end = DateTime.Today.AddDays(2);
var res = await context
.FromTableQuery<events>("githubarchive:day", t => BqFunc.Day(BqFunc.Timestamp(BqFunc.TrimLeft(t.table_id, "events_"))) >= 20)
.Select(x => new
{
x.type,
count = BqFunc.Count()
})
.GroupBy(x => x.type)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.type}, {row.count}");
}
}
|
リスト5と同じくこのクエリは800MBytes以上スキャンするため、実行するときは課金額に注意してほしい。
テーブルデコレーター
テーブルワイルドカード関数が複数のテーブルにまたがってクエリを実行するための機能であるのに対し、テーブルデコレーターは1つのテーブルに対してデータスキャン量を削減するための機能である。ポストフィクス付きのテーブルを活用することで、日ごとのテーブルを作成している場合でも、アクセスログといった絶えずレコードが挿入されているテーブルでは全体のレコード量が多くなり、その結果クエリのデータスキャン量も増えがちである。そのようなテーブルに対し、「指定した時刻のスナップショット」もしくは「指定した時間帯に挿入されたレコード」をクエリの対象にできるのが、テーブルデコレーターという機能である。
スナップショットデコレーター
まず、指定した時間のスナップショットを取得する機能であるスナップショットデコレーターを使ってみよう。リスト7のように、From<T>メソッドを実行した後にWithSnapshotメソッドを呼び出す。現在時刻と比べた相対時間をTimeSpanで指定する、もしくは絶対時間をDateTimeOffsetで指定する2通りが使える。またテーブルデコレーターは単一のテーブルに対して作用するため、Fromメソッドでリスト2のようにTablePrefix属性を付けたクラスを利用する場合、引数にテーブル名を指定する。前回記事のようにTableName属性を付けて引数にテーブル名を指定しない方法も利用できるが、同じスキーマの日付が違うテーブルを同時に利用することを考慮すると、テーブル名を引数で指定する方が利用しやすいと思う。なお、テーブルデコレーターで指定する時刻は現在時刻より7日以内である必要がある。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var start = DateTime.Now.AddHours(-2);
var res = await context
.From<events>("githubarchive:day.events_20151115")
.WithSnapshot(TimeSpan.FromHours(2)) //相対時間を指定する場合
//.WithSnapshot(start) // 絶対時間を指定する場合
.Select(x => new
{
x.type,
count = BqFunc.Count()
})
.GroupBy(x => x.type)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.type}, {row.count}");
}
}
|
実際に利用する場合は、実行日に近いテーブルを指定し、データが存在している時間をスナップショットに指定しないと実行時エラーが発生する。
さて、リスト7で実行された実際のクエリを見てみるとリスト8のようになる。
|
# 相対時刻を指定した場合
SELECT
[type],
COUNT(*) AS [count]
FROM
[githubarchive:day.events_20151115@-7200000]
GROUP BY
[type]
# 絶対時刻を指定した場合
SELECT
[type],
COUNT(*) AS [count]
FROM
[githubarchive:day.events_20151115@1447556859324]
GROUP BY
[type]
|
テーブル名の後の@以降の数値に注目してほしい。どちらも単位はミリ秒で、相対時刻の場合は負の値、絶対時刻の場合は1970年1月1日からの経過時間を指定している。これはBigQuery側の仕様で決まっているためであるが、LINQ to BigQueryを使えば、TimeSpanもしくはDateTimeOffsetという.NET標準のオブジェクトで指定できる点が便利である。
レンジデコレーター
最後に時間範囲を指定して、その時間中に挿入されたレコードのみをクエリの対象とするレンジデコレーターを紹介する。使い方はWithSnapshotの代わりにWithRangeを指定すればよい。レンジデコレーターの場合も、相対時間をTimeSpanで指定する方法と、絶対時間をDateTimeOffsetで指定する方法が使える。また、第2引数を省略すると、現在時刻までの時間範囲となる。
|
static async Task ExecuteAsync()
{
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
var start = DateTime.Now.AddHours(-2);
var end = DateTime.Now.AddHours(-1);
var res = await context
.From<events>("githubarchive:day.events_20151115")
.WithRange(TimeSpan.FromHours(2), TimeSpan.FromHours(1)) //相対時間を指定する場合
//.WithRange(start, end) // 絶対時間を指定する場合
.Select(x => new
{
x.type,
count = BqFunc.Count()
})
.GroupBy(x => x.type)
.RunAsync();
// 実行されたクエリを表示
Console.WriteLine(res.Query);
// スキャンしたバイト数を表示
Console.WriteLine($"{res.TotalBytesProcessedFormatted} scanned.");
// 結果を出力
foreach (var row in res.Rows)
{
Console.WriteLine($"{row.type}, {row.count}");
}
}
|
まとめ
前回と今回で、LINQ to BigQueryによるクエリの実行について説明してきた。次回はBigQueryにデータを投入してテーブルを作る方法について、C#からAPIを実行して処理する方法を説明したい。
※以下では、本稿の前後を合わせて5回分（第2回～第6回）のみ表示しています。
連載の全タイトルを参照するには、［この記事の連載 INDEX］を参照してください。
2. Google API Client Library for .NETの使い方
BigQueryをはじめ、GoogleのほとんどのサービスはAPIが提供されている。これを.NETから利用するためのライブラリの基本的な使用方法を解説する。
5. 【現在、表示中】≫ LINQでBigQuery： データスキャン量を抑えたクエリの実行方法
膨大なデータへのクエリで、スキャン量を減らしてクエリの課金額を抑えるには？ テーブルワイルドカード関数とテーブルデコレーターを説明する。
6. BigQueryでのテーブル作成とデータ挿入
連載最終回。C#のクライアントライブラリを使ってBigQueryへデータを挿入する方法を説明。Google Cloud Storage APIの活用や、Streaming Insertについても解説する。
