Amazon QuickSight

昨年のAWS re:Invent 2015で発表された「Amazon QuickSigt」が先日一般公開されました。AWSのサービスですので、当然クラウド駆動で、低価格な、いわゆるBIサービスです。データセットはAWSのDBサービスであるRDS等や従来DBのMySQL等から作り出すことができます。現在はの3リージョンに対応しています。詳しくはサービスページSlideshareあたりを見るのが良さそうです。
qs-0

kintone連携の用途

kintoneのグラフ・集計機能は結構優秀なのですが、次にあげるようなケースは標準機能では難しいため、要件に応じてカスタマイズし直すか、BIツールに渡して可視化する等の方法が取られています。
・レコード数が多いアプリのグラフ
・多軸・多種類の掛け合わせグラフ
・アプリまたぎのデータグラフ

ということで、昨年のAWS re:Inventでも「QuickSightとkintoneは相性良さそう」と話していたのですが、やっと試せる時が来ました。

Amazon QuickSightでkintoneデータを可視化する方法

QuickSightはデータセットの取込みもしくは作成からスタートしていきますので、kintoneからのデータ取込みを行うのにどんな方法があるかというところですが、ここには幾つかのパターンが考えらそうです。

・kintoneのレコードデータをRedshiftに保存して、QuickSightから取り込む
・kintoneのCSV出力機能でダウンロードしたCSVをQuickSightで取り込む
・kintoneのレコードデータをCSVでS3に保存して、QuickSightから取り込む

等々。ですが、今回はAmazon S3を経由した方法をご紹介したいと思います。

quicksight

S3を経由したkintoneとAmazon QuickSightの連携

はじめに、操作ステップを先に見ていきましょう。なお、AWSのリージョンは今回QuickSightが利用できるリージョンを利用していきます。
1 kintoneからCSV化したデータをS3にアップロードする。同時にQuickSightでデータセット作成時に必要なManifestファイルもアップロードする
2 QuickSightのコンソールに遷移し、1.の手順でアップロードされたManifestを使ってデータセットを作成する
3 作成したデータセットからグラフを設定する

これらのステップを作り出すのに、設定の負担としては、2と3は大したことなく、1のkintoneからS3へのCSVアップロード部分が占める部分が大きいです。

それでは、それぞれのステップを実現する設定手順と、操作方法を見ていきましょう。

1 kintoneからCSV化したファイルをS3にアップロードする設定

このパートは更に幾つかのポイントに細分化されますので、先に確認しておきましょう。
1-1 QuickSightのデータセットの元になるデータCSVやManifestを格納するためのS3バケットを設定する
1-2 kintone JavaScriptカスタマイズを設定する
1-2-1 kintoneデータからCSVファイルを作成。今回は開いている一覧の条件に合致したデータのCSVを生成する
1-2-2 1-2-1で作成したCSVファイルをAmazon S3にアップロードする
1-2-2 1-2-2でアップロードされたCSVファイルをQuickSightでデータセット生成するためのManifestファイルをAmazon S3にアップロードする

1-1 S3バケットの設定

(S3が利用可能なユーザーのアクセスキー、シークレットキーが必要になりますので、未設定の場合には必要に応じてこちらを参考にIAMユーザーの設定を済ませておいてください)

(1) QuickSight用のS3バケットを作成
QuickSightのデータセットの元となるCSVと、QuickSightでデータセット作成時に必要になるManifestファイルを格納するためのバケットを作成しておきます。S3コンソールから「バケットを作成」をクリックして、設定開始です。
s3-1

(2) CORS設定

今回はkintoneのJavaScriptカスタマイズでCSVをS3へファイルアップロードしますので、CORS設定を行います。(1) で作成したバケットに設定していきます。
s3%e2%88%922
こちらのドキュメントを参考に今回は次のように設定します。

