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)

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できるのは確かなようですが、

もしかしたら完全に無制限とはいかないのかも・・・？

WiFi Tunes Sync Ver.1.2.10 リリース

1年4ヶ月ぶりにWiFI Tunes Syncを更新しました。
ずっと本業が多忙であったため随分間が空いてしまいました(´・ω・｀)

・WiFi Tunes Sync  Ver.1.2.10 (Google Play)
・WiFi Tunes Sync Pro  Ver.1.2.10 (Google Play)
追記:Pro版もリリースしました。

◯アプリ概要

iTunesとAndroidをWiFiで同期するためのアプリです。
AndroidとPCにインストールしたアプリ同士が通信することで同期を行います。

◯変更点

- Ver.1.2.10 (2013/03/14)
 -Android 4.xでメニューの「プレイリスト」押下時にシステムエラーになる問題を修正。

- Ver.1.2.10 (Mar.14.2013)
 -Fixed the issue which would result in system errors when pressing "Playlist" of the menu in Android 4.x.

◯変更内容について

・Android 4.xでメニューの「プレイリスト」押下時にシステムエラーになる問題を修正。

Android4.xの端末を入手する前からアプリを更新していなかったのですが、
Android4.xの端末ではメニューの「プレイリスト」押下時にシステムエラーが発生していたようです。
(設定画面や画面上部のリンクからは問題なく動作していたようです)

あからさまなバグだったので取り急ぎ修正しました。
あまりに久しぶりの更新で若干不安な事と、代替手段もあるので、
Pro版の更新は数日様子を見た後に実施しようと思います。
追記:Pro版もリリースしました。

◯技術的な話

結構古いSDK(1年4ヶ月以上前)で作成したアプリをAndroid 4.xで実行したところ妙なエラーが発生していました。

今回の対応は
・最新のSDKでapkを作成し直す
・SDKのバージョンアップに伴ってエラーチェックが厳しくなったため設定ファイルの記述を修正
のみです。

Androidの古いアプリの互換性は完全じゃないのでしょうか。
しばらく更新していないアプリがあるディベロッパーは注意が必要かも？


この1年4ヶ月の間に開発マシンがWindowsのPCからMacになったのですが、
apkを作成する際に使う証明書をMacに移していませんでした。
Windows PC初期化してなくて良かった。。(；´∀｀)
速攻でGoogleドライブに放り込みました。

◯接続テストに成功しない場合の解決方法

質問にYes/Noで答えていくだけですので「接続できない」という人は一度試してみてください。
接続テストに成功しない場合の解決方法

