Redash x AWS Lambda x Google SpreadsheetでKPI集計

mixiグループ Advent Calendar 2017 12日目の記事です。

現在、株式会社ミクシィ・リクルートメントで、今年で20周年を迎えたIT・Webの転職・求人情報サイト Find Job!のエンジニアを担当しています。

今回は、RedashとAWS LambdaとGASとGoogle Spreadsheetを利用して、KPIの集計をする話を書きたいと思います。

Redashを導入した話と上記で挙げた技術選定の話

（具体的にはどうするのって話を知りたい方は読み飛ばしちゃってください）

Find Job!では今年に入ってから、BIツールとしてRedashを導入しました。
導入する前までは、定常的なデータ抽出が必要なときはエンジニアがSQLやコードを書いてcronで定期実行し結果をメールで送信する…みたいなことを行なっていました。
Redashを導入後は、cron書く必要もなくなりめっちゃ便利 という状態になりました。

しかしながら、欲張って凝ったことをしようとするとRedashでは難しい部分もあります。
例えば、DBから日ごとの売上を計算してテーブルまたはグラフとして表示する際に、日ごとの目標値との比較をしたいという場合、同一のDBにその目標データがないと目標値の取得と描画はできません。（Redash 3.0では複数のデータソースを扱えるようになり、Redash単体で実現可能になるはずなのでリリースをワクワクして待ってます。）
じゃあ、Google Spreadsheetを使おうという結論にいたりました。

Google Spreadsheetに書き出す際の要件

• 自動でGoogle Spreadsheetに書き出したい
  • 定期実行が必要
• 外からのアクセスは極力避けたい
  • RedashはセキュリティグループでオフィスIPアドレスからのみ許可してる

Google Spreadsheetで自動的に何かをしたいというときはGAS(Google Apps Script)を利用することを思い浮かべる方が多いと思います。
ただ、RedashはオフィスIPアドレスのみのアクセスを許可しており、できるだけ外からのアクセスは避けたいので、GASから直接Redashにアクセスするのは難しそうでした。
そのため、逆にRedashまたはRedashへ安全にアクセスができるものからGoogle Spreadsheetへ書き込む方法を考えました。

検索してみると、Execution APIでGASの関数をHTTP経由で外から呼び出せるようでした。
そこで、AWS LambdaでRedashのAPIを叩く→Lambdaがその結果を引数にGASの関数を呼び出す→GASがAPIの結果をparseしてGoogle Spreadsheetに書き込む…という形をとることにしました。

Google SpreadsheetとGASの用意

Google Spreadsheetのファイルを作り、「ツール」メニューから「スクリプトエディタ」を選択します。
エディタ部分に以下のコードを貼り付けてください。
Google SpreadsheetのURLは、https://docs.google.com/spreadsheets/d/シートID/edit#gid=0という構造なので、シートIDに当てはまる部分をコピペしてコード中のsheetIdに代入してください。

コード.gs

function writeRedashData(data) {
  // データの加工、書き込みの仕方はご自由に書いてください。
  var rows = data.query_result.data.rows;
  var user_count = rows[0].user_count;

  var sheetId = 'シートのID';
  var sheetName = 'シート1';

  var ss = SpreadsheetApp.openById(sheetId);
  var sheet = ss.getSheetByName(sheetName);
  var range = sheet.getRange('A1');
  var value = range.setValue(user_count);
}


function test() {
  var testData = {
    query_result: {
      data: {
        rows: [
          {user_count: 1}
        ]
      }
    }
  };
  writeRedashData(testData);
}

テスト実行

test関数を実行すると、A1セルに1が書き込まれます。

Execution APIを使って外から関数を叩けるようにする

外から叩けるようにする

スクリプトエディタの「実行」メニューから「実行可能APIとして導入」を選択します。
バージョンは適当に付けて公開をします。

さらにスクリプトIDとスコープを確認しておきます。
スクリプトエディタの「ファイル」メニューから「プロジェクトのプロパティ」を選択し、「情報」タブで「プロジェクト キー（サポート終了）」と、「スコープ」タブで「このスクリプトで必要な OAuth スコープ」を確認します。
（プロジェクト キーと書いてありますがスクリプトIDとして使います。また、「サポート終了」と書いてありますが今回利用するのに必要です。）

OAuth 2.0 クライアントIDとシークレットの取得

スクリプトエディタの「リソース」メニューから「Cloud Platform プロジェクト」を選択します。
開いたダイアログから「このスクリプトが現在関連付けられているプロジェクト」のリンクをクリックし、GCPのプロジェクトダッシュボードを開きます。

ダッシュボードの「APIとサービス」メニューから「認証情報」を選択します。

アプリケーションの種類は、「ウェブアプリケーション」を選択し、下記の情報を入力して作成ボタンをクリックします。

• 名前: 適当に付けてしまってかまいません。
• 承認済みの JavaScript 生成元: https://developers.google.com
• 承認済みのリダイレクト URI: https://developers.google.com/oauthplayground

作成が完了すると、クライアントIDとシークレットが発行されるのでメモっておいてください。

「Google Apps Script API」の有効化

さらに作成したプロジェクトでGoogle Apps Script APIを有効化します。

さきほどのダッシュボードのメニューから「APIとサービス」→「ライブラリ」を選択します。
「Google Apps Script API」を検索し、有効にします。

Execution APIでのテストとアクセストークンとリフレッシュトークンの取得

