AWS Startup Tech 2017/10/19 Kinesis Firehose, QuickSight, Athena を使った行動ログ分析 株式会社Speee ヌリカエ兼開発基盤部エンジニア 森岡周平

改めて今日のお話し 下記3 つのAWS の機能を使って、 行動ログ分析の基盤 を作ったというお話し Kinesis Firehose Athena QuickSight このシステムを作る際の作業内容・ 注意点とコストが 掛かるポイントを実際に運用した経験を元に話します ※ 何も無いところから手探りのログ基盤作りなので、 間違った こと言ってたら色々 指摘して頂けると嬉しいです :bow:

今日のター ゲット AWS でサー バー は建てたことがある デー タ分析の基盤は作ったことが無い Athena, QuickSight , Kinesis Firehose のいずれか に触ったことが無く興味がある

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

会社: Speee 所属部署: 開発基盤部 / ヌリカエ 名前： 森岡周平 GitHub Account: Selmertsx サー バー サイドエンジニア 業務での使用言語: 主にRuby AWS 歴: 1 年 ( たまに触る程度)

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

外壁塗装の一括見積もりサー ビス 専門のアドバイザー が、 お客様にヒアリングした 情報を元に、 適切な業者様を紹介させて頂く

施工業者様に紹介、 成約することで売上を得る

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

やりたいこと 社内のアドバイザー の行動量に対する、 利益 ( お客様の満足度と 施工業者様の売上 ) を最大化したい アドバイザー は専門的な知識や相場の把握が求められる アドバイザー の育成には時間が掛かり、 簡単に採用できない 一人あたりの効率を最大化することが事業の成長に直結 見たいデー タ アドバイザー の通話時間 アドバイザー の通話回数 アドバイザー の管理画面の操作内容

アドバイザー が利用しているツー ル 顧客への架電業務 Android App ( 出先でも対応できるように) Windows App ( 通常利用するもの) 施工業者への紹介業務 Web App 上記端末の操作ログが取得できなければならない

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

Kinesis Firehose+Athena / MySQL にデー タを集積 QuickSight でデー タを可視化

説明 このシステムの説明を 行動ログの集積 行動ログの集計 行動ログの可視化 上記３つの処理を通して説明します

行動ログの集積について

Kinesis Firehose 任意のデー タを API だけでS3/RedShift に送信できる機能 Delivery Stream の設定はコンソー ルから5 分で可能 フロントのJS やアプリからもログが送れる # ruby sample code client.put_record( delivery_stream_name: "DeliveryStreamName", record: { data: [name,path,time].to_csv } ) #s3://dict/year/month/day/hour/xxx 森岡周平,/path/nurikae_app/a,2017-06-01 05:58:15.979 森岡周平,/path/nurikae_app/b,2017-06-01 05:58:19.326 森岡周平,/path/nurikae_app/c,2017-06-01 05:58:22.507

お客様との通話は自作 windows/android app 施工業者様への紹介はweb の管理画面 Kinesis Firehose からS3 にデー タを集積する

行動ログの集計について

Amazon Athena S3 内のデー タをSQL を使って分析するツー ル

