Archive - BigQuery

BigQueryを中心としたヴァルハラゲートのログ分析システム

というタイトルでGoogle for Mobile | Game Bootcampで発表しました。4月なので3ヶ月遅れでスライド公開です。

BigQueryを中心としたヴァルハラゲートのログ分析システム from Yoshifumi Kawai

なんかあまり上手く話せなかったな、という後悔がなんかかなり残ってます:) スライドもフォント細くて吹き出しの文字が見辛いな!とりあえず、WindowsでBigQueryなシステムとしては一つの参考例にはなるのではないかなー、と思います。第一部完。

第二部はEtwStreamへの移行と、BigQuerySinkのOSS公開かなー、というところなんですがまだまだまだまだまだ先っぽいのでアレでコレでどうして。できれば誰もが秒速でASP.NETアプリケーションのログをBigQueryに流し込める、みたいな状況にしたいのですけれどねえ、そこはまだまだ遠いかなー、ですね。そのへんの.NETのエコシステムは弱いと言わざるをえない。けれどまぁ、地道に補完していきたいと思ってます。

LINQPad Driver + LINQ to BigQueryによるBigQueryデスクトップGUIクライアント

Happy signed!何かというと、長らく署名の付いていなかったGoogle APIの.NET SDKに署名が付いたのです!署名が付くと何ができるかというと、LINQPadのDriver(プラグイン)が作れます。LINQPadのDriverは署名なしだと起動できないので……。正直、私ももはや署名とか全然重視してないし100億年前の化石概念の負の異物だろ、ぐらいに思ってなくもないのですが、さすがに、LINQPad Driverを作れない、という事態には随分と嘆いたものでした。が、やっと作ることが出来て感無量。そして、実際動かしてみると相当便利ですね。これがやりたかったんですよ、これがー。

LINQ to BigQueryのLINQPad Driverが可能にする範囲は、

  • サイドバーでのスキーマのツリー表示
  • thisを読み込んでいるConnectionで認証済みのBigQueryContextに変更
  • 関連するアセンブリと名前空間を自動で読み込み
  • スキーマに対応するクラスを動的に生成/読み込み
  • ちょっとしたユーティリティDumpの追加(DumpRun/DumpRunToArray/DumpChart/DumpGroupChart)
  • もちろんクエリのローカルでの保存/読み込みが可能

