AWS Athena + QuickSightでS3に蓄積されたCSVデータを可視化

# やりたい事
サービスの統計情報を取得するために、データベースの登録状況をバッチ処理でCSVに落として保存している運用をされている方は結構いらっしゃるのではと思います。そのS3データを簡単に可視化する方法として、AWS AthenaとQuickSightを利用してみました。

# AWS Athena
Amazon S3 内のデータを標準 SQL を使用して簡単に分析できるようになります。Athenaを利用の流れは以下のような形になります。

1.S3にバケットを作成して、分析したいCSVファイルを置く
2.AWS Athenaのコンソールでデータベースの作成
3.AWS Athenaのコンソールでテーブルを作成する
4.作成したデータベースに対して、SQLのクエリを発行して必要な情報のみ取得する。

SQLのクエリはPresto 0.172 に基づいていますので、通常のSQL文とは差分がありますので、注意が必要です。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions-operators-reference-section.html

# S3データの準備
今回は、単純化するために、以下の2種類のCSVファイルを用意しました。

users_YYYYMMDD.csv

```csv
time,user_id,plan_id
2019-09-01,user_001,planA
2019-09-01,user_002,planA
2019-09-01,user_003,planB
2019-09-01,user_004,planB
2019-09-01,user_005,planC
```

devices_YYYYMMDD.csv

```csv
date,device_id,user_id,
2019-09-01,deviceA,user_001
2019-09-01,deviceB,user_001
2019-09-01,deviceA,user_003
2019-09-01,deviceB,user_004
2019-09-01,deviceA,user_005
2019-09-01,deviceB,user_005
```

S3に以下のように配置します。

![スクリーンショット 2019-09-25 13.29.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/e186025a-0693-0f12-829f-77dc6c9ee2ee.png)　![スクリーンショット 2019-09-25 13.29.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/46d8ec3e-663e-51ab-4363-a7a09f2fa3c5.png)

# データベースの作成
AWS Athenaの利用は最初はデータベースを作成する事から始めます。ただし、この時点ではS3との関係などは意識する必要はなく、これからS3のデータと関連づけるためのデータベースを作成します。データベースの作成も通常通り、SQL文で実施します。

```sql
CREATE DATABASE sandboxusers;
```

# テーブルの作成
データベースの作成は、S3のバケット毎に行います。時系列のCSVファイルなど、複数のCSVファイルを1つのバケットに置く場合、同一形式のCSVファイルである必要があります。

```SQL
CREATE EXTERNAL TABLE IF NOT EXISTS users_table (
  `date` date,
  user_id string,
  plan_id string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES (  'escapeChar'='\\',  'quoteChar'='\"',  'integerization.format' = ',',  'field.delim' = ',' ) 
LOCATION 's3://sandbox-1234/athena/users' 
TBLPROPERTIES ('has_encrypted_data'='false',  'skip.header.line.count'='1')
```

```SQL
CREATE EXTERNAL TABLE IF NOT EXISTS devices_table (
  `date` date,
  device_id string,
  user_id string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES (  'escapeChar'='\\',  'quoteChar'='\"',  'integerization.format' = ',',  'field.delim' = ',' ) 
LOCATION 's3://sandbox-1234/athena/devices' 
TBLPROPERTIES ('has_encrypted_data'='false',  'skip.header.line.count'='1')
```

WITH SERDEPROPERTIESで区分する文字を指定します。またLOCATIONでs3のバケットの場所を指定します。最後の'skip.header.line.count'='1'はCSVの1行目を飛ばす設定となっています。上記のクエリを実施することで、AWS Athenaにテーブルが作成されます。

![スクリーンショット 2019-09-25 13.35.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/7f08a994-87c3-5939-2557-bc2166cdb950.png)

# クエリを行う
テーブルを作成した後は、SQLを用いてクエリする事ができます。特に時刻の扱いは通常のSQLと差分がある場合があり、注意が必要です。

```SQL
SELECT * from users_table where (date between date '2019-09-01' and date '2019-09-02') and plan_id = 'planA';
```
![スクリーンショット 2019-09-25 14.14.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/62add91a-44ca-1ade-cfe0-24c353c2da0f.png)

JOINも動作します。

```SQL
SELECT *
from users_table 
inner join devices_table 
on users_table.user_id = devices_table.user_id 
where (users_table.date = CAST('2019-09-01' as date)) and (devices_table.date = CAST('2019-09-01' as date))
```

![スクリーンショット 2019-09-25 14.15.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/555ca268-c6d1-fb65-91ac-c65886d9ec2c.png)

# QuickSightの設定
ここではスタンダードプラン、Tokyoリージョンを設定してQuickSightのアカウントを設定します。
データセットでAWS Athenaを選択します。

![スクリーンショット 2019-09-25 15.13.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/1541021e-18a7-4ca4-5ef7-e8125d028631.png)

先程作成したテーブルと、QuickSightにデータを取り込むのではなく、都度クエリーする形を選択します。

![スクリーンショット 2019-09-25 15.13.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/9661c451-577e-45ed-c8a7-5af51367415e.png)　![スクリーンショット 2019-09-25 15.14.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/e5e460c0-2a5b-0426-d9ce-ae59f71602b1.png)

カラムとグラフの種類を選択すると、簡単にグラフ化する事ができます。

![スクリーンショット 2019-09-25 16.10.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/eeaea7e8-7e48-d225-07f8-8a788a92334b.png)

# QuickSightのカスタムSQLの利用
AWS Athenaのテーブルをそのまま表示するだけではなく、複数のテーブルをジョインした結果を用いる事もできます。方法は、先程と同様にデータセットでAWS Athenaを選択して、カスタムSQLを選択します。

![スクリーンショット 2019-09-25 17.47.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/0bb510f0-dbdb-05d5-d1d6-5aa2f1e2f16e.png)

この例では、users_tableのplanAのユーザーでかつ、devices_tableでdeviceAを持つユーザーのみの抽出を行っています。

![スクリーンショット 2019-09-25 18.58.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/9995f106-bbcb-83b1-3555-fe552c90879d.png)

![スクリーンショット 2019-09-25 18.59.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/85182/5e6195de-0739-321e-8714-11ff84fb498d.png)

# 追記 タイムスタンプの扱いについて

ISO 8601方式のタイムスタンプはAthenaではTIMESTAMP型としてデータベースに登録できません。それが不便だなと悩んでいたのですが、以下の良記事で対応できました。ここで記述されているQuickSightでの計算フィールドを用いた方法で対応しました。

https://dev.classmethod.jp/cloud/aws/tips-quicksight-date-type-format/?fbclid=IwAR0esHLvp80M4iIeZpJNNxc8EPJNxKN3ZsG57RKN90Z8QePGZ3mzmxVHJ7M