ヤマムギ

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文を発行すると結果が返ってきました。

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


最後までお読みいただきましてありがとうございました!
【PR】 「AWS認定試験対策 AWS クラウドプラクティショナー」という本を書きました。

 - BI , , , ,

ad

ad

  関連記事

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

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

Backlogの実績工数をAmazon QuickSightで可視化してわかったこと

今年に入ってから、Backlogで個人タスクを登録しだして、予定工数、実績工数を …

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

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

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

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

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

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

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

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

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

目次 エラー内容ログ調査sitecustomize.pyの設定 エラー内容 re …

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

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

re:dashで 「Error running query: ‘ascii’ codec can’t encode」

目次 re:dash の画面に表示されたエラーcelery_error.logに …

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

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