BigQuery内のデータをグラフ化（可視化）してSlackへの投稿を自動化する方法

こちらの記事は Google Cloud Platform Advent Calendar 2017 の 4日目への投稿になります。

みなさん、毎日GCP使ってますか？
わたしは最近あまりさわれていません。。こんにちは。chidakiyoです。

先日 GCPUG にて、 GCPでNoAdmした話 という内容で発表させていただきました。
資料の中で詳細はQiitaで書きます、と言っていたのでその記事になります。

前提

エンジニアという仕事をしていると、毎日何かしらのデータの確認など定点観測を行っていると思います。
毎日決まったSQLを投げてコツコツ確認するということを頑張れる人もいると思いますが、私は技術は自分がラクをするために使いたい派なので、毎日朝起きたら見たい情報がきれいなグラフになってSlack投稿されている状態が嬉しいです。

今回は、サービス運営にあたって、BigQueryへデータが蓄積されているのでそのデータを元にSlackにグラフを自動的に投稿するという仕組みについて書きます。

汎用性

要点はGoogle SpreadSheetにグラフが作成できればそれを定期的にSlackに投げれるというものなので、

• 日々追いたいデータがある非エンジニア（株価とか、競合他社の価格調査など）
• 口座の残高の推移（データを取れる方法があればできるかも）

そういうものが欲しい人もチャレンジしてみてもいいかもしれません。
データソースはBigQueryに限らずに利用できると思います。

構成

構成は以下のようになります。

おおまかな流れとしては

1. AppsScript(1)がSQLを発行してデータを取得してくる
2. Google SpreadSheetが取得したデータを用いてグラフを作成する　（この部分は特にプログラミングは必要なく、普段使っているように表を元にグラフを作っておけば新しいデータを取得した際にグラフが更新される）
3. AppsScript(2)がSlackへグラフを画像として送る。

手順1 （データ取得側を作る）

Google SpreadSheetの新規ファイルを作成する

既存のファイルを利用してもOKです。

AppScriptを作成する

メニューの 「ツール」 → 「スクリプトエディタ」 からAppScriptを開きます。

こんな画面になります。

スクリプト(1)を貼ります

こちらのスクリプトは、定期的に実行し、BigQueryから取得したデータをSpreadSheetに入力するという役割になります。

一番最初のprojectIdはBigQueryのあるプロジェクトのIDを入力します。

main.gs

/* 実行するProject ID */
var projectId = 'gas-bq-sample';

/* シートの全クエリを実行 */
function runAllQueries() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var queries_sheet = spreadsheet.getSheetByName("Queries");

  for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
    var sheet_name = queries_sheet.getRange(i, 1).getValue();
    var sheet = spreadsheet.getSheetByName(sheet_name);
    if(sheet == null) {
      sheet = spreadsheet.insertSheet();
      sheet.setName(sheet_name);
    }
    runQuery(
      queries_sheet.getRange(i, 2).getValue(),
      sheet,
      queries_sheet.getRange(i, 3).getValue().toString(),
      queries_sheet.getRange(i, 4).getValue().toString()
    );
  }
}

/* クエリを実行 */
function runQuery(query, sheet, title, query_type) {
  var request = {
    query: query
  };

  if (query_type == 'legacy') {
    request.useLegacySql = true
  } else {
    request.useLegacySql = false
  }

  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    // clear exists data and set title
    sheet.clear();
    sheet.appendRow([title]);

    // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);

    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
        if(headers[j].substr(-3)=="_at") {
          data[i][j] = cols[j].v
          if(data[i][j] < 10000000000000) {
            data[i][j] = new Date(data[i][j]);
          }
          else if(data[i][j] < 10000000000000*1000) {
            data[i][j] = new Date(data[i][j] / 1000);
          }
        }
      }
    }
    sheet.getRange(3, 1, rows.length, headers.length).setValues(data);
  } else {
    Logger.log('No rows returned.');
  }
};

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "このシートの内容を更新",
    functionName : "runCurrentSheetQuery"
  },
  {
    name : "全シートの内容を更新",
    functionName : "runAllQueries"
  }];
  spreadsheet.addMenu("BigQuery", entries);
};

データを出力するシートを作成する

以下のシートを作成します。

• Queries シート。
  • 実行するクエリを定義するシートです。1カラム目が結果を出力するシート名、2カラム目がSQLクエリ、4カラム目がstandard/legacyの切り替えのフラグです。（3カラム目を利用しないので注意）

例）

クエリを作成する

BigQueryで実行するクエリを2行目以降に書いていきます。

以下の例は、GCPの課金データをexportしたテーブルに対して実行している例です。
GCPの課金Exportのv1のスキーマは自分でもまだあまり使っていないのであまり良くわかってませんが例としてざっくりしたSQLを実行しています（こちらどのようなSQLが良いかご存じの方はご指摘ください）

StandardSQLで実行するのでD列のカラムは何も書きません。（LegacySQLの場合にはLegacyと入力してください）

スクリプト側でBigQueryAPI利用を有効にする

メニューの 「リソース」 → 「Googleの拡張サービス」 を選択し、

BigQueryAPIを有効にする

GoogleAPIコンソールでも有効にする必要があると書いてあるので、
そのリンクを押し、遷移した先で 「APIとサービスの有効化」 をクリックする

bigquery と検索し、BigQueryAPIを選択する

BigQueryAPIを有効にする

ここまでやればひとまずBQ接続周りの設定はOK。

