Tweet
BigQuery gets big new features to make data analysis even easier - Google Developers Blog
待望の新機能「Big JOIN」を始め、BigQueryに新しい機能が追加されました。
Big JOINについて試したのと、他の機能も少し読んでみたのでブログに書いてみます。
◯Big JOINとは
Big QueryではこれまでもテーブルのJOINをすることはできたのですが、JOINするテーブルのサイズに制限がありました。
(クエリのメインのテーブルのサイズには元々制限は無いので、JOIN相手のテーブルに制限があるという感じ)
データ集計においてJOINを必要とする頻度は高く、
これまではBigQueryを使う際にこの制限を回避するための対応が必要でした。
具体的には、
1.JOIN対象のテーブルをサブクエリ化して不要な列や不要なレコードを削る (限界がある)
2.データ設計の時点でデータ集計を見据えてデータを保存しておく (新しいパターンに対応しづらい)
3.MapReduce等を使って「事前にJOINした後の状態のデータを作っておく」(とにかく手間がかかる)
等です。
* 私が以前に書いた下記のブログ記事を見ると、苦労が伺えるかと思います。。
10.Google App EngineとBigQuery ノウハウ編(3/3)
(「Joinの制限」が該当部分)
経験上この制限の回避に割く時間は集計クエリを作成する作業の中で結構な時間を占めていたと思います。
また、長期的に見た場合にデータの増加や新しい集計パターンに対処できなくなる懸念もありました。
それが今回のBig JOINのサポートによって、JOINするテーブルのサイズに制限が無くなりました。
これはBigQueryユーザーが上記の手間や懸念から開放される事を意味します。
よって、BIG JOINは多くのBigQueryユーザーが待ちに待った、
現在進行形の手間や今後の懸念を解消してくれる新機能と言えるでしょう。
今回この「Big JOINのサポート」により、
BigQueryでは2種類のJOINが可能になりました。
・Small JOIN (従来からあるJOIN)
JOINするテーブルのサイズに制限があるが高速
・Big JOIN (新しく使えるようになったJOIN)
JOINするテーブルのサイズに制限は無いがSmall Joinに比べて速度的には劣る
※サイズ制限について最後に追記あり(2013/03/19)
それぞれメリット・デメリットがあるので、ケースに応じて使い分けると良いでしょう。
参考:
・Query Reference#JOIN clause
◯Big JOIN (JOIN EACH)を試してみる
以下は「wikipediaテーブルのtitle」と「shakespeareテーブルのword」が一致するレコードを抽出する。
という簡単なクエリです。
各テーブルの件数は以下の通り
・wikipediaテーブル・・・約3億件
・shakespeareテーブル・・・約16万件
まずはBig JOINを使わずに試してみます。
SELECT title, id, timestamp FROM [publicdata:samples.wikipedia] w, JOIN [publicdata:samples.shakespeare] s on w.title=s.word ORDER BY timestamp limit 1000;
実行結果:
Query Failed
Error: Large table publicdata:samples.shakespeare must appear as the leftmost table in a join query
「shakespeareテーブルが大きすぎる」というエラーですね。
よろしい ならばBig JOINだ。
「JOIN EACH」にして再実行してみると、
実行結果:
Query complete (14.0s elapsed, 11.5 GB processed)
| Row | title | id | timestamp | |
| 1 | Spirits | 26793 | 982571938 | |
| 2 | Spirits | 26793 | 982571938 | |
| 3 | Spirits | 26793 | 982571938 | |
| 4 | Jesus | 1095706 | 983610563 | |
| 5 | Jesus | 1095706 | 983610563 | |
| 6 | Jesus | 1095706 | 983610563 | |
| 7 | Jesus | 1095706 | 983610563 | |
| 8 | Doors | 7919 | 983666981 | |
| 9 | Doors | 7919 | 983668101 | |
| 10 | Stop | 26739 | 983742576 |
となり、エラーにならずに結果を取得することができました。
確かにテーブルのサイズによる制限はなくなっているようです。
データ量によるので一概には言えませんが、BigQueryにしては少し時間がかかる印象は受けるかも？
それではSmall JOINも試してみましょう。
「shakespeareテーブル」をそのままJOINするのではなく、「word」列のみを取得するようにします。
列が減ることでJOIN対象のサイズが減るので、
これがSmall JOINの制限数値以内であればクエリを実行できるわけです。
SELECT title, id, timestamp FROM [publicdata:samples.wikipedia] w, JOIN ( SELECT word FROM [publicdata:samples.shakespeare] )s on w.title=s.word ORDER BY timestamp limit 1000;
実行結果:
Query complete (4.9s elapsed, 11.5 GB processed)
(取得結果は同じ)
ドキュメントにも書いてある通り、たしかにSmall JOINの方が速そうです。
しかし実際には速度だけでは決められず、トレードオフが必要かもしれません。
バッチ処理から呼ぶ等でクエリの実行速度をそこまで求めない場合には
BIG JOINにしてしまえばクエリ作成は楽ですし、
「shakespeare」テーブルのデータが増大した結果Small JOINでは動かなくなる、という心配もありません。
速度が要求される、かつ今後も踏まえて心配が無いならSmall JOINで、
その確証が無いならBIG JOIN、という方針が無難でしょうか。
参考:
・Query Reference#JOIN clause
◯GROUP EACH BYを試してみる
また、Big JOIN同様にGroup Byにも「EACH」の指定ができるようになりました。Group Byの条件に指定した項目が大量の種類ある場合、
(つまりは「その項目をdistinctした際の件数」が大量にある場合)にクエリを実行できない問題も、
「EACH」を付けることで解消されます。
これもJOIN同様「可能であればEACHをつけない方がパフォーマンスは良い」とのことです。
簡単なクエリで試してみましょう。
「wikipediaテーブル」をtitleでGroup Byしてtitle毎の件数をカウントします。
こちらもまずは「EACH」を指定せずに実行します。
SELECT title, count(*) as cnt FROM [publicdata:samples.wikipedia] s GROUP BY title order by cnt DESC limit 1000;
実行結果:
Query Failed
Error: Resources exceeded during query execution. The query contained a GROUP BY operator, consider using GROUP EACH BY instead. For more details, please see https://developers.google.com/bigquery/docs/query-reference#groupby
エラーになりました。異なる「title」のレコードなんて大量にありそうですもんね。
「代わりにGROUP EACH BYを使え」というエラーメッセージ。
ドキュメントのリンクまで書いてあってとても親切です。
GROUP EACH BYにして再実行すると、今度は取得できました。
実行結果:
Query complete (10.2s elapsed, 6.79 GB processed)
| Row | title | cnt | |
| 1 | Wikipedia:Administrator intervention against vandalism | 643271 | |
| 2 | Wikipedia:Administrators' noticeboard/Incidents | 419695 | |
| 3 | Wikipedia:Sandbox/Archive | 326337 | |
| 4 | Wikipedia:Sandbox | 257893 | |
| 5 | User:Cyde/List of candidates for speedy deletion/Subpage | 226802 | |
| 6 | Wikipedia:Reference desk/Science | 204469 | |
| 7 | Wikipedia:WikiProject Spam/LinkReports | 191679 | |
| 8 | Wikipedia:Reference desk/Miscellaneous | 186715 | |
| 9 | Template talk:Did you know | 184508 | |
| 10 | Wikipedia:Help desk | 169952 | |
GROUP EACH BYも便利です。
このパターンに遭遇した事はありませんが、
これも「GROUP EACH BY」無しで回避しようとしたらちょっと面倒そうです。
・Query Reference#GROUP BY clause
◯TIMESTAMPデータ型をネイティブでサポート
「timestamp」という型が追加され、関連した関数がいくつも追加されました。データをUploadする際のスキーマ定義で
「カラム名:timestamp」とすることで使用できます。
スキーマ定義はこんな感じ。
name:string, age:integer, birthday:timestamp
実際のデータはこんな感じ。
john,23,1989-10-02 05:23:48 kim,54,1958-06-24T12:18:35.5803 sara,24,1988-08-15T19:06:56.235
これまでBigQueryではTimeZoneの概念が無かった(実質UTCのみ対応)ため、
日本時間で集計しようとした場合に少し面倒でしたが、
これで楽に使えるようになっているといいな、と。
# AppEngineからimportする時はどうなるんでしょ
参考:
・Query Reference#Date and time functions
・Using TIMESTAMP
◯その他の新機能
他にも以下の機能追加が行われています。・既存テーブルへのカラム追加
BigQuery APIの「Tables: update」や「Tables: patch」からできるそうです。
・データセットを共有しやすくするためのダイレクトリンクの追加とメール通知
データセットの共有を容易にするためのダイレクトリンクの追加。
データセットを共有した際に相手にメールで通知できるようになった。
とのこと。
◯まとめ
Big JOINの追加はBigQueryにおける本当に大きな進化だと思います。また、まだあまり調べていませんが、
Timestamp型の追加によってタイムゾーンの扱いも楽になるのではないでしょうか。
あとは「色々なデータソースから簡単にデータをインポートする仕組み」があるといいですね。
AppEngineのDatastoreのデータはバックアップデータを取り込めるけど、
公開されているのはWebのUIだけで、
コマンドラインツールもREST-APIでの取り込み方法も公開されてないので
現状自動化はしづらそうです。
ー｀).｡oO（Trusted Testerはいつ終るんだろうか・・・実は終わってる？）
◯追記
※2013/03 追記「もっとでかいJOINも試してほしい」というリクエストをいただいたので試してみたところ、
予想外の展開となってしまいました。
サンプルとして用意されているテーブルの中から
データ件数が多く、かつJOINの条件にできそうな項目を持つテーブルを探し、
以下のテーブルを使うことにしました。
・wikipediaテーブル・・・313,797,035 (約3億件)
・trigramsテーブル・・・ 68,051,509 (約7000万件)
SELECT w.title, w.id, w.timestamp FROM [publicdata:samples.wikipedia] w, JOIN EACH [publicdata:samples.trigrams] w2 on w.title=w2.third ORDER BY w.timestamp limit 1000;
結果が返ってこないまま、10時間(36,000s)以上ずっとクエリの実行時間の表示がカウントアップされていましたが、
気がつくと
Query Failed
Error: Backend Error
となっていました。
次の日にもう一度試したところ、今度は何度試しても80〜130秒程度で
Query Failed
Error: Resources exceeded during query execution.
という結果が毎回返ってくるようになりました。
当初に試したテスト内容から考えると
BIG JOINによってSmall JOINより大きなサイズのテーブルをJOINできるのは確かなようですが、
Tweet
0 件のコメント:
コメントを投稿