Tポイント分析基盤の歴史とSnowflakeへ移行した話③
記事の目的
レガシーが多いシステムをモダンアーキテクチャに移管するのは非常に労力を伴いますが、どのように移行したかの具体事例や、特に途中の労苦を語ったものは少なく、皆様の参考になればと思い、恥ずかしい話もありますが勇気をもって投稿します！
TポイントからVポイントへ2024/4/22にブランド名が変わってますが、
当時の話なので、Tポイントで記載しています
前回までの話
分析DB・基幹DBのsnowflake統合
2021年に図表下部の商用分析ASPサービスの基盤をsnowflakeに入れ替えましたが、
2022年から、いよいよ本丸の分析DB（分析基盤）の統合に着手しました。
DBダブル移行＆クラウド引っ越し
基幹DB：OracleExadata ⇒ Snowflake
・10年以上手つかずのPL/SQLなどの基幹処理群
分析DB：AzureSynapseAnalytics ⇒ snowflake
・3年がかりで移行した基盤をさらに再移行
分析ASP：Snowflake-AWS東日本 ⇒ snowflake-Azure東日本リージョン
・前回Verticaからsnowflake化したが、AWSからAzureへのクラウド間お引っ越し
Oracle、Synapseの2つのDBをSnowflakeへ統合し、
またタイミングの事情で、AWS東日本リージョンで稼働していた分析ASPのsnowflakeをAzure東日本リージョンへの移管も実施。
シングルソース、マルチワークロード、デリバリーイージーのビジョンを達成するためとはいえ、
なかなかのプロジェクト規模になっていました。
プロジェクト計画
Sypanseからのsnowflake移行とOracleExadataのsnowflake移行に辺り、
その分析DBと基幹DBを併せると1万件近いテーブル数があるため、
その多くは重複しているため、この統合や更新ルートの整理を行いました。
その結果、分析DB(Synapse）をメインDBとしてSnowflakeへ移行し、
そこへ基幹DB（Exadata）を後追い移行する方が移行方式がシンプルで工数も低く、適切だと判断しました。
これを元にオンプレのEOLタイミングを考慮して、以下のステップで進める計画としました。
22年03月：Exadata、Synapseのsnowflake化PoC（互換性、性能評価）
22年09月：snowflake化PoC評価、その後、投資コストやインフラコスト諸々整理
23年03月：分析DB-Snowflake化を開始
24年01月：分析DB-Snowflake化を完了（※24年3月まではSynapseと並行期間）
24年03月：基幹DB-Snowflake化を開始
25年03月：基幹DB-Snowflake化を完了
25年06月：OracleExadataの停止と廃棄
※Synapseは25年6月まで3年R/Iがあるため、中途解約
うーん、また綱渡り・・
PoCだらけ
4つの領域に分けて、それぞれの移行可否を事前評価しました。
システム①：基幹DBのSnowflake化によるクエリの移植性、性能評価、データ移行方式
システム②：分析DBのSnowflake化によるクエリの移植性、性能評価、データ移行方式
システム④：AWS→Azureの移行によるインフラ性能（パフォーマンスなど）
アナリスト：分析DBのSnowflake化によるクエリの移植性、性能評価
〇×によっては完成形が変わるので、それぞれを並行で評価を実施しました。
システム①＆②：OracleExadata、Synapseの評価観点
基幹DBの移植性や性能評価とまた分析DBも基幹DBからの連携部分も含め、
保守をお願いしているベンダーさんにて評価をしてもらいました。
その際にデータ群としては重複を除き、評価パターンに応じた数百テーブルを移行して評価を実施
①SQL互換性の評価：Oracle、Synapseからの互換性
②処理性能評価：各主要クエリの性能比較
③ETL検討：perlやShellなどのレガシープログラムの移行検討
④データ移行方式：データの抽出から取込までの実現性および想定時間、規模
⑤インフラ比較（AWS/Azure）：お引越しに伴う性能評価（何か劣化しないか？）
結論
移行に当たっての課題はなしと判断しました。以下詳細です。
①SQL互換性の評価：〇
・Oracle
非互換で修正すべきパターンは累計54種
やっぱりOracleは癖強いよなー。とにかく直せばいいという意味では〇（笑）
この辺りの具体内容はこちらに纏めています。
・Synapse
非互換は15種で、概ね想定内。癖は少な目
②処理性能評価：〇
・Oracle
ヘビークエリであればあるほど改善されている
ショートクエリ（1分以内）は若干劣化（これはExadata早い）
クラスタリングキーを貼る事でさらに改善
・Synapse
・セグメント配信用の機能などパフォーマンスが要求されるアプリで評価
これらはSynapseの10000DWUと比較してLサイズで150%の速度向上
・ただし、前回のPoCでもあった会員軸で一部悪化があったがトータルでは向上
Synapseの会員キーでのハッシュ分散はやはり速いところは速い結果に
クラスタリングキーでも完全突破とはいかなかったが、
SearchOptimizerionやSnowflake自身の性能向上でリリースまでには解決可能
場合によってはこれらの処理だけXLにすればよいという見切りで〇しました
③ETL検討：×
・Oracle
AzureDataFactoryを使っていることもあり、Perl系の置き換えを検討したが
オンプレでかなり作り込みをしている数百テーブルについては
置き換えコストが高すぎてADFへの移行は断念
モダン化したかったので、それを断念した事では、評価は×ですが、
レガシーながら処理として最適化され、変更も少ない部分のため、
工数をかけてまで注力すべき箇所ではないと判断しました。
・Synapse
これはOracleと統合する前提のため個別の評価は実施せず。
ロード処理の改修が中心のため、数は多いものの難易度のある改修はほぼありませんでした。
④データ移行方式：〇
Oracle
・Synapseの分析DBをメインとするため、差分のデータ群のみ連携という事で
特に難度の高い作業はなし
ただし、各プログラムの動作検証は相当な規模
Synapse
・今回は大量の移行が発生する事もあり、ETLツール等でやるよりも
sqlcmdを共通プログラム化するのが処理速度的、開発効率的にも良く、結論は力技で行くと判断
・ファイル形式はParquet形式の方が抽出、書込ともに早かったのだが、
CSV形式がParquetよりも記述が簡便で済むため、開発生産性を優先し、CSVに決定
⑤インフラ比較（AWS/Azure）：〇
・試験内容としては、2年前の分析ASPのPoCデータでAzure上に環境構築し、
前回のクエリを再度実行したところ、なんと前回PoCより2倍速くなっていました。
当時AWSが先行でEC2のスペックを上げているという話を聞いていたので、
速度劣化するかもという話をしていましたが
そもそも2年前より早くなっているのであれば問題なしとしました。
ほんとは純粋にAWSとAzureの比較をしたかったのですが、
互換性の精査やら移行方式の検討に時間を要したため、断念。
2年で2倍速くなったのは衝撃過ぎて、Synapseの進化を待つ意見は全くなくなりました。
またこの頃、AzureSynapseAnalytics-Gen3の計画が凍結されたと風の噂で聞き、
Synapseを引っ張らず、Snowflakeを選択して良かったなと思いました。。
アナリストとのPoC
Synapseは前回の移行時にアナリストの分析パターンの検証が不足しており、性能課題で大きく躓いた反省点がありました。
そのため、アナリスト部門には分析ASP移行時から先行して、
snowflake環境を提供し、慣れてもらい、使ってもらい、入れたいと思ってもらうことを優先していました。
その時点で速さや手軽さを体感してもらい、導入意欲を持たせる事が出来ており、
今回、実際のPoCに当たっては、アナリスト部門より評価パターンやストリーム、そのストリームの実行に必要なデータ群を整理してもらいました。
IT側のPoCで用意したデータ＋アナリストの評価で必要なデータを追加し、
ストリームのパフォーマンスや結果整合性の評価はアナリスト部門主導で行うものとしました。
アナリスト側のSPSS Modeler（早く葬って）でのPoCに辺り、ODBCドライバーをインストールし、環境設定などを実施
DBへの接続やロールの設定などを行ったうえで、以下4つの観点で評価を行いました。
①各ストリームをDBを切り替えてオブジェクトが認識できるか
⇒これは概ね問題なく完了
②切替後のストリームが正常に実行出来るか
⇒UDFなど整備したこともあり、設定変更などは発生しましたが問題なく対応可能
③処理速度は現状同等もしくは向上しているか
⇒概ねは問題なく、性能同等かそれ以上だったが、一部極端に劣化しているものが発生
④出力結果は妥当か
⇒ここは浮動小数点の取り扱いなど若干の差はあるものの問題なし
劣化したクエリ
性能劣化したクエリはいくつかパターンがあり、それぞれの対応を図りました。
一時テーブルの利用
Synapseでは性能対策として、クエリ内でメモリ上に展開する一時テーブルを多用していましたが、
snowflakeでは劣化するケースが多く、一時テーブルの使用を辞めてもらい、
ダイレクトに集計するように変更してもらいました。
その結果、ほとんどは現行以上に処理時間が短縮されたこともあり、
残りもストリームの修正等で解消したため、問題なしとしました。
Window関数(SUM)
関数周りではwindow関数のsumのみ、明らかに劣化しました。
何等かオプティマイザの問題なのか、異様に遅く、最終的にこの関数の利用は非推奨とし、
ストリームを修正する事で対応してもらいました。
クラスタリングキーの追加
その他も一部劣化したものも、クラスタリングキーの設定などで解消しました。
クラスタリングキーの注意事項
クラスタリングキーはパフォーマンス向上に非常に有力ですが、
文字列については注意事項があり、貼り方に工夫をしました。
具体的には文字列の場合、先頭5byteでクラスタリングされるという制約があり、
弊社の場合、レガシーなデータ群で日付を文字列型で保持しているテーブルがありました。
そのため、YYYMMDDのフィールドで先頭5byteの場合、YYYYMでクラスタリングされるため、
20240101～20241231の場合、
簡単に言えば、クラスタリングとしては、20240（202401～202409）、20241（202410~202412)
といった形になり、プルーニングが劣化していました。
そのため、Substring（EIGYOU_DATE,3,5) YYMMD（24010～24123）といった形式に変えました。
あくまでもクラスタリングキーの貼り方を変えるだけなので、データの整合性は影響を受けず、プルーニングは効くようになり、クエリ性能は改善されました。
アナリストの最終評価
このような細かいながらも、少しでも躓きがある事で、なかなか”問題なし”と言い切ってくれないPJメンバーに対し、コミットを求めたい気持ちと前回の苦労もあり確実な判断をしたい気持ちで強く出れない状態でもありましたが、アナリスト部門長と話し合い、最終的に各リーダーから、"問題なし、移行可能"と結論を出してもらいました。
これによりシステム部門およびアナリスト部門として、snowflake化を決断し、最終的な社内承認に進みました。
移行プロジェクトの開始
事業承認にどんな情報が必要かはその企業によりますが、
かつての上司から「システム部門は3つの言葉を話せないといけない。」と言われてきました。
その時の３つの言葉というのが以下でした。
・システムの専門家としてITとしての妥当性や最適であることを話せる事
・事業と同じ言葉で事業価値を話せる事
・財務会計の言葉で、会計インパクトを説明できる事
・IT観点
PoCの結果より、単純な性能的な向上＋アーキテクチャとしての拡張性や将来性
オンプレ更改に向けたロードマップや基盤としての統合価値
Synapse廃止により、Azureの利用費が下がる事によるMicrosoftとの次回契約更新時の交渉観点
・事業メリット
・性能面やBIなどのワークロードへの対応など事業メリット
・今までよりもデータデリバリーが早くなること
・マーケットプレイスなどのオープンな基盤であること（これ、当時はあんまり刺さらなかった）
・会計メリット
・移行開発工数に対する、Synapseからsnowflakeへの乗り換えによる実削減コスト
・並行稼働中の重複コストやソフトウェア資産の除却などの会計観点も含めたP/L試算
・保守運用や効率性などに関する関連コストの削減効果
・エモい情報
・デカコーン企業としての市場の期待値やいくつか定性的なインプット（アーリーアダプタ採用）
・自分自身がsnowflakeを選んだ理由（まだ実績が少ないサービスでも採用したい）
・社内で勉強会を実施した結果の報告（みんな導入して欲しいって!!）
などなど、それぞれの観点でまとめ、大きな指摘もなく、まずは分析DBのsnowflake化をPhase1として承認され、2023年3月よりプロジェクトを開始しました。
プロジェクトの進行
PrivateLinkなどのセキュリティ要件に沿って再設計を行って環境構築をしつつ、
SynapseからSnowflakeの移行に伴う数千テーブルをBlobストレージにファイル出力、
それをsnowflakeへ移行しつつ、
並行でロード処理などのDML変更を行っていきました。
ある程度データが蓄積されたタイミングから、アナリスト内のリードメンバーなどに接続確認を行ってもらい、動作に支障がない事を確認
またRBAC設計に基づき、ロール設計を行い、ユーザーIDの発行も開始
毎週プロジェクトの定例の中で検討⇒決定⇒設計⇒実装のサイクルを回しながら順調に進行していきました。
本番移行に向けた課題
秋になり、本番移行に向けた最終段取りを確認していくと一つ問題が発生しました。
システム部門では数千テーブルの大半の移行は完了しましたが
アナリスト部門や事業部門のエンジニアが独自に作成したデーターマート群もあり、これらの移行も並行で進めました。
ただ業務の合間で行っていたり、システム部門の移管が終わらないと移行出来ないテーブル群もあり、
この辺りのクリティカルパスのハンドリングが甘く、移行がスタックしてしまいました。
データが揃っていないとストリームの動作確認や結果確認が出来ないため、
アナリスト部門より、IT部門側の作業遅延で移行不可と言われてしまいました。
リーダーからは、大丈夫です！間に合わせます！などの報告を受けていましたが、
実際の移行リストを確認するとほぼほぼ真っ白という事が分かりました。
色々案件が重なり、業務負荷が高い時期だったので、出来ていない事を出来ているといった点は叱った上で、リーダーと一緒にEXCELの移行未実施の数百テーブルを眺め、
システム側で先に実施しないといけないもの、アナリスト部門でも優先的に移行しないといけないもの、もしくは実際は利用していないので移行対象外として良いもの などを一つずつ色分けし、優先順位に従い、IT内で担当を振り直し、移行作業の目途を立てました。
その上でアナリスト部門や関係部門にも改めて依頼し、テーブル移行や依存するマート系などの移行が完了し、何とか1カ月遅れで実利用を開始してもらいました。
この辺り、現物確認するというのはいつになっても大事だなと思いました。
一緒に棚卸しする中でリーダー自身も優先順位や仕事の可視化など意識してくれるようになり、元来、人に好かれるタイプなので、各ステークホルダーとも関係値を作り、後は任せられるようになりました。(お父さん目線)
またこの間に平行して、分析ASPのAWSからAzureへのクラウド移行を完了させました。
技術的にはAWSからAzureへDataReplicationで同期を取り、
その後、システムをメンテナンスモードにした上で、Replicatonを切って、
Azure側をPrimaryに昇格させ、切替を実施しました。
切替後の性能もPoC結果通りの結果となり、これは本当にサクッと完了しました。
分析DBの移行完了
データ移行で一部遅れはあったものの、その間に分析ASPに切替を終え、
11月よりアナリストの分析業務はSnowflake上で稼働し始めました。
性能的な課題はなく、snowflakeのコストも想定より少し少ないぐらいで稼働し、
アナリストの稼働に合わせ、Synapseで運用していたセグメント配信などの基幹システムもSnowflakeへ置き換えし、こちらも問題なく稼働しました。
そして並行期間を当初24年3月末までとしていましたが、
あまりに順調すぎたため24年1月頭よりSynapseのスペックは半分に落とし、1月末に完全に停止しました。
PoC以降もsnwoflake自身のクエリパフォーマンス改善なども定期的に行われており、
各アナリストのストリームも順調に捌け、すんなりと運用出来ており、Synapseの際の苦労は本当にマネジメント不足だったなと痛感しました。
そしてプロジェクトは次のフェーズとして、OracleExadataの完全廃止とSnowflake移行に進んでいきます。
分析DBのSnowlflake移管まとめ
・SynapseからSnowflakeへの移行は無事完了
・Synapseから数千テーブルを移行（棚卸後）
・アナリスト100名がそれぞれ実行する膨大なストリームは全てSnowflake接続へ置換完了
・IT部門のETL処理等もSQLを置換し、移行完了
・クラスタリングキーは必要に応じて貼る程度
・PoC時点でSynapseより劣後していた会員軸のセグメント処理も劣化なし（Snowflakeの性能向上）
・アナリスト向けには、Lサイズ
・ロード処理は、M or Lサイズで実施。一部重たい処理だけ個別にXLサイズを割当
・社外向け分析ASPやBIツールもAWSからAzureのsnowflakeに移行し、それぞれ競合せずに稼働
そしてSnowflake化のラストマイルへ
snowflake化のプロジェクトは、分析ASPから始まり、既に3年ぐらいやっているのですが、
私自身は気が付けば、分析基盤以外の基幹システムや2Cサービス、Webフロントやデータサイエンス部門などほかのテック部門も管轄になってしまい、移管プロジェクトはPJメンバーに任せて行かざるを得ず、寂しさと心配な気持ちはありつつも一緒にPJする中で成長したリーダーや次世代メンバーへバトンタッチをしながら、プロジェクトを見守る立場になっていきました。（がんばれ、みんな！）
一昨日もプロジェクトが順調に進んでいることの報告を受け、
これからのテスト工程での注意ポイントやその手前で統合後のジョブスケジュールの精査を確実に済ませる事などを伝え、主体的に取り組んでくれているメンバーに感謝しながら、会議を終えました。（急にエモい？）
分析基盤の現在地（完成予定図）
ここから先は現在進行形のお話なのですが、
上記のプロジェクトと並行して取り組んでいた、Databricksの導入や新会社のイチからの分析基盤構築なども書いていきたいと思います。
Discussion