スクリプトを一旦手動実行する

初回実行時にAPI接続の権限などを確認する処理が必要になるので、初回の1回は手動で実行します。

初回実行すると承認が必要です。というダイアログが表示されるので「許可を確認」をクリックし、認証情報をポチポチする。

認証情報の入力が完了すると、何のエラーも出力されずに終了した状態であれば、SpreadSheet側にBigQueryでSQLを実行した結果が指定したシートに出力されているはず。

（値はダミーです。）

グラフを作成する

SpreadSheeｔ上でグラフを作成する。
初回のみ、SQLからの出力データに対し、グラフを作成する必要があります。
次回から新しいデータが入力された際にグラフが自動的に更新され、その更新されたグラフを図としてSlackに投稿するということになります。

詳しいグラフの作成の仕方は他にいい記事がたくさんあるのでここでは割愛します。

Tips: ここまでの部分がグラフを作成するという処理になります。BQをスクリプトで取得するという部分を別の仕組み（Adsenseから自動的にデータを取ってくる）などに切り替えることで別のデータソースを利用することも出来ます。

手順2 （グラフをSlackに送る側を作る）

スクリプト(2) を作成する

メニューの 「新規作成」 → 「スクリプトファイル」 から新規でスクリプトファイルを作成する。

スクリプト(2) を貼ります

slack.gs

function notify(){

  var sheetName = 'billing' // 送りたいグラフがあるシート名を入力する
  var title = '◯◯のレポート' // Slackに通知する際の画像のタイトル
  var index = 0            // グラフが1つだけの場合0 （複数のグラフがある場合には１などと指定する）

  var chartImage = getChart(sheetName, index)

  slack(chartImage, title);
}

// Slackに送る
function slack(chart, title) {
    var url        = 'https://slack.com/api/files.upload'; // slackのAPIのURL
    var token      = 'xoxb-000000000000-XXXXXXXXXXXXXXXXXXXXXXXX'; // Slackのボットのトークン

    var channel    = '#hoge_channel'; // チャンネルに送る時。（事前にboｔのinviteが必要）
  //var channel    = '@foo_san'; // 個人宛に送りたい時

    var payload = {
        'token'      : token,
        'channels'   : channel,
        'file'       : chart,
        'filename'   : title
    };

    var params = {
        'method' : 'post',
        'payload' : payload
    };

    var response = UrlFetchApp.fetch(url, params);
}

// シートからチャートを取得する
function getChart(sheetName, index) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName); // シート名を指定
  var charts = sheet.getCharts();

  // グラフが複数ある場合は調整が必要です。
  var chartImage = charts[index].getBlob().getAs('image/png').setName("graph.png");
  return chartImage
}

書き換える必要がある箇所は以下です

• sheetName
• title
• index （必要に応じて）
• token
• channel

スクリプトを一旦手動実行する

こちらのスクリプトも同様に外部にHTTPアクセスする際の権限の確認が必要なため、一旦手動実行します。

Slackに通知が届いていることを確認する

この段階でSlackの指定したチャンネルに先程作ったグラフが送られてくるので確認する。

こんな感じで届いていると思います。
Slack上に送った場合に見づらい部分やフォントのサイズはSpreadSheet上で修正することで画像に反映されます。
グラフの色や、フォントサイズを大きくすることでSlackのタイムライン上でパッと見やすくなります。

定期的に通知が送られるようにする

ここから定期的に処理が行われるようにする設定です。
ここまで読んできてわかるように、

1. データを集める処理
2. Slackにグラフを投稿する処理

はそれぞれ独立しているため、2つの設定をそれぞれ設定し、タイミングも「データ取得→Slack送信」となるように調整する必要があります。（あたりまえですね）

定期実行を設定する

メニューの 「編集」 → 「現在のプロジェクトのトリガー」 を選択し、

2つのトリガーを設定します。

1. データを集める処理は runAllQueries を設定
2. Slackにグラフを投稿する処理は notify を設定します。

処理時間はそれぞれ、 「午前3時〜4時」　→ 「午前7時〜8時」 という形で設定しています。

まとめ

これで定期的に確認したい情報をグラフとしてSlackに投稿する仕組みができました。
データの量によりますが、かなり格安（ほぼ無料）で運用できるので、個人で集めているデータをSlackに連携するというイメージであればすぐにできると思います。

ヒントとおまけ

• Queriesのシートは縦にどんどんSQLを増やせます。
• 接続先のBQのプロジェクトが単一であればAテーブル、Bテーブルなど別々にそれぞれSQLを実行しその結果を用いてグラフを作成し通知できます。
• SQLが苦手な人は、シンプルにSQLでテーブルのデータを取ってきて、SpreadSheet上で様々な加工をした後、グラフ化して通知することも出来ます。（ピボットしたいなどという非エンジニアは多いかと）
• SpreadSheet側で大量の処理をすると「コンピュート時間が長い」とお怒りのメールが届きます。SpreadSheet上にもQuery関数と言うものがあり、セルに対してSQLを書ける機能なのですがそちらを利用するほうがセルごとに関数を入れるより処理が軽いような気がします（個人の感想）

参考

[まだBIツールで消耗してるの？ ~ サーバレス・KPI分析ダッシュボードをGAS + Slackで]
http://blog.yamotty.com/entry/20161114/1479079800
[BigQueryを簡単にグラフにするGoogle Apps Script]
http://toreta.blog.jp/archives/20649904.html