ずっと更新できていなかったのに有料版買ってくれた皆様ありがとうございます。
久しぶりにランキング見たらまだ有料音楽カテゴリで12位にいてうれしいですヽ(´▽`)ﾉ

22.Google Cloud Endpointsを試してみた (3/3)

Ryo Yamasaki(@vierjp)です。

今回もGoogle Cloud Endpointsについてです。

前回・前々回の
・Google Cloud Endpointsを試してみた (1/3)
・Google Cloud Endpointsを試してみた (2/3)
では、「Server側(バックエンド)の作成」と「Androidクライアントの作成」を試してみました。

その後、Web先端技術味見部#17 （今回は「Google Cloud Endpoints」！）
という勉強会でのサポートの依頼があったので、
JavaScriptからのEndpointsの呼び出しも試してみました。

隣でサポートするだけの予定が急遽前に出て話す事になったので
当日の説明はグタグダになってしまいましたが。。orz

◯JavaScriptからの呼び出し方

JavaScriptからAPIを呼び出す場合もAndroidの場合と同様、
関数を呼び出すだけです。

以下は一覧取得APIを実行して結果をコンソールログに出力するだけの簡単なサンプルです。

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<title>Endpointsのテスト</title>
<script type="text/javascript">
function init() {
 var ROOT = 'http://localhost:8888/_ah/api';
 // APIを使うための初期化処理
 gapi.client.load('testEndpoint', 'v2', function() {
  // 一覧取得APIの実行
  gapi.client.testEndpoint.tests.list().execute(function(resp) {
   console.log(JSON.stringify(resp));
  });
 }, ROOT);
}
</script>
<script src="https://apis.google.com/js/client.js?onload=init"></script>
</head>

<body>
 <div>
  <h1>Endpointsのテスト</h1>
 </div>
</body>
</html>

注意点として、公式ドキュメントに書いてある以下の記述について、

var ROOT = 'https://your_app_id.appspot.com/_ah/api';
gapi.client.load('your_app_id', 'v1', function() {
    doSomethingAfterLoading();
}, ROOT);

「load」関数の第一引数として書かれている「your_app_id」はおそらく間違いで、
@APIアノテーションの「name」で指定したAPI名を指定するのが正しいと思います。
(実際にドキュメントの後半のサンプルでは「API名」を指定しています)

参考:
・Using Endpoints in a JavaScript Client

◯JavaScriptからアクセスする場合は明示的なクライアント・ライブラリの生成が不要

JavaScriptでアクセスする場合には、
Androidの場合に行ったような「明示的なクライアント・ライブラリの生成作業」は必要ありません。
2013/03/29追記
「WEB-INF/*****.api」ファイルは自動的に生成されますが、
・「WEB-INF/*****-rest.discovery」
・「WEB-INF/*****-rpc.discovery」
の2ファイルは明示的に「クライアント・ライブラリの生成作業」を行うことで生成されます。


Endpointsを使用して作成したAPIは、「Google Discovery API」の仕様に準拠するために必要な機能が自動で用意されます。

そして前項のサンプルのように、
この仕様に基づいてAPIの実行に必要な情報をアプリから取得(load)し、
目的のAPIを実行(execute)できるようになっています。

第一回で触れた「API Explorer」で、
作成した各APIをそれぞれのAPIの仕様に沿ったフォームから簡単に実行できるのも
「Google Discovery API」の仕様に準拠しているためです。

Googleが公開している各種のAPIも多くが「Google Discovery API」の仕様に準拠しています。
例えば以下のブログはEndpointsとは関係の無い記事ですが、
JavaScriptだけでGoogle URL Shortener APIを使う | せかいろぐ

Googleが提供する短縮URLを生成するためのサービス「Url Shortner」の
JavaScript APIも、具体的なAPI名や関数名を除けば
前項のEndpointsのサンプルと使い方が同じである事がわかると思います。

このようにEndpointsで公開したAPIは「Google Discovery API」の仕様に準拠しているため、
特に専用のクライアントライブラリを必要とすることなく、
Googleの各種APIを扱うのと同じように利用することができるわけです。

参考:
・API Reference - Google APIs Discovery Service — Google Developers

◯サンプルアプリ

・JavaScript経由でEndpointsを通じてCRUDする動作サンプル
突貫で作ったので結構適当です(；´∀｀)

・サンプルアプリのeclipseプロジェクト
Bitbucketの公開リポジトリに配置しています。
サーバー側の実装も前回までに使用したものとは少し変わっていますが、やってることはほとんど同じです。

・サンプルアプリのAPI Explorer
Endpointsの第一回に書いた「API Explorer」を実際に動作させることが可能です。

◯まとめ

Endpointsを使う場合、JavaScriptからAPIにアクセスする場合にも
自分で非同期通信の処理等を書くことなく、
関数を呼ぶだけでAPIを呼び出して処理を実行して結果を取得することができます。

Endpointsで作成したAPIは、自動的に「Google Discovery API」の仕様に準拠します。

そのため明示的なクライアント・ライブラリの生成を行う必要はなく、
JavaScriptで「Google Discovery API」の仕様に沿って呼び出すことで、APIを実行することが可能です。


といったところで、Endpointsの第三回は「JavaScriptからの実行方法」でした。
もしかしたらEndpoints でのOAuth2.0対応についてもそのうち調べて書くかも？

それではまた(｀･ω･´)ﾉｼ

* 2013/04/12 追記 OAuth2については以下をどうぞ

「appengine ja night #24 Google Cloud Endpoints and BigQuery」
のスライドで説明しています。