はじめに
テクノロジー戦略本部の吉村です。
普段の業務では社内基幹システムのGYROの開発を主に行っています。
この記事ではBigqueryとGoogle Apps Script(以下GAS)を使った例をご紹介させていただければと思います。
業務でGYROの開発を通して実際にシステムを利用している方々から、
「この機能がどれぐらい、どのような内容で行われたか教えてほしい。」
というような依頼をいただくことがあります。
弊社ではBigqueryを利用していて、このようなrequestの分析を行うときなどにとても重宝しています。
ただBigqueryで取得したrequestのデータを一つ一つ数えていくようでは時間的にも精神的にも厳しいのでGAS（Google Apps Script）を利用してすきなフォーマットに整えて出力して共有するようにしています。
この記事では実際に上記のようなユースケースでどのようにデータを取得して、出力しているのかをGoogleが提供してくれている一般公開のデータをもとにご紹介させていただきます。
無料枠の範囲での紹介になりますが、別途利用されていると料金がかかる可能性があるため料金ページを参考にしてください。
この記事で紹介しないこと
以下は本題とずれるので、この記事では言及しません。
- GCPのプロジェクト作成方法
- JavaScriptの構文の説明
環境
- Google Cloud Platform
- Bigquery
- Google Apps Script（V8 ランタイム）
Bigqueryのセットアップ
まずGCPでプロジェクトを作成し、
Bigqueryを利用するので有効にします。
有効になると以下のような画面が表示されていると思います。
次にBigqueryが一般公開用のデータセットを用意してくれているのでそちらを利用するようにします。
一般公開用のデータセットはたくさん用意されているのでこちらのページから探すといいと思います。
今回は普段利用しているGitHubのデータセットを利用したいと思います
Bigqueryのコンソールのリソース横にあるデータを追加から一般公開データセットを調べるを選択して
GitHubを検索して以下のデータセットを表示を押すとデータが追加できます。
Bigqueryはこちらで準備完了なので、次にGASでBigqueryAPIを叩く準備をしていきます。
まずスプレッドシートを作成し、GASを利用できるようにスクリプトエディタを選択します。
スクリプトエディタが開いたらBigqueryのAPIが叩けるように設定します。
画面左上のサービスからBigqueryAPIが開いたらドキュメントを選択し、追加します。
追加できたらいよいよGASを使ってBigqueryからデータを取得していきます。
今回はBigqueryが用意してくれている
Githubのsampleテーブル内で最も人気のあるGoのpackageを取得するSQLを
利用したいと思います。
画像最下部のRun this queryをクリックするとSQLが入力された状態のBigqueryの画面が表示されるので、そちらのSQLを利用します。
準備ができたので実際に取得してみましょう。
流れとしては以下になります。
- queryシートからクエリを取得
- 1のクエリをもとにBigqueryのデータ取得APIをrequest
- 受け取ったresponseをもとにresultシートを更新する。
スプレッドシートに以下の２つのシートを用意します。
- result
→ 結果を出力するシートです。
- query
→ SQLをセットするシート
それでは結果を出力するresultシートのA1セルに「package」、B1セルに「count」と入力してください。
次に前述のGoのpackageを検索するSQLをqueryシートのB2セルに貼り付けてください。
A2には該当のGCPのproject_idを貼り付けてください。
以下のようになっていると思います。
最後にGASを編集していきます。
コード.gs内に以下のように記述してください。
function myFunction() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const querySheet = spreadsheet.getSheetByName("query"); // クエリが記載されているシートを取得
const resultSheet = spreadsheet.getSheetByName("result"); // 結果を出力するシートを取得
const projectId = querySheet.getRange("A2").getValue(); // プロジェクトIDをqueryシートから取得
const query = querySheet.getRange("B2").getValue(); // SQLをqueryシートから取得
// Queryパラメータを作成
let queryRequest = BigQuery.newQueryRequest();
queryRequest.query = query;
const result = BigQuery.Jobs.query(queryRequest, projectId);
let rows = [];
for (let i = 0; i < result.rows.length; i++) {
let resultRow = result.rows[i];
let url = resultRow.f[0].v;
let count = resultRow.f[1].v;
let row = [];
row.push(url, count);
rows.push(row);
}
const startRow = 2,
startColumn = 1,
numColumns = 2;
resultSheet.getRange(startRow, startColumn, rows.length, numColumns).setValues(rows);
}
これで準備完了です！
GASの実行ボタンを押してみましょう！
以下のように画面に結果が出力されると思います。
GitHubのsample_contentsではfmtパッケージが一番利用されていることがわかりました！！
参考
おわりに
大量データを保存しておけて、
素早く取得して分析に活用できるのはBigQueryの素晴らしいところですね。
今回は 普段の開発でのアプリケーションコードとは
違うところの紹介をさせていただきました。
こちらがBigqueryを活用する上で少しでも参考になれば嬉しいです。
バイセルテクノロジーズではエンジニアを募集しています。
もし興味をお持ちいただけましたらぜひご応募下さい。