です。元々のLINQ to BigQueryが提供している機能としては

  • TableDateRangeに対するサポート
  • DateTimeの自動変換(一部のBigQueryの機能はUnix Timestampで書く必要があり、実質手で書くのは不可能なものもありましたが、自動変換により救われる)
  • 結果セットをローカル時間に自動変換(基本的にUTCで帰ってくるので、ローカル時間で考える際に+9時間しなきゃいけなかったりしますが、C#側でデシリアライズする際にローカルタイムに自動変換する)
  • 全てが型付きで入力補完が全面的に効く
  • 全てのBigQuery関数の入力補完にドキュメント付き

があって(この辺の詳しい話は以前に書いたLINQ to BigQuery - C#による型付きDSLとLINQPadによるDumpと可視化を見てください)、相乗効果でかなり強まったのではないでしょうか。

公式ウェブコンソールで叩くのとどっちがいいかといったら、まぁ私自身も結構、ウェブから叩くのは多かったりしますので、どっちでもいいといえばいいんですが、それもプラグインを作る前は……かしら。今後は私自身もLINQPad利用が増えるかなー。明らかにウェブから叩くのじゃ提供できない機能というか、素のBigQuery SQLじゃ中々できない機能を多く提供しているわけで、LINQPad + LINQ to BigQUeryにはかなりのアドバンテージがあります。

Excel統合

問答無用に愚直なExcel統合があります。

legendary_dump_to_excel

そう、DumpToExcel()で実行すると結果セットがダイレクトにExcelで開く……。しかし実際こういうのでいいんだよこういうので感あります。Excelでクエリ書く系の統合は面倒くさい(実際アレはダルいのでない)。いちいちCSVに落として開くのは面倒くさすぎる。LINQPadでクエリ書く、結果がExcelで見れる。あとはピボットテーブルなりで好きに分析できる。そう、そういうことなんですよ、これなんですよ #とは

入れ方

ExplorerのAdd Connection→View More Drivers からLINQ to BigQueryを探して、clickでインストールできます。簡単。

image

かなり上の方のいい位置に入れてもらいました!

using static

BigQueryの関数はLINQ to BigQueryではBqFunc以下に押し込める形をとっていますが、C# 6.0から(Javaのように)静的メソッドのインポートが可能になりました。また、LINQPad 5でもスクリプトのバックエンドがRoslynになり、C# 6.0にフル対応しています。LINQ to BigQueryのDriverでは、LINQPad 5以上に読み込ませた場合のみ、using static BigQuery.Linq.BqFunc が自動インポートされます。

これにより、クエリを書いた際の見た目がより自然に、というかウザったいBqFuncが完全に消え去りました!関数名を覚えていない、ウロ覚えの時はBqFunc.を押して探せるし

image

慣れきった関数なら、直接書くことができる。完璧。

How to make LINQPad Driver

難しいようで難しくないようで難しいです。しっかりしたドキュメントとサンプルが付属しているので、スタートはそれなりにスムーズに行けるかと思います。一つ、大事なのはプラグイン開発だからってデバッグ環境に妥協しないでください。ふつーの開発と同じように、F5でVisual Studioが立ち上がってすぐにブレークポイント貼ってステップ実行できる環境を築きましょう。細かいハマりどころが多いので、それ出来ないと挫けます。逆に出来てれば、あとは気合、かな……?細かいやり方はここに書くには余白が(以下略

変わったハマりどころとしては、例えば別々に呼ばれるメソッド間で変数渡したいなー、と思ってprivate fieldに置くと、そもそも都度頻繁にコンストラクタが呼ばれて生成されなおすので、共有できない。なるほど、じゃあせめてstatic変数だったらどうだろうか?というと、LINQPadの内部の実行環境の都合上、AppDomainがガンガン切られて飛んで来るので、static fieldすら消える!マジか!なるほどねー厳しいねー、などなど。

ちなみに動的なアセンブリ生成ではCodeDomのCSharpCodeProviderを利用しています。つい先月、Metaprogramming Universe in C# - 実例に見るILからRoslynまでの活用例でCodeDomはオワコン、使わないとか言ってたくせに!舌の根も乾かぬうちに自分で使うことになるとは思わなかった!

まとめ

社内でのBigQuery活用法として、定形クエリのダッシュボードはDomoにより可視化、アドホックなクエリはLINQPad + LINQ to BigQueryによりクエリを色々書いたり、そのままExcelに送り込んで(LINQPadはデスクトップアプリなので、DumpToExcel()メソッドとかを作ることによりシームレスに結果セットをExcelに投げ込んだりできるのも強い)PowerPivotでこねくり回したり、などをしてます。とはいえ、今までは事前にスキーマに対応するクラスを生成して保存しておかなければならないという面倒くささがあったので、イマイチ活用しきれてなかったのも事実。実際、私自身ですらBigQueryの公式ウェブコンソールでクエリ叩いたりが多かったですし。それが、今回のLINQPad Driverにより圧倒的に利便性が上がった(というか前のがもはや原始時代に見える)ので、使える度合いが桁違いに上がったんじゃないかなー、と思います。

デスクトップGUIクライアントの便利さは、例えばMySQLだったらウェブでphpMyAdminよりもHeidiSQLやMySQL Workbenchのほうが100億倍便利なわけでして、良いところ沢山あるんですよね。BigQuery関連だとCloud DataLabなんかもちょうど出ましたが、ウェブとデスクトップ、それぞれ良さがあるので、ここはうまく使い分けていきたいところです。

最近のBigQueryのアップデートへの追随だと、新メソッドは全部実装が完了してます。また、GroupByへのRollupなど文法の追加もOK。ただ、大きな目玉であるUDF(User Defined Function)への対応がまだです。別にそんな難しくもないんですが、APIの馴染ませ方どうしようかな、とか思ってる間にLINQPad Driverの作成に時間喰われたので、対応入れるのは近いうちの次回ということで。

BigQueryで数列生成とC#クラスからのTable生成とデータインサート

連番を作りましょう!突然!SQL的なものを見ると、まず連番を作りたくなるのはSQLで数列を扱うからなのですが、というわけでBigQueryでも作りますし作れます。実際、Enumerable.Rangeはダイジですからね?また、地味にLINQ to BigQueryもver 0.3.3になってました。ひっそり。そんなわけで、LINQで書くと何が嬉しいのかPart2です。

LINQ to BigQuery(やBigQuery)については、最初の記事LINQ to BigQuery - C#による型付きDSLとLINQPadによるDumpと可視化をどーぞ。

0-9を作る

TempTableにInsertというわけにもいかないので、まずは愚直にUNION ALLで並べましょう。BigQueryのUNION ALLはFromをカンマで並べること(ふつーのSQLとそこが違います)で、また、Subqueryも突っ込めます。ド単純に書くとこうなる。

// 以下contextとでてきたらコレのこと
var context = new BigQueryContext(/* BigqueryService */, /* projectId */);
 
var seq = Enumerable.Range(0, 10).Select(x => context.Select(() => new { num  = x }));
context.From(seq)
    .Select(x => new { x.num })
    .Run()
    .Dump(); // DumpはLINQPadのDumpね。
 
// ↓で、こんなクエリが出てくる
/*
SELECT
  [num]
FROM
(
  SELECT
    0 AS [num]
),
(
  SELECT
    1 AS [num]
),
// 以下9まで続くので(略) */

普通に動きはしますが、馬鹿っぽいですね!少しだけカッコヨク書いてみましょうか。どうやって列を増やすか、が割と課題なのですが、BigQueryではSplitを使って増やせます。

// LINQPadでRun().Dump()って書くの面倒いのでまとめちゃう:)
public static class MyExtensions
{
    public static QueryResponse<T> DumpRun<T>(this IExecutableBigQueryable<T> source)
    {
        return source.Run().Dump();
    }
}
 
// SELECT query which references non constant fields or uses aggregation functions
// or has one or more of WHERE, OMIT IF, GROUP BY, ORDER BY clauses must have FROM clause.
context.Select(() => new { digit = BqFunc.Integer(BqFunc.Split("0123456789", ""))}).DumpRun();

怒られました!FROM句を含めないとSplitが使えないそーですなので、wordはサブクエリに分離しましょう。この辺は覚えられないので怒られたらそーいうものなんだ、って感じに対応していきましょふ。案外エラーメッセージは(親切な時は)親切です。親切じゃない時は何言ってるのか分からないエラーメッセージを吐いてきますが、まぁ7割ぐらいは分かりやすいエラーメッセージを吐いてくれます、偉い。

context
    .Select(() => new { word = "0123456789" })
    .AsSubquery()
    .Select(x => new { digit = BqFunc.Integer(BqFunc.Split(x.word, ""))})
    .DumpRun();

さすがにFROM句に並べまくるよりは、綺麗に書けてる感が出てる気がします!

0-99を作る

0-9が出来たら、あとは簡単に増やせます。ここはCROSS JOINです。0-9と0-9の直積を取ればおk。LINQでBigQueryを書くことの利点に変数にクエリを渡せて、合成可能という点が挙げられます(また、合成可能というのはLINQらしい感じさせるための重要な要素でもある)。0-9を変数に置いてやれば、コピペで同じSQLを書かないでも済みます。

var digit = context.Select(() => new { word = "0123456789" })
    .Into()
    .Select(x => new { digit = BqFunc.Integer(BqFunc.Split(x.word, ""))});
 
// これは動かないけどネ
// Cannot query the cross product of repeated fields 
digit.Into()
    .JoinCross(digit, (d1, d2) => new { d1, d2 })
    .Select(x => new { seq = x.d1.digit + x.d2.digit * 10 })
    .DumpRun();

ネ。まぁこれは動かないんですけどネ。例によってエラーメッセージが出てから対処すればいいんですが、これはSplitで生成したカラムがrepeated fieldになってるのでcross joinできないよ、とのこと。FLATTENを使えば解決します。あとOrderByを忘れてるのでOrderByも足してやりましょうか。

var digit = context.Select(() => new { word = "0123456789" })
    .Into()
    .Select(x => new { digit = BqFunc.Integer(BqFunc.Split(x.word, ""))})
    .Into()
    .Flatten(x => x.digit);
 
digit.JoinCross(digit, (d1, d2) => new { d1, d2 })
     .Select(x => new { seq = x.d1.digit + x.d2.digit * 10 })
     .OrderBy(x => x.seq)
     .DumpRun();
SELECT
  ([d1.digit] + ([d2.digit] * 10)) AS [seq]
FROM FLATTEN(
(
  SELECT
    INTEGER(SPLIT([word], '')) AS [digit]
  FROM
  (
    SELECT
      '0123456789' AS [word]
  )
), [digit]) AS [d1]
CROSS JOIN FLATTEN(
(
  SELECT
    INTEGER(SPLIT([word], '')) AS [digit]
  FROM
  (
    SELECT
      '0123456789' AS [word]
  )
), [digit]) AS [d2]
ORDER BY
  [seq]

この辺まで来ると、圧倒的に手書きよりも捗るのではないでしょうか。というか、LINQならサクサク書けますが(エラー来たら、ああはいはいIntoね、みたいに対処するだけだし)、手書きSQLはシンドイ。むしろ無理。その上で、別に意図と全然違うクエリが吐かれるわけではない、というラインはキープされてると思います。

それとネストが深くなるクエリはどう整形したらいいか悩ましいものなのですが(Stackoverflowには可読性ゼロのめちゃくちゃなインデントのBigQueryのクエリの質問が沢山転がっている!実際きちんと書くのむつかしい)、LINQ to BigQueryは、まぁまぁ読みやすい感じにきっちりフォーマットして出してくれます。若干冗長に思えるところもあるかもですが、まぁそこはルールなのだと思ってもらえれば。見やすいフォーマットといえるものにするため、微調整を繰り返したコダワリがあります。

パラメータを使う

もう一個LINQ to BigQueryのいいとこは、パラメータが使えるとこです。パラメータというか、クエリ文字列にたいして値を埋め込めるの。例えば

// こんなメソッドを作るじゃろ
Task<string[]> GetTitleBetweenRevision(int revisionIdFrom, int revisionIdTo, int limit)
{
    return context.From<wikipedia>()
        .Where(x => BqFunc.Between(x.revision_id, revisionIdFrom, revisionIdTo))
        .Select(x => x.title)
        .Limit(limit)
        .ToArrayAsync();
}
 
// こういうふうに使いますね、的な 
var rows = await GetTitleBetweenRevision(1, 200, 100);
-- 1と200が文字列置換なくSQLに埋め込まれる
SELECT
  [title]
FROM
  [publicdata:samples.wikipedia]
WHERE
  ([revision_id] BETWEEN 1 AND 200)
LIMIT 100

その場でのクエリ書きには使いませんが、プログラムに埋め込んで発行する場合なんかは当然ながらあるといいですよね、と。文字列置換や組み立てはかなり手間かかるので、ずっとぐっと遥かに楽になれるかと思います。LINQなら条件によってWhereを足したり足さなかったり、みたいな書き方も簡単です。

(この機能は0.3.1から入れました!アタリマエのように見えて、ExpressionTreeを操作する上で、地味に微妙に面倒くさいのですよー。とはいえ実用性考えるとこういうのないとアリエナイというか私が使ってて不便したんでようやっと入れました)

クエリ書きに使うのに便利といえば日付の操作は圧倒的に楽になります。例えば昨日の20時というのをBigQueryだけでやると……

context // 走査範囲を狭くするために適当に5日前ぐらいからのRangeにしてる
    .From<github_timeline>("[githubarchive:github.timeline]").WithRange(TimeSpan.FromDays(5))
    .Where(x => x.type=="CreateEvent" 
        && BqFunc.ParseUtcUsec(x.repository_created_at) >= BqFunc.ParseUtcUsec(BqFunc.StrftimeUtcUsec(BqFunc.TimestampToUsec(BqFunc.DateAdd(BqFunc.UsecToTimestamp(BqFunc.Now()), -1, IntervalUnit.Day)), "%Y-%m-%d 20:00:00"))
        && x.repository_fork == "false"
        && x.payload_ref_type == "repository")
    .Select(x => x.repository_name)
    .DumpRun();
 
// SQL
SELECT
  [repository_name]
FROM
  [githubarchive:github.timeline@-432000000-]
WHERE
  (((([type] = 'CreateEvent') AND (PARSE_UTC_USEC([repository_created_at]) >= PARSE_UTC_USEC(STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'DAY')), '%Y-%m-%d 20:00:00')))) AND ([repository_fork] = 'false')) AND ([payload_ref_type] = 'repository'))

