C#エンジニアのためのBigQuery入門(3)

C#エンジニアのためのBigQuery入門(3)

LINQ to BigQueryによるクエリ実行

2015年10月5日

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
リスト1 パッケージ・マネージャー・コンソールからLINQ to BigQueryを追加するためのコマンド

 追加したらまずは認証情報を設定する。前々回前回で説明した通り、Google Developers Consoleで認証用のアカウントを作成する。

 下に載せたコードはサービスアカウントによる認証であるが、JSONによる認証でも問題ない。まずはBigqueryServiceクラス(Google.Apis.Bigquery.v2名前空間)のインスタンスを作成する(リスト1)。

C#
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
  });
}
リスト2 認証を行い、BigqueryServiceクラスのインスタンスを作成するコード(Program.csファイルのProgramクラス内)

.p12ファイルのパスとパスワード、およびサービスアカウントのメールアドレス(=[認証情報]のページで取得できる)は各自の環境に合わせてほしい。

 そして、LINQ to BigQueryの起点となるBigQueryContextクラス(BigQuery.Linq名前空間)のインスタンスを生成する(リスト2)。その際、引数に先ほどのBigqueryServiceのインスタンスとプロジェクトIDを与える。プロジェクト名は、認証アカウントを作成したプロジェクトIDを指定する。この後のクエリの実行で、publicdataというプロジェクトIDに属するsampleデータセットに対してクエリを実行するが、第三者のプロジェクトに対してクエリを実行する場合でも、このプロジェクトIDは認証情報を作成したプロジェクトのものを指定する。

C#
var bigquery = CreateClient();
var context = new BigQueryContext(bigquery, "<プロジェクトID>");
リスト3 LINQ to BigQueryの起点となるBigQueryContextを生成するコード

プロジェクトIDはGoogle Developers Consoleのトップページなどで確認できる。

 クエリを実行する前に、クエリ対象のテーブルをC#のクラスにマッピングするためのクラス定義を出力しよう。クラス定義を使うことで、BigQueryのクエリをLINQで記述できる。LINQ to BigQueryの最新版では、データセットを指定することで、そのデータセットに属するテーブルに対応したC#のクラス定義を出力するメソッドが用意されている。これを使ってpublicdata:samplesデータセットに属するテーブルを出力しよう。

 出力されるクラス定義が長いため、以下のコードでは一時ファイルに書き出し、書き出したファイル名をコンソールに出力している。

C#
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();
}
リスト4 指定したデータセットに属するテーブルに対応するC#のクラス定義を一時ファイルとして出力するコード

 リスト3のコードの後にDumpCSharpClass(context, "publicdata:samples");のような1行を追加してプログラムを実行し、実際に一時ファイルが出力されたら、そのクラス定義をプロジェクト内の適当な.csファイルとして保存する(ファイル冒頭にusing BigQuery.Linq;using System;を追記する必要がある)。

 これで準備ができた。前回のAPIによるクエリの実行と同じクエリをLINQ to BigQueryで実行してみよう(リスト5)。

C#
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}");
  }
}
リスト5 クエリを実行するコード

 もし上のクエリをコピー&ペーストして実行された方がいたら、ぜひもう一度Visual Studioで同じコードを入力してほしい。LINQ to BigQueryのAPIがIntelliSenseと親和性がよいことが分かるだろう。まず、contextの後でIntelliSenseを開くと、From<T>を含めたFromXXXなメソッドが表示される。これは、クエリ対象のテーブルを決めるためのメソッドである。例えばFromRangeはテーブルレンジクエリのためのメソッドである(次回説明)。これらメソッドの型引数のTには、BigQueryクエリ構文におけるFROM節で指定するテーブルに対応したC#のクラス名を指定する。

 次にFromメソッドでテーブル指定した後にIntelliSenseを出すと、WhereSelectもしくはWithSnapshotなどのメソッドが表示される。WhereSelectは、BigQueryのWHERE節、SELECT節に対応したもので、WithSnapshotはある時点でのテーブルのスナップショットをクエリの対象に指定するメソッドだ。

 Selectメソッドの引数は、匿名オブジェクトを使って、プロパティにSELECT節で指定するフィールドを定義するのが簡単だろう。TOP()Count(*)といったBigQueryの組み込み関数は、BqFuncクラス(BigQuery.Linq名前空間)に定義されているものを使う。Selectメソッドの後でIntelliSenseを出すと、今度はGroupByなどが表示されていることが分かるだろう。これは、GROUP BY節で指定するフィールドは事前にSELECT節で指定していないといけないためである。このように、LINQ to BigQueryのIntelliSenseは正しいクエリを記述しやすくなっている。

 最後にRunメソッドもしくはRunAsyncメソッドを実行してクエリを発行し、結果を取得する。今回のコードを実行すると、下のようなコンソールの表示になるだろう。

