社内ツールを駆使してExcelへのレポートを自動化した話

こんにちは! Pairs事業部Analyzeチームの鉄本です。
 
この記事はEureka Advent Calendar 2016 5日目の記事です。
4日目は竹内くんの「FalbaTech製ErgoDoxを使ってみた」でした。


私のチームでは、データの分析はもちろん、分析関連の要望や課題の解決にも日々取り組んでいます。
 
今回は最近取り組んだ要望へのアプローチを1つ、紹介します。同じ環境の方は少ないかもしれませんが、置き換えできる部分もありますので、システム設計やアプローチの仕方が参考になれば幸いです!

集計結果をExcelへ自動更新したい

Pairs事業部では普段、re:dashというBIツールにデータベースを接続して集計結果の出力をしています。re:dashでは定期更新された結果をCSVでダウンロードすることができ、サービスの分析に活用しています。
 
※re:dashの利用に関しては「Pairsでの活用例から学ぶre:dash導入のすゝめ」で紹介しているので是非ご一読ください!


とある日、ディレクターからこんな相談をもらいました。
 
「毎日ファイルをダウンロードしてExcelに取り込ませる作業が辛いので、自動化したい」
 
既存のExcelの更新手順は以下の図のように、手動でre:dashからCSVファイルを落としてExcelに反映するものでした。

flow_before

さて、みなさんでしたらこの要望、どのように実現しますか?

実行環境

今回の課題解決で利用した主なツール/環境は以下になります。

  • re:dash:BIツール
    分析用のDataBaseを接続しており、クエリ結果を表示できます。APIで結果のCSVダウンロードも可能です。
  • サーバ:Ubuntu 14.04.4 LTS Linux
    re:dashを稼働させています。
  • Box:クラウドストレージ
    個人用は10GBまでは無料で使うことができます。(250MBのファイルアップロード制限あり)
  • Box Sync:ファイルミラーリング
    Box上のファイルをローカルPCへ自動で同期できます。

解決手順

早速ですが、以下の図のような全体像になりました。

flow_after

具体的な手順を説明します。

手順

下準備1. re:dashクエリの作成

re:dashで集計したいクエリを用意し、定期実行の設定をします。
試しにDemoページサンプルクエリをExcelに自動同期してみます。「Show Source」の押下でメニューを表示し、「Show API Key」から対象のクエリのAPI Keyを取得します。

redash_demo

下準備2. Boxのセットアップ

Boxのサイトにアクセスし、専用のでディレクトリを作成します。
同期したいディレクトリの同期設定をONにします。

box_setting

Box Syncを自分のPCにダウンロードしてログインします。
PCにディレクトリの同期が確認できれば成功です!

手順1. Boxの領域確保

稼働中のre:dashを載せているサーバに、Boxの領域をマウントします。
マウントディレクトリのユーザー権限は必要に応じて変更してください。

sudo apt-get install davfs2
mkdir ~/Box
sudo mount -t davfs https://dav.box.com/dav /home/user/Box/
# Boxのアカウント情報を入力します

手順2. re:dashのAPIでCSVを定期ダウンロードする

CSVのダウンロードはスクリプト化し、cronで定期実行します。

#!/bin/sh

BOX_ROOT=/home/user/Box

REDASH_ID=762               # 対象のredashクエリのID
REDASH_API_KEY="********"   # 下準備1で取得したAPI Key
REDASH_DIR="$BOX_ROOT/$REDASH_ID"

mkdir -p "$REDASH_DIR"
wget -O "$REDASH_DIR/demo.csv" "http://demo.redash.io/api/queries/$REDASH_ID/results.csv?api_key=$REDASH_API_KEY"

以下をcrontabに追加すると、毎日午前5時にファイルの更新が行われるようになります。

0 5 * * * sh download_demo.sh

手順3. Box Syncへの同期確認

まずは手動で、ファイルのダウンロードのコマンドを実行してみます。

sh download_demo.sh

その後自分のPCのBox Syncのディレクトリを確認し、先ほどダウンロードしたCSVファイルの同期が確認できれば成功です!

手順4. ExcelでCSVの取り込み

Excelには「外部データの取り込み」という機能があるのはご存知でしょうか?
 
csvデータを読み込む場合は「データ> 外部データの取り込み > テキスト ファイル」に進み、対象のCSVを選択します。さらに、「接続プロパティ」からExcelの自動更新のトリガーを設定することが可能です。詳しくは、以下の公式サイトをご確認ください。

実施にあたって気をつけたこと

以上の手順は、わかりやすくなるようにかなり簡略化をして紹介したものです。
実際には運用にのせるにあたって、以下も考慮しました。

  • サーバーの転送量を考慮する
  • Boxの権限を適切に設定する

サーバへのBox領域のマウントは、使用したアカウントが保有するファイル全てを含みます。
専用のアカウントを用意するなどして、必要以上のファイルを保持しないように気をつけないと、思わぬところで容量の圧迫ファイルの転送量の増加を招いてしまう可能性があります。
気をつけましょう!

最後に

私の所属しているAnalyzeチームでは、幅広い経験や知識を活かしつつ、ツールを最大限に活用して課題解決に取り組んでいます。特に今回は、コードを書くだけがエンジニアリングではないと感じる案件であり、エンジニアとしての幅を楽しむことができました。

みなさんもぜひ、ツールを駆使して快適な分析運用ライフをお楽しみください!

6日目は小島くんの「【基礎編】Elasticsearchの検索クエリを使いこなそう」です!

  • このエントリーをはてなブックマークに追加

エウレカでは、一緒に働いていただける方を絶賛募集中です。募集中の職種はこちらからご確認ください!皆様のエントリーをお待ちしております!

Recommend

大規模サービスあるある、”属人化”解消のための開発体制パターン

RaspberryPi×Go言語で電子工作