こんにちは、SaaS プロダクト開発部テックリードの丸山です。
先日、プライベートで使用している AWS アカウントに 15 万円の請求書が届きました。AWS Batch を使用して動画を GPU エンコードする仕組みを構築して運用していたのですが（構築したのは 5 年前）、プログラムの例外処理に不適切な点があり、プロセスが実行中のまま終了しない状態になってしまいました。そのため、コンピューティング環境（GPU を搭載した EC2 インスタンス）が動きっぱなしになり、高額請求される結果となりました。これを教訓に現在は毎日 AWS の料金を Slack に通知するようにしています。読者の皆さまにおかれましても、くれぐれも油断されなきよう。
さて今回は、少し前の話にはなりますが、昨年実施した視聴動向データの分析基盤のリニューアルプロジェクトについてご紹介したいと思います。
視聴動向データの分析基盤について
視聴動向データとは、誰が、いつ、どこで、どの動画を、どんなデバイスで、どのシーンまで見たか、といったようなデータを指します。ユーザーが動画を視聴している間、プレイヤーから定期的に送信されてくるデータを収集、蓄積しておき、事業者がそのデータを集計、分析できるといったサービスを弊社では SaaS として提供しています。
従来の分析基盤は、視聴動向データの収集には Google Analytics を、収集されたデータを蓄積し分析するためのデータウェアハウスには Amazon Redshift を、集計結果の可視化には独自開発の SPA アプリケーションを使用するという構成でした。
# 従来の構成
+--------------+ +-----------+ +----------+ +---------+ +---------+
| Video Player | | Google | | Amazon | | API | | Browser |
| (JavaScript) | ---> | Analytics | ---> | Redshift | ---> | Servers | ---> | (SPA) |
+--------------+ +-----------+ +----------+ +---------+ +---------+
[収集] [蓄積] [可視化]
この構成は、比較的安価に分析パイプラインを構築できるという利点がある一方で、Google Analytics の仕様の限界により機能が制限される場合があります。また、長期間の運用によりデータの蓄積量が増加してきたことで Redshift のクエリ応答時間も長くなってきており、今後のスケーラビリティにも不安が残る構成となっていました。
そこで昨年、分析基盤の全体的なリニューアルを実施しました。具体的には、視聴動向データの収集を Google Analytics から Amazon Kinesis を中心とした独自開発のエンドポイントに変更し、データウェアハウスについても、Redshift からの乗り換えを含めて再検討することにしました。
# リニューアル後の構成
+--------------+ +-----------+ +----------+ +---------+ +---------+
| Video Player | | Amazon | | ??? | | API | | Browser |
| (JavaScript) | ---> | Kinesis | ---> | | ---> | Servers | ---> | (SPA) |
+--------------+ +-----------+ +----------+ +---------+ +---------+
[収集] [蓄積] [可視化]
今回はここのお話
Amazon Kinesis を使用したデータの収集とリアルタイム分析のアーキテクチャについては、別の機会にお話しするとして、本稿ではデータウェアハウスの乗り換え検討について取り上げたいと思います。
データウェアハウスの選定
今回、データウェアハウス (DWH) を再検討するにあたり、もともと使用していた Amazon Redshift (Provisioned) *1 に加え、新サービスである Amazon Redshift Serverless、Google Cloud の BigQuery、そして Snowflake を含めた以下 4 つの DWH 製品を比較検討することにしました。
製品選定の軸としては、以下の 3 つの点を重視しました。
- 少ない開発工数で既存システムから移行できること
- 単位コストあたりのクエリ処理性能が優れていること
- 月間あたりのコストが予測可能であること
これらの軸を念頭におきながら、4 つの製品について評価していきます。
Redshift のパフォーマンス改善に挑戦
まずは、現行の分析基盤で使用している Amazon Redshift (Provisioned) に対し、さまざまなチューニングを試みることで性能を改善できないか検討しました。以下の記事などを参考に検討しました。
https://aws.typepad.com/sajp/2015/12/top-10-performance-tuning-techniques-for-amazon-redshift.htmlaws.typepad.com
具体的には以下のような改善を試みました。
- 適切なデータ型を設定
数値データなのに varchar 型になっていたカラムを数値型に変更。 - 適切なカラムサイズを設定
0〜100 の数値しか入らないのに bigint 型になっていたカラムを smallint 型に変更。 - 圧縮エンコーディングを変更
当初はほぼ全ての列が ZSTD アルゴリズムで圧縮されていましたが、これを AZ64 アルゴリズムに変更することで、データの圧縮率が向上し クエリ速度の改善が見込める とのことで、数値型や日付型などいくつかの列についてANALYZE COMPRESSIONコマンドの実行結果に基づき圧縮エンコーディングの変更を行いました。 - ノードタイプを変更
従来は DC2 ノードを使用していましたが、RA3 などの新しいノードタイプも試してみました。DC2 ノードタイプは、インスタンスのローカルディスクにデータが格納される構造なのに対し、RA3 ノードタイプは、Amazon Aurora と同様にコンピューティング層とストレージ層が分離された構造になっています。また、AQUA (Advanced Query Accelerator) のような RA3 ノードタイプでのみ利用できるクエリ高速化機能などもあります。 - ソートキーを変更
ソートキーは、データがディスク上にどのような順序で格納されるかを決定するものです。WHERE 句や ORDER BY 句に頻繁に指定されるカラムを中心に、単純ソートキーや複合ソートキーを設定することで、クエリ速度の改善を狙いました。
これらのチューニングを実施しても、クエリの応答時間は 1 割ほど速くなった程度だったため、結論、お金を払ってスペックを上げない限りは、これ以上の改善は見込めないだろうと判断しました。また、さらに時間をかけてチューニングを行い性能が改善できたとしても、今後カラムが追加されるなどして再度チューニングが必要になったとき、その都度それを行うためのリソース（人や時間）を確保することは難しいだろうと考え、ここでいったん Redshift の改善検討には見切りをつけました。
新登場の Redshift Serverless を試す
ちょうど Redshift の性能改善を試していたのと同時期に、Amazon Redshift Serverless が プレビュー版として発表 されました。東京リージョンでも試せる状態だったため、本番環境と同等のデータを使って実験してみたところ、以下の結果が得られました。
| Query | Provisioned (dc2.xlarge x4) |
Serverless (32 RPU) |
|---|---|---|
| Query 1 | 5.7 seconds | 1.2 seconds |
| Query 2 | 49.8 seconds | 3.2 seconds |
| Query 3 | 39.3 seconds | 3.6 seconds |
| Query 4 | 130.5 seconds | 9.8 seconds |
Redshift Serverless は、最小キャパシティの 32 RPU *2 であっても、既存環境の Provisioned な Redshift と比較して最大 15 倍も高速であることが分かりました。とはいえ、Redshift Serverless は、定常的に動作させるとかなりのコストがかかります。*3 言い換えると、Provisioned な Redshift でも、お金を払ってスペックを上げれば、これぐらいのポテンシャルはあるのだなと感じました。
検討当時はまだプレビュー版で、正式リリースの予定が見えなかったこともあり、Redshift Serverless は本格的な導入検討には進みませんでした。
Google BigQuery を試す
続いて、Amazon Redshift と並んでよく比較される DWH 製品である Google BigQuery を試してみました。Redshift と同じデータを使用して性能を検証するため、まずは S3 バケットに検証用データを Parquet 形式で Redshift からアンロード（エクスポート）しておきます。
UNLOAD ('SELECT * FROM test_table') TO 's3://example-bucket/exports/export-' CREDENTIALS 'aws_access_key_id=AKIA....;aws_secret_access_key=....' FORMAT PARQUET;
次に、この検証用データを BigQuery にロードします。S3 バケットにアンロードしたデータを Google Cloud Storage (GCS) にコピーし、GCS から BigQuery にデータをロードしました。このとき、データをもとにテーブル定義を自動的に作成することができます（最初は BigQuery Data Transfer Service によるロードを試みたのですが、この方法は検討に時間を要したため断念しました *4）。
次に、BigQuery テーブルに対して、従来 Redshift に投げていたものと同じ SQL を投げようとしたところ、SQL の構文エラーが発生しました。BigQuery は Redshift とは関数の仕様が異なっていたり、列名のエイリアスを ORDER BY 句や GROUP BY 句の中で使用できなかったりと *5 、結構クエリを書き換える必要がありました。
ちなみに今なら Google 謹製の SQL 変換ツールが提供されているようですので、こちらを使用することで簡単にクエリの書き換えができるかと思います。
実際に BigQuery テーブルに対してクエリを投げてみたところ、クエリの種類によって異なりますが、Redshift Serverless とほぼ同じか若干速いくらいのパフォーマンスでした。
しかし、BigQuery はその料金モデルがネックとなりました。BigQuery には「オンデマンド料金」と「定額料金」という 2 種類の料金モデルがあります。「オンデマンド料金」の場合、クエリによって処理されたバイト数に基づき課金されるのですが、弊社のサービス提供形態上、お客様がどれだけの規模の分析を行うかが予測できないため、お客様が大量に分析を行うと、予期せぬ多額のコストが発生するおそれがあります。一方で、「定額料金」の場合は、そのコミット量に応じて利用可能なコンピューティングリソースに上限が設定されます。最小コミットの 100 スロット（月額およそ 2,000 ドル）では、複数のクエリを並列で投げるとすぐに上限に到達してしまうことが分かったため、他の DWH 製品も見てから採用を決めることにしました。
Snowflake を採用した理由
本記事のタイトルを読めば自明かとは思いますが、最終的には Snowflake をデータウェアハウスとして採用しました。
Snowflake は、もともと Oracle 出身者らを中心に創業した Snowflake 社が提供しているデータウェアハウス製品で、2019 年には日本法人も設立されており日本語でのサポートが受けられます。昨今、データウェアハウスの新たな選択肢として注目されており、サイバーエージェントさんも Redshift からの移行先として採用していますし、DevelopersIO にも 2023 年 7 月時点で 600 件を超える記事 が投稿されています。
ではなぜ、新しいデータウェアハウスとして Snowflake を選択したか、その理由を説明します。
理由1： S3 からのデータロードが簡単
Snowflake の使用感を知るために、まずは無料トライアルアカウントを取得し、検証用データを流し込みました。S3 バケットにアクセスするための IAM 認証情報を Snowflake に渡せば、Snowflake は S3 バケットに置かれたデータを直接取り込むことができます。
Snowflake はマルチクラウドで展開されているサービスであり、ユーザーは AWS 上の Snowflake、GCP 上の Snowflake、Azure 上の Snowflake から自分が使いたい環境を選択することができます。弊社のサービスは基本的に AWS 上に構築されているため、AWS 上の Snowflake を選択しました。これにより、データのやりとりが AWS のネットワーク内で完結するため、データの高速なロード＆アンロードが可能になります。
さらに、Snowflake には Snowpipe と呼ばれる強力なデータ取り込み機能があります。これは S3 バケットにファイルを置くことで、ほぼリアルタイムで Snowflake に取り込んでくれるというものです。*6 2023 年時点では、Redshift にも auto-copy from Amazon S3 と呼ばれる機能が実装されているため、Redshift でも同じことができるのですが、検討当時はまだこの機能は存在していなかったため、これは Snowflake にしか存在しない画期的な機能でした。この機能のおかげで、データ取り込みの仕組みを既存システムよりも大幅に簡略化することができました。
なお、BigQuery の場合は、BigQuery Data Transfer Service を使用することで、S3 バケットから定期的にデータを取り込むことができますが、こちらは定期実行の間隔が最小でも 24 時間となっており、それよりも高頻度で取り込むためには、自前で仕組みを構築する必要が出てきます。
理由2： 優れたコストパフォーマンス
これまでに検証した 4 つのデータウェアハウス製品について、同じクエリを投げたときの処理時間を比較した結果を下図に示します。
QUERY 1〜7 は、いずれも実際にアプリケーションから投げられている SQL で、比較的軽量なものから走査範囲がかなり広いものまで、無作為にピックアップしたものです。
サービスごとに料金体系などが異なるため、単純に横並びで比較できるものではありませんが、赤色の Redshift Provisioned (dc2.large x9) と、水色の Snowflake (XS) はほぼ同じ料金です。この 2 つを比較しても、ほとんどのクエリでは Snowflake のほうが性能が高いことが分かります。
各サービスおよびノードタイプごとに、処理性能とそれにかかるコストをグラフにプロットすると、下図のようになります。
いずれのサービスについても、コストを 2 倍にすれば性能も 2 倍になるという、比例の関係が見て取れます。しかしながら、その比例係数に着目すると、Snowflake は Redshift よりもグラフの傾きが小さく、コストパフォーマンスに優れていることが分かります。
さらに、Snowflake には 一定時間クエリがないと自動的にリソース（仮想ウェアハウス）が停止される というサーバーレス的な要素もあります。停止している仮想ウェアハウスに対しては課金されませんし、停止中にクエリを投げても 1 秒〜数秒程度で素早く起動して処理されます。この仕組みがあるため、BigQuery を「定額料金」プランで契約するよりも、Snowflake のほうがよりコストを抑えられる可能性が高くなります。
理由3： 柔軟なキャパシティ管理が可能
Snowflake は Redshift RA3 ノードタイプや Amazon Aurora などと同様に、コンピューティング層とストレージ層が分離されたアーキテクチャとなっています。
今回検討しているプロジェクトは、マルチテナント型の SaaS アプリケーションであるため、複数のお客様がひとつのデータベースを共有しています。そのため、一部のお客様が負荷の高い分析クエリを実行している間、その他のお客様が影響を受けてしまう可能性があります。Snowflake では、仮想ウェアハウス（コンピューティング層）を複数用意することで、別の仮想ウェアハウスには影響が及ばないようにできるため、高負荷の分析を行うお客様には個別の仮想ウェアハウスを割り当てることで、それ以外のお客様への影響を抑えることができます。
比較表
ここまでを整理すると、下表のようになります。
| Amazon Redshift (Provisioned) |
Amazon Redshift (Serverless) |
Google BigQuery | Snowflake | |
|---|---|---|---|---|
| 移行コストの低さ | ○ | ○ | △ | △ |
| コストあたりの性能 | △ | △ | ○ | ○ |
| コストの予測可能性 | ○ | △ | △ | ○ |
| S3 からのロード機能 | △ | △ | △ | ○ |
移行に合わせて実施したこと
データウェアハウスの移行と合わせて、性能改善やリファクタリングのため以下の対応を行いました。
正規化にこだわらず JOIN の回避を優先するテーブル設計にした
従来のテーブル設計では、データベースを正規化することを強く意識していたため、分析時に複数のテーブルを JOIN する必要がしばしばありました。しかしながら、膨大な規模のデータを取り扱うデータウェアハウスでは、RDBMS における考え方とは異なり、厳密にデータを正規化するよりも、多少の不整合は起こるかもしれないが、高速に集計できるように複数のテーブルで重複してデータを持つ設計とすることが一般的です。そのため、今回データウェアハウスを移行するにあたり、テーブルの設計もゼロから見直し、JOIN しなくても必要な集計ができるような構造に改めました。
ユーザー定義関数を活用することで SQL をシンプルにした
Snowflake では ユーザー定義関数 (UDF) を作成することができます。Snowflake のユーザー定義関数は、標準的な SQL だけでなく、JavaScript でも記述できるという点が個人的にはユニークだと思います。私も普段 JavaScript で開発することが多いので、スムーズに記述することができました。
例えば以下は、文字列中の連続する空白文字を半角スペース 1 個に置換し、先頭と末尾の空白文字を除去するユーザー定義関数 normalize_string() の実装例です。$$ で囲まれた部分が JavaScript で記述されています。
CREATE OR REPLACE FUNCTION normalize_string(str VARCHAR) RETURNS VARCHAR LANGUAGE JAVASCRIPT AS $$ if (!STR) return null; return STR.trim().replace(/\s+/g, ' '); $$;
作成したユーザー定義関数は、以下のように SELECT 文などで使用することができます。
SELECT normalize_string(' A B '); --> 'A B'
データの事前集計にストリーム機能とタスク機能を活用した
Snowflake を使っていて「この機能よく考えられているな」と感じたのが、ストリーム と呼ばれる機能です。あるテーブルに対してストリームを設定しておくと、そのテーブルに対して発生した差分が自動的にストリームに書き込まれていきます。そして、そのストリームに記録された差分データは、消費されると自動的にストリームから削除されます。この性質がとても便利だなと感じています。
-- my_table テーブルの更新差分が記録される my_stream ストリームを作成 CREATE STREAM my_stream ON TABLE my_table;
一般的なユースケースとして、生データに対してクエリを投げると時間がかかるので、あらかじめデータを集計してレコード数を減らしておき、その集計後のデータに対してクエリを投げるということがあるかと思います。このようなケースにおいて、事前の集計をどのように行うかが課題になることがあります。ストリーム機能を使うと、例えば Snowpipe 経由でテーブルにデータが入ったとき、その差分データがストリームにも書き込まれます。あとは、INSERT INTO SELECT 文などを使って、定期的にこのストリームに対して集計クエリを投げ、その結果を別テーブルに書き込むだけです。集計に使われたレコードは自動的にストリームから削除されるため、次回の集計時に「前回どこまで処理したっけな」といったことを考える必要がありません。そのため、データが重複して集計されたり、逆に欠けたりすることなく安全な処理が可能になります。
-- my_stream ストリームに記録されたレコードを分単位で COUNT して結果を aggregated_table テーブルに書き込む --（このクエリを実行すると my_stream ストリームは一旦 TRUNCATE され、それ以降の差分が新たに記録されていく） INSERT INTO aggregated_table SELECT DATE_TRUNC('MINUTE', event_timestamp) AS minute, COUNT(*) AS event_count FROM my_stream GROUP BY minute;
さらに、この定期的な集計クエリの実行には Snowflake の タスク と呼ばれる機能を使うと便利です。タスク機能を使用すると、クエリやストアドプロシージャを決められたスケジュールに従い実行することができます（スケジュールはインターバル時間または cron 式により指定可能）。Redshift で同じことをやろうとすると、Lambda 関数を作成してそれを EventBridge 経由で呼び出すといった仕組みを構築する必要がありますが、それよりもずっと手軽にスケジュール実行ができます。
-- 定期実行タスクを作成 CREATE TASK my_aggregation_task USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL' -- 実行スケジュールを cron 式で指定（毎時 30 分に実行） SCHEDULE = 'USING CRON 30 * * * * Asia/Tokyo' WHEN -- my_stream に更新差分が 1 件以上ある場合のみ実行 SYSTEM$STREAM_HAS_DATA('my_stream') AS INSERT INTO aggregated_table SELECT DATE_TRUNC('MINUTE', event_timestamp) AS minute, COUNT(*) AS event_count FROM my_stream GROUP BY minute; -- 定期実行タスクを有効化 ALTER TASK my_aggregation_task RESUME;
実際に本番環境で使ってみて
サービスの安定性について
Snowflake の本番運用を開始してから 1 年ほど経ちますが、特にトラブルもなく安定稼働しています。
また、Snowflake では毎週のようにアップデートが行われていますが、その中には性能改善のアップデートも含まれており、何もしなくても勝手にクエリが高速化していることもあり、これが地味に嬉しいポイントだったりします。
動作変更リリースへの対応が必要
注意点として、Snowflake では月に 1 回、動作の変更を伴うアップデート が実施されることがあります。アップデートによって既存のアプリケーションが動作しなくなることを防ぐため、事前に検証する必要があります。Snowflake では、アカウントごとにこれらのアップデートの早期適用やオプトアウトを設定できます。したがって、テスト環境と本番環境で Snowflake アカウントを分けておき、テスト環境にアップデートを早期適用してアプリケーションの動作に影響がないか事前に確認する運用を行うことを推奨します。ちなみに、この 1 年間で実施された動作変更リリースでは、弊社側のアプリケーションを改修する必要はありませんでした。
-- "2023_06" アップデートバンドルの現在の適用状態を確認 SELECT SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS('2023_06'); -- "2023_06" アップデートバンドルを有効化（早期適用） SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2023_06');
おわりに
今回はデータウェアハウスの選定をテーマに Snowflake の機能や特徴についてご紹介しました。今回の分析基盤のリニューアルプロジェクトは、検討を開始してから完全に移行が完了するまで 1 年半ほどを要しましたが、リニューアル後は大きな不具合などもなく安定しており、スケーラビリティに関する不安も解消されたため、実施してよかったと思っています。
また、今回は Snowflake の機能のごく一部しか紹介できませんでしたが、この記事の反響次第では、Snowflake のより高度な使い方や、さらにパフォーマンスを高めるための手法についてもご紹介できればと思います。
※ 本稿の公開にあたり Snowflake 社よりロゴの使用許諾を得ております。
*1:サーバーレスな Redshift に対し、従来の Redshift を本稿では Provisioned と表記します。
*2:検討当時は最小のキャパシティは 32 RPU でしたが、2023 年 3 月より最小のキャパシティが 8 RPU に引き下げられています。https://dev.classmethod.jp/articles/20230310-amazon-redshift-rpu-8/
*3:検討当時の最小キャパシティである 32 RPU で 1 ヶ月間動作させ続けると 100 万円以上かかる計算でした。現在は、最小のキャパシティが 8 RPU に引き下げられており、単価も安くなっています。
*4:BigQuery 側にあらかじめテーブルを作成しておき、そのテーブルに対して BigQuery Data Transfer Service を使用してデータのロードを試みたのですが、テーブル定義とデータ構造が合わない旨のエラーがたびたび発生しました。また、転送ジョブを開始してからエラーが発生するまで毎回 1 時間近く待たされ、トライアンドエラーで進めるのもなかなかにツラい状況だったため、方針を変更しました。
*5:Redshift や Snowflake では列名のエイリアスを同一 SQL 内から参照できます。https://aws.amazon.com/jp/about-aws/whats-new/2018/08/amazon-redshift-announces-support-for-lateral-column-alias-reference/
*6:S3 のイベント通知を設定する必要はあります。具体的には、Snowflake 側で発行される SQS に対し S3 のイベント通知を送信する必要があります。