図1 リスト5のクエリ結果が表示された画面

 クエリの結果から、発行されたSQL文やスキャンしたデータ量などを取得できる。もし、クエリが実行可能かどうかやスキャンするデータ量を事前に知りたいなどの場合は、RunDryメソッドでDry実行ができる。また、クエリのみ取得したい場合は、RunAsyncRunDryの代わりに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)。

C#
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}");
  }
}
リスト6 DateTimeを使ったクエリの実行

デフォルトでは、実行している環境のタイムゾーンにおける9時~18時の間で検索する。

図2 リスト6の実行結果

 時間の比較をするのに、DateTimeオブジェクトを生成し、そのままWhereメソッドの比較条件に渡している。BigQueryではUTC表記でリテラルとして表現したり、関数を経由したりする必要があるので、DateTimeで記述できるLINQ to BigQueryは便利である。

クエリの具体例

 ここからは、グーグルのQuery Referenceのページ原文日本語訳にあるサンプルを引用して、LINQ to BigQueryでの具体的な書き方を紹介したい。「BigQueryのクエリ構文による書き方」「LINQ to BigQueryによる書き方」「実行結果」「説明」という順序で、サンプル項目別に説明していく。

サブクエリ

SQL
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
リスト7 https://cloud.google.com/bigquery/query-reference より引用したクエリ
C#
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}");
  }
}
リスト8 リスト7に対応するLINQ to BigQueryのコード
図3 リスト8のクエリ結果が表示された画面

 サブクエリをLINQ to BigQueryで記述する場合、まずサブクエリを先に記述し、Intoメソッドで記述したクエリをサブクエリとして扱うようにする。その後はまたFromでテーブルを指定したときと同じようにクエリを記述すればよい。

集計値を使ったクエリの結果のフィルタリング

 HAVING節を使った、集計関数の結果をもとに結果をフィルターするクエリである。

SQL
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
リスト9 https://cloud.google.com/bigquery/query-reference より引用したクエリ
C#
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}");
  }
}
リスト10 リスト9に対応するLINQ to BigQueryのコード
図4 リスト10のクエリ結果が表示された画面

 これも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」といった月表示の文字列を取得している。

SQL
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;
リスト11 https://cloud.google.com/bigquery/query-reference より引用したクエリ

このクエリで出てくるtimestampint型の秒単位のUNIX時間である。

C#
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}");
  }
}
リスト12 リスト11に対応するLINQ to BigQueryのコード

なお、図5に示す通り、16GBytesほどデータをスキャンするため、実行する場合は課金額に注意されたい。

 LINQ to BigQueryでも同様のアプローチをとるが、こちらではSTRFTIME_UTC_USEC関数に対応する、StrftimeUtcUsecメソッドを使っており、書式を指定している。

図5 リスト12のクエリ結果が表示された画面

Window関数

 結果セットの中の特定のパーティションだけを対象として操作する処理である。BigQueryのクエリ構文では、Window関数(ウィンドウ関数、窓関数)として、CUME_DIST()RANK()など多数の関数が用意されている。

