GoogleAppsScript
GoogleCloudPlatform
GoogleBigQuery
bigquery

BigQueryとスプレッドシートとGASと。

More than 3 years have passed since last update.


BigQueryのフロントエンド

BigQueryを使っていてフロントエンドのアプリケーションは何を使っているのか良く聞かれる。もちろん、BigQueryのwebインターフェイスでクエリを作って、CSVでダウンロードとかも良いと思うのですが、やっぱりちょっと違うなぁと。


どんなものがあるのでしょうか?

BigQueryの公式にはこんなものがありました。

https://cloud.google.com/bigquery/third-party-tools?hl=ja


Tableau

Googleの中の人も使ってるぐらい良いものです。

私自身も何度か使っているのですが、やっぱり良いです。ただし、複雑なことしたいなぁと思うととんでもなく遅くなったり。

後ほど書きますけど、Viewなんかで回避すれば良いし、ローカル(もしくはサーバ)にキャッシュデータを持っておけばかなり使えます。便利だし、可視化も良い感じ。


BIME

こちらもなかなか良いです。クラウドサービスでEC2上で動いています。

Salesforceなんかとも連動するようで、うちの会社でも検討しているところです。


あ、あ、あの〜題目と違いますけど?

で、本題。

正直言って、この手のツールってお値段がかなりのものです。

一人、もしくは少数で使うのには良いのですが、みんなで使うとなると結構な金額になります。まぁ、使い方なんだと思うんですけどね。じゃ、どうする?

スプレッドシートがあるじゃないか!(笑)

もちろんスプレッドシート単独だと無理なわけで。ここでGAS(GoogleAppsScript)の登場です。

やってみましょう!


GASでBigQueryAPIが使えるように。

まずはスプレッドシートを開いて『ツール』→『スクリプトエディタ』を選択してGASの画面にします。

spreadsheet_001.jpg

GASの編集画面が立ち上がったら『リソース』→『Googleの拡張サービス』を選びます。

spreadsheet_002.jpg

各APIを有効にする画面になるので、BigQueryAPIを有効にしてやってください。

ただし、これだけじゃありません!この画像にもありますが、『Googleデベロッパーコンソール』でも有効にする必要があります!(つーか、これ面倒。1回でいいじゃん!といつも思う)

spreadsheet_003.jpg

と、いうことでデベロッパーコンソールでもAPIを許可します。

spreadsheet_004.jpg

これで使えるようになりました。


ではGASをぶち込んでやりましょう!

今回のスクリプトはこんな感じです。

①入力するシート名『master』、結果を出力するシート名『result』としました。

 (あらかじめこの2つのシートをスプレッドシートに作っておいてください)

②masterシートのC2セルにSQLを入力するようにしました。

③出力結果の開始位置を指定できます。

 ・masterシートのC3にresultシートの何行目から出力するか指定しています。

 ・masterシートのC4にresultシートの何列目から出力するか指定しています。

④スプレッドシートのメニューバーにBigQuery→実行というメニューを追加しています。

 spreadsheet_005.jpg

なんでこうやったかと言うと、出力結果の列名などを自由に決めることが出来るので、汎用性があるかなぁ?と思った次第です。実際にこれ使ってます。(笑)

サンプルスクリプトです。


bigquery.gs

//自分のProjectNumberを入れてやってください。

var projectNumber = '*******';
var ss = SpreadsheetApp.getActiveSpreadsheet();

//SQLとか、出力セルを入力するシート
var master_sheet = ss.getSheetByName('master');

//SQLの結果を出力するシート
var result_sheet = ss.getSheetByName('result');

function onOpen() {
var menus = [{name:'実行',functionName:'onRunQuery'}];
ss.addMenu('BigQuery',menus);
}

function onRunQuery() {

var sql = master_sheet.getRange('C2').getValue();
var start_row = master_sheet.getRange('C3').getValue();
var start_column = master_sheet.getRange('C4').getValue();

var del_col = result_sheet.getLastColumn();
var del_row = result_sheet.getLastRow();
result_sheet.getRange(start_row,start_column,del_row,del_col).clear();
result_sheet.getRange(start_row,start_column,del_row,del_col).clearFormat();
result_sheet.getRange(start_row,start_column,del_row,del_col).clearDataValidations();

var query_results;
var resource = {
query : sql,
timeoutMs: 1000000
};

try {
query_results = BigQuery.Jobs.query(resource, projectNumber);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
while (query_results.getJobComplete() == false) {
try {
query_results = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
if (query_results.getJobComplete() == false) {
Utilities.sleep(3000);
}
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
}

var resultCount = query_results.getTotalRows();
var resultSchema = query_results.getSchema();

var resultValues = new Array(resultCount);
var tableRows = query_results.getRows();

for (var i = 0; i < tableRows.length; i++) {
var cols = tableRows[i].getF();
resultValues[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
resultValues[i][j] = cols[j].getV();
}
}
result_sheet.getRange(start_row,start_column,resultCount,tableRows[0].getF().length).setValues(resultValues).setBorder(true,true,true,true,true,true);
}


あとはこれを保存してやって、スプレッドシートをリロードしてやってください。メニューに追加されていると思います。


どーでしたか?

ごめんなさい。結果画面がなくて。

でも、ここまでくればもう結果画面が出てるかと。(笑)

私の場合はこのスクリプトをライブラリ化しています。わざわざGASにいちいち書くの面倒ですからね。

そのライブラリを仕込んだスプレッドシートにSQLを書いて、現場に渡しています。そうすれば現場部門はメニューの『BigQuery』→『実行』とやるだけでレポートが出てくるわけで。

もしくは、SQLを好きに書きたい現場はこいつにぶち込んだりしています。(笑)

ここでいくつか注意点。

①SQLはあまり複雑にしない。

文字数制限があるようです。

ですから、BigQueryのwebコンソールである程度作って、Viewにすると良いと思います。

②描画はもっと速くできる!

すいません、実はもっと描画を速くする方法があるんだけど、スクリプト忘れちゃった。

(てへぺろ)


と、いうことで・・・

どっちかって言うとGASじゃね?と言われそうですが、私はこれでBigQueryを色々使ってます。

現場にもこれで使ってもらっています。現場の人間ってExcel使いの人多いですからこういうのがとっつきやすいみたいですね。

もし、わかんないこととかあれば是非コメントで書いてみてください!

次回はこれをバッチで処理してメールで定期的にレポートを出すを書いてみます。

じゃ、ばいなら。