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

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の画面にします。

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

各APIを有効にする画面になるので、BigQueryAPIを有効にしてやってください。
ただし、これだけじゃありません！この画像にもありますが、『Googleデベロッパーコンソール』でも有効にする必要があります！（つーか、これ面倒。１回でいいじゃん！といつも思う）

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

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

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

今回のスクリプトはこんな感じです。
①入力するシート名『master』、結果を出力するシート名『result』としました。
　（あらかじめこの２つのシートをスプレッドシートに作っておいてください）
②masterシートのC2セルにSQLを入力するようにしました。
③出力結果の開始位置を指定できます。
　・masterシートのC3にresultシートの何行目から出力するか指定しています。
　・masterシートのC4にresultシートの何列目から出力するか指定しています。
④スプレッドシートのメニューバーにBigQuery→実行というメニューを追加しています。

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

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

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使いの人多いですからこういうのがとっつきやすいみたいですね。
もし、わかんないこととかあれば是非コメントで書いてみてください！

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