RaspberryPi + Athena + QuickSightで可視化する＜後編＞

#**概要**
本コンテンツは、[**JAWS DAYS 2017**](http://jawsdays2017.jaws-ug.jp)の[**IoTハンズオン**](https://jaws-days.doorkeeper.jp/events/57107)向けに作成したもので、[前編](http://qiita.com/Ichiro_Tsuji/items/d366203c3621e6c15def)の続きとなるコンテンツです。

前編に引き続き、構築するデータフローは以下の通りです。
**温度センサー -> RaspberryPi -> Kinesis Firehose -> S3 -> Athena -> QuickSight**
![スクリーンショット 2017-02-06 21.28.43.png](https://qiita-image-store.s3.amazonaws.com/0/80392/08be9470-5ce9-537c-400f-de0af1152e36.png)


##＜後編＞の範囲
＜後編＞では、**S3 -> Athena -> QuickSight** の部分を説明します。成功すればグラフィカルな画面でセンサーデータを可視化する事ができます。

#**必要な機材**
* PC
* 前編で構築済みのRaspberryPi 2または3

#**前提条件**
* 前編の手順が完了している事

#**＜後編＞で利用するAWSサービス**
##[Amazon Athena](https://aws.amazon.com/jp/athena/)
Amazon S3内のデータをSQLを使って分析できるサービスです。サーバーレスなサービスなため、セットアップや管理は不要、すぐに即座にデータ分析を開始できます。データをAthenaにロードする必要は無く、S3に保存されているデータと直接連携できます。
CSV、JSON、Apacheログなど様々な形式を扱う事ができます。QuickSightと統合する事で簡単に可視化する事ができます。

* GZIP等の圧縮ファイルも読み込める
* JDBC経由でのアクセスも可能
* 検索した結果がS3に保存される
* [実行したクエリに対して課金](https://aws.amazon.com/jp/athena/pricing/)
* 東京リージョンでは使えない(2017年2月時点)

##[Amazon QuickSight](https://quicksight.aws/)
AWSが提供する高速なBIサービスです。データソースとして、ファイル、S3、RDS、Redshift、Salesforceなどが使え、昨年12月にはAthenaに対応しました。
インメモリ処理に最適化されたデータベースである「SPICE」(**S**uper-fast **P**arallel **I**n-memory **C**alculation **E**ngine)にデータをインポートする事で、高速な分析ができるようになっています。
作成したグラフはモバイルからの閲覧にも対応しています。

* 東京リージョンでは使えない(2017年2月時点)
* [利用するアカウント数やSPICEの容量で課金](https://quicksight.aws/pricing/)
* 60日間の試用期間、無料枠もある(1ユーザー、SPICEは1GBまで)
![スクリーンショット 2017-02-14 9.48.14.png](https://qiita-image-store.s3.amazonaws.com/0/30557/00083757-edf4-8429-5907-61ebe40cf1d7.png)

#**[ToDo 8] Amazon Athenaを設定する**
Amazon Athenaにテーブルを作成し、S3を参照できるようにします。

* Athenaコンソールを開く
<img width="842" alt="スクリーンショット_2017-02-11_9_12_25.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/e1306dfd-daf1-10ae-7894-44cf0389ea62.png">
<BR><BR>
* Amazon Athenaは2017年2月現在、東京リージョンでは提供されていないので、利用可能な北バージニア(N.Virginia)リージョンを選択する<BR>
![9bfa9b74-a4c3-9188-2046-af6fbd0dc3b7.png](https://qiita-image-store.s3.amazonaws.com/0/30557/47fbdb12-0a3f-4008-2454-cb98141271a6.png)
<BR><BR>
* [Get Started]をクリックする<BR>
<img width="1007" alt="スクリーンショット_2017-02-11_9_19_45.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/c44ad966-6b4f-a024-9e2d-5ead3e68a7ba.png">
<BR><BR>
* Tutorialが表示されたら×で閉じる<BR>
<img width="740" alt="スクリーンショット_2017-02-11_9_22_40.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/7e6f71aa-b85b-3d80-8bf9-a092de11ab28.png">
<BR><BR>

##サンプルデータベースを使ったクエリの試行
Athenaにはサンプルデータ(Elastic Load Balancingのログ)が用意されているので、こちらを使ってクエリの実行を試してみます。

* Query Editorの画面でDATABASEに「sampledb」が選択された状態で、以下のクエリを入力し、「Run Query」をクリックする<BR>

```mysql:sampledbのレコード取得
SELECT * FROM elb_logs limit 100;
```

* 数秒待つと画面下部に実行結果が表示される
<img width="1266" alt="スクリーンショット_2017-02-19_12_01_13.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/8c66cba0-0ed3-0d8f-d39d-ea5ffbdad8d3.png"><BR><BR>
* 実行した結果はCSV形式でS3に保存される
<img width="1170" alt="スクリーンショット_2017-02-19_12_10_54.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/825e93e9-a138-ea08-88ea-923863170a3b.png"><BR><BR>
* その他の実行例

```mysql:HTTPのステータスコード別のカウント
SELECT backend_response_code, count(*) FROM elb_logs GROUP BY backend_response_code ORDER BY backend_response_code;
```

```mysql:アクセスURL別のカウント
SELECT count(*) AS C, url FROM elb_logs GROUP BY url ORDER BY c desc;
```

##データベースとテーブルの作成
* Query Editorの画面で「Add table...」をクリックする
<img width="895" alt="スクリーンショット_2017-02-11_9_44_26.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/714aebbb-3cb8-9887-c887-85c56de6ee9a.png">

###Name & Location
データベースとテーブルの名前、S3のパスを指定します。

* Databaseに「Create new database」を選択された状態で、データベース名を入力する（ここでは「jawsdays2017」としている）<BR><BR>
* Table Nameに任意のテーブル名を入力する（ここでは「sensor」としている）<BR><BR>
* Location of Input Data Setには、前編で作成したセンサーデータが格納されるS3のパスを入力する。今回の場合は、```s3://バケット名/YYYY/MM/DD/HH/ファイル名```となるため、パスとして固定な```s3://バケット名/```を設定する。（バケット名は、スクリーンショットでは「jaws-days-2017-iot-handson」となっているが、前編では「iotan-f」として作成しているので注意）<BR><BR>
* 全て入力したら「Next」をクリックする。
<img width="899" alt="スクリーンショット_2017-02-11_10_04_41.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/d3b78c96-4b7e-c9ef-0f1e-9689923fb5c0.png">

###Data Format
S3に格納されているデータのフォーマットを設定します。

* 「JSON」を選択して「Next」をクリックする
<img width="602" alt="スクリーンショット_2017-02-11_10_07_44.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/a170a71b-d75a-7186-d725-a0605a09cdce.png">

###Columns
S3のデータに合わせて、Athenaのテーブルにカラムを追加します。

* センサーから送信されるJSONデータはtimeとtemperatureのため、それぞれのカラムを追加する
* 「Add a column」をクリックすると、入力エリアを増やす事ができる
* 2カラム分の情報を入力したら、「Next」をクリックする
<img width="910" alt="スクリーンショット_2017-02-11_10_43_38.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/45ff54c1-780a-f53d-a2d7-5e15bc69ad5e.png">

###Partitions
* 今回は省略して、「Create table」をクリックする
* Query Editorに戻り、「Query successful.」と表示されたら完了
<img width="1252" alt="スクリーンショット_2017-02-11_10_28_27.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/b97ebe2c-89d8-9e78-c6c6-5289bb9af7b0.png">

#**[ToDo 9] Amazon Athenaでクエリを実行する**
[ToDo 8]と同様の手順で、SQLを発行してS3上のログファイルが参照できるか確認してみます。

* Query Editorに以下のクエリを入力し、「Run Query」をクリックする<BR>(左側の眼のアイコンをクリックしても同じクエリを実行できる)

```mysql:先頭10件を取得
SELECT * FROM sensor limit 10;
```
<img width="1251" alt="スクリーンショット_2017-02-27_23_55_03.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/56b2a51d-cdda-bd61-0dab-81d081dbd1f5.png">

**画面下部に結果が表示されるので、ご自分の環境で確認してみてください。**
検索結果がCSVファイルとしてS3に保存される事も確認してみましょう。

#**[ToDo 10] QuickSightのアカウントを作成する**
QuickSightを使う場合、最初にアカウントの登録が必要となります。

* QuickSightコンソールを開く
<img width="845" alt="スクリーンショット_2017-02-11_22_08_29.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/07b0840a-630f-f489-72b4-e9ad11592230.png">
<BR><BR>
* QuickSightを利用開始する前の場合、以下のような画面が表示されるため、「Sign up」をクリックする
<img width="1273" alt="2_q.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/0fd947eb-09b1-69d4-b643-fbb007f5e51b.png">
<BR><BR>
* [Standatd edition]が選択された状態で、「Continue」をクリックする
![3_q.png](https://qiita-image-store.s3.amazonaws.com/0/30557/e1175129-4180-5f82-2d55-d01bf8a11379.png)
<BR><BR>
* アカウントの作成に必要な情報を入力し、「Finish」をクリックする
![4_q.png](https://qiita-image-store.s3.amazonaws.com/0/30557/73aac7e3-ca71-6eaf-8376-6433d515aed1.png)
<BR><BR>

* 「Create your QuickSight account」が表示されたらアカウントの作成が完了したので、「Go to Amazon QuickSight」をクリックする
![5_q.png](https://qiita-image-store.s3.amazonaws.com/0/30557/584c0154-313e-453a-31a6-d6b848808e28.png)
<BR><BR>

#**[ToDo 11] Amazon QuickSightのデータセットを作成する**
データソースから必要な部分を取り出して、データセットを作成します。データセット作成時に前述のSPICEに取り込むか選択する事ができます。
データセットのデータを整形できる「Prepare」により、不要な列の削除・列名の変更、計算フィールドの設定、ジョイン、カスタムSQLなどが可能です。

## QuickSightに対してS3バケットへの権限を追加する
* これを実施しないと、データセットの作成時に以下のエラーが発生する
<img width="595" alt="スクリーンショット 2017-02-11 22.52.42.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/d7c2b905-1533-246d-8087-b83383e1537f.png">
<BR><BR>
* 右上のアイコンから[Manage QuickSight]をクリックする<BR>
<img width="328" alt="スクリーンショット_2017-02-11_22_56_59.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/e7402566-0313-24e4-63da-e35c5ab7dad8.png">
<BR><BR>
* 「Account setting」を開き、「Edit AWS permissions」をクリックする
<img width="1270" alt="スクリーンショット_2017-02-11_22_59_20.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/3231b1da-152e-777d-0b2b-695c09836fc4.png">
<BR><BR>
* 「Choose S3 buckets」を選択し、該当のバケットにチェックを入れてから「Select buckets」をクリックする（その後の画面で「Apply」をクリックする）
<img width="660" alt="スクリーンショット_2017-02-11_23_00_39.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/123cb49a-5be2-44cf-b44a-6b308496b0cf.png">
<img width="594" alt="スクリーンショット_2017-02-11_23_04_30.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/1f50d9df-f091-8997-9673-aefb39aeab97.png">

## データセットを作成する
* 以下の画面では作成済みのAnalysis（分析画面）を選択できる。今回は 「Manage data」をクリックする
<img width="1274" alt="スクリーンショット_2017-02-11_22_34_53.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/68208461-af4b-65d5-94ba-d9d1d62523b4.png">
<BR><BR>
* 以下の画面では作成済みのデータセットを選択できる。 「New data set」をクリックする
<img width="1269" alt="スクリーンショット_2017-02-11_22_40_31.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/19eaea26-2062-34fe-2d58-9a4d34010949.png">
<BR><BR>
* データセットを作成するためのデータソースを選択する。ここでは「Athena」を選択する
<img width="1271" alt="スクリーンショット_2017-02-11_22_43_46.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/f765c06d-84cd-aedb-01d5-828cfe735ae1.png">
<BR><BR>
* Data source nameを入力し、「Create data source」をクリックする
<img width="599" alt="スクリーンショット_2017-02-11_22_48_03.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/f3b04204-1e41-8e4a-aadb-6a8cc4bfbb13.png">
<BR><BR>
* Databaseのプルダウンから、事前に作ったAthenaデータベースを選択し、「Select」をクリックする
<img width="596" alt="スクリーンショット 2017-02-11 22.51.21.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/638a3be8-27d5-96af-d92b-2ecdc1421e7f.png">
<BR><BR>
* 最後にAthenaのデータをSPICEにインポートするか、直接クエリを実行するか選択する。ここでは「Import to SPICE for quicker analytics」を選択し、「Visualize」をクリックする
<img width="593" alt="スクリーンショット_2017-02-11_23_27_50.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/9130b5f6-a03d-7d58-37ed-6de3b64d426f.png">
**注意**<BR>
    * [AWSのドキュメント](http://docs.aws.amazon.com/ja_jp/quicksight/latest/user/importing-data-to-spice.html)によると「AthenaはSPICEへのインポートに未対応」となっていますが、実際の挙動はインポート可能となっています。
    * AWSのドキュメントより抜粋
        * You can improve the performance of database data sets by importing the data into SPICE instead of using a direct query to the database (with the exception of Amazon Athena data sets, which currently only support direct query). 
        * データベースへの直接クエリを使用するのではなく、データをSPICEにインポートすることで、データベースデータセットのパフォーマンスを向上させることができます（現在、直接クエリのみをサポートするAmazon Athenaデータセットを除く）。
<BR><BR>
* Analysisの画面でImport completeが「100% success」になったらSPICEへのデータインポートが完了した事になる<BR>
<img width="371" alt="スクリーンショット_2017-02-11_23_30_22.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/dc7c51f3-4d18-e194-7b15-5df44a48d686.png">

#**[ToDo 12] Amazon QuickSightのAnalysisを作成する**
* ここでは時間ごとの温度平均値を折れ線グラフで表示します

## フィールドを配置する
* Visual typesの「Line chart」を選択する<BR>
<img width="251" alt="スクリーンショット_2017-02-12_21_27_40.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/5c233691-9477-9a08-3e81-478f730f4adb.png">
<BR><BR>
* X軸に表示するデータを指定するため、Fields listの「time」をクリックする（「time」をField wellsの「X axis」にドラッグ＆ドロップしても同じ事ができる）<BR>
<img width="552" alt="スクリーンショット_2017-02-12_21_46_47.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/f808c1df-c3b8-971d-441a-371bc9e3f605.png">
<BR><BR>
![スクリーンショット_2017-02-12_21_46_18.png](https://qiita-image-store.s3.amazonaws.com/0/30557/5b9bc4f6-2eb4-f673-fb6e-32ce2f5edd00.png)
※ドラッグ&ドロップした場合
<BR><BR>
* Y軸に表示するデータを指定するため、Fields listの「temperature」をクリックする（「temperature」をField wellsの「Value」にドラッグ＆ドロップしても同じ事ができる）<BR>
<img width="826" alt="スクリーンショット_2017-02-12_21_51_15.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/9e5bdd7a-9bcd-993b-5293-9090f762089a.png">
<BR><BR>

## 集計する条件を変更する
* この時点では年単位の温度の合計値となっているため、時間単位の平均値になるように設定を変える
* 「X axis」の「V」をクリックし、「Aggregate」を「Hour」に変更する
<img width="694" alt="スクリーンショット_2017-02-12_22_00_36.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/95cafe87-1247-af32-e5dc-2fc384c38f5b.png">
<BR><BR>
* 「Value」の「V」をクリックし、「Aggregate」を「AVERAGE」に変更する
<img width="871" alt="スクリーンショット_2017-02-12_22_02_51.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/54313292-3f91-acf6-f718-188bd2d40328.png">
<BR><BR>
* 時間単位の平均温度が表示されるようになる
<img width="920" alt="スクリーンショット 2017-02-12 22.05.55.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/07805ed9-2b73-4c8d-0c7a-89ed88b90db2.png">

#**[ToDo 13] SPICEのデータを更新する**
~~2017年2月現在、SPICEのデータを自動でリフレッシュする機能は搭載されていないため、手動でリフレッシュする必要があります。~~
2017年2月22日にスケジュールリフレッシュの機能が搭載されました。今回は手動でリフレッシュしますが、設定方法は[こちら](http://qiita.com/ha_ru_ma_ki/items/60fc8da64f9ef6df3dfa)を参照。<BR>

* 左上のアイコンをクリックする<BR>
<img width="328" alt="スクリーンショット_2017-02-12_22_39_07.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/1dedfe11-2aa7-44ff-15f2-4e3797183b33.png">
<BR><BR>
* 「Manage data」をクリックする<BR>
* リフレッシュするデータセットをクリックする。<BR>
* 「Refresh Now」をクリックする。<BR>
<img width="597" alt="スクリーンショット_2017-02-13_9_50_25.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/ff9df083-adde-6c9f-d238-4627c5715ed2.png">
* 「Refresh」をクリックする。<BR>
<img width="597" alt="スクリーンショット_2017-02-13_9_51_30.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/af2bf4c4-f036-60ac-3948-0dfde20a402d.png">
* 「We have submitted your refresh request.」と表示されたら、「OK」をクリックする。
* 「Last Refreshed」が「a few seconds ago」になったらインポート完了。インポートされたレコード数も更新される。<BR>
<img width="597" alt="スクリーンショット_2017-02-22_23_04_22.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/f65906df-da01-3815-bd0c-0dcfffda74a8.png">

## 補足
* 最後の画面で「Edit data set」をクリックすると、SPICEの中を参照したり、設定を変える事ができる。(Preparing data)
<img width="1272" alt="スクリーンショット 2017-02-13 9.59.33.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/eb7b0486-c403-6d7c-61fd-412bdce9e978.png">

# **[ToDo 14] 計算フィールドを使う**
計算フィールド（Calculated field)を使う事で、データソースのデータに対して演算を行う事ができます。もちろん演算後のデータを使って可視化ができます。
ここでは取得した温度データを高い・中間・低いに分類して、パイチャート(円グラフ)で可視化します。

## 計算フィールドの追加
* 前述の「補足」の手順でPreparing dataの画面を表示する
* 「New field」をクリックする
<img width="775" alt="スクリーンショット_2017-02-22_23_42_21.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/0a33a84a-41e1-4d09-24b8-6731f215e496.png"><BR><BR>
* 「New calculated field」画面が表示されたら「Function list」の「ifelse」をクリックする（選択したら水色に変わる）
<img width="821" alt="スクリーンショット_2017-02-22_23_29_22.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/434d2671-5f87-f5a3-3a0b-5acfd8aa32a1.png"><BR><BR>
* 「Calculated field name」に任意の名称を入力、「Formula」に以下の内容を入力したら、「Create」をクリックする(完了までに時間がかかる場合がある）<BR>
```ifelse(temperature < 20,'low',temperature > 29,'high','middle')```
<img width="824" alt="スクリーンショット_2017-02-22_23_35_36.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/f19f5459-bca8-f807-dc55-79229a199289.png">
**注意**<BR>
    * ifelseでは、温度を判定し、20度未満ならlow、29度より大きければhigh、それ以外はmiddleに分類しています。
    * ifelse内の数値は温度を表します。会場の温度に合わせて調整が必要となる可能性があります。
<BR><BR>
* 完了すると「Calculated fields」にフィールドが追加され、分類されたデータが格納される
<img width="1188" alt="スクリーンショット_2017-02-22_23_46_59.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/10894bf7-e778-0b82-3cb8-5ff88901766f.png"><BR><BR>
* 「Save」をクリックする
<img width="931" alt="スクリーンショット_2017-03-04_22_21_07.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/f68509e6-62b7-f028-804a-059bbfb78470.png">

## パイチャート(円グラフ)の設定
作成済みのAnalysisにパイチャート(円グラフ)を追加します。

* 左上のアイコンをクリックする<BR>
<img width="165" alt="スクリーンショット_2017-02-22_23_55_03.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/a286f749-030e-f8da-605d-b353c8b09d25.png"><BR><BR>
* 作成済みのAnalysisをクリックする
<img width="1260" alt="スクリーンショット_2017-02-22_23_56_45.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/0e836401-1d3f-114b-a4a4-b6ac65b31dcb.png"><BR><BR>
* 左上の「＋Add」をクリックし、「Add visual」をクリックする<BR>
<img width="248" alt="スクリーンショット_2017-02-22_23_59_21.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/d3a81ae6-c83c-39bb-8972-986a963c6e34.png"><BR><BR>
* 未設定のグラフが追加されるので、「Visual types」の「Pie chart」をクリックする
<img width="1188" alt="スクリーンショット_2017-02-23_0_03_18.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/78688992-b1aa-c8e7-384d-8e843dae0e92.png"><BR><BR>
* 追加した計算フィールドをクリックすると、「Group/Color」に設定され、グラフが表示される
<img width="1258" alt="スクリーンショット_2017-02-23_0_05_24.png" src="https://qiita-image-store.s3.amazonaws.com/0/30557/c37413aa-4c60-f129-7662-82291ed805db.png">