やりたいこと
デジタルプロモーションのお仕事にも携わることになりまして
・複数の広告媒体のレポートデータを取得してセキュリティが担保された場所に置きたい
・複数の広告媒体のレポートデータを、ある軸で集計できるよう良い感じに加工したい
・加工したデータを良い感じにダッシュボードにしたい
と、いったことをしたくなりました。
課題感
- 広告媒体のレポートは取得できるデータ項目がとても沢山あるため、古来の手法だと1つずつ調べてRDBMSのテーブル設計に落としていくことになり日が暮れて朝が来る事が繰り返されそう
- 取得する「元データ」をどこでどう管理するか悩ましい
- ダッシュボードの変更をすると、データセットの変更となり開発サイドの工数が掛かりがち
やったこと
・S3をDatalake Data lake Storageとして利用
・Athenaでデータ確認、データ加工、データチェックまでを担当
・Tableau Serverを活用
S3/Athenaの選定理由
AWS を使っていたため
身も蓋もないかもですが、普段使っているものはスピード、セキュリティ、コスト面で優位となりやすいです。
汎用的なETLツールとしても利用できそうだった
社内では、S3およびSQLはデータ分析チームでも扱うことが出来るため、Athenaを採用することで今後の人的リソースの拡張性がありそうと感じました。AWS Glueも検討したのですが、GUI上で複数データのJOINが出来れば現状の組織に適合したかもですが、列を削るくらいに留まるようで、事実上プログラミングスキルが必要となるため理由で断念しました。
安い
激安です。そこまでのビッグなデータではない場合、かなりの低コストとなります。運用開始して2カ月経ちますが現在Athenaのクエリで総額100円も掛かっていません。ログのようなデータではない辺りも適合しています。
サーバーレス
データマートをRDSにするとメンテナンスの影響を受けます（それはそれで慣れているのですが）。Athenaもまたサーバーレスの恩恵を最大限に受けられるサービスです。
Tableau Serverの選定理由
自社に既に環境があったため
身も蓋も無いですが...どちらかというと↓ 後述の結果自社内に環境があった、という感じです。
自社内にTableauが浸透していたため
2013年くらいからTableauを社内で活用していたこともあり、エンジニアやデータ分析チーム以外のマーケティングコンサルタントや広告運用メンバーもTableauが使えます。何かをやりたいときにデータソースに接続されたワークブックを渡せば後は運用メンバーに任せることで、ダッシュボードの変更に開発リソースが介在しないことが、かなりのメリットと言えます。
データレイクの作成
取得したレポートを「どう置くか」を考えます。後述のパーティション構成など試行錯誤しましたが、S3に置く場所を変えるだけなので試行錯誤しながら進めやすいのが最大の利点だった気がします。
前提条件
- 広告媒体と言っても沢山種類があります。ここでは仮に広告A、広告Bの2つを扱うものとします。
- また広告ごとに複数の種別IDがあり、広告の種類を横断して種別IDごとの分析を行うものとします。
- 毎日1回データを取得するものとします。
取得するデータの確認
広告媒体は、指標によっては単純に1レコードで取得できないものがあります（詳細は割愛しますが、インプレッションとコンバージョンは同時に取ってこれないなど）。しかしそこはデータレイクなので、分かれた状態でとりあえず置いてしまいます。また、前述の種別IDを意識して配置します。種別IDごとに分析をする前提のため、この種別ID（typeid）をパーティショニングのキーとして扱うことにしました。
s3://(バケット名)/report/athena/広告媒体A/latest/impression/typeid=a1/
s3://(バケット名)/report/athena/広告媒体A/latest/conversion/typeid=a1/
s3://(バケット名)/report/athena/広告媒体B/latest/impression/typeid=a1/
s3://(バケット名)/report/athena/広告媒体B/latest/conversion/typeid=a1/
※typeid=a1となる4つが1セットとして同じ分析対象
DDLは以下のようになります。少々略し過ぎですが
CREATE EXTERNAL TABLE `ワークグループ名`.`広告A_impression`(
★略★
PARTITIONED BY (
`typeid` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
★略★
LOCATION
's3://(バケット名)/report/athena/広告A/latest/impression/'
★略★
こんな感じで、1つの分析対象につき、2つの広告媒体で2つの指標のデータがあるとすると、4つテーブルができます。
広告A_impression
広告A_conversion
広告B_impression
広告B_conversion
そういえばAWS Glueによるテーブル定義の自動取得なども試したのですが、自分の扱った範囲では判定が上手く行かない場合もあり結局目検になるので、Athena側で「最初全部stringで定義して後の行程でSQLを書いてて数値集計対象となった項目は適切な型に変更する」という方法を採りました。データの中身の確認自体はstringで事足りますし、数値指標の数は目検で対応できるくらいしかないのでこの方法でスピードが出せました。割とそういった無茶苦茶な進め方が出来るのもこのデータレイクの強みだと思います。
余談ですが、LOCATIONで指定した以下は全てデータと言う扱いになるので、パスの途中に必ず「athena」で使っていることが分かる文字列が入っていると良いと思います。なんらかのアレでデータを置かれると事故ります。
スナップショット的なものを取得
毎日データを取得するのですが、広告の特性上1日ごとのデータを積み重ねていけばよい、という訳ではなく（広告媒体によりますが）毎日一定時間遡って取得する必要があります。なので、昨日と本日でデータが一致しないため、当日のデータを置くと共にスナップショットとして履歴に残すようにしました（使わないことを祈る類のデータですが）。
s3://(バケット名)/report/history/広告媒体A/(YYYY)/(MM)/(DD)/impression/
s3://(バケット名)/report/history/広告媒体A/(YYYY)/(MM)/(DD)/conversion/
s3://(バケット名)/report/history/広告媒体B/(YYYY)/(MM)/(DD)/impression/
s3://(バケット名)/report/history/広告媒体B/(YYYY)/(MM)/(DD)/conversion/
いま思うと、これは若干パーティションと混同しそうな気がするのでイケてない気もします。
データマートの作成
作成したデータレイクから、良い感じにExtractしてTransformします。今回の設計の場合、最終的なLoad先もS3にしたので、ETLを全てAthenaで行う事が出来ます。
CASE expression
広告媒体によっては「この項目がAだったらのちの集計定義AAとする」みたいな変換が必要となります。
また、広告媒体が複数あると、とある項目で返却される文字列が異なっていたりします。このままレポートしてしまうと別のディメンションとして集計されますので、このタイミングで合わせます。
曖昧に書き過ぎですが、ようは意外と細やかなTransformが発生するので、それらを全てcase文で吸収しました。
UNION ALL
広告媒体AとBをまたいでの集計となります。今回は、各々流したSQLをUNION ALL して1つのデータマートに集約することをしています。
select 指標1 , 媒体名 from
(
select 広告媒体Aの指標1 as 指標1, '広告A' as 媒体名 from 広告A_impression left join 広告A_conversion on (適切な結合)
union all
select 広告媒体Bの指標1 as 指標1, '広告B' as 媒体名 from 広告B_impression left join 広告B_conversion on (適切な結合)
)
#この結果をまた EXTERNAL TABLE として格納。
補足
元データをTableauのデータセットとしてJOINしていくことも可能なのですが、以下の理由からデータマート作成フェーズを入れています。
- 整合性をシステムで検知したい（後述）
- Transform行程（ビジュアライズに利用しているデータの作成行程）をGitHubで管理できる
Tableauは便利なのですが、当然ですがパッと見て計算フィールドで何をしているのか分からないので、Tableau部分を他チームに任せるからこそ、このTransformの行程が重要となると考えています。
また、この方法の良いところは、広告媒体がいくら増えても上記のSQLに追加をするだけでデータマートの再構築完了となる点です。
整合性の確認をする
おそらくデータレイク構築の注意点の1つだと思うのですが、主キーの無いデータを加工したりしていくので、かならず別のクエリを流して元データとの整合性を確認します。Athenaに限らずTableauでもそうですが、JOINは誤るとデータセットがおかしなものになってしまいます。今回の場合は、join元の広告X_impressionデータとレコード数と、データマートのレコード数や数値指標が一致するようなものを始め、様々なチェックをしています。
これもAthenaを利用して、データマートとデータレイクの元データの差分を出力＝出力が発生したらエラー検知するようなチェックを定期的に実行しています（EC2を利用）。こうした検知にクエリ1本で出来るのもこのアーキテクチャの強みだと思います。
その結果として、例えば新しいことを運用側でしようとしたときに、その変更がデータセットに影響のあるものだったとしても検知します（しました）。また、一連の過程でPresto側がこちらの意図しない挙動をしたとしても検知します（しました）。
まとめ（感想）
- Athenaを使うと試行錯誤しながらつくれた
- Athenaを使うと仕様変更へ対応も楽だった
- SQLによるTransformが思いのほか良かった
- データの整合性チェックもSQLベースで行えるのが良かった
処理をSQLで書いていけば良いのは楽です。また、SQLで行える便利さを支えているのは、テーブルへのデータのロード時間がゼロであるため変更が容易な点だと思います。
どこまで書くか迷った結果、曖昧な記事になっており（データ量すら書いてない！）、実際にはもう少し複雑な構成やもっと要件があったりしますが、概ね基礎部分は記事の通りです。
地味に苦労した点
-
Prestoのクエリの書き方をググるとだいたいTD関数（Arm Treasure Data）の方がSEOが強いため、素の？Prestoの使い方があまり出てこない。AWSのドキュメントを読んでいけば辿り着くのですが。
-
元の広告データに重複は無かったため、ただのunionにしていたところ、抽出した全フィールドが重複していなくても、特定の複数のフィールドだけが重複していると除外されることがあり、そういうのもあるのか...と思ってunion all にしました（AthenaなのかPrestoなのかSQLのそもそもの話なのかは深追い出来ていません）。