OAuth 2.0 Playgroundで外から関数を叩くテストをするとともに、アクセストークンとリフレッシュトークンの取得をします。

OAuth 2.0 Playground を開きます。

右上の設定から、さきほど取得したクライアントIDとシークレットを入力し、作成したOAuth 2.0クライアントを使って実行するようにします。

「Select & authorize APIs」の入力欄に、さきほど確認したスコープを入力し、「Authorize APIs」ボタンをクリックします。

STEP2. Exchange authorization code for tokens に移動するので、「Exchange authorization code for tokens」ボタンをクリックし、リフレッシュトークンとアクセストークンを発行します。

STEP3に自動的に移動しますが、再度STEP2のアコーディオンを開いて、リフレッシュトークンとアクセストークンをメモっておいてください。

STEP3では実際にAPIリクエストを発行します。

HTTP MethodをPOSTに変更し、Request URIにhttps://script.googleapis.com/v1/scripts/{scriptId}:runを入力します。
（{scriptId}の部分には最初の方に確認したスクリプトIDを入れてください。）

「Enter request body」ボタンをクリックし、以下のJSONを入力します。

{
  "function": "writeRedashData",
  "parameters": [{
    "query_result": {
      "data": {
        "rows": [{
          "user_count": 1
        }]
      }
    }
  }],
  "devMode": true
}

parametersには関数に渡す値の配列を指定します。

「Send the request」をクリックし、200が返ってくれば成功です。
Spreadsheetを確認すると更新されたことが確認できるはずです。

AWS Lambda

AWS LambdaはRedashで取得したデータをほぼそのままGASへPOSTするだけです。

lambda_function.js

const axios = require('axios');
const google = require('googleapis');
const OAuth2 = google.auth.OAuth2;

const getRedashQueryResult = queryId => {
  const redashUrl = `${process.env['REDASH_BASE_URL']}/api/queries/${queryId}/results.json?api_key=${process.env['REDASH_API_KEY']}`;
  return axios.get(redashUrl, {timeout: 3000}).then(response => {
    return response.data;
  });
};

const writeToSpreadsheet = (scriptId, functionName, data) => {
  const auth = new OAuth2(process.env['CLIENT_ID'], process.env['CLIENT_SECRET']);
  auth.setCredentials({
    access_token: process.env['ACCESS_TOKEN'],
    refresh_token: process.env['REFRESH_TOKEN']
  });
  const script = google.script('v1');
  return new Promise((resolve, reject) => {
    script.scripts.run({
      auth: auth,
      scriptId: scriptId,
      resource: {
        function: functionName,
        parameters: [data],
        devMode: true
      }
    }, (err, result) => {
      if (err) {
        console.log(err);
        reject(new Error(err));
      } else {
        resolve(result);
      }
    });
  });
};

exports.handler = (event, context, callback) => {
  getRedashQueryResult(event.queryId).then(data => {
    return writeToSpreadsheet(event.scriptId, event.functionName, data);
  }).catch(error => {
    context.fail(error);
  });
};

環境変数

環境変数として下記のものが必要です。

変数名	内容
REDASH_BASE_URL	RedashのURL
REDASH_API_KEY	RedashのAPIキー
CLIENT_ID	GASを叩くためのGCPのOAuth 2.0 クライアント ID
CLIENT_SECRET	GASを叩くためのGCPのOAuth 2.0 クライアントシークレット
ACCESS_TOKEN	上記のCLIENT_IDとCLIENT_SECRETを利用して取得した自分のアカウントのアクセストークン
REFRESH_TOKEN	上記のCLIENT_IDとCLIENT_SECRETを利用して取得した自分のアカウントのリフレッシュトークン

ちなみにセキュリティグループ付けてVPC上で実行する場合は、そのVPC内部のローカルIPアドレスへアクセスする必要があるので、Lambdaで実際に実行する場合はIPアドレスを渡してあげるといいと思います。（Route53でプライベートDNS作るのもありです）

イベント

AWS Lambdaもlambda functionという名の通り関数なので、外から値を渡すことができます。
クエリIDやGASのスクリプトID、呼び出すGASの関数名を以下のようなJSONで指定することで、使い回しができるようになります。

event.json

{
  "queryId": "RedashのクエリID",
  "scriptId": "GASのスクリプトID",
  "functionName": "呼び出すGASの関数名"
}

その他細かいLambdaの設定は省きますが、上記のRedashのIPアドレスを指定する部分でハマりがちなのでそこを押さえておけば問題なく動くかと思います。

最後の方は力尽きて適当になってしまいましたが、上記のLambdaを定期実行するよう設定することで、定期的にRedashの情報をGoogle Spreadsheetに書き込むことができます。

最後に

最後に、今回のRedashとGoogle Spreadsheetでこんな感じにKPI集計してるよってのを紹介して終わりたいと思います。

書き込まれた実績値とあらかじめ用意しておいた予算値をVLOOKUPやらHLOOKUPやらを駆使しつつ、Excel力(Spreadsheet力)を発揮するとイイカンジにKPIの予実管理ができるようになります。
コツは、Redashからのデータは別のシートに書き込むようにしておいて、日付等からVLOOKUPや参照を利用して実際の表に持ってくることです。

（実際に使用したシートに適当な数字を当てはめたものです。微妙に最後の計算が合わないのは目をつぶってください。）

という感じでRedashとGoogle Spreadsheetを組み合わせるとさらに便利になるので、興味があれば試してみください。