ヤマムギ

growing hard days.

*

RedashでSendGridのActivityを見る(Amazon S3 -> Athena経由)

      2017/12/10


Redash Advent Calendar 2017に参加しましたので、今年リリースの v2 でダイレクトでの接続が実装されたAmazon Athenaデータソースを使ってSendGridのActivityを見たことについて書きたいと思います。
v2より前はDockerを使ったProxy経由でAmazon Athenaデータソースに接続していました。

概要

  • SenGridのEvent WebHookでAmazon API GatewayへEventを送信
  • Amazon API GatewayをトリガーにしたAWS LambdがEventを受け取ってCSV形式でAmazon S3へ格納
  • Amazon S3 をデータソースにしてAmazon Athenaでテーブルを作成
  • RedashでAmazon Athenをデータソースにしてクエリ実行、可視化

Amazon S3でCSVを格納するバケットを作成する

バケット名とリージョンだけ指定してあとは何も設定してません。

バケット名は「sendgrid.activity.yamamugi.com」、
リージョンはRedashのEC2を起動しているバージ二ア北部にしました。

AWS LambdaでSendGridのActivityをCSVに変換してS3バケットに格納する

ソースコードはこちらGitHubです。

S3のオブジェクトキーは年月日時間で階層のようなプレフィックスにしています。
大量にActivityが発生するような場合はキーをランダムにしたほうがいいと思います。
このあとのクエリーではオブジェクトキーは何であっても問題はありません。

環境変数でバケット名とSlackのURL,チャンネルを指定しています。
Slackはエラー通知用にしているだけです。

Lambdaの権限

Lambdaのロールには、作成したS3バケットにput objectを許可あしたポリシーを割り当てたロールを設定しました。

Amazon API Gatewayを設定する

作成したAWS LambdaがPOSTメソッドをトリガーに起動するようにAPI Gatewayを設定しました。
Lambda関数を指定する以外はデフォルトのままでデプロイしました。

SendgridのWebHookを設定する

SendGridのダッシュボード [Settings]-[Mail Settings]で、[Event Notification]をONにして[HTTP POST URL]にAmazon API GatewayでデプロイしたAPIのエンドポイントを設定します。

ここまでの設定で、SendGridで何かイベントがおこるとS3に格納されるようになりました。

Amazon Athenaの設定

次にAmazon S3に格納されたCSVからAmazomn Athenaのテーブルを作成します。

Amazon AthenaのQuery Editorで以下のSQLを実行しました。

  • [add table]からGUIでテーブル作成を実行することも出来ます。

プレビューをしてみると、S3に格納されたActivityデータが見えます。

RedashでAthenaのテーブルのデータを可視化する

さて、いよいよRedashです。
バージョンは、もちろんv2以上です。

公式のRedash Amazon Athena Setupを参照して設定しました。

AWS IAMユーザーの作成

ユーザーを作成してポリシーを割り当ててアクセスキーを発行します。

ドキュメントにもありますがポリシーは下記です。
* Athenaのクエリ結果が入っているバケット配下へのs3:GetObject
* Athenaのクエリ結果が入っているバケットヘのs3:GetBucketLocation , s3:ListBucket
* AWSQuicksightAthenaAccess
* AmazonAthenaFullAccess

Amazon Athenaデータソースの設定

  • AWS Region: Amazon Athenaのリージョン
  • AWS Access Key , AWS Secret Key : 先程作ったIAMユーザーのアクセスキーとシークレットキーです。
  • S3 Staging Path : Amazon Athenaのクエリー結果が格納されているS3バケットを指定します。

実行してみた

クエリーでAmazon Ahenaを設定したデータソースを選択すると、左ペインにAmazon Athenaのテーブルが見えます。

クエリーペインでSELECT文を発行すると結果が返ってきました。

クエリー結果をビジュアライザーを用いてグラフにしてダッシュボードを作成することも出来ました。

@yamamanx

開発ベンダー5年、ユーザ企業システム部門通算9年、ITトレーナー1年目のSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。

このブログの内容/発言の一切は個人の見解であり、所属する組織とは関係ありません。

また、勉強会やイベントのレポートは自分が気になったことをメモしたり、聞いて思ったことを書いていますので、登壇者の意見や発表内容ではありません。

 - BI , , , ,

ad

ad

  関連記事

re:dash Error running query: ‘ascii’ codec can’t encode characters

エラー内容 re:dashの画面に「Error running query: & …

TuneCoreの売上データCSVをS3に格納してAthenaのクエリをRe:dashのデータソースにして可視化する

先日参加しましたAWS Summit Tokyo 2017で、 [JapanTa …

PentahoでMySQLテーブルデータソースを作成しようとした時のエラー対応

PentahoでMySQLのテーブルへデータソースを作成しようとしてエラーが発生 …

Re:dashを0.12系から1.0系へアップグレードする

1.0系が正式リリースになったので0.12系からアップグレードしました。 .en …

Re:dash CSVの出力文字コードをUTF-8からShift-JISに変更する

Re:dashの[Download Dataset] – [Down …

re:dashのパラメータクエリでシングルクォーテーションが文字参照に変わって検索できないので出来るようにする

re:dashでシングルクォーテーションをパラメータとして渡したい場合の方法に悩 …

勉強会用にre:dashの環境構築した記録

先日の「ヤマムギVol5 BI ファーストステップ ~re:dash , Pow …

AWS上にMetabaseサーバーを起動してみました

データ可視化ツールのMetabaseがいいらしいという声をいくつか聞いたのでAW …

Re:dash CSVの出力コードをUTF-8から他の文字コードに変更する事が出来るようになります

先日、Re:dash CSVの出力文字コードをUTF-8からShift-JISに …

re:dashで必須でない検索条件を作りたい

re:dashで必須ではない任意検索のためのフォームを実装したい、となりましたの …