7年使ったRedshiftから6ヶ月かけてSnowflakeへ移行した話 〜手の内全部お見せします〜

SNOWDAY JAPAN 2023で「7年使ったRedshiftから6ヶ月かけてSnowflakeへ移行した話　〜手の内全部お見せします〜」というタイトルで登壇した資料です。
https://www.snowflake.com/about/events/snowday-japan-2023/?lang=ja

独自のテレビ視聴質データを利用したCM効果分析サービスを提供するREVISIO株式会社の片岡が、7年間使用してきたRedshiftからSnowflakeへ移行した際の手法やツール、検証内容や両DWHの差異などについて詳しく語りました。

スライド内で発表した移行ツールはOSSで公開中です。
https://github.com/tvision-jp/redshift-to-snowflake-migration-utils

https://revisio.co.jp/

Transcript

1. 7年使ったRedshiftから 6ヶ月かけてSnowflakeへ 移行した話 〜手の内全部お見せします〜 2023.2.14 REVISIO株式会社
2. こんな方向けに 1. RedshiftからSnowflakeに移行すべきかどうか検討中 2. どんな差異があるのか？移行コストが高いのか？を知りたい 3. 移行するにはどんな手順で進めればいいか知りたい 4. どんな問題があってどう解決すればいいのか知りたい 5. （これから新規構築するのにどう選択すればいいか迷っている） 2
3. コンテンツ 1. 自己紹介・会社紹介 2. 移行の背景 3. 移行手順詳細と完了までの流れ 4. 移行後の振り返りと今後の期待 3
4. 自己紹介 REVISIO株式会社 エンジニアリングマネージャー 片岡 基 SIerでエンジニアとして受託開発に従事した後、スポーツテック企業へ。 プロサッカーの試合データを分析するプロダクト開発をリードし、2020年にREVISIOにジョイン。 メインプロダクト「Telescope」の開発や、全社データ基盤の開発・運用をプレイングマネージャー としてリードしている。 今回の移行プロジェクトでは全体を統括。使い込むごとにSnowflakeの魅力を実感中。 4 ♡ 浦和レッズ、タイ料理、ソフトウェア開発、筋トレ、Twitter、マリオカート ♡
5. REVISIOのご紹介 メディアプランニングから出稿の振り返り、クリエイティブ制作まで 広告主・広告会社・放送局の皆様にお使いいただいています 人体認識技術搭載センサーで自動計測 24時間365日、どの番組やCMが見られているのかという テレビの「究極の視聴率」データを提供 REVISIOは、テレビの視聴の質を測っている会社です。 「テレビの前に誰がいて、注視しているのか？」がわかります。 5
6. CM効果分析SaaS 独自データを軸に様々なサービスを提供 マーケティング分析 コネクテッドTVデータ提供 CM制作～出稿・振り返り 6
7. 7 ナショナルクライアント スタートアップ *2022年10月時点、ご利用企業様には放送局様や広告会社様も含みます ご活用企業の一例
8. 移行の背景
9. 【データ基盤概要】 データサイズ：15TB（圧縮後） テーブル数：510 VIEW数：196 ETL/ELT数：11種類 BIツールユーザー：約20名 データウェアハウスを中心としたシステムアーキテクチャ 9 ※2016年から24時間365日毎秒単位のデ ータを保持しているためデータ量が大きく なっている
10. 当社のRedshift周りの課題 • ビジネス拡大 ＝ データ量・ユーザー・ワークロード増加 → 処理速度・安定性が低下 😨 • それをカバーするためにスケールアップ → コストアップ 😵 • クラスター管理やパフォーマンス・チューニングの運用負荷 → 社内のエンジニアがうまくコントロールできていなかった 😢 10
11. 新たなテクノロジーへの期待 • 最近話題のSnowflakeが高速、安価、ニアゼロメンテナンスと聞く → 課題を解決できるのでは？ 🤩 🤩 🤩 • ちょうどRedshift Serverlessもリリースされる → 比較検証してどちらかに移行する方針に 🤩 🤩 🤩 11
12. 移行チーム データサイエンティスト2名 データエンジニア5名 リーダー 片岡 12 Webエンジニア1名
13. タイムライン 2021年 10月 2022年 4月 2022年 6月 2022年 7月 2022年 8月 2022年 11月 2022年 12月 2023年 1月 2022年 9月 2022年 10月 2022年 5月 情報収集 本格的に検討開始 トライアル開始。 同時並行で Redshift Serverless も検証 トライアル終了 全システム Snowflake対応版 開発・テスト 全データ移行実施、 Redshift/Snowflake 並行稼動開始 12.8 Snowflake へ完全移行完了！ 13
14. 移行手順詳細と完了までの流れ
15. タイムライン 2021年 10月 2022年 4月 2022年 6月 2022年 7月 2022年 8月 2022年 11月 2022年 12月 2023年 1月 2022年 9月 2022年 10月 2022年 5月 15 情報収集 本格的に検討開始 トライアル開始。 同時並行で Redshift Serverless も検証 トライアル終了 全システム Snowflake対応版 開発・テスト 全データ移行実施、 Redshift/Snowflake 並行稼動開始 12.8 Snowflake へ完全移行完了！
16. トライアル確認事項の洗い出し 16 1. データ移行方法とその速度 2. 既存の各システムで利用しているRedshift用のSQLが実行可能か 3. 〃SQLが同一結果になるか（特に関数） 4. 〃速度が速いか（単一SQL実行時、複数SQL同時実行時） 5. コスト試算 6. Pythonで問題なく利用可能か 7. Redash（OSSのBIツール）で問題なく利用可能か 8. AWS Data Pipelineの代替を何にするか（レガシーでSnowflake未対応） 9. マテリアライズドビューが適切に自動更新されるか（Redshiftで苦労していた） 10. Snowflake管理画面の機能・使用感確認
17. 約1ヶ月半の検証の結果、充分に良い結果となり、Snowflakeへ移行決定！！ トライアル結果 17 1. ◎データ移行手法の確立と速度の見通しが立つ 2. ◦Redshiftで使用していたSQLは一部エラーとなり変更が必要となったが、変更可能と判断 3. △SQL仕様の差異により、同一結果にならずそれなりの数変更が必要となった 4. ◎SQL処理速度は十分に速い。特に複数同時実行時の速度に満足（マルチクラスター使用） 5. ◎当社のワークロードを実際に実行してRedshiftより大幅に安価になる試算が出た 6. ◦Pythonでの利用は、メタデータの大文字小文字差異やDjangoでの対応が必要となった 7. ◦RedashでマルチステートメントのSQL実行がエラーに。RedashをForkして解決 8. ◦AWS Data Pipelineの代替はSnowflakeのサーバレスタスク（タイミングよくGA）を採用 9. ◦マテリアライズドビューはバックグラウンドで更新され、常に最新になる仕様で問題なし 10. ◦Snowflake管理画面はシンプルで使いやすい。特にクエリプロファイル（実行計画）が良い
18. 項目 Redshift Serverless Snowflake 課金単位 1秒（SQL実行中のみ課金） 1秒単位（最低60秒。SQL実行がないと自 動休止。ウェアハウス実行中のみ課金） 処理速度とコスト（一番重い日次 バッチ1回分で検証） 基本RPU=128で5時間40分。コストは $411 サイズS（マルチクラスター）で4時間25 分。コストは約$202 👍 サイジング 基本RPUで調整。ワークロード別にワー クグループを複数立てる等で最適化 ウェアハウスサイズで調整。ワークロード 別にウェアハウスを複数立てる等で最適化 スナップショット復元時間 （リカバリー／テスト環境作成） 本番スナップショットから復元するのに 17TBで10分程度 本番データベースをクローンして7分程度 👍 休止状態からの起動時間 約10秒 約1秒 👍 Redshiftクラスターからの移行 非常に簡単。データ移行はスナップショ ット復元。各ワークロードでのSQL変更 は不要。接続先変更のみ。 👍 かなり工数がかかる。データ移行や各ワー クロードでの検証・変更に時間が必要。 ※2022年6月頃の情報です Redshift ServerlessとSnowflake比較概要 18
19. タイムライン 2021年 10月 2022年 4月 2022年 6月 2022年 7月 2022年 8月 2022年 11月 2022年 12月 2023年 1月 2022年 9月 2022年 10月 2022年 5月 19 情報収集 本格的に検討開始 トライアル開始。 同時並行で Redshift Serverless も検証 トライアル終了 全システム Snowflake対応版 開発・テスト 全データ移行実施、 Redshift/Snowflake 並行稼動開始 12.8 Snowflake へ完全移行完了！
20. 移行において問題となるRedshiftとSnowflakeの差異 20 • テーブル定義DDLのDISTSTYLE、DISTKEY、SORTKEY、ENCODE等はSnowflakeには存在しない • Redshiftではテーブル名、カラム名等のメタデータは小文字、Snowflakeでは大文字 • RedshiftではDATE型を整数型と比較できるが、Snowflakeではできない • RedshiftではROW_NUMBER()のOVER()とORDER BYを省略できるが、Snowflakeではできない • Redshiftでは整数同士の除算が整数で返るが、Snowflakeでは小数で返る • RedshiftではLISTAGG関数で一件もヒットしない場合NULLを返すが、Snowflakeでは空文字 • RedshiftではDATEDIFFの他にDATE_DIFFが使えるが、Snowflakeには存在しない その他多数...
21. 移行において問題となるRedshiftとSnowflakeの差異 BigQueryやRedshiftには、他DWHからの移行用SQL変換ツールが公式で用意 されているがSnowflakeには・・・ない。 であれば・・・ 21
22. SQL変換ツールを開発 SQL（DDL/DML）を記述したテキストファイルを読み込み、Snowflakeとの差異部分を 自動変換して別のファイルに出力するPythonツールを開発。 SnowflakeのScripts Libraryにあるsql2sfをベースに拡張した。 https://community.snowflake.com/s/scripts 80個の差異の変換に対応。複雑な変換はできないので人間がやるものと分けて進めた。 22 SQLファイル Redshift用 SQLファイル (DDL/DML) SQL変換ツール (Python) SQLファイル Snowflake用 SQLファイル (DDL/DML) 読み込み 出力
23. SQL変換ツール内容 23 import re date_diff_re = re.compile('(.*)¥ (DATE_DIFF)(.*)', re.IGNORECASE) # 変換パターンごとに正規表現を用意 for line in src_sql: # 変換元のSQLを全行見ていく sql = line.rstrip() result = date_diff_re.match(sql) if result: pre = result.group(1) clause = result.group(2) sql = f"DATEDIFF{clause}" # DATE_DIFFをDATEDIFFに置換 comment = append_comment(comment, pre + clause, no_comments) DATE_DIFFをDATEDIFFに変換する例 SELECT DATE_DIFF(minute, x_hour, y) AS diff, FROM ... SELECT DATEDIFF(minute, x_hour, y) AS diff, FROM ...
24. SQL結果データ比較ツールも開発 24 SQL結果データ 比較ツール (Python) Redshift 2.SQL実行 4.SQL実行 比較結果 (Excel) 5.出力 SQLファイル Redshift用 SQLファイル (SELECT) SQLファイル Snowflake用 SQLファイル (SELECT) 1.読み込み 3.読み込み 一致しない！ →調査→差異発覚 →SQL変換 の繰り返し
25. （参考）RedshiftとSnowflakeのSQL実行速度比較 25 当社の実際のSQL43個で比較した結果 Snowflake 39勝 Redshift 4勝 [実行環境] Snowflakeウェアハウス：S（マルチクラスター） Redshiftクラスター：ra3.4xlarge 7ノード ※Redshiftのチューニングがうまくできてなかった せいかも・・💦 Snowflakeのチューニングは比較的シンプルで分か りやすく、改善しやすいと感じる👍 単位：秒 （合計84CPU、672GBメモリ）
26. Snowflakeのパフォーマンスチューニングキーワード 26 • ウェアハウス数と振り分け・・当社では開発用含め6個使用 • ウェアハウスサイズ（スケールアップ／ダウン）・・1つだけMでその他はS • マルチクラスターウェアハウス（スケールアウト／イン）・・オン（SQL同時実行性能アップ） • テーブルのクラスタリング・・特に重いテーブルに設定。効果大 • マテリアライズドビュー・・特に重いSQLで使用 • クエリアクセラレーション・・オフ（検証&検討中） • 各種キャッシュ・・キャッシュ利用されるようSQL組み換え等 • サポート・・チューニングの相談も受けてくれる 参考：https://select.dev/toronto-snowflake-user-group-cost-optimization/index.html
27. タイムライン 2021年 10月 2022年 4月 2022年 6月 2022年 7月 2022年 8月 2022年 11月 2022年 12月 2023年 1月 2022年 9月 2022年 10月 2022年 5月 27 情報収集 本格的に検討開始 トライアル開始。 同時並行で Redshift Serverless も検証 トライアル終了 全システム Snowflake対応版 開発・テスト 全データ移行実施、 Redshift/Snowflake 並行稼動開始 12.8 Snowflake へ完全移行完了！
28. 全データ移行 28 ２つの選択肢を検討 • データパイプラインサービス（Fivetran、Airbyte等）の利用 • S3経由でRedshiftからエクスポート・Snowflakeへインポート SQL変換ツールで DDLをSnowflake版 に変換 Snowflakeにテーブ ル・VIEW作成 Redshiftから Parquet形式でS3に エクスポート （UNLOAD） Snowflakeで外部テ ーブル（中身はS3の ファイル）を作成し、 一括INSERT ParquetはCSVより高速でフ ァイルサイズも軽い 外部ステージを使う方法もある が外部テーブルの方が高速。 ウェアハウスサイズを上げると ある程度までは倍々で高速化。 → 実際に試して手順のシンプルさと速度面から、こちらを採用
29. UNLOADからINSERTまでのコードサンプル 29 -- RedshiftからS3にUNLOAD（エクスポート） UNLOAD ('SELECT * FROM public.sample_table') TO 's3://バケット名/****/20221206/public/sample_table/' FORMAT AS PARQUET PARALLEL ON MAXFILESIZE AS 250MB IAM_ROLE 'arn:aws:iam::****:role/****'; -- SnowflakeでTRUNCATE TRUNCATE TABLE public.sample_table; -- Snowflakeで外部テーブルの作成（※外部ステージPROD2PROD_SNOWFLAKE_MIGRATION_STAGEは先に作成しておく） CREATE EXTERNAL TABLE public.ext_sample_table_20221206 WITH LOCATION = @public.PROD2PROD_SNOWFLAKE_MIGRATION_STAGE/20221206/public/sample_table/ FILE_FORMAT =(TYPE=PARQUET); -- Snowflakeに外部テーブル経由でINSERT（※外部テーブル作成後S3の読み込み完了までタイムラグあるので注意） INSERT INTO public.sample_table SELECT -- ※ここは*にできないので1つずつ書く必要があるが面倒なのでツールを作って自動生成 $1:id::NUMBER(18,0), $1:first_name::TEXT(1024), $1:family_name::TEXT(1024), $1:address::TEXT(1024), $1:created_at::TIMESTAMP_NTZ, $1:updated_at::TIMESTAMP_NTZ FROM public.ext_sample_table_20221206;
30. 移行データチェックツール開発 正しく全テーブルデータがSnowflakeに移行できたか確認するPythonツールを開発し、 移行の正確性を担保。 全行全カラムの比較は量的に困難なため、 テーブル行数、カラムごとの合計値、最大値、最小値、平均値等を集計して比較。 30 SQLファイル Redshift 各テーブル 移行データ チェックツール (Python) SQLファイル Snowflake 各テーブル 集計 （SELECT） チェック結果 (Excel) 出力 集計 （SELECT）
31. 1テーブルずつ、テーブルメタデータを取得し、カラムの型に応じて集計する値を決定。 • 数値型： SUM(col), MIN(col), MAX(col), AVG(col) • 文字列型： MIN(col), MAX(col), COUNT(DISTINCT TRIM(col)) • その他の型：MIN(col), MAX(col), COUNT(DISTINCT col) これをRedshift、Snowflake両方からSELECTして一致しているかを判定する。 ※大量の小数の集計値はどうしても誤差が出るので、14桁目以降の差異は無視 移行データチェックツール内容 31
32. RedshiftとSnowflake並行稼動 32 Redshift用 データ更新系 ETL/ELT Redshift 更新 Snowflake用 データ更新系 ETL/ELT 更新 データソース 参照 参照 データ一致を 毎日確認 参照 参照系システム 移行データ チェックツール (Python) Snowflakeに常に同等データが入るよう ETL/ELTを新規構築
33. 並行稼動期間で大変だったこと 33 • RedshiftとSnowflakeのデータが一致しない事が多々あり、調査・修正に追われた😵 • 一日の様々な時間帯で各種ETLが流れており、整合性を調整するのが大変だった😵 一致しない！ Redshift/Snowflakeテーブルデータ比較結果
34. タイムライン 2021年 10月 2022年 4月 2022年 6月 2022年 7月 2022年 8月 2022年 11月 2022年 12月 2023年 1月 2022年 9月 2022年 10月 2022年 5月 34 情報収集 本格的に検討開始 トライアル開始。 同時並行で Redshift Serverless も検証 トライアル終了 全システム Snowflake対応版 開発・テスト 全データ移行実施、 Redshift/Snowflake 並行稼動開始 12.8 Snowflake へ完全移行完了！
35. Snowflake完全移行 35 Redshift用 データ更新系 ETL/ELT Redshift 更新 Snowflake用 データ更新系 ETL/ELT 更新 データソース 参照 参照 参照 参照系システム 全てのシステム をSnowflakeに 切り替えて移行 完了！
36. 移行後の振り返りと今後の期待
37. 移行を終えての感想 • 高速化、安定化、コストダウンという一番の目的が達成できた🎉 37 • 大きな移行作業だったが障害を出すことなく完了できてよかった🎉 • 速度やコストはまだまだ改善の余地があるので今後改善したい💪 • 移行を通してチームにSnowflakeのナレッジがたまった🎉 • 社内の非テック系メンバーからも絶賛&感謝の声をもらって嬉しかった🎉 • 総合的に、Snowflakeに移行してめちゃくちゃ良かった🎉🎉🎉
38. Snowflakeへの今後の期待 • Snowflake自身によるさらなる高速化とコストダウン • 自動的に最適な設定にしてくれる・提案してくれる機能が欲しい （ウェアハウス、SQL、クラスタリング、マテリアライズドビュー等） • ストアドプロシージャやファンクションからSlackへ直接通知機能が欲しい • Dynamic Table・・・マテリアライズドビューの高機能版で高速化 • Unistore・・・他システムの複数のRDBの役割も統合してDB一本化へ • データシェアリングやマーケットプレイスでビジネス拡大 38
39. 各職種募集してます！ データエンジニア データサイエンティスト Webエンジニア SQL / Snowflake Python データサイエンス Redash etc... SQL / Snowflake Python Docker AWS etc... Python TypeScript Docker AWS etc... データアナリスト ドメイン知識 データ分析 Redash etc... 39
40. SQL変換ツール、データ比較ツール等 OSS公開予定！ Coming soon!! 40
41. 41 ご質問・ご依頼などありましたらお気軽にご連絡 ください！ CM出稿・分析のお手伝いも！ Twitter @motoy3d e-mail jptech@revisio.com Web https://revisio.co.jp