appbrew Tech Blog

appbrewのエンジニアチームの日々です

【BigQuery & Redshiftで使える!】Firebase Analyticsのクエリ課金を1/100にした方法

クラウド大好きエンジニアである株式会社AppBrewの@anoworlです。これは「Firebase Advent Calendar 2020」8日目の記事、ならびに「💰 AWSの費用削減 💸 Advent Calendar 2020」2日目の記事です。

さて今回のテーマは「ログ分析のスキャンを大幅に減らす方法」です。

弊社では定量・定性による仮説検証を掲げ、そのために「SQL学習モチベを爆上げする「SQLテスト制度」を導入している話」のように、全社員がクエリを叩いて仮説検証出来る環境の構築を目指しています。

そこで重要になるのがクエリ結果が返ってくる速度です。BigQueryにしろRedshiftにしろ、金で殴ってもそもそものデータスキャンの量が多いと、どうしてもクエリに時間がかかってしまいます。

この記事ではFirebase AnalyticsのBigQuery Exportで出来たテーブルを例に挙げ、ログを保存するテーブルの作り方を工夫することで、クエリ実行時間を短縮し、なおかつ費用削減も出来る方法をご紹介します。

料金の要素分解

BigQueryはスキャン量と容量の従量課金となります。

Redshiftはインスタンスになり、CPUとディスク(容量・速度)が効いてきます。

どちらもクエリのスキャン量が効いてくることが、ここから分かると思います。これらの要素をいかに減らしていくかが、費用削減のキモになります。

【コラム】ディスク容量が足りない?

BigQueryは従量制なので、容量が足りなくてもインスタンスを乗り換える必要はありません。

ではRedshiftは…?確かに以前ディスク容量が足りない場合は、インスタンスをスケールアップ or スケールアウトする必要がありました。

ですが昨年、「Amazon Redshift で、コンピューティングとストレージの独立したスケーリングを可能にする、マネージドストレージが付属した RA3 ノードがリリース」され、一つのインスタンスで64TBまで保存できるようになりました!

ディスク容量に苦しんでいる御仁は検討してみるのも良いかもしれません。

ログのテーブル構造

今回はログの保存形式の具体例としてFirebase AnalyticsからBigQueryにエクスポートした時のスキーマを例に使います。

各社ログテーブルのスキーマはそれぞれ異なると思うのですが、この例にあるようにイベント日時イベント名は含まれているのではないでしょうか?ここではそれを使いスキャン量を減らす実演を行います。

Before: 経過時間: 6.4 秒、処理されたバイト数: 109.8 GB

今回、実際に弊社のイベントログを使ってクエリを叩いてみます。直近90日のある画面におけるPVの変動です。クエリはこちら。

SELECT  _TABLE_SUFFIX as dt, COUNT(*)
FROM
  `analytics_old.events_*`
WHERE
  (_TABLE_SUFFIX between FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 91 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
  AND event_name = 'screen_view_Articles'
 GROUP BY 1
 ORDER BY 1

Firebase Analyticsのスキーマはちょっと素直では無いので、クエリを解説していきましょう。

こちらのスキーマはBigQueryのパーティション分割機能は使われておらず、テーブルの末尾が%Y%m%d、つまり日付の文字列でそれぞれテーブルが作られています。それを_TABLE_SUFFIXで指定し、直近90日で絞っています。加えてevent_nameで絞り、日毎に出しています。今回は簡単のためにタイムゾーンは考慮しておらずUTCで出しています。

実行結果は「経過時間: 6.4 秒、処理されたバイト数: 109.8 GB」でした。

スキャンを大幅に減らす方法

テーブル構造の所でお気づきの方はいるかもしれませんが、分析時に必ず指定するであろうカラムを使って絞り込みを行うとスキャンを大幅に減らせます。

つまりそれはevent_nameです。これを使ってスキャン範囲を絞り込むことで、スキャンを大幅に削減出来ます。Redshiftではソートキー、BigQueryではクラスタ化を使用します。

例えばBigQueryの場合は以下で既存のFirebase Analyticsのテーブルを、パーティション分割およびクラスタ化出来ます。

CREATE OR REPLACE TABLE `analytics_new.events`
PARTITION BY DATE(event_time)
CLUSTER BY event_name
AS
SELECT *, TIMESTAMP_MICROS(event_timestamp) as event_time FROM
  `analytics_old.events_*`

【コラム】もっと絞り込めないか?

他に指定できるカラムは無いでしょうか?弊社だとデバイスタイプ(web_mobile, web_pc, app_ios, app_android)もよく使うので、こちらも併せて指定することで、よりスキャンを削減しています。

みなさんも他によく使うものがあれば、教えていただけると嬉しいです!

After: 経過時間: 3.0 秒、処理されたバイト数: 1 GB

では、これを活用したクエリに書き換えましょう。

SELECT  DATE_TRUNC(DATE(event_time), DAY), COUNT(*)
FROM
  `analytics_new.events`
WHERE
  event_time between TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 91 DAY)) AND TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND event_name = 'screen_view_Articles'
 GROUP BY 1
 ORDER BY 1

実行結果は「経過時間: 3.0 秒、処理されたバイト数: 1 GB*1」でした。

どうでしょうか?スキャンしたバイト数は1/100に、実行時間は1/2になりました。大きいのはevent_nameでクラスタ化したこと、日時でパーティション分割をしたことです。

タイトルにある100分の1ですが煽りすぎだと思うでしょうか?実際、定量を見る際には数多くのイベントを取得する必要がある上に、何らかのImp(投稿セルなど)を取るとそれだけで非常に多くのログがテーブルに記録されます。それを考えると全く誇張ではなく、ガクンと減るのが想像出来ると思います。

Redshiftでは?

先程の通りソートキーを使います。ここで注意しなければならないのは、最後に時系列カラムを持ってくること。そしてソートキーに指定したカラムは圧縮しない、encode rawにすることが重要です。それで数十倍、実行時間に差が出ることがあります。

create table public."new_log"(
...
,"event_timestamp" timestamp    encode raw DISTKEY
,"event_name" varchar(255)   encode raw
,"device_type" varchar(32) encode raw
...
)
 SORTKEY(event_name, device_type, event_timestamp)
 ;

event_timestampを最後に持ってくることで定期的なVacuumの手間は必要ですが、こうしなければnameやdevice_typeのインデックスが非常に効きづらくなってしまいます(B+Treeなどを想像すると、なぜかそうなるのか分かりやすいと思います)。

ソートキーに指定したカラムを圧縮しないのは、ゾーンマップを有効活用するためです。このあたり、調べてみるとReshiftの裏側が見えて面白かったです。

おわりに

今回は単純なクエリだったので、そもそもスキャン量はそんなに多くありませんでした。

ただこれがevent_name以外のカラムも使用するようになるとスキャン量は更に上がり、差分もより大きくなります。

費用削減を通して会社全体の生産性向上にも取り組んでいきましょう!

We are hiring!

費用削減を通して会社全体にレバレッジをかけたい方、お話しましょう!

www.wantedly.com

*1:ちなみにクラスタ化したとき、ドライランした際の処理されたバイト数は参考にならないです 👶