結構しんどいです。厄介な日付部分を取り出すと

PARSE_UTC_USEC(STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(DATE_ADD(USEC_TO_TIMESTAMP(NOW, -1, 'DAY')), '%Y-%m-%d 20:00:00'))))

ですからね!結構かなり絶望的……。これをC#のDateTimeで操作すれば

// 今日から一日引いてその日付のみのほうを取って20時間足す
var yesterday = DateTime.UtcNow.AddDays(-1).Date.AddHours(20);
 
context
    .From<github_timeline>("[githubarchive:github.timeline]").WithRange(TimeSpan.FromDays(5))
    .Where(x => x.type=="CreateEvent" 
        && BqFunc.Timestamp(x.repository_created_at) >= yesterday // ほら超スッキリに!
        && x.repository_fork == "false"
        && x.payload_ref_type == "repository")
    .Select(x => x.repository_name)
    .DumpRun();
 
// 日付比較部分のSQLはこう出力される
TIMESTAMP([repository_created_at]) >= '2014-10-03 20:00:00.000000')

その場で書いてクエリ実行する分には、別に日付が埋め込まれようとNOW()からSQLで全部操作しようと変わらない話ですからね。楽な方でやればいいし、日付操作は圧倒的にC#で操作して持ってたほうが楽でしょう、明らかに。

Tableを作る、データを投げる

サンプルデータを扱ってるのもいいんですが、やっぱ自分でデータ入れたいですね、テーブル作りたいですね。基本的には(Google API SDKの)BigqueryServiceを使え!っていう感じなのですが、それはそれでやっぱりそれもプリミティブな感じなので、テーブル作成に関してはちょっとしたユーティリティ用意してみました。以下の様な感じで作れます。

// DataTypeUtility.ToTableFieldSchemaでTableFieldSchema[]を定義から作れる
// 匿名型を渡す以外に既存クラスだったら<T>やtypeof(T)を渡すのもOK
// もちろん手でTableFieldSchema[]を作って渡すのも構わない
new MetaTable("project_id", "mydata", "people")
    .CreateTable(service, DataTypeUtility.ToTableFieldSchema(new
    {
        firstName = default(string), // STRING REQUIRED
        lastName = default(string), // STRING REQUIRED
        age = default(int?), // INTEGER NULLABLE
        birth = default(DateTimeOffset) // TIMESTAMP REQUIRED
    }));

Web Interfaceから作ると、「空のテーブルが作れない」「スキーマはなんかカンマ定義で指定してかなきゃいけなくてダルい」という点があって存外ダルいです。bqも同様。やはり時代はLINQPad、で作る。ちなみにSTRING NULLABLEはクラス定義から抽出するのが不可能だったので(こういうところが不便なのよね……)、まあTableFieldSchema[]を作ってから schemas[1].Mode = “NULLABLE” とでも書いてください。

データの投下も同じようにMetaTableを作ってInsertAllAsyncで。

// ExponentialBackOffを渡した場合はそれにのっとってリトライをかける
await new MetaTable("project_id", "mydata", "people")
    .InsertAllAsync(service, new[]
    {
        new { firstName = "hoge", lastName = "huga", age = 20, birth = new DateTime(2010,1,1,12,13,14, DateTimeKind.Utc)},
        new { firstName = "tako", lastName = "bcbc", age = 30, birth = new DateTime(1983,3,1,10,33,24, DateTimeKind.Utc)},
        new { firstName = "oooo", lastName = "zzzz", age = 45, birth = new DateTime(2043,1,3,11,4,43, DateTimeKind.Utc)},
    }, new Google.Apis.Util.ExponentialBackOff(TimeSpan.FromMilliseconds(250), 5));

これでBigQueryのStreming Insertになります。ひどーきなので別テーブルに並走して書きたい場合は複数書いてWhenAllすれば高速で良いでしょふ。Streaming Insertはそんな頻繁、ではないですけれどそれなりに失敗することもあるので、引数にExponentialBackOff(これ自体はGoogle API SDKに含まれている)を渡せばExponential backoffでリトライを試みます。

まとめ

基本的な機能は完全に実装完了したかなあ、という感じ。0.1 ~ 0.3.3の間に自分で使っててイラッとした細かい部分をチクチク修正してきましたが、そろそろ完全に満足!といったところです。不満ない!完璧!パーフェクち!というわけで、残るはRECORD型サポートに向けて改装すれば敵なし、LINQったらサイキョーね!

な、わけですが、まぁ.NET + BigQueryというニッチに二乗かけたようなアレなので、興味関心、はあっても使ってみた!という人は少ないでしょう、というかいないでしょう、残念無念。でもBigQueryは本当に凄く良いので使ってみて欲しいんだなー。ビッグデータなんてアタクシには無縁、と思ってる人も、実は使い出、使いドコロって、絶対あります。まずはログを片っ端から突っ込んでみましょう、から始めてみませんか?