ユー ザー の行動ログをSQL で分析 ex: Action A から Action B までの時間を算出するSQL SELECT "operator_name", "log_date", "operatability", "total_elapsed_time" FROM (SELECT operator_name, date(created_at) AS log_date, operatability, sum(elapsed_time) AS total_elapsed_time FROM (SELECT operator_name, created_at, lag(operatability) over(partition by operator_name, date(created_at) COALESCE(date_diff('minute', lag(created_at) over(partition by ORDER BY created_at), created_at), 0) AS elapsed_time FROM nurikae.operatability_histories ORDER BY created_at asc) AS elapsed_time_relation WHERE operatability is NOT NULL GROUP BY operator_name, date(created_at),operatability ORDER BY log_date, operatability asc) AS "operator elapsed time query"

行動ログの可視化について

QuickSight AWS から提供されているBI ツー ル (Re:dash っぽい)

デー タの集計について 架電ログなどは Athena から取得 売上デー タなどは MySQL から取得

各オペレー タ毎の工数を可視化

ドリルダウン/ ドリルアップ

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

一般的な行動ログの取得方法 モバイルアプリの行動ログを送信するエンドポイント をどうするか調べてみた 自前でendpoint を建てる Kinesis Stream を利用する Kinesis Firehose を利用する

自前で Endpoint を建てる

treasure data から移行コストは一番少ない デー タの出力先をRDS/S3 等柔軟に選べる 昔からある方法なので情報が多い 管理しなければならないリソー スが多い ELB aggregater server

Kinesis Stream を利用する

管理するリソー スが減った Kinesis Stream + Lambda だけ lambda でデー タを加工しなければならなくなった Lambda も減らせないか？

Kinesis Firehose を利用する

Kinesis Firehose だけで ログが送れるようになった

エンドポイント比較表 現状のデー タ量において 種別 自由度 運用/ 実装 費用 scalability 自前実装 ◯ ☓ ☓ ◯ Kinesis Stream △ △ ◯ ◯ Kinesis Firehose ☓ ◯ ◯ ◯ 自前実装した場合、S3 だけでなくRDS に出力するなど柔軟にできるが、 uentd のaggregater をメンテナンスしなければならない Kinesis Firehose では1 度 S3 に置くしか無い Kinesis Firehose には簡便性という利点もある. stream を指定してAWS Cli のAPI を叩くだけで実現可能 Kinesis Firehose の維持費は大体 200000 message で3 円

ヌリカエにおけるログ分析の全体像

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

利用方法と運用上の注意 Amazon Athena Partition 効いたQuery を実行する Firehose で使うときは ALTER TABLE ADD でPartition を切る Kinesis Firehose ALITER TABLE ADD を使わなければならない理由 Quick Sight SPICE を有効活用してAthena へのアクセスを減らそう 大人数で共有するにはちょっと向いてないかも？

利用方法と運用上の注意 Amazon Athena Partition 効いたQuery を実行する Firehose で使うときは ALTER TABLE ADD でPartition を切る Kinesis Firehose ALITER TABLE ADD を使わなければならない理由 Quick Sight SPICE を有効活用してAthena へのアクセスを減らそう 大人数で共有するにはちょっと向いてないかも？

Athena の利用料金 基本的にQuickSight 経由でQuery を実行 課金ポイントはQuery 実行時にScan したデー タ量 値段は $5/TB ( 最小単位は10MB で¥0.006) 参考: 下記のデー タフォー マットの約3 万件のデー タが無圧縮で1MB desc operatability_histories; operator_name string operatability string created_at timestamp

Partition 効いたQuery を実行する # Partition を気にしていないQuery (36MB 検索対象) select * from admin_requests; # (Run time: 27.7 seconds, Data scanned: 36.1MB) # Partition の内容確認 SHOW PARTITIONS admin_requests; > ingestdatetime=2017-06-18-13... # Partition を使ったQuery の発行 select * from admin_requests where ingestdatetime >= '2017-10-16-09'; # (Run time: 1.54 seconds, Data scanned: 205.01KB) 10MB 以上の単位で partition を切るのが良さそう

Partition の設定方法 S3 に格納する際に hive format で格納する % aws s3 ls s3://xxxx/samples/hive-ads/tables/impressions/ PRE dt=2009-04-12-13-00/ PRE dt=2009-04-12-13-05/ ALTER TABLE ADD PARTITION を利用する <= 今回はコレ ALTER TABLE admin_member_access ADD PARTITION (year='2017', month='05', day='29') location 's3://xxxx/admin_requests/2017/05/29/' # sidekiq で定期的に実行 ※ 理由については Kinesis Firehose のところで説明します 参考URL:https://dev.classmethod.jp/cloud/aws/athena-partition-reinvent/

利用方法と運用上の注意 Amazon Athena Partition 効いたQuery を実行する Firehose で使うときは ALTER TABLE ADD でPartition を切る Kinesis Firehose ALITER TABLE ADD を使わなければならない理由 Quick Sight SPICE を有効活用してAthena へのアクセスを減らそう 大人数で共有するにはちょっと向いてないかも？

Kinesis Firehose の利用料金 転送量 1GB あたり$0.029 で¥3 (Ohio region) 1 USD => 111.931945 円 (2017/10/16) 1 レコー ドあたりの送信量は5KB 単位で繰り上げ 大体 200000 message で1GB => 3 円 S3 の料金体系にも影響を受ける Kinesis Firehose はS3 にupload する前のbuffer 一定の時間・ 容量でS3 にupload する S3 に格納する際のbuffer size は考える必要あり Athena で検索することも考えると、 ある程度大きくした 方が良さそう

利用上の注意 デー タ格納時にS3 のパスを指定出来ない Hive フォー マットでの格納が不可 Athena と連携する場合パー ティションの追加は ALTER TABLE ADD で行う必要がある aws> s3 ls sample/admin_requests/2017/10/01/01/ 2017-10-01 10:01:36 94 sample-5-2017-10-01-01-00-33-xxx 2017-10-01 10:09:33 4831 sample-5-2017-10-01-01-08-31-xxx 2017-10-01 10:10:34 1906 sample-5-2017-10-01-01-09-32-xxx

利用方法と運用上の注意 Amazon Athena Partition 効いたQuery を実行する Firehose で使うときは ALTER TABLE ADD でPartition を切る Kinesis Firehose ALITER TABLE ADD を使わなければならない理由 Quick Sight SPICE を有効活用してAthena へのアクセスを減らそう 大人数で共有するにはちょっと向いてないかも？

基本的な料金体系 1 user $13/month ( 年契約で $9) SPICE の容量1GB あたり$0.25 ( 基本 1 user 10GB) SPICE デー タの集計を高速化するためのエンジン ここにデー タを入れておけば集計が高速で行われる SPICE is Amazon QuickSight's in-memory optimized calculation engine, designed speci cally for fast, ad hoc data visualization. “ “

SPICE を使ってAthena のアクセスを減らす Athena の集計結果を格納する場所 SPICE にデー タを読み込んでおくと、QuickSight で見たいデ ー タを修正しても、Athena にデー タを再取得しなくなる SPICE のデー タ更新を定期的に実行する機能もある QuickSight の利用上の注意 閲覧のみアカウントが存在しない 大人数で情報を共有するのには不向きか

Agenda 自己紹介 事業説明 ( ぬりかえ) 可視化対象のデー タについて 構築システムの説明 システムの選定理由 利用方法と運用上の注意 まとめ

まとめ Kinesis Firehose, Athena, QuickSight を使って行動 ログ分析の基盤を作った 料金は$15 程度 Kinesis Firehose はスケー ラビリティに注目される けど簡便性も注目したい！