お手軽集計システムの構築

UUUM Advent Calendar 2019 9日目の記事です。

こんにちは、@key___snです。
今回は、システムユニットで利用している集計システムで使うサービスの紹介をしていきたいと思います。

今は下記のようなサービスを利用しています。

利用しているサービス

AWS S3

AWSのストレージサービス
これは結構利用している人も多いかと思います。
macのfinderでフォルダやファイルを作成・保存するように簡単にデータを格納・取得することができます。

AWS Athena

S3にあるデータをSQLクエリで取得することができる。
スキャンするデータ量によって金額が変わる。

AWS Glue

AthenaでS3に入ってるデータをクエリで取得できるように紐づける役割
データベースを作成して、S3に入ったデータに対してテーブル定義をする事ができます。

実際に使ってみる

sample-qiita.csv

Day	Video ID	Channel ID	Title	Views
20191001	hogehoge	HOGEHOGE	○○やってみた!	10
20191001	fugafuga	FUGAFUGA	○○について	40
20191002	hogehoge	HOGEHOGE	○○やってみた!	20

S3にデータを保存する

AWS S3から「バケットの作成」（ここでは、「sample-qiita」）
作成したバケットをクリックして「アップロード」から上記のCSVファイルを選択してアップロードする

↑こんな感じになればOK！

GlueでAthenaとひもづける

AWS Glueから「テーブルの追加」→「手動でのテーブルの追加」を押下する

次に読み込むS3の場所を指定する必要があるので、「データの場所」は自分のアカウントで指定されたパス(デフォルトのまま)。「インクルードパス」は右側のフォルダーアイコンを押下してsample-qiitaを指定。
「データ形式を選択」はCSVを選択。「区切り記号」はCommaを選択

スキーマの定義は↓こんな感じ

これで作成しましょう。

最後にCSVの先頭の1行はいらないので、作成したテーブルのページから「テーブルの編集」を押下して下記のパラメータを追加しましょう。

これで「適用」を押下して、完成です。

Athenaで設定する

[AWS Athena]から下記の画像のようにGlueの設定が反映されていることを確認したら、試しにクエリを実行してみます。「Preview table」を押すと10件のデータを取得してくれるので正しくデータが取れていることを確認してください。

初めてAthenaを利用する場合

クエリ実行時の情報をS3に保存する必要があり、それ用のバケットを用意する必要があります。
右上にある「Settings」から下記の画像のようにとりあえず設定しておきましょう。

計算してみる

SQLできる人にとってはもうあとは好きにしてくださいって感じですが、試しに動画ID毎にViewsをまとめてみます。
クエリは

SELECT video_id,
         max(title) AS title,
         sum(views) AS total_views
FROM "sample-qiita"."video"
GROUP BY  video_id 

こんな感じで、出力結果は下記のようになります。

注意点は、AthenaではSQL Query EngineにFacebookの開発したprestoを採用しています。
そのため、いつものSQLの書き方ではうまくいかない場合もあるかもしれません。
うまくいかない場合は下記のAWSのドキュメントに、採用しているprestoのバージョンとドキュメントが記載してあるので参考にしてください。
DML クエリ、関数、および演算子 - Amazon Athena