Google SpreadsheetからTreasure Dataにデータをimportする

13745 ワード
TreasureData JavaScript GoogleAppsScript GoogleSpreadSheet JavaScript テキストリンク

SpreadsheetのデータをTreasure Dataに挿れたくなった

Treasure DataとGoogle Spreadsheetを連携させて集計システムを作りたい

SQLを叩けない営業チームでもデータドリブンな意思決定を求められる昨今ですので、使いやすいログ集計環境は必須です。
本当はTableauなりDomoなりのBIツールを導入するのが楽なんですが（TDはそのあたりの連携が強い）、
お金がない諸々の事情で有りモノだけで集計環境を作る必要がありました。

そこで必要となるのが集計条件をDBへ伝えるインターフェイスです。

BIツールならば期間の入力や集計軸の決定、描きたいグラフの選択もお手の物ですが、そんな高級なものはありません。
必要最低限の集計条件を、無料で誰でも扱えるプラットフォーム上で入力させて、それをDBに持っていかねばなりません。
そこで目をつけたのがGoogle Spreadsheetです。Google App ScriptというJava Scriptに似たマクロが使えます。何より使っている人が多い。

集計JOB発注表としてSpreadsheetを使う

例えばこんな感じに集計条件を書いてもらいます。

Excel感覚でどのDBに、どの期間で、どんな分析をしたいかを書いてもらいます。
この集計条件に従って、DB側でクエリを動かします。結果を再び別のGoogle Spreadsheetに返せば簡易集計システムの完成です。
今回は書き込まれた集計条件をTreasure Data上のTableに自動で反映するための方法について書き記します。

参考にしたブログ

こちらのTD開発者ブログを参考にしているので、詳しいことはこちらを参照してください。

Spreadsheetの内容を配列に読み込み

// activeSheet: 読みたいSheetを指定
// rowIndex: 何行目からデータが入っているか。header付きなら2
// colNames: 列名が入った配列を指定
// rowCount: 一度に読み込む行数を指定する。
function readMultipleRows(activeSheet, rowIndex, colNames, rowCount) {
  // データをrowCount分だけ読んで配列に格納する関数
  var rowJSONs = [];
  // time列の有効範囲を指定
  var timeLowerBound = (Date.parse(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd 00:00:00')) / 1000 - 7 * 86400).toFixed();
  var timeUpperBound = (Date.parse(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd 00:00:00')) / 1000 + 3 * 86400).toFixed();
  for (var offset = 0; offset < rowCount; offset++) {
    // 何もない行があったら終端と認識する
    var rowValues = activeSheet.getRange(rowIndex+offset, 1, 1, colNames.length).getValues();
    var isEnd = true;
    for (var ii in rowValues) {
      if (rowValues[0][ii] !== "") {
        isEnd = false;
        break;
      }
    }
    if (isEnd) { break; }
    rowJSONs.push((function() {
      var o = {};
      for (var ii in colNames) {
        o[colNames[ii]] = rowValues[0][ii];
      }
      // time列の値チェック（七日前～三日後）
      if ( timeLowerBound < Date.parse(o["time"]) / 1000 && Date.parse(o["time"]) / 1000 < timeUpperBound){
          return o;
      }
    })());
  }
  return rowJSONs;
}

※time列のチェックの部分は参考ブログのものより簡略化してあります

読み込んだ配列をJSONに変換してTDにpost

// databaseとtableにTD上のスキーマを指定
// apikeyは別途TDから取得しておく
function postTreasureData(rowJSONs, database, table, apikey) {
  var data = {};
  data[database+"."+table] = rowJSONs;
  var payload = JSON.stringify(data);
  var options = {
    "method": "POST",
    "contentType" : "application/json",
    "headers" : {
      "X-TD-Write-Key": apikey,
      "X-TD-Data-Type": "k"
    },
    "payload": payload
  };
  // TreasureDataのREST APIにJSON形式で渡す
  var response = UrlFetchApp.fetch("http://in.treasuredata.com/js/v3/event/", options);
  Logger.log(response);
}

optionsを構成してTreasure DataのREST APIに渡しています。
Spreadsheetの内容をJSON形式に変換して渡しているだけなのでいくらでも書きようはあります。

Treasure Data API key (write only)が必要

timeの罠

TDのTableはすべからくtime列を必要とします。Importの時にこのtimeの範囲に制限があることを知らず、しばらくハマりました。
Javascript SDKの方のドキュメントにさらっと書いてあります。

When you try the above example, please change time value to current unixtime value, we're currently ignoring records which have a timestamp older than 7 days, and newer than 3 days ahead.
https://docs.treasuredata.com/articles/javascript-sdk

つまり今日から見て七日前～三日後までのunixtimeの範囲の値がtime列に入っていないとimportさせないよ、と。罠ですね。

ドキュメント

参考にさせていただいたTD開発者ブログ
https://blog.treasuredata.com/blog/2016/03/24/importing-and-exporting-google-spreadsheets-with-treasure-data/

REST APIでデータImportする時のドキュメント
https://docs.treasuredata.com/articles/rest-api-import

Javascriptで記述するときはこちらも参考
https://docs.treasuredata.com/articles/javascript-sdk