ナカミチといいます。freeeのデータ基盤でエンジニア業に勤しむ日々です。
今回は長年freeeの分析環境を支えてくれたRedshiftをBigQueryに移行したお話。
なお技術的な詳細までは触れず、移行プロジェクト全体に関して記述しています。
(Techieな記事を期待した方スミマセンｍｍ)
移行の規模はどんなもんか
ボリューム的にはざっと下記の通りです。
テーブル数: 約2,000テーブル
データ量: 約180TB(snappy)
クエリ数: 約500件
移行期間: 約1年4ヶ月(準備期間含む)
そもそもなんで移行したの？
大別すると移行を決めた理由は3つほど。
- パフォーマンス向上が見込めた
- 手段を多様化したい
- エンジニアリソースの最適化
以下にそれぞれ細かく記述します。
1. パフォーマンス向上が見込めた
SQLによりますが、それまで使っていたRedshift環境と比べて平均5〜6倍ほどの高速化が確認できました。
これはRedshiftのインデックス(分散キー、ソートキー)設計や圧縮設計次第でRedshift側のパフォーマンスを改善することもできたかもしれません。しかし各テーブル/カラムがどのように参照されるかをある程度予測できていることが前提となるので、ユースケースを踏まえる必要がありました。
我々としてはそこにリソースを注ぐよりは、BigQueryのマシンパワーを使って半ば強引にパフォーマンスを改善した方がトータルでメリットがあると判断しました。
2. 手段を多様化したい
これまで弊社の分析環境としては基本RedashからRedshiftにつなげる一択でした(Tableauも使っていますが、社内の限定された部門のみの利用)が、それ以外の手段を提供したいという思いが予てよりありました。
具体的にはインターフェースとしてCloud ConsoleやGoogle Colaboratoryが使えるようになったり、他プロジェクトのBQエクスポートデータ(GA、firebase等)と繋げた分析ができることを目指しました。
3. エンジニアリソースの最適化
1に付随した話になりますが、これから先テーブル数や利用ユーザーが増えていくとより一層インデックス設計や圧縮設計の最適化を求められると予想しました。技術者としてその部分を突き詰めていくのはそれはそれで魅力的ですが、一方で属人化の懸念やメンテナンスに要するコストを考えると、我々としてはそのリソースをセキュリティ強化や活用強化など別の方面に振り分けたい意図があります。
結果的にどうだったか？
このブログを執筆している時点ではまだ移行して1ヶ月しか経っていませんが、その限りで結果どうかを評価してみます。
1. パフォーマンスは向上できた？
当初の目論見通りチューニングレスで数倍高速化したSQLもあれば、残念ながら逆に時間がかかるようになったSQLもあります。
後者がどのようなSQLパターンの場合に起きるのかはまだ明確になっていませんが、外部テーブル※を多数参照しているケースで見られることが多いと感じます。現状だとスキャン対象を限定するなどで乗り切れていますが、利用状況を継続モニタリングしてデータの保管形式を再考するなどより多くのSQLが高速に実行できる環境を目指していきたいところです。
とはいえRedashの利用状況を見ると、Redshift時代はRedashのキュー上で待機状態となるジョブが最大100以上となっていたのが、BigQuery切り替え後は50いかない程度とキューが掃けるのも早くなっていますので、パフォーマンス面ではユーザーに恩恵を与えることができていると言えそうです。
※ freeeのデータ基盤では毎日0:00を静止点としたスナップショットを蓄積する形でデータを貯めており、直近のスナップショットはBigQuery内部テーブル=ネイティブテーブルに保存し、過去スナップショットは外部テーブルで見られる形式となっています。この辺のデータ基盤構成の細かい話はまた別の機会に。
2. 手段は多様化できた？
Cloud ConsoleとRedashの使い分けについては現状では使い慣れたRedashを引き続き使っているユーザーが多いようです。
ただ徐々にシンタックスチェックやドライランスキャン、プレビューといったCloud Consoleのメリットが周知されつつあるので、引き続き各ユーザーの利便に適った使い分けを訴えていきたいと思います。
また他プロジェクトのBQエクスポートデータと繋げた分析ニーズの声も高まってきており、手段の多様化という目的を徐々に成しつつあります。
3. エンジニアリソースは最適化できた？
上述の通りチューニングは特に気にする必要はないのでその点は想定通りでした。
一方で管理運用面に関しては長時間SQLの検出やジョブ一括killなど機能としてBigQueryに備わっていない部分を自前でHackする必要があり、走り始めはそれなりにリソースを割いて運用を固める必要がありました。
・
・
・
全体として概ね当初の目論見から外れることはなくBigQueryを使い始めることができていますが、やはり運用していく過程で課題はパラパラと出てきている状況です。
移行の全体像
移行は検証を2020年10月から始め、Redshiftの停止が2022年2月だったので全体で1年4ヶ月ほどかかりました。全体の流れとしてはざっと以下の通りです。
ポイントとなった部分をいくつかピックアップします。
一部ユーザー向けプレオープンの位置づけ
全社向けのBigQueryオープンが2021年10月中頃、Redshift停止が2022年1月いっぱいのスケジュールだったため、本来は約2.5ヶ月がSQL書き換えに使える時間でした。
ただRedashに保存しているSQLは重要度で大別すれば、数字ズレの一切許容できないものとそうでない(多少ズレがあっても差し支えない)ものがあります。
前者について約2.5ヶ月ですべての数字を合わせるには、本業を停止してSQLコンバートにひたすら専念する必要があるぐらいには工数を要する見込みでした。そこで全社的なSQL移行に先駆けて一部ユーザーに限定して環境を解放することで、移行期間を通常より長く取って優先的に移行を進め、移行ギリギリになって重要クエリが移行できてない！という事態の発生を未然に防ぐことにしました。(結果的に1、2件ほどRedshift停止直前で移行できていないことが発覚しましたが、逆に1、2件で済んだとポジティブに見ています)
SQLの移行
弊社ではSQL実行インターフェースとして専らRedashを使っていますが、これまでにユーザーが書いたSQLはRedshift向けのもので大抵のSQLはBigQueryで動かないため書き換えを行いました。
Redashには定期実行SQLとアドホックSQLの2つがありますが、今回は定期実行SQLを移行対象とし、アドホックSQLは任意としました。
アドホックSQLは他人の作ったSQLを単純にコピーしたものや明らかにテスト用のSQLも多くあったこと、また重要なSQLは定期実行SQLとして作成される傾向があることから、一部要移行と判明しているものを除いて悲鳴ドリブンを前提としました。
従量制 -> 定額制へ切り替え
全社にオープンする少し前にBigQueryの課金体系を従量制から年定額スロット制に切り替えました。ユーザーがSQLを叩く際に料金を気にする必要がない、基盤コストをコントローラブルにしたいといった目的からです。
これでリソース使用料については手放しで問題なしですが、計算リソースについてはユーザー全員の共用で使っていくことにはなります。そのため弊社では長時間実行してリソースを専有しているSQLがないかを定期的にチェックし、オペレーションでジョブを停止するツールを自前作成して運用しています。
反省点
今回の移行プロジェクトは反省点がモリモリです。いくつか取り上げます。
セキュリティ運用強化を移行スケジュールに組み込んだ
従来よりデータ基盤ではデータ連携を開始する時点でそのテーブルやカラムにどのような値が入ってくるかを人力でチェックする運用は存在していましたが、それを継続的/機械的にチェックする運用はありませんでした。
BigQuery移行を機にその定期自動チェック部分の強化をマイルストーンに置きましたがスケジュール的には結構しんどい時期でした。ルール策定、実装、運用を固めるところまでかなり短期間で行ったため、チームに疲弊をもたらした感は否めません。移行とは切り離し、しっかりとセキュリティ運用が固められてから移行を進めた方が筋は良かったのかなと個人的には思います。
運用の検証が荒かった
既に触れましたが、管理運用面に関してBigQueryに機能として不足した部分があることが移行過程〜移行後に判明してきました。
現状は諸々自前でHackして乗り切っていますが、事前にどのような運用ケースが想定されるかを洗い出しきれていなかった点は次回無いようにしたいです。
SQLのユースケース検証が不十分
移行期限かなりギリギリのところで以下のようなエラーメッセージを出すSQLがいくつか出てきました。
「Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex..」
今回は運良くBigQuery Scriptingによるワークアラウンドを提示できましたが、かなりギリギリのところで発覚したので正直焦りました。もっと早期にこのケースを発見して解決しておくべきだったという懺悔。
最後に
味方の多さが決め手
移行作業は旗振りやシステム移行こそデータ基盤が中心となって進めてきましたが、大きな事件なく移行が完了できたのはチームを超えてたくさんの人が味方に付いてくれたことが大きかったと思います。前職でも小さいながらいくつかの基盤移行を経験しましたが、各方面の理解と協力が得られていることは成功するファクターの1つだと思っています。
- 初期の検証から最後まで長らく二人三脚してくれたAnalyticsチーム
- 巨大なSQL移行に果敢に挑んでくれた事業企画チーム・SaaS Navigatorチーム
- コスト部分で親身に相談に乗ってくれたIT Strategyチーム
- その他SQL移行に協力してくれたユーザー各位
非常に多くの皆さんに助けてもらいました。ご協力ありがとうございました！
これからの進化
今回のBigQuery移行は「データ基盤を継続的に進化させていく」というテーマの議論で生まれた、進化のための足がかりでしかありません。実際BigQueryに移行しただけで「データ基盤進化したよね」とユーザーに感じてもらえてはいないと思っています。
ここからどう進化させていくか。これまでデータ基盤ではあまりできていない部分でしたが、ユーザーの利用状況や日々流れるクエリをしっかりモニタリングして方向性を見定めていきたいと考えています。
これから先もfreeeデータ基盤の進化にご期待ください！
