CA Beat エンジニアリーダーのヤマサキ(@vierjp)です。
前回までにBigQuery3連続と銘打って以下の記事を書きました。
・08.Google App EngineとBigQuery 導入編(1/3)
・09.Google App EngineとBigQuery 実践編(2/3)
・10.Google App EngineとBigQuery ノウハウ編(3/3)
今回は「まだCA Beatで使ってないから」という理由で見送った「BigQueryのREST-API」についてです。
結局4連続になってしまいました。
基本的にはJava言語を想定した記事ですが、
参考リンクにはPythonのコードも載っているので、
Pythonな方は参考リンクを見て読み替えていただければと思います。
最後にJavaのサンプルプロジェクトを置いておきました。
eclipseにインポートすればすぐに動作すると思います。
○想定される使い所
概ね以前に書いた通りですが、
・自前のシステムからBigQueryにデータをUploadしたい
・自前のシステムにBigQueryでクエリした結果を表示したい
・クエリの結果を取得して集計処理を自動化したい
といった場合には、REST APIを使うのが良いでしょう。
○準備
BigQueryの導入方法は以下の記事をご覧ください。
08.Google App EngineとBigQuery 導入編(1/3)
上記リンクを見ながら、最低限下記だけ実施してください。
・APIs ConsoleでBigQueryを有効にする。(Billingも有効にする)
・Biiling設定の反映を確認するためにブラウザツールで適当なDataset・テーブルを作成してみる。
(Billingを有効にしたのが反映されていれば作成できます)
○「Client ID」と「Client Secret」を生成して取得
1.「APIs Console」にアクセスする
2.画面左の「API Access」をクリックし、「Create another client ID...」を押す
3.「Application Type」として「Installed application」を選択、
「Installed application type」は「Other」を選択して「Create client ID」を押す。
→「Client ID for installed applications」という項目が表示されます。
4.「Download JSON」リンクを押して、「client_secrets.json」という名前で保存する。
このファイルはAPIを実行するためのClientIDやClientSecret等が書かれたファイルです。
参考:Generate a Client ID and Client Secret for your Application
○Google API Client Librariesのインポート
・Google APIs Client Library for Java
http://code.google.com/p/google-api-java-client/wiki/Setup
「GoogleのAPIを使う際に利用する汎用的なライブラリ」と「BigQuery用のライブラリ」を追加します。
詳細な内容は参考リンクか、mavenな方はサンプルプログラム内のpom.xmlをご確認ください。
参考:Import Google API Client Libraries
○BigQuery APIへのアクセスを認可する仕組み
・公式ドキュメントの和訳
BigQueryへのアクセスを承認するための最初のステップは、
client_secrets.jsonファイルから値を読み取るために必要なコードを記述することです。
そのアプリケーションはBigQuery内のAPIにアクセスする事を認可するようユーザーに促し、
結果の認可コードを使用する新しいBigQueryAPIクライアントを作成します。
これらのタスクの複雑さの多くは、
「Google API Client Libraries」で使用可能なメソッドによって処理されます。
1.アプリケーションは「Google APIの認可ページ」へのURLを表示します。
2.ユーザはこのページからAPIへのアクセスを認可します。
ユーザーがAPIへのアクセスを許可した場合、「アクセスコード」が表示されます。
3.ユーザはアプリケーションによって提供されるコマンドラインプロンプトに
この「アクセスコード」を貼り付けます。
4.アプリケーションは認可されたBigQuery内APIクライアントを作成するための資格を取得するために
このアクセスコードを使用しています。
アクセスコードはユーザーが自分の「Googleアカウント設定」ページでアクセス権を取り消さない限り、
クエリを実行するために使用されます。
(OAuth2.0的には認可コードは一度使ったら向こうになるような？実際のコードでも無効になりますし)
・公式ドキュメントのサンプルコードから該当部分を抜粋
// 認可するためのURLを生成します。
String authorizeUrl = new GoogleAuthorizationCodeRequestUrl(clientSecrets, REDIRECT_URI,
Collections.singleton(BigqueryScopes.BIGQUERY)).build();
// 1.アプリケーションは「Google APIの認可ページ」へのURLを表示します。
// Prompt the user to visit the authorization URL, and retrieve the provided authorization code
System.out.println("Paste this URL into a web browser to authorize BigQuery Access:\n" + authorizeUrl);
System.out.println("... and paste the code you received here: ");
// 3.ユーザはアプリケーションによって提供されるコマンドラインプロンプトにこの「アクセスコード」を貼り付けます。
// (アプリは入力を待ちます)
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
String authorizationCode = in.readLine();
// Create a Authorization flow object
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,
clientSecrets, Arrays.asList(BigqueryScopes.BIGQUERY)).build();
// Exchange the access code for a credential authorizing access to BigQuery
GoogleTokenResponse response = flow.newTokenRequest(authorizationCode).setRedirectUri(REDIRECT_URI)
.execute();
Credential credential = flow.createAndStoreCredential(response, null);
参考:Authorize Access to the BigQuery API
しかし、このサンプルの内容そのままだとアプリを起動する度に毎回認証する必要があって面倒です。
○リフレッシュトークンを使って繰り返し実行できるようにする
Authorizing access from installed and desktop applications
に「リフレッシュトークン」を使う方法が書いてあるのでこれを参考にしてみました。
*上記ページにはApp Engineのアカウント(「[アプリケーションID]@appspot.gserviceaccount.com」)から認証するサンプルも載っています。
public class BigQueryRestApiSample {
// Your Google Developer Project number
// ApisConsoleのoverviewに書かれているProject Numberを指定する
private static final String PROJECT_NUMBER = "**********";
// Load Client ID/secret from client_secrets.json file
private static final String CLIENTSECRETS_LOCATION = "client_secrets.json";
// Load Client ID/secret from client_secrets.json file
private static final String SAMPLE_QUERY_LOCATION = "sample.sql";
// Load Client ID/secret from client_secrets.json file
private static final String REFLESH_TOKEN_FILE_NAME = "token.dat";
// For installed applications, use the redirect URI
// "urn:ietf:wg:oauth:2.0:oob"
private static final String REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";
// Objects for handling HTTP transport and JSON formatting of API calls
private static final HttpTransport HTTP_TRANSPORT = new NetHttpTransport();
private static final JsonFactory JSON_FACTORY = new JacksonFactory();
/**
* @param args
*/
public static void main(String[] args) throws Exception {
if ("**********".equals(PROJECT_NUMBER)) {
System.err.println("PROJECT_NUMBERを設定する必要があります。");
return;
}
try {
doQuery();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param args
*/
public static void doQuery() throws Exception {
InputStream resourceInputStream = BigQueryRestApiSample.class.getResourceAsStream(CLIENTSECRETS_LOCATION);
if (resourceInputStream == null) {
System.err.println("「src/main/resources/jp/vier/sample/bigquery」以下に「" + CLIENTSECRETS_LOCATION
+ "」を配置する必要があります。");
return;
}
// Sample通りに「getResourceAsStream」の返り値のInputStreamをそのまま使ったところ、
// 「GoogleClientSecrets.load」でエラーになってしましました。
// そのためちょっと変なコードになってますが、とりあえずこれで動きました。
// 素直にFileInputStream使うべきだったと反省。。
String jsonString = IOUtils.toString(resourceInputStream);
InputStream stream = new ByteArrayInputStream(jsonString.getBytes("UTF-8"));
// ClientSecretを取得する
GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(new JacksonFactory(), stream);
// Credentialを取得する
Credential credential = getCredential(clientSecrets);
// Use the credential to create an authorized BigQuery client
Bigquery bigquery = new Bigquery(HTTP_TRANSPORT, JSON_FACTORY, credential);
// Create a query statement and query request object
// クエリを取得する
// SELECT TOP(title, 10) as title, COUNT(*) as revision_count
// FROM [publicdata:samples.wikipedia] WHERE wp_namespace = 0;
URL queryUrl = BigQueryRestApiSample.class.getResource(SAMPLE_QUERY_LOCATION);
String query = IOUtils.toString(queryUrl);
System.out.println(query);
QueryRequest request = new QueryRequest();
request.setQuery(query);
// テストしたら何度かタイムアウトしたので、クエリのタイムアウト時間を伸ばしました。
request.setTimeoutMs(1000L * 240);
// BigQueryのAPIを実行してクエリ結果を取得する
QueryResponse queryResponse = bigquery.jobs().query(PROJECT_NUMBER, request).execute();
// クエリ結果を出力する
Listrows = queryResponse.getRows();
System.out.println("Query Results:\n----------------");
if (rows != null) {
for (TableRow row : rows) {
for (TableRow.F field : row.getF()) {
System.out.printf("%-50s", field.getV());
}
System.out.println();
}
} else {
System.out.println("no results.");
}
}
/**
*
* @param clientSecrets
* @return
* @throws IOException
*/
public static Credential getCredential(GoogleClientSecrets clientSecrets) throws IOException {
// 既存のリフレッシュトークンの取得を試みる
String storedRefreshToken = loadRefleshToken(REFLESH_TOKEN_FILE_NAME);
// リフレッシュトークンの有無をチェックして、取得できなければOAuthの認可フローを開始する
if (storedRefreshToken == null) {
// Create a URL to request that the user provide access to the BigQuery API
String authorizeUrl = new GoogleAuthorizationCodeRequestUrl(clientSecrets, REDIRECT_URI,
Collections.singleton(BigqueryScopes.BIGQUERY)).build();
// Prompt the user to visit the authorization URL, and retrieve the provided authorization code
System.out.println("Paste this URL into a web browser to authorize BigQuery Access:\n" + authorizeUrl);
System.out.println("... and paste the code you received here: ");
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
// ユーザーが入力した認可コードを取得する
String authorizationCode = in.readLine();
// Create a Authorization flow object
GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,clientSecrets, Arrays.asList(BigqueryScopes.BIGQUERY))
.setAccessType("offline")
.setApprovalPrompt("force").build();
// Exchange the auth code for an access token and refesh token
GoogleTokenResponse response = flow.newTokenRequest(authorizationCode).setRedirectUri(REDIRECT_URI)
.execute();
Credential credential = flow.createAndStoreCredential(response, null);
// リフレッシュトークンをファイルに保存
saveRefleshToken(REFLESH_TOKEN_FILE_NAME, credential.getRefreshToken());
return credential;
// リフレッシュトークンを取得できた場合
} else {
// リフレッシュトークンを使って新しいアクセストークンを取得する
GoogleCredential credential = new GoogleCredential.Builder()
.setTransport(HTTP_TRANSPORT)
.setJsonFactory(JSON_FACTORY).setClientSecrets(clientSecrets).build()
.setFromTokenResponse(new TokenResponse().setRefreshToken(storedRefreshToken));
credential.refreshToken();
return credential;
}
}
/**
* RefleshTokenを取得する
*
* @param path
* @return
*/
public static String loadRefleshToken(String path) {
try {
String refleshToken = FileUtils.readFileToString(new File(path), "UTF-8");
return refleshToken;
} catch (IOException e) {
return null;
}
}
/**
* RefleshTokenを保存する
*
* @param path
* @param refleshToken
* @return
*/
public static void saveRefleshToken(String path, String refleshToken) throws IOException {
FileUtils.write(new File(path), refleshToken, "UTF-8");
}
}
当初リフレッシュトークンを使うサンプルの存在に気が付かず試行錯誤していたため、
リンク先のサンプルコードとは若干違うコードになってしまいました。。
やっている事は基本的に同じです。
○サンプルプロジェクト
・ファイル
【BigQueryRestApiSample.zipをダウンロード】
・使い方
1.BigQueryRestApiSample.javaの「PROJECT_NUMBER」を自分のプロジェクトの番号に変更する
(Apis Consoleのoverviewに書いてあります)
2.「src/main/resources/jp/vier/sample/bigquery」以下に「client_secrets.json」を配置する
(上述の:【「Client ID」と「Client Secret」を生成して取得】を参考にしてください)
3.eclipseから「BigQueryRestApiSample」を実行する。
*エラーになる場合はcleanした上で「target/classes」にリソースファイルが生成されていることを確認してみてください。
*クエリがタイムアウトする場合は何度か試してみてください。
ブログを書いていた日曜の夜、なぜかWikipediaのテーブルへのクエリが重かったです。
自前のテーブルは変わらず速かったのですけど。月曜夜現在、また戻っています。
○クエリの生成方法
プログラムから実行するクエリには、たいてい動的に変更される項目があります。
「集計対象の日付」などはわかりやすい例かと思います。
CA Beatでは動的項目の置換を含むクエリ文字列の生成はDomaを使って行なう事にしました。
長いことSeasar2を使ってきた自分としては、
SQLを自分で書くなら(プログラムから自動生成するので無いなら)「2Way SQL」は神仕様だと思うのですよ。
過去のプロジェクトでは2Way SQL使いたさに、Seasar2のコードをベースにSeasar2抜きで動くものを作った事もあります。
今回も作るか・・・？と思ったところでメンバーがDomaを見つけてくれました。
Domaは「Seasar2への依存が一切ありません」とドキュメントに書いてあるとおり、
Seasar2抜きで動作するので「非Seasar2環境だけど2Way SQLを使いたい」という場合に便利です。
*2Way SQLについて
DBFluteのサイトの解説がわかりやすいかと思います。
簡単に言うと、
「プログラム内で使用するテンプレートとして使える」だけでなく、
「テンプレートをそのままコピペして「SQL Plus」等のツールでもそのまま実行できる」
(SQLの定義ファイルにテスト用の値が含まれているため)
そのため「アプリケーションを実行しなくても無編集でクエリのテストができる」ところが利点です。
Seasar2での経験があるエンジニアにとってはこの部分の学習コストがゼロというのも良い点です。
○まとめ
以上、REST-APIを使ってプログラムからBigQueryにクエリを使って投げて結果を取得する方法でした。
リフレッシュトークンを使えば無期限にクエリを実行することができるそうなので、
結果を加工して出力するようにした上で、定期的なバッチ処理を作る事ができます。
これまでに書いた
・ブラウザツール
・bqツール
・REST-API
を状況に応じて使い分ければ、色々なケースでBigQueryを便利に使えるのではないでしょうか。
BigQueryに関して「AppEngineのバックアップデータをBigQueryに転送する方法」を書き残しているので、
少なくともあと一回はBigQueryの記事を書こうと思います。
「AppEngineのバックアップデータをBigQueryに転送」もそのうち自動化したいところです。
既にブラウザツールからはバックアップデータのインポートができるようになっていますが、
まだ正式なリリースが出ていないのか、
bqツールからインポートする方法が公開されていないようなので、
正式にリリースが出た後にブログを公開しようと思います。(書いてはあるんですけどね)
それではまた次回もよろしくお願いしまっす(｀･ω･´)ﾉｼ
○関連記事
・08.Google App EngineとBigQuery 導入編(1/3)
・09.Google App EngineとBigQuery 実践編(2/3)
・10.Google App EngineとBigQuery ノウハウ編(3/3)
★宣伝★
CA BeatではTwitter、Facebookページの運営も行っております！
ブログの更新情報だけでなく、役立つスマホトピックニュースを
選りすぐって配信しております。
ブログ右サイドバーからぜひフォロー、いいね！してくださいねヽ(´▽`)ﾉ