CUEBiC TEC BLOG

キュービックTECチームの技術ネタを投稿しております。

自動でログからメディアごとに按分を作成できるようにしてみた

こんにちは！キュービック TECインターンの根岸です。 今回は、この前に作成したメディア按分表の説明をしていきたいと思います！

背景

メディアごとのアクセス数やAWSリソースのコストを算出をこのメディア按分表での得たい成果は、S3に保存されているWADATSUMIサーバーのログデータを1ヶ月ごとに集約を行い、共有ドライブ内にあるGoogleスプレッドシートにてメディアごとにログデータを視覚化するとともに、スプレッドシート内の指定の場所にコストの金額を記入することでメディアごとの金額を算出することです。

アーキテクチャー

今回、構築したアーキテクチャーは以下の図のようになります。

アーキテクチャ図

使用するリソース

使用する言語

動作説明

説明にするにあたり、①〜⑩でAWSリソースに番号を振り分けた以下のアーキテクチャ図を基に説明いたします。

  1. まず、弊社のメディアのアクセスログが①のS3にJSON形式のファイルに格納されており、このファイルを毎月31日23:59に②のAWS Glueでパーティション化をします。この動作により、ログファイルから今回利用する"media-id"という値を取得しやすくなります。

  2. 次に、毎月1日12:00に動作をする③のEventBrigeをトリガーに、④のLambdaが作動します。この④のLambdaでは1ヶ月分の"media-id"をAthenaでクエリするクエリ文が仕込まれており、⑥のAmazon Athenaで"media-id"で各メディアごとにクエリをした後に、⑦のS3バケットにクエリしたデータをCSV形式のファイルに保存し、最後に⑧のLambdaを呼び出します。

  3. 呼び出された⑧のLambdaが動作し、⑦のS3バケットの最新のCSVファイルを取得してPandasとMatplotlibを利用し、ランキング化やパーセンテージの算出などのデータフレーム整形と円グラフの作成を行い、⑨のS3バケットにCSV＆PNGファイルを保存します。

  4. ⑨のS3バケットにCSVファイルがPUTされたことをトリガーに、➓のLambdaが動作します。この➓のLambdaでは⑨のS3バケットにあるCSV＆PNGファイルを取得して、そのファイルデータをGoogleスプレッドシートに上書きし、以下の画像のような形でメディア名、アクセス数、ランキングを展開し、E1セルに全体のコストを記入すると自動で割合からコストを算出します。

５. Google認証によるエラー回避のためデータを挿入するスプレッドシートは上書きをして使用しているため、毎月15日12:00に当月分のスプレッドシートのファイルのコピーをGASを利用して自動的に作成を行います。この動作により、"2○○○年○○月按分”というスプレッドシートファイルが自動的に生成されます。

苦労した点

苦労した点は二つあります。 一つ目は元々、DataDogのAPIを利用してDataDogのグラフをそのまま取得する、もしくはCSVを取得しようとしていましたがどちらとも上手くいかず… そこで、ログデータに"media-id"というメディアごとの分類を区別できるものを設定してそこからアクセス数を取得することに変更しました。しかし、ログデータが格納されたたくさんのS3ファイルをLambdaでただ取得すると今度はたくさんの呼び出しでLambdaの実行回数がすごいことになってしまうという問題がありました。 なので、今まで使ったことのなかったAWS GlueとAWS Athenaを利用して、"media-id"という値で一ヶ月分のアクセスログをクエリできるように構築し直したところが大変でした。

二つ目はGoogleの認証にかなり苦戦しました。AWSリソース同士の連携を考えて、Lambdaから新規にスプレッドシートを作成し按分データと円グラフの画像データを貼り付けた上でGoogleの共有ドライブにアップロードをするように設計を行いましたが、新規にスプレッドシートを作成しても開けないことや円グラフの画像がS3のURlしか表示されないなどGoogleの認証周りがうまくいかず、既存のスプレッドシートに上書き更新を行うように設計し直したところです。

しかし、こちらはスプレッドシートの円グラフの自動生成機能を利用する、GASで定期的にスプレッドシートのコピーを作成し過去ファイルを残すことでなんとか問題解決しました。