SQL
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
リスト13 https://cloud.google.com/bigquery/query-reference より引用したクエリ。一部改変してある
C#
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}");
  }
}
リスト14 リスト13に対応するLINQ to BigQueryのコード

 Window関数は、Selectメソッドの引数の匿名オブジェクトのプロパティを定義するときに使っている。Window関数もBqFuncクラスに定義されているが、そのWindow関数(この例ではCumulativeDistributionRankメソッド)を呼び出し、さらにPartitionByメソッドやOrderByメソッドを呼び出した後、最後のValueプロパティを取得する必要がある。

図6 リスト14のクエリ結果が表示された画面

結果セットのバケットを使った分類

 クエリの結果セットをBigQuery以外で定義されているバケットを使って分類する例である。リスト15では、stateごとにあらかじめ定義した対応するregionを表示するようにしている。

SQL
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;
リスト15 https://cloud.google.com/bigquery/query-reference より引用したクエリ

 クエリだけで完結させるためにCASE構文を使っているが、LINQ to BigQueryの場合、クエリを実行した後の結果セットに対し、クライアントサイドでデータ加工を行うことができる。今回はサンプルのため、メソッド内でstateとregionの対応を定義しているが、これが他のデータストアに定義されている場合でも同様に利用できるだろう。

C#
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}");
  }
}
リスト16 リスト15に対応するLINQ to BigQueryのコード
図7 リスト16のクエリ結果が表示された画面

 リスト15のように分類処理がトップレベルのクエリであれば、リスト16のようにLINQ to BigQueryの結果をクライアント側の処理で分類することができた。しかし場合によってはサブクエリ内で分類処理を行い、処理結果をさらにクエリで処理したいケースもあるだろう。その場合はCASE文をLINQ to BigQueryで記述する必要がでてくる。その際はリスト16-Bのように参考演算子で記述すればよい。(※2015/11/17追記

C#
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}");
  }
}
リスト16-B リスト15に対応するLINQ to BigQueryのコード

最近のBigQueryのUpdate

 今回のテーマとは少し離れるが、最近、BigQueryに大きなUpdateがあったので紹介しておきたい。

UDF(ユーザー定義関数)

 JavaScriptでユーザー定義関数を定義し、クエリから参照できるようになった。前述している通り、非同期なクエリ実行APIでしか現状サポートしていない。グーグルのドキュメントにあるサンプルをC#から実行すると、以下のようになる。

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);
    }
  }
}
リスト17 UDFを利用するサンプルコード

ジョブのキャンセルをサポート

 例えば、上のUDFを利用するサンプルコードで、ジョブの完了を待機している箇所で、時間がかかりすぎている場合、APIからCancelメソッドを呼び出してキャンセルできるようになった。

Streaming Insertの課金体系の変更、Federated DataSource、Streaming Inserts用のstreaming buffer statisticsの導入

 これらはデータ投入に関連するため、次回連載記事で説明する予定である。巨大なデータセットを扱う場合に追加課金が発生するようになっている。

Web UIの改善

 新機能のUDFを扱うためのエディター機能が追加されたのに加え、テーブルの末尾に日付を入れて日ごとにテーブルを分けて扱っている場合に、まとめて表示する機能が追加された(図8)。今までは日付分、左側のペインにテーブルが並んでいて非常に見づらい状況であった。

図8 Web UIの改善

JOINや GROUP BYでEACH不要

 巨大なデータセットをJOINGROUP 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から利用するためのライブラリの基本的な使用方法を解説する。

3. BigQuery API(Client Library)を用いたクエリ実行

BigQueryをより便利に使うために、C#プログラムからAPIを利用する方法を紹介する。

4. 【現在、表示中】≫ LINQ to BigQueryによるクエリ実行

C#プログラマーにおなじみのLINQでBigQueryのクエリを実行するライブラリを紹介。

5. LINQでBigQuery: データスキャン量を抑えたクエリの実行方法

膨大なデータへのクエリで、スキャン量を減らしてクエリの課金額を抑えるには? テーブルワイルドカード関数とテーブルデコレーターを説明する。

サイトからのお知らせ

Twitterでつぶやこう!