<?xml version="1.0" encoding="UTF-8"?>
<CORSConfiguration xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
    <CORSRule>
        <AllowedOrigin>*</AllowedOrigin>
        <AllowedMethod>GET</AllowedMethod>
        <AllowedMethod>PUT</AllowedMethod>
        <AllowedMethod>POST</AllowedMethod>
        <AllowedMethod>DELETE</AllowedMethod>
        <AllowedHeader>*</AllowedHeader>
    </CORSRule>
</CORSConfiguration>

s3-3
s3-4
これで、kintoneからJavaScriptでS3にアクセスできます。

1-2 kintone JavaScriptカスタマイズ

kintone JavaScriptカスタマイズには大きく次の3つの機能を実装します。

1-2-1 kintoneデータからCSVファイルを作成。今回は開いている一覧の条件に合致したデータのCSVを生成する
1-2-2 1-2-1で作成したCSVファイルをAmazon S3にアップロードする
1-2-2 1-2-2でアップロードされたCSVファイルをQuickSightでデータセット生成するためのManifestファイルをAmazon S3にアップロードする

コードは次のようになりますが、{ACCESS_KEY}、{SECRET_KEY}、{BUCKET_NAME} を置き換えてもらえれば、kintoneで表示中一覧の条件に一致するCSVファイルがS3にアップロードできるようになります。

jQuery.noConflict();
/*
 * global kintone
 * global aws-sdk
 * global jQuery
 * global moment
 * global sweetalert
 * global spin
 */
(function($) {
  'use strict';

  // AWSの設定情報
  var ACCESS_KEY = '{ACCESS_KEY}';
  var SECRET_KEY = '{SECRET_KEY}';
  var REGION = 'us-east-1';
  var S3_BASIC_URL = 'https://s3.amazonaws.com/';
  // S3のバケット名
  var BUCKET_NAME = '{BUCKET_NAME}';
  // 対象外フィールドタイプ
  var EXCEPT_FIELD_TYPES = [
    /*
    '__ID__',
    'RECORD_NUMBER',
    '__REVISION__',
    'CREATOR',
    'CREATED_TIME',
    'MODIFIER',
    'UPDATED_TIME',
    */
    'STATUS',
    'STATUS_ASSIGNEE',
    'CATEGORY',
    'MULTI_LINE_TEXT',
    'RICH_TEXT',
    'CHECK_BOX',
    'MULTI_SELECT',
    'FILE',
    'USER_SELECT',
    'SUBTABLE',
    'ORGANIZATION_SELECT',
    'GROUP_SELECT',
    'GROUP'
  ];

  // AWSの認証
  function initAWS() {
    AWS.config.update({
      accessKeyId: ACCESS_KEY,
      secretAccessKey: SECRET_KEY
    });
  }

  // S3オブジェクトの作成
  function buildS3Object() {
    var s3 = new AWS.S3({
      params: {
        Bucket: BUCKET_NAME
      }
    });
    return s3;
  }

  // S3 PUTのパラメータ設定
  function buildS3PutParams(key, file, type) {
    var params = {
      Key: key,
      ContentType: type || 'text/csv',
      Body: file,
      ACL: 'public-read'
    };
    return params;
  }

  // kintoneのレコード全件取得
  function fetchAllRecords(appId, condition, opt_offset, opt_limit, opt_records) {
    var offset = opt_offset || 0;
    var limit = opt_limit || 500;
    var allRecords = opt_records || [];
    var params = {
      app: appId,
      query: condition + ' order by $id asc limit ' + limit + ' offset ' + offset
    };
    return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
      allRecords = allRecords.concat(resp.records);
      if (resp.records.length === limit) {
        return fetchAllRecords(appId, condition, offset + limit, limit, allRecords);
      }
      return allRecords;
    });
  }

  // ビューのフィールド取得
  function getFields(viewId) {
    return kintone.Promise.all([
      kintone.api(kintone.api.url('/k/v1/app/views', true), 'GET', {
        app: kintone.app.getId(),
        lang: "ja"
      }),
      kintone.api(kintone.api.url('/k/v1/preview/app/form/fields', true), 'GET', {
        app: kintone.app.getId()
      })
    ]).then(function(r) {
      var views = r[0].views;
      var properties = r[1].properties;
      var fields = [];
      for (var key in views) {
        var view = views[key];
        if (view.id == viewId && view.fields.length >= 1) {
          for (var i = 0; i < view.fields.length; i++) {
            var viewField = view.fields[i];
            var obj = {
              code: properties[viewField].code,
              label: properties[viewField].label,
              type: properties[viewField].type
            };
            fields.push(obj);
          }
        }
      }
      if (fields.length === 0) {
        for (var key in properties) {
          var obj = {
            code: properties[key].code,
            label: properties[key].label,
            type: properties[key].type
          };
          fields.push(obj);
        }
      }
      return fields;
    });
  }

  // CSV(UTF-8)を作成してblobで返す
  function buildCSV(fields, records) {
    var csv = [];
    var row = [];
    // 項目をセット
    for (var i = 0; i < fields.length; i++) {
      row.push('\"' + fields[i].label + '\"');
    }
    csv.push(row);
    // データ部分をセット
    for (var i = 0; i < records.length; i++) {
      row = [];
      var record = records[i];
      for (var j = 0; j < fields.length; j++) {
        var code = fields[j].code;
        row.push('\"' + record.value + '\"');
      }
      csv.push(row);
    }
    // CSVセパレータ
    var csv_separater = ','; // '\t'
    // blob化
    var csvbuf = csv.map(function(e) {
      return e.join(csv_separater)
    }).join('\r\n');
    var bom = new Uint8Array([0xEF, 0xBB, 0xBF]);
    var blob = new Blob([bom, csvbuf], {
      type: 'text/csv'
    });
    return blob;
  }

  // Manifestファイルの作成
  function buildManifestFile(csv) {
    // http://docs.aws.amazon.com/quicksight/latest/user/supported-manifest-file-format.html
    var manifest = {
      "fileLocations": [{
        "URIs": [
          'https://' + BUCKET_NAME + '.s3.amazonaws.com/' + csv
        ]
      }],
      "globalUploadSettings": {
        "textqualifier": "\""
      }
    };
    var blob = new Blob(
      [JSON.stringify(manifest)], {
        type: 'application\/json'
      }
    );
    return blob;
  }

  // kintoneの表示中一覧の条件からCSV(Blob形式)を作成
  function buildCSVFromRecords(viewId) {
    return kintone.Promise.all([
      getFields(viewId),
      fetchAllRecords(kintone.app.getId(), kintone.app.getQueryCondition())
    ]).then(function(r) {
      var fields = r[0];
      var records = r[1];
      //EXCEPT_FIELD_TYPES
      var filteredFields = fields.filter(function(field, index) {
        if (EXCEPT_FIELD_TYPES.indexOf(field.type) === -1) return true;
      });
      return buildCSV(filteredFields, records);
    });
  }

  // kintoneの一覧画面に対応するCSVファイルをS3にPUTする
  function putS3Object(viewId) {
    var fileName;
    var manifestFileName;
    return buildCSVFromRecords(viewId).then(function(blob) {
      return kintone.api(kintone.api.url('/k/v1/app', true), 'GET', {
        id: kintone.app.getId()
      }).then(function(r) {
        var appName = r.name;
        fileName = appName + '_' + moment().format() + '.csv';
        var file = new File([blob], fileName);
        var s3 = buildS3Object();
        var putParams = buildS3PutParams(fileName, file);
        return s3.putObject(putParams).promise();
      });
    }).then(function() {
      manifestFileName = fileName.slice(0, -4) + 'manifest.json';
      var blob = buildManifestFile(fileName);
      var file = new File([blob], manifestFileName);
      var s3 = buildS3Object();
      var putParams = buildS3PutParams(manifestFileName, file);
      return s3.putObject(putParams).promise();
    }).then(function() {
      return {
        url: {
          csv: S3_BASIC_URL + BUCKET_NAME + '/' + fileName,
          quickSightManifest: S3_BASIC_URL + BUCKET_NAME + '/' + manifestFileName
        }
      };
    });
  }

  // スピナー表示
  function showSpinner() {
    // 要素作成等初期化処理
    if ($('.kintone-spinner').length == 0) {
      // スピナー設置用要素と背景要素の作成
      var spin_div = $('<div id ="kintone-spin" class="kintone-spinner"></div>');
      var spin_bg_div = $('<div id ="kintone-spin-bg" class="kintone-spinner"></div>');

      // スピナー用要素をbodyにappend
      $(document.body).append(spin_div, spin_bg_div);

      // スピナー動作に伴うスタイル設定
      $(spin_div).css({
        'position': 'fixed',
        'top': '50%',
        'left': '50%',
        'z-index': '510',
        'background-color': '#fff',
        'padding': '26px',
        '-moz-border-radius': '4px',
        '-webkit-border-radius': '4px',
        'border-radius': '4px'
      });
      $(spin_bg_div).css({
        'position': 'absolute',
        'top': '0px',
        'z-index': '500',
        'width': '150%',
        'height': '150%',
        'background-color': '#000',
        'opacity': '0.5',
        'filter': 'alpha(opacity=50)',
        '-ms-filter': "alpha(opacity=50)"
      });

      // スピナーに対するオプション設定
      var opts = {
        'color': '#000'
      };

      // スピナーを作動
      new Spinner(opts).spin(document.getElementById('kintone-spin'));
    }

    // スピナー始動(表示)
    $('.kintone-spinner').show();
  };

  // スピナー停止
  function hideSpinner() {
    // スピナー停止(非表示)
    $('.kintone-spinner').hide();
  };

  // kintone一覧画面表示イベント
  kintone.events.on(['app.record.index.show'], function(event) {
    // AWS認証
    initAWS();
    // S3アップロードのボタン要素追加
    var el = kintone.app.getHeaderMenuSpaceElement();
    var viewId = event.viewId;
    if (!$('#s3-put')[0]) {
      $(el).append(
        $('<button>').prop({
          id: 's3-put'
        }).addClass('kintoneplugin-button-dialog-ok').text('upload to S3')
      );
    }
    // ボタンクリックイベント
    $('#s3-put').click(function() {
      // 表示中一覧のCSVを作成して、S3にアップロード
      showSpinner();
      putS3Object(viewId).then(function(r) {
        hideSpinner();
        return new kintone.Promise(function(resolve, reject) {
          swal({
            title: 'The CSV file is uploaded to S3.',
            text: 'Manifest file for QuickSight: "' + r.url.quickSightManifest + '".',
            type: 'success'
          }, function() {
            return resolve();
          });
        });
      }).catch(function(e) {
        hideSpinner();
        swal({
          title: 'Error occurred while uploading the CSV file to S3.',
          text: '',
          type: 'error'
        }, function() {
          return;
        });
        console.log(e);
      });
    });
    return event;
  });
})(jQuery);

どのようなkintoneアプリでも試せますが、今回はkintone Café Vol.4(GitHub)で利用したkintoneアプリテンプレート(ダウンロード)とデータセット(ダウンロード)を利用しています。

カスタマイズは今回のJSファイルを「quicksight.js」として、次のように設定します。CDNから参照できるものは、AWS SDK for JavaScriptCybozu CDNからそれぞれ執筆時点で最新のものを利用します。AWS SDK for JavaScriptはv2.6.10でした。
%e3%82%b9%e3%82%af%e3%83%aa%e3%83%bc%e3%83%b3%e3%82%b7%e3%83%a7%e3%83%83%e3%83%88-2016-12-24-2-23-49

これで、kintoneの表示中一覧のレコードを、CSV形式でS3にアップロードできるようになります。
s3-5

「upload to S3」のボタンを押してCSVアップロードが終わると、QuickSightで(S3から)データセットを作る際に必要なManifestファイルのURLが表示されますので、これをコピーしておきましょう。
s3-6

Amazon QuickSightの利用設定

今回はQuickSightを初めて利用しますので、QuickSightコンソールで、初期設定(サインアップ)から進めていきます。AWSマネジメントコンソールにAdministratorsでログインした状態で、QuickSightコンソールに遷移し、次のように設定します。
qs-1

サインアップするとこのような画面が現れますので、「Next」をクリックして設定を続けていきます。
qs-2

AWSサービスへのアクセス権設定の画面が現れますので、「Amazon S3」にチェックを入れます。
qs-3

今回はすべてのバケットにQuickSightからアクセスできるようにしておきます。
qs-4

qs-5

qs-7

qs-8

これで、QuickSightのコンソールで実際にデータセットやビジュアライズの設定が行えるようになります。
qs-9

Amazon QuickSightコンソールでデータセット作成

(サインアップ・サインインしたら、) QuickSightのコンソールで、先ほどアップロードされたManifestを使ってデータセットを作成します。

今はひとつも設定がありませんし、「New analysis」からスタートします。
qs-10

そして、今回のkintoneデータからのデータセットを作成するために、「New data set」をクリックします。
qs-11

既にkintoneからのCSVアップロードを終えている「S3」をクリックします。
qs-12

そして、データソース名と先ほどコピーしておいたManifestファイルのURLを入力して「Connect」をクリックします。

ちなみに、Manifestファイルの中身はこのようになっています(こちらの形式に則って生成しています)。

{
  "fileLocations": [{
    "URIs": ["https://analytics-yamashita.s3.amazonaws.com/kintone契約情報管理_2016-11-25T16:47:20+09:00.csv"]
  }],
  "globalUploadSettings": {
    "textqualifier": "\""
  }
}

qs-13

読み込みが終わると、次のような画面が現れます。今回はそのまま「Visualize」をクリックして、進めていきます。
qs-15

現れるのは「Visualize」の設定画面です。今回は「Pivot table」を設定してみたいと思います。
qs-16

ドラッグ&ドロップで、カテゴリや集計したい値を選択・設定していきます。
qs-17

これで、ひとつのVisualが設定されたことになります。

あとは、同じデータセットで「折線」や「パイチャート」等を追加するもよし、このまま「Share」からユーザーを招待して共有するもよし、ダッシュボード化するもよし、という感じです。
qs-18

所感・まとめ

今回はkintoneのデータをS3を経由してQuickSightで可視化する方法を見てきました。今回はkintoneのいちアプリの表示中一覧からデータセットを作ってのビジュアライズでしたので、これであれば元々kintoneに備わっているCSVダウンロード機能でダウンロードしたCSVをQuickSightにアップロードしてデータセットを作った方が早いでしょう。また、今回の延長だと複数アプリからデータセットを作れるようにしたりするためには、事前のkintone-JSカスタマイズに手を入れたりすることが必要になりそうに思います。

現状でkintoneとQuickSightの連携のベストプラクティスは、Lambdaで定期的にkintoneからRedshiftにデータを入れて、QuickSightからRedshiftに接続する方法かもしれません。これであれば、複数テーブルをJOINしたり、SPICEを経由せずに直接QuickSightからRedshiftのテーブルに接続できるといったリアルタイム性の利点も得られそうです。

一方で、kintoneには備わっていない、散布図や(3軸以上が可能な)ピボットテーブル等の表示が可能ですので、それだけで便利なケースもあると思います。

全体には、少し設定に慣れる必要があるかとは思いますが、現状世に存在するBIツールの中では低価格ですし、AWSのサービスもアップデートが早く、kintoneとの相性はより良くなっていくと思いますので、今のうちから慣れておくと良いでしょう。

 


株式会社ジョイゾー