2013年3月15日金曜日

23.BigQueryの新機能 (2013/03/15)

このエントリーをはてなブックマークに追加



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)

Rowtitleidtimestamp
1Spirits26793982571938
2Spirits26793982571938
3Spirits26793982571938
4Jesus1095706983610563
5Jesus1095706983610563
6Jesus1095706983610563
7Jesus1095706983610563
8Doors7919983666981
9Doors7919983668101
10Stop26739983742576
・・・以下略・・・

となり、エラーにならずに結果を取得することができました。
確かにテーブルのサイズによる制限はなくなっているようです。
データ量によるので一概には言えませんが、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)
Rowtitlecnt
1Wikipedia:Administrator intervention against vandalism643271
2Wikipedia:Administrators' noticeboard/Incidents419695
3Wikipedia:Sandbox/Archive326337
4Wikipedia:Sandbox257893
5User:Cyde/List of candidates for speedy deletion/Subpage226802
6Wikipedia:Reference desk/Science204469
7Wikipedia:WikiProject Spam/LinkReports191679
8Wikipedia:Reference desk/Miscellaneous186715
9Template talk:Did you know184508
10Wikipedia:Help desk169952
・・・以下略・・・

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できるのは確かなようですが、
もしかしたら完全に無制限とはいかないのかも・・・?



このエントリーをはてなブックマークに追加