LINQ to BigQuery - C#による型付きDSLとLINQPadによるDumpと可視化

と、いうものを作りました。BigQueryはGoogleのビッグデータサービスで、最近非常に脚光を浴びていて、何度もほってんとりやTwitterに上がってきたりしてますね。詳細はGoogle BigQuery の話とかGoogleの虎の子「BigQuery」をFluentdユーザーが使わない理由がなくなった理由あたりがいいかな、超でかいデータをGoogleパワーで数千台のサーバー並べてフルスキャンするから、超速くて最強ね、という話。で、実際凄い。超凄い。しかも嬉しいのが手間いらずなところで、最初Amazon RedShiftを検討して試していたのですが、列圧縮エンコードとか考えるのすっごく大変だし、容量やパワーもインスタンスタイプと睨めっこする必要がある。それがBigQueryだと容量は格安だから大量に格納できる、チューニング設定もなし、この手軽さ!おまけにウェブインターフェイスが中々優れていてクエリが見やすい。Query Referenceもしっかり書かれてて非常に分かりやすい。もう非の打ち所なし!

触ってすぐに気に入った、んですが、C#ドライバがプリミティブすぎてデシリアライズすらしてくれないので、何か作る必要がある。せっかく作るならSQLっぽいクエリ言語なのでLINQだろう、と。それとIQueryableは幻想だと思っていたので、じゃあ代替を作るならどうするのか、を現実的に示したくて、ちょうど格好の題材が出現!ということで、LINQで書けるようなライブラリを作りました。

ダウンロードは例によってNuGetからできます。今年はそこそこ大きめのライブラリを作ってきていますが、LINQ to BigQueryは特に初回にしては大きめで割と充実、非常に気合入ってます!是非使ってみてねー。GitHubのReadMe.mdはこのブログ記事で力尽きたので適当です、あとでちゃんと書く……。

簡単なDEMO

BigQueryの良い所にサンプルデータが豊富というところがあります、というわけでGitHubのデータを扱って色々集計してみましょう。データは[publicdata:samples.github_timeline]を使ってもいいのですが、それは2011年時点のスナップショットでちょっとツマラナイ。GitHub Archiveから公開データを引っ張ってくれば、現時点での最新の、今ついさっきのリアルタイムの情報が扱えて非常に素敵(あとBigQueryはこういうpublicなDataSetが幾つかあるのが本当に最高に熱い)。ひっぱてくるやり方は書いてありますが(超簡単)、テーブル名は[githubarchive:github.timeline]です。

まずは単純なクエリということで、プログラミング言語だけでグループ化して個数を表示してみます。github.timelineは、例えばPushしたとかBranch作ったとか、雑多な情報が大量に入っているので、別にリポジトリ数のランキングではなくて、どちらかといえばアクティビティのランキング、ぐらいに捉えてもらえれば良さそうです。とりあえずトップ5で。

この例では記述と表示はLINQPadで行っています。LINQPadは非常に優れていて、C#コードが入力補完付きでサクッと書けるほか、実行結果をDumpして色々表示させることも可能です。DumpChartはLINQ to BigQueryのために独自に作ったDumpなのですが、それにより結果のグラフ化がXとYを指定するだけのたった一行

.DumpChart(x => x.repository_language, x => x.count)

だけで出来てしまう優れものです。描画は.NET標準のチャートライブラリを使っているため、棒グラフの他にも円グラフでも折れ線グラフでも、SeriesChartTypeにある35個の表示形式が選べます。見たとおり、Tooltip表示もあるので個数が大量にあっても全然確認できるといった、チャートに求められる基本的な機能は満たしているので、ちょっとしたサクッと書いて確認する用途ならば上等でしょう。

(DumpChartやQuery.GetContextのコードはこの記事の末尾にコード貼り付けてあるので、それで使ってください)

Resultsタブのほうを開けば、クエリ結果の詳細が見れます。

クエリ文字列はBigQueryの性質上、色々なところで使うはずです。そうした他所で使える可搬性のために、生成結果を人間の読める綺麗なものにする事にこだわりました(TypeScript的な)。純粋なクエリビルダとして使う(ちなみにToString()すればRunしなくてもクエリを取り出せます)ことも十分可能でしょう。Rowsに関しては切り離してグリッド表示も可能で、そうすれば簡単なソートやCSVへの書き出しといった、データベース用IDEに求められる基本的な機能も満たしています。

TotalBytesProcessedが読みづらかったのでひゅーまんりーだぶるな形に直してあるのも用意してあるところが優しさ(普通に自分が使ってて困ったので足しただけですが)。

BigQueryはウェブインターフェイスが非常に優れている、これは正直感動ポイントでした。いやぁ、RedShift、データベース管理用のIDEがろくすっぽなくて(PostgreSQL互換といいつつ違う部分で引っかかって動かないものが非常に多い)どうしたもんか、と苦労してたんですが、BigQueryはそもそも標準ウェブインターフェイスが超使いやすい。スキーマも見やすいしクエリも書きやすい。まさに神。

てわけでウェブインターフェイスには割と満足してるんですが、表示件数をドバッと表示したかったり、グラフ化もサクッとしたいし(何気にGoogle SpreadSheet連携は面倒くさい!)、日頃からデータベースもSQL Server Management StudioやHeidi SQLといったデスクトップツールを使って操作するWindows野郎としては、デスクトップで使えるIDE欲しいですね、と。それに分析やる以上、結構複雑なクエリも書くわけで、そういう時に型が欲しいなーとは思ってしまったり。LINQ to BigQueryはAlt BigQuery Query、Better BigQuery Queryとして、ただたんにC#で書けます以上のものを追求しました。そして、LINQPadとの組み合わせは、現存するBigQuery用のIDEとして最も良いはずです(そもそもBigQuery用のIDEは標準ウェブインターフェイス以外にあるのかどうか説もあるけれど)。日常使い、カジュアルな分析にも欠かせない代物となることでしょう。

Why LINQ?

LINQ to BigQueryで書く場合の良い点。一つは型が効いているので、間違っていたらコンパイルエラーで(Visual Studioで書けばリアルタイムにエラー通知で)弾かれること。別にカラム名の名前間違いなどといったことだけじゃなくて、文字列であったりタイムスタンプであったりといった型も厳密に見えているので、型の合わない関数を書いてしまうといったミスもなくせます。例えばDate and time functionsの引数が文字列なのかタイムスタンプなのかUNIX秒なのか、そして戻り値もまた文字列なのかタイムスタンプなのかUNIX秒なのか、ってのは全く覚えてられないんですが、そんな苦痛とはオサラバです。

github_timelineのカラム数はなんと200個。さすがに覚えてられませんし、それの型だってあやふやってものです(例えばboolであって欲しいフォークされたリポジトリなのかを判定するrepository_forkというカラムには”false”といったような文字列でやってくるんですぜ!?)。

全ての関数はBqFuncの下にぶら下がっていて、引数と戻り値、それにドキュメント付きです。これなら覚えてなくても大丈夫!ちなみに、ということはクエリ中の全ての関数呼び出しにBqFunc.がついてきて見た目がウザいという問題があるのですが、それはC# 6.0のusing staticを使えば解決します。

// C# 6.0 Using Static
using BigQuery.Linq.BqFunc;

楽しみに待ちましょう(C# 6.0は多分2015年には登場するんじゃないかな?)。

LINQ to BigQueryはO/Rマッパーじゃありません。いや、もちろんクエリの構築やC#オブジェクトへのマッピングは行いますが、リレーションの管理はしません。かわりに、書いたクエリがほとんどそのままの見た目のクエリ文字列になります。なので意図しない酷いクエリが発行されてるぞー、というありがちななことは起きません。そして、LINQ to BigQueryで99%のクエリが記述できます、LINQで書けないから文字列でやらなきゃー、というシチュエーションはほぼほぼ起きません。LINQとクエリ文字列を1:1に、あえてほぼ直訳調にしているのはそのためです。

また、順序を強く規制してあります、無効なクエリ順序での記述(例えばGroupBy使わずにHaving書くとかLimitの後にWhere書いてしまうとか)やSelectなしの実行はコンパイルエラーで、そもそも書けないようにしています。

左はWhereの後のメソッド、これが全部でSelectとOrderByとWhere(ANDで連結される)しか使えない。右はSelect後で、GroupBy(奇妙に思えるかもしれませんが、GroupByの中でSelectの型が使えることを考えるとこの順序が適正)やLimit、そしてRunなどの実行系のメソッドが使えるようになっています。

これらにより、LINQ to BigQueryで書いたクエリは一発で実行可能なことが期待できるものが作れます(文字列で書くと、カラムの参照周りとかで案外つまづいてエラりやすい)。さすがにExpressionの中身は検査できないんですが、概ね大丈夫で、”守られてる感”はあるかと思います。ちなみにこんな順序で書けます。

From(+TableDecorate) -> Join -> Where -| -> OrderBy(ThenBy) -> Select ->                     | -> Limit -> IgnoreCase
                                       | -> Select | -> GroupBy -> Having -> OrderBy(ThenBy) | -> IgnoreCase
                                                   | -> OrderBy(ThenBy) ->                   |

そういうの実現するためにLINQ to BigQueryはIQueryableじゃないんですが、そのことはこの長いブログ記事の後ろのほうでたっぷりポエム書いてるので読んでね!あと、こんな割とザルな構成でもしっかり機能しているように見えるのは、BigQueryのSQLがかなりシンプルなSQLだから。標準SQLにできることは、あんま出来ないんですね。で、私はそこが気に入ってます。好きです、BigQueryのSQL。別に標準SQLにがっつし寄せる必要はあんまないんじゃないかなー、SQL自体は複雑怪奇に近いですから、あんまり良くはない。とはいえ、ある程度の語彙は共用されていたほうが親しめるので、そういったバランス的にもBigQueryのSQLはいい塩梅。

最後に、Table DecoratorsTable wildcard functionsが圧倒的に記述しやすいのも利点です。

// Table Decorators - WithRange(relative or absolute), WithSnapshot 
 
// FROM [githubarchive:github.timeline@-900000-]
.From<github_timeline>().WithRange(TimeSpan.FromMinutes(15))
 
// FROM [githubarchive:github.timeline@1411398000000000]
.From<github_timeline>().WithSnapshot(DateTimeOffset.Parse("2014-09-23"))
 
// Table wildcard functions - FromDateRange, FromDateRangeStrict, FromTableQuery
 
// FROM (TABLE_DATE_RANGE([mydata], TIMESTAMP('2013-11-10'), TIMESTAMP('2013-12-01')))
.FromDateRange<mydata>("mydata", DateTimeOffset.Parse("2013-11-10"), DateTimeOffset.Parse("2013-12-1"))
 
// FROM (TABLE_QUERY([mydata], "([table_id] CONTAINS 'oo' AND (LENGTH([table_id]) >= 4))"))
.FromTableQuery<mydata>("mydata", x => x.table_id.Contains("oo") && BqFunc.Length(x.table_id) >= 4)
 
// FROM (TABLE_QUERY([mydata], "REGEXP_MATCH([table_id], r'^boo[\d]{3,5}')"))
.FromTableQuery<mydata>("mydata", x => BqFunc.RegexpMatch(x.table_id, "^boo[\\d]{3,5}"))

Table decoratorは、例えばログ系を突っ込んでる場合は障害対応や監視で、直近1時間から引き出したいとか普通にあるはずで、そういう場合に走査範囲を簡単に制御できる非常に有益な機能です。が、しかし、普通に書くとUNIXタイムスタンプで記述しろということで、ちょっとムリゲーです。それがC#のTimeSpanやDateTime、DateTimeOffsetが使えるので比較にならないほど書きやすい。

FromTableQueryも文字列指定だったりtable_idってどこから来てるんだよ!?という感じであんま書きやすくないのですが、LINQ to BigQueryでは型付けされたメタテーブル情報が渡ってくるので超書きやすい。(ところでCONTAINSだけ、BqFuncじゃなくてstring.Containsが使えます、これはCONTAINSの見た目がこれだけ関数じゃないので、ちょっと特別扱いしてあげました、他の関数は全部BqFuncのみです)

Table DecoratorsとTable wildcard functionsは非常に有益なので、テーブル名の設計にも強く影響を及ぼします。これらが有効に使える設計である必要があります。TABLE_DATE_RANGEのために(垂直分割するなら)末尾はYYYYMMDDである必要があるし、Range decoratorsを有効に使うためには極力、水平シャーディングは避けたほうが良いでしょう。そこのところを無視して、ただ単にシャーディング、シャーディングって言ってたりするのは、ちょっと、ないなー。

複雑なDEMO

ひと通り紹介は終わったので、より複雑なクエリを一つ。同じく最新のGitHubのデータを扱って、一ヶ月毎に、新しく作られたリポジトリを言語毎で集計して表示してみます。まずはグラフ化の結果から。

LINQPadではちゃんと多重グラフもメソッド一発で書けるようにしてます。コードは後で載せるとしてグラフの説明ですが、縦がパーセント、横が日付、それぞれの折れ線グラフが言語。一番上はJavaScriptで今月は43000件の新規リポジトリが立ち上がっていて全体の19%を占めてるようです。2位はJava、3位はCSS、そしてRuby、Python、PHPと続いて、この辺りまでが上位組ですね。C#はその後のC++、Cと来た次の9位で9251件・全体の4%でした。

コードは、ちょっと長いよ!

Query.GetContext()
    .From<github_timeline>()
    .Where(x => x.repository_language != null && x.repository_fork == "false")
    .Select(x => new
    {
        x.repository_url,
        x.repository_created_at,
        language = BqFunc.LastValue(x, y => y.repository_language)
            .PartitionBy(y => y.repository_url)
            .OrderBy(y => y.created_at)
            .Value
    })
    .Into()
    .Select(x => new
    {
        x.language,
        yyyymm = BqFunc.StrftimeUtcUsec(BqFunc.ParseUtcUsec(x.repository_created_at), "%Y-%m"),
        count = BqFunc.CountDistinct(x.repository_url)
    })
    .GroupBy(x => new { x.language, x.yyyymm })
    .Having(x => BqFunc.GreaterThanEqual(x.yyyymm, "2010-01"))
    .Into()
    .Select(x => new
    {
        x.language,
        x.yyyymm,
        x.count,
        ratio = BqFunc.RatioToReport(x, y => y.count)
            .PartitionBy(y => y.yyyymm)
            .OrderBy(y => y.count)
            .Value
    })
    .Into()
    .Select(x => new
    {
        x.language,
        x.count,
        x.yyyymm,
        percentage = BqFunc.Round(x.ratio * 100, 2)
    })
    .OrderBy(x => x.yyyymm)
    .ThenByDescending(x => x.percentage)
    .Run()  // ↑BigQuery
    .Dump() // ↓LINQ to Objects(and LINQPad)
    .Rows
    .GroupBy(x => x.language)
    .DumpGroupChart(x => x.yyyymm, x => x.percentage);

規模感は全体で153GBで行数が2億5千万行ぐらいだけど、この程度は10秒ちょいで返してきますね、速い速い(多分)。

メソッドチェーンがやたら続いているのですが、実際のところこれはサブクエリで入れ子になってます。随所に挟まれてるIntoメソッドで入れ子を平らにしてます。入れ子の形で書くこともできるんですが、フラットのほうが直感的で圧倒的に書きやすいく、(慣れれば)読みやすくもあります。こういう書き方が出来るのもLINQ to BigQueryの大きなメリットだとは、書いてればすぐに実感できます。

(BqFunc.GreaterThanEqualが奇妙に思えるかもしれないのですが、これは文字列だけの特例です。数値やタイムスタンプの場合は記号で書けるようにしてあるのですが、文字列はそもそもC#自体に演算子オーバーロードが定義されていないのでコンパイラに弾かれる、けどBigQuery的には書きたい時がある、というのの苦肉の策でLessThan(Equal)/GreaterThan(Equal)を用意してあります)

チャート化はGroupBy.DumpGroupChartを叩くだけなんですが、ちょっと面白いのは、ここのGroupByはLINQ to Objects(C#で結果を受け取った後にインメモリで処理)のGroupByなんですよね。

.Run()  // ↑BigQuery
.Dump() // ↓LINQ to Objects(and LINQPad)
.Rows
.GroupBy(x => x.language)
.DumpGroupChart(x => x.yyyymm, x => x.percentage);

二次元のクエリ結果を、シームレスに三次元に起こし直せるってのもLINQの面白いところだし、強いところです。モノによっては無理にSQLでこねくり回さなくてもインメモリに持ってきてから弄ればいいじゃない?という手が簡単に打てるのが嬉しい(もちろん全件持ってこれるわけがないのでBigQuery側で処理できるものは基本処理しておくのは前提として、ね)。

例えば、実のところこれの結果は、言語-日付という軸だと歯抜けがあって、全ての月に1つは言語がないと、チャートが揃いません。グラフの見た目の都合上、今回は2010-01以降にHAVINGしてありますが、その後に新しく登場した言語(例えばSwift)なんかはうまく表示できません。まぁ主要言語は大丈夫なので今回スルーしてますが、厳密にやるため、その辺の処理を、しかしSQLのままやるのは存外面倒くさい。でも、こういう処理、C#でインメモリでやる分には簡単なんですよね。なんで、一旦ローカルコンピューター側に持ってきてから、少しだけC#で処理書くか、みたいなのがカジュアルにできちゃうのもLINQ to BigQuery + LINQPadのちょっと良いところ。

さて、実際に吐かれるSQLは以下。

SELECT
  [LANGUAGE],
  [count],
  [yyyymm],
  ROUND(([ratio] * 100), 2) AS [percentage]
FROM
(
  SELECT
    [LANGUAGE],
    [yyyymm],
    [count],
    RATIO_TO_REPORT([count]) OVER (PARTITION BY [yyyymm] ORDER BY [count]) AS [ratio]
  FROM
  (
    SELECT
      [LANGUAGE],
      STRFTIME_UTC_USEC(PARSE_UTC_USEC([repository_created_at]), '%Y-%m') AS [yyyymm],
      COUNT(DISTINCT [repository_url]) AS [count]
    FROM
    (
      SELECT
        [repository_url],
        [repository_created_at],
        LAST_VALUE([repository_language]) OVER (PARTITION BY [repository_url] ORDER BY [created_at]) AS [LANGUAGE]
      FROM
        [githubarchive:github.timeline]
      WHERE
        (([repository_language] IS NOT NULL) AND ([repository_fork] = 'false'))
    )
    GROUP BY
      [LANGUAGE],
      [yyyymm]
    HAVING
      [yyyymm] >= '2010-01'
  )
)
ORDER BY
  [yyyymm], [percentage] DESC

まず、ちゃんと読めるクエリを吐いてくれるでしょ?というのと、これぐらいになってくると手書きだと結構しんどいです、少なくとも私は。ウィンドウ関数もあんま手で書きたくないし、日付の処理の連鎖は型が欲しい。それと、サブクエリ使うとプロパティを外側に伝搬していく必要がありますが、それがLINQだと入力補完が効くのでとっても楽。Into()ですぐにサブクエリ化できるので、すごくカジュアルに、とりあえず困ったらサブクエリ、とぶん投げることが可能でめちゃくちゃ捗る。大抵のことはとりあえずサブクエリにして書くと解決しますからね!処理効率とかはどうせBigQueryなので何とかしてくれるだろうから、ふつーのMySQLとかで書く時のように気遣わなくていいので、めっちゃカジュアルに使っちゃう。

ところでどうでもいい余談ですが、LAST_VALUEウィンドウ関数はリファレンスに載ってません。他にも載ってない関数は幾つかあったりして(追加された時にブログでチラッと告知はされてるようなんですけどね、リファレンスにもちゃんと書いてくださいよ……)。LINQ to BigQueryならそういうアンドキュメントな関数もちゃんと網羅したんでひじょーにお薦めです!

Generate Schema

型付けされてるのがイイのは分かったけれど、それの定義が面倒なのよねー。と、そこで耳寄りな情報。まず、全部のテーブルのちょっとした情報(table_idとかサイズとか)はGetAllTableInfoという便利メソッドで取ってこれるようにしてます(実際便利!)。で、そこから更にテーブルスキーマが取り出せるようになってます。更にそこからオマケでC#コードをstringで吐き出せるようになってます。

var context = new BigQueryContext(/* BigqueryService, projectId */);
// Get All tableinfo(table_id, creation_time, row_count, size_bytes, etc...)
var tableInfos = context.GetAllTableInfo("mydataset");
// ToString - Human readable info
tableInfos.Select(x => x.ToString()).Dump();
 
// Get TableSchema
var schema = tableInfos[0].GetTableSchema(context.BigQueryService);
 
// Build C# class definition
schema.BuildCSharpClass().Dump();

まあ、そんなに洗練されたソリューションじゃないんでアレですが、一時凌ぎには良いでしょふ。publicdataとか自分のプロジェクト下にないものは直接MetaTableクラスを作ってからスキーマ取れるようになってます。

new MetaTable("publicdata", "samples", "github_timeline")
	.GetTableSchema(Query.GetContext().BigQueryService)
	.BuildCSharpClass();
 
// =>
 
[TableName("[publicdata:samples.github_timeline]")]
public class github_timeline
{
    public string repository_url { get; set; }
    public bool? repository_has_downloads { get; set; }
    public string repository_created_at { get; set; }
    public bool? repository_has_issues { get; set; }
    // snip...(200 lines)
	public string url { get; set; }
	public string type { get; set; }
}

TableName属性がついたクラスはFrom句でテーブル名を指定しなくてもそこから読み取る、っていう風になってます(今までのコードでテーブル名を指定してなかったのはそのお陰)

リアルタイムストリーミングクエリ

Streaming Insertによりリアルタイムにログを送りつけてリアルタイムに表示することが可能に!というのがBigQuery超イカス。今までうちの会社は監視系のログはSumo Logicを使っていたのですが、もう全部BigQueryでいいね、といった状態になりました、さようなら、Sumo……。

で、リアルタイムなんですが、リアルタイム度によりけりですが、1分ぐらいの遅延やそれ以上のウィンドウを取るクエリならBigQueryで十分賄えますね。Range decoratorsが最高に使えるので、定期的にそれで叩いてやればいい。そして最近流行りのReactive ProgrammingがC#でも使えるというかむしろC#はReactive Programmingの第一人者みたいなもんなので、Reactiveに書きましょふ。Rxの説明は……しないよ?

// まぁgithub.timelineがリアルタイムじゃないからコレに関しては意味ないヨ、ただの例
 
// [githubarchive:github.timeline@1411511274158000-1411511574167000]
// [githubarchive:github.timeline@1411511574167000-1411511874174000]
// [githubarchive:github.timeline@1411511874174000-1411512174175000]
// ...
Observable.Timer(TimeSpan.Zero, TimeSpan.FromMinutes(5))
    .Timestamp()
    .Buffer(2, 1) // Buffer Window
    .SelectMany(xs =>
    {
        var context = Query.GetContext();
        context.UseQueryCache = false;
        return context.From<github_timeline>().WithRange(xs[0].Timestamp, xs[1].Timestamp)
            .Select(x => new { x.repository_name, x.created_at })
            .ToArrayAsync();
    })
    .Dump();

アプリケーション側のStreaming Insertの間隔(バッファとかもするだろうし本当のリアルタイムじゃあないでしょう?)と、そしてBigQueryのクエリ時間(数秒)の絡みがあるので、まぁ1分ぐらいからでしょうかねー、でもまぁ、多くのシチュエーションでは十分許容できるんじゃないかと思います、障害調査で今すぐログが欲しい!とかってシチュエーションであっても間に合う時間だし。

よほどの超リアルタイム(バッファもほとんど取らず数秒がマスト)でなければ、もはやAmazon Kinesisのような土管すらもイラナイ感じですね。ストレージとしてもBigQueryは激安なので、Streaming Insertが安定するならば、もうBigQuery自体を土管として使って、各アプリはBigQueryから取り出して配信、みたいな形でも良いというかむしろそれでいい。Range decoratorsが効いてるなら走査範囲も小さいんで速度も従量課金も全く問題ないしねぇ。BigQuery最強すぎる……。

データ転送

本筋じゃないのでちょっとだけ話ますが、C#ってことは基本Windows Server(AWS上に立ってる)で、データをどうやってBigQueryに送るのー?と。もちろんFluentdは動かないし、(Windowsブランチあるって?あー、うーん、そもそも動かしたい気がない)、どうしますかね、と。ストレージに突っ込んでコピーは簡単明快でいいんですが、まぁ↑に書いたようにStreamingやりたいね、というわけで、うちの会社((株)グラニ。gihyoに書いた神獄のヴァルハラゲートの裏側をCTOが語り尽くす!とか読んでくださいな)では基本的にStreaming Insertのみです。ETW/EventSource(簡単な説明はWindows high speed logging: ETW in C#/.NET using System.Diagnostics.Tracing.EventSourceを)経由でログを送って、Semantic Logging Application Block(SLAB)のOut-of-process Serviceで拾って、自家製のSink(ここは今のところ手作りする必要あり、そのうちうちの会社から公開するでしょふ)でStreaming Insert(AWS->BigQueryでHTTP経由)。という構成。

今のとこリトライは入ってますが完全インメモリなんでまるごと死んだらログはロスト。といった、Fluentdが解決している幾つかの要素は解決されてないんですが、それなりに十二分に実用には使えるところかな、と。速さとかの性能面は全く問題ありません、ETWがとにかく強いし、そっから先もasync/awaitを活かした並列インサートが使えるので他のでやるよりはずっと良いはずきっと。

TODO:

実はまだRecord型に対応してません!なのでそれに関係するFLATTENやWITHIN句も使えません!99%のクエリが再現できる、とか言っておきながら未対応……。おうふ、ま、まぁ世の中のほとんどは入れ子な型なんて使ってませんよね……?そんなことはないか、そうですね、さすがに対応は必須だと思ってるので、早めに入れたいとは思ってます。

あと、LINQPadにはDataExplorerがあって、ちゃんとスキーマ情報の表示やコネクション保持とか出来るんですねー。というわけで、真面目にそのLINQPadドライバは作りたいです、というか作ろうとしていましたし、割と作れる感触は掴んだんです、が、大きな障壁が。LINQPadドライバは署名付きであることを要求するのですが、Google APIs Client Library for .NETが、署名されてない……。署名付きDLLは全部の参照DLLが署名付きであること必要があって、肝心要のGoogleライブラリが使えないという事態に。俺々署名してもInternalVisibleToがどうのこうのとかエラーの嵐で一歩も進めないよー。Googleが署名さえしてくれてれば全部解決なのに!だいたい著名なライブラリで署名されてないのなんかGoogleぐらいだよ!もはやむしろありえないレベル!なんとかして!

IQueryable is Dead. Long live Expression!

ちょっとだけC#の話もしよふ。以下、LINQ好きだからポエム書くよ!

LINQ to BigQueryはIQueryableじゃあ、ありません。この手のクエリ系のLINQはIQueryableでQuery Providerである必要が……、あるの?IQueryableは確かにその手のインフラを提供してくれるし、確実にLINQになる。けれど、絶対条件、なの?

私がLINQ to BigQueryで絶対譲れない最優先の事項として考えたのは、LINQで書けないクエリをなくすこと。全てのクエリがLINQで書ける、絶対に文字列クエリを必要としないようにする。そのためにはIQueryableの範囲を逸脱する必要があった。そして同時に強く制約したかった、順序も規定したいし、不要なクエリは(NotSupported!)そもそも書けないようにしたかった。これらはIQueryableに従っていては絶対に実現できないことだった。

LINQがLINQであるためにはクエリ構文はいらない。Query Providerもいらない。LINQ to XMLがLINQなのは何故?Parallel LINQがLINQであるのは何故?Reactive ExtensionsがLINQであるのは何故?linq.jsがLINQであるのは何故?そこにあるのは……、空気と文化。

LINQと名乗ること自体はマーケティングのようなもので、形はない。使う人が納得さえすれば、LINQでしょう。そこにルールを求めたがる人がいても、ないものはないのだから規定しようがないよ?LINQらしく感じさせる要素をある程度満たしてればいい。FuncもしくはExpressionを使ってWhereでフィルタしSelectで射影する(そうすればクエリ構文もある程度は使えるしね)。OrderBy系の構文はOrderBy/OrderByDescending/ThenBy/ThenByDescendingで適用される。基本的な戻り値がシーケンスっぽい何かである。うん、だんだん満たせてくる。別に100%満たさなくても、70%ぐらい満たせばLINQらしいんだよ。SelectManyがなくたって、いい。どうせNotSupportedExceptionが投げられるのなら、最初からないのと何が違うというの?

LINQ to BigQueryからはLINQらしさを感じられると思っています。最優先事項の全てのBigQueryのクエリを書けるようにすることやNotSupportedを投げないことなどを持ちつつも、可能な限りLINQらしさを感じさせるよう細心の注意を払ってデザインしました。極論言えば私がLINQだって言ってるんだからLINQなのですが(何か文句ある?)、多くの人には十分納得してもらえると考えています。LimitをTakeで”書けない”とかね、BigQueryらしくすることも使いやすさだし、LINQらしくすることも使いやすさ。この辺は私の匙加減。

と、いうわけでIQueryableは、データベース系クエリの抽象化というのが幻想で、無用の長物と化してしまったのだけど、しかし役に立たなかったかといえば、そうじゃあない。LINQだと感じさせるための文化を作る一翼をIQueryableは担っていたから。データベース系へのクエリはこのように定義されていると”らしい”感じになる。その意識の統一にはIQueryableは必要だった、間違いなく。しかし時は流れて、もう登場から6年も経ってる。もう、同時にかかった呪いからは解放されていいんじゃないかな?みんなでIQueryableを埋葬しよう。

と、いうのがIQueryableを使ってない理由。死にました。殺しました。IQueryableは死んだのですが、しかしExpressionは生きています!LINQ to BigQueryも当然Expressionで構成されています。空前のExpression Tree再評価の機運が!で、まぁしかしだからってふつーのアプリのクエリをExpression Treeでやりたいかは別の話ね。やっぱ構築コストとか、そもそもBigQueryは比較的シンプルなSQLだから表現しきれたけどふつーのSQLは複雑怪奇で表現できないだろー、とか、色々ありますからね。まぁ、あんま好ましく思ってないのは変わりません。

コストの話は、BigQueryの場合は完全に無視できるのよね。クエリのレスポンスが普通のDBだったら数msだけど、BigQueryは数千~数万msと桁が4つも5つも違う。リクエスト数もふつーのクエリは大量だけどBigQueryはほとんどない(一般ユーザーが叩くものじゃないからね)。なので、ほんとうの意味でExpression Treeの構築や解釈のコストは無視できちゃう。そういう、相当富豪的にやっても何の問題もないというコンテキストに立っています。だからLINQ to BigQueryはあらゆる点で完全無欠に有益。

LINQPad用お土産一式

Query.GetContextとかDumpChartとかは、LINQPadの左下のMy Extensionsのとこに以下のコードをコピペってください。それで有効になります。本当はLINQPad Driver作ってそれ入れれば有効になるようにしたかったんですが、とりあえず今のところはこんなんで勘弁してくだしあ。こんなんでも、十分使えますので。

// Import this namespaces
BigQuery.Linq
System.Windows.Forms.DataVisualization.Charting
Google.Apis.Auth.OAuth2
Google.Apis.Bigquery.v2
Google.Apis.Util.Store
Google.Apis.Services
 
public static class Query
{
    public static BigQueryContext GetContext()
    {
        BigQueryContext context;
        // Replace this JSON. OAuth2 JSON Generate from GCP Management Page. 
        var json = @"{""installed"":{""auth_uri"":""https://accounts.google.com/o/oauth2/auth"",""client_secret"":"""",""token_uri"":""https://accounts.google.com/o/oauth2/token"",""client_email"":"""",""redirect_uris"":[""urn:ietf:wg:oauth:2.0:oob"",""oob""],""client_x509_cert_url"":"""",""client_id"":"""",""auth_provider_x509_cert_url"":""https://www.googleapis.com/oauth2/v1/certs""}}";
 
        using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(json)))
        {
            // Open Browser, Accept Auth
            var userCredential = GoogleWebAuthorizationBroker.AuthorizeAsync(ms,
                new[] { BigqueryService.Scope.Bigquery },
                "user",
                CancellationToken.None, new FileDataStore(@"LINQ-to-BigQuery")) // localcache
                .Result;
 
            var bigquery = new BigqueryService(new BaseClientService.Initializer
            {
                ApplicationName = "LINQ to BigQuery",
                HttpClientInitializer = userCredential
            });
 
            context = new BigQueryContext(bigquery, "write your project id");
        }
        // Timeout or other options
        context.TimeoutMs = (long)TimeSpan.FromMinutes(1).TotalMilliseconds;
        return context;
    }
}
 
public static class MyExtensions
{
    public static IEnumerable<T> DumpChart<T>(this IEnumerable<T> source, Func<T, object> xSelector, Func<T, object> ySelector, SeriesChartType chartType = SeriesChartType.Column, bool isShowXLabel = false)
    {
        var chart = new Chart();
        chart.ChartAreas.Add(new ChartArea());
        var series = new Series { ChartType = chartType };
        foreach (var item in source)
        {
            var x = xSelector(item);
            var y = ySelector(item);
            var index = series.Points.AddXY(x, y);
            series.Points[index].ToolTip = item.ToString();
            if (isShowXLabel) series.Points[index].Label = x.ToString();
        }
        chart.Series.Add(series);
        chart.Dump("Chart");
        return source;
    }
 
    public static IEnumerable<IGrouping<TKey, T>> DumpGroupChart<TKey, T>(this IEnumerable<IGrouping<TKey, T>> source, Func<T, object> xSelector, Func<T, object> ySelector, SeriesChartType chartType = SeriesChartType.Line)
    {
        var chart = new Chart();
        chart.ChartAreas.Add(new ChartArea());
        foreach (var g in source)
        {
            var series = new Series { ChartType = chartType };
            foreach (var item in g)
            {
                var x = xSelector(item);
                var y = ySelector(item);
                var index = series.Points.AddXY(x, y);
                series.Points[index].ToolTip = item.ToString();
            }
            chart.Series.Add(series);
        }
        chart.Dump("Chart");
        return source;
    }
}

GCPの管理ページからOAuth2認証用のJSONをベタ貼りするのとプロジェクトIDだけ書いてもらえれば使えるかと。最初にブラウザ立ち上がって認証されます、2回目以降はローカルフォルダにキャッシュされてるので不要。まぁ色々ザルなんですが、軽く使う分にはいいかな、と。

まとめ

いやもう本当に、この手のソリューションではBigQueryが群を抜いて凄い。Azure使ってる人もAWS使ってる人(実際、うちのプロダクトはAWS上で動かしてますがデータはBigQueryに投げてます)もオンプレミスの人もBigQuery使うべきだし、他のものを使う意味が分からないレベル。とにかく試せ、であり、そして試すのは皆Googleアカウントは絶対持ってるはずだからワンポチするだけで立ち上がってるし、最初から膨大なサンプルデータがあるので簡単に遊べるし、一発で気にいるはず、間違いない。

そしてWindows(C#)の人には、LINQ to BigQuery + LINQPadがベストなツールとなってくれるはず。むしろあらゆるBigQueryを扱う環境の中でC#こそが最高といえるものになってくれるよう、色々やっていきたいですね。

Search/Archive

Category

Profile


Yoshifumi Kawai
Microsoft MVP for .NET(C#)

April 2011
|
March 2017

Twitter:@neuecc
GitHub:neuecc
ils@neue.cc