AWS QuickSight + AWS Athena + AWS Glueで業務ログを統計化して可視化してみた

# はじめに
新規自社サービスを開発すると、サービス利用状況の統計出力がしたくなってくる。
また、統計情報はセールスチームや開発チームなどステークホルダに広く展開するべきだろう。
毎月DBやログからデータ取ってきてexcelで図表化は大変なので、自動化＆スマートなUIでさくっと仕組みを作ってしまおう。

# やりたいこと

* S3に保存されている業務ログからアクセス履歴を統計出力する
* Aurora MySQLからトランザクションデータやマスタデータを統計出力する
* 統計データは可視化されWEBブラウザで関係者に展開できる（もちろんユーザ認証は行う）
* データの更新作業は自動化する
* 本来の開発業務の時間を削ることなく、可能な限りAWSマネージドサービスを利用してサクッと作る

# やれること
QuickSightの公式を見るとイメージが湧くだろう。
https://aws.amazon.com/jp/quicksight/

# 環境
![quicksight.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423950/01f5befb-e79d-0db2-373d-863ce992e503.png)


# 構築ガイド

`やりたいこと`にあるとおり、綺麗さなどは微塵も気にせずにサクッと作る。

## S3バケットの準備
Athena用のS3バケットを用意する。
業務ログ用とDB用の2種類用意しておくと使い勝手が良い。
また、後述するAWS Glueジョブ用に使用するjsonファイルを格納するS3バケットも作成しておく。

|バケット名の例|備考|
|:---|:--|
|logs-athena|業務ログ用|
|db-athena|DBデータ用|
|glue-job|AWS Glueジョブ用に使用するjsonファイル格納用|

## 業務ログの抽出
筆者の環境においては、業務ログとALBのアクセスログなど全て同じS3ディレクトリ内に圧縮して出力されていたため、以下の通り統計に必要な業務ログデータのみを抽出した。

1. 全量のログファイルを保存しているS3バケットに対してAWS Athenaでテーブル作成
2. 全量のログファイルから業務ログのみを抽出
3. 業務ログのテーブル化

上記の作業は`AWS Glue`と`AWS Athena`を利用して実現する。

### 全量ログのテーブル化
AWS Glueの管理画面において、クローラを作成し、実行する。
実行後、AWS Athenaの管理画面において、`all_logs`テーブルが作成されており、標準SQLでクエリ検索ができることを確認できるだろう。

|項目|内容|
|:---|:--|
|クローラの名前の例|all_logs|
|crawler source type|Data stores|
|データストア|{全量ログが保存されているS3バケットパス}|
|IAM ロール|(新規作成)|
|スケジュール|オンデマンドで実行|
|データベース名の例|logs|

### 業務データのみを抽出

以下の2つの準備を行う。

1. 業務データ抽出用のSQL文を記述したjsonファイルを作成。
2. AWS Glueで上記SQLを実行し、結果を上述の`S3バケットの準備`で作成した業務ログ用のバケットに保存するジョブを作成。

以下の先達の記事を参照して構築した。
https://dev.classmethod.jp/articles/20180528-aws-glue-etl-job-with-spark-sql/


#### 業務データ抽出用jsonファイルの作成

作成した`all_logs`テーブルに対して、業務ログのみを抽出可能なSQLを実行することで抽出を行う。
SQL文を記述したjsonファイルをS3上にアップロードしておくことでAWS Glueから実行する。

以下のようなjsonファイルを作成し、上述の`S3バケットの準備`で作成した`AWS Glueジョブ用に使用するjsonファイル格納用`S3バケットにアップロードする。
ここで、業務ログメッセージは`message`に格納されており、かつ`message`の先頭に`application-logs`が付与されている前提とする。

```json:export_application_logs.json
{
    "source_database":"logs",
    "source_table":"all_logs",
    "target_s3_url":"s3://logs-athena/application_logs/",
    "target_format":"parquet",
    "sql":"SELECT * FROM stagingtable WHERE message LIKE 'application-logs%'"
}
```

#### 業務ログ抽出用ジョブの作成

AWS Glueの管理画面において、上記のjsonファイルを読み込み、SQL実行して結果を保存するジョブを作成する。
また、スクリプト編集画面にて、先達の記事を参考にスクリプトを作成する。
ここで、全量ログファイルはjson形式で、かつ`year`、`month`、`day`、`hour`でHive形式で保存されている前提とする。

|項目|内容|
|:---|:--|
|ジョブ名の例|export_application_logs|
|IAMロール|(新規作成)||
|Type|Spark|
|Glue version|Spark 2.4, Python 3|
|このジョブの実行|ユーザーが作成する新しいスクリプト|
|ジョブのブックマーク|有効化|
|ジョブパラメータ1(キー)|--extra_job_parameters_bucket|
|ジョブパラメータ1(値)|glue-job|
|ジョブパラメータ2(キー)|--extra_job_parameters_key|
|ジョブパラメータ2(値)|export_application_logs.json|


スクリプトの詳細は先達の記事を参照してほしい。
https://dev.classmethod.jp/articles/20180528-aws-glue-etl-job-with-spark-sql/


#### 業務ログテーブルの作成
AWS Glueの管理画面において、クローラを作成し、実行する。
AWS Athenaに`application_logs`テーブルが作成される。

|項目|内容|
|:---|:--|
|クローラの名前の例|application_logs|
|crawler source type|Data stores|
|データストア|s3://logs-athena/application_logs/|
|IAM ロール|(先ほど作成したもの|
|スケジュール|オンデマンドで実行|
|データベース名の例|logs|

## DBデータの抽出
業務ログのみでもほとんどのケースで統計情報は作成可能であるが、業務要件上、バッチ処理等でいくつかのデータはDBに既に統計処理されて保存されているケースが多いだろう。
また、業務ログデータとマスタデータを紐付けておくことで、可視化において何かと便利になる。（IDではなく和名で出力するなど）
業務で利用しているDBを統計に使用する場合、リソース負荷も気になることから、日次等でAthenaにデータ投入しておくことをお勧めする。

### DBデータのエクスポート
トランザクションデータやマスタデータなどをcsv出力して、上述の`S3バケットの準備`で作成したDBデータ用のS3バケットに保存する
例えば、簡易にメンテナンス用の踏み台EC2サーバからcronでcsvエクスポートしてS3にアップロードする場合は、以下のようなシェルをcronに登録しておく。

```bash:s3_db_sync.sh
#!/bin/sh

dir_name=`date "+%Y%m%d_%H%M%S"`
dir=~/s3_sync/
dir_path=${dir}${dir_name}
mkdir ${dir_path}
mkdir ${dir_path}/transactions
mkdir ${dir_path}/master

mysql -hdb.local db -uroot -e "SELECT * FROM transactions" > ${dir_path}/transactions/transactions.csv
mysql -hdb.local db -uroot -e "SELECT * FROM masters" > ${dir_path}/masters/masters.csv

aws s3 sync ${dir_path} s3://db-athena/ --acl bucket-owner-full-control
```

### DBデータテーブルの作成
AWS Glueの管理画面において、クローラを作成し、実行する。
AWS Athenaに`transactions`テーブルと`masters`テーブルが作成される。

|項目|内容|
|:---|:--|
|クローラの名前の例|db|
|crawler source type|Data stores|
|データストア1|s3://logs-athena/transactions/|
|データストア2|s3://logs-athena/masters/|
|IAM ロール|(先ほど作成したもの）|
|スケジュール|オンデマンドで実行|
|データベース名の例|logs|

## ワークフローの作成
AWS Glueでは、ワークフローを作成し、スケジュール実行することができる。
上記で作成したジョブとクローラをワークフロー化し、日次実行させておく。
DBデータのエクスポートはワークフローに組み込むことはできないので、ワークフロー開始までの時間に十分なバッファを設けておこう。
<img width="486" alt="quicksight_workflow.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423950/c90e5aa7-6c3a-bfe5-bdcb-51a9730c73ed.png">


## QuickSightによる可視化
ここまでで、統計出力させるための下準備が完了している。
AWS QuickSightを利用して可視化してみよう。
以下では、業務ログデータにマスタデータを結合し、折れ線グラフでアクセス数の遷移を表示させるケースを例に記載する。

## データセットの作成
* QuickSightにログインし、`データの管理`から`新しいデータセット`をクリックする。
* `Athena`をクリックし、以下を入力/選択し、データの編集/プレビューをクリックする。   

|項目|内容|
|:---|:--|
|データソース名の例|data_set|
|Athena workgroup|[primary]|
|データベース|logs|
|テーブル|application_logs|

可能であればSPICE利用にし、Athenaへのクエリ課金を抑えることをお勧めする。
SPICEを利用する場合は、定期的にデータ更新する必要があるので、忘れずに更新スケジュール設定も行っておこう。

* データの編集画面に遷移したら、画面上部のデータ追加をクリックし、マスタデータを追加する。

|項目|内容|
|:---|:--|
|スキーマ|logs|
|Athena workgroup|[primary]|
|テーブル|masters|

* 結合判定部分をクリックし、結合タイプに`Left`を選択する。結合区はID等のログデータとマスタデータを紐付ける項目を選択する。

* 画面上部の保存をクリックする。

## 分析の作成
* `新しい分析`をクリックし、ユーザのデータセットから先ほど作成したデータセットを選択する。
* ビジュアルタイプに`折れ線グラフ`を選択する。

|項目|内容|
|:---|:--|
|X軸|Timestamp等の日時データ|
|値|ログID等を集計：カウント|
|色|アクセス元種別等の和名|

ここで、業務ログ上はアクセス元種別等のグループ情報はID等の2Byte文字を含まないデータとなっていることがほとんどであるため、DBのマスタデータで対応する和名等を保持していれば、和名をセットすることが望ましい場合が多い。
セールスチームなどで、ビジネス統計をマーケティング活用するために利用する場合において、直感的に判別可能な情報で出力することで利便性が向上する。

## ダッシュボードの公開
* 分析画面の右上にある`共有` > `ダッシュボードを公開`をクリックする。
* QuickSightのトップページに戻り、右上の`QuickSightの管理`> `ユーザを管理` > `ユーザーを招待` から公開したいメンバのメールアドレスを入力する。


# 終わりに
統計に必要なデータが全てAWS上に保存されている場合、上記の簡単な手順でビジネス情報を統計化し、ダッシュボードを公開することができる。
更に詳細なビジネス分析を行う場合は、物足りない部分が多いと思われるが、顧客要望等でなるべく手間を掛けずに統計出力したい、自動化したい場合は、ぜひ利用することをお勧めする。