Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Motoi Kataoka
February 15, 2023

 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/

Motoi Kataoka

February 15, 2023
Tweet

More Decks by Motoi Kataoka

Other Decks in Programming

Transcript

  1. 当社のRedshift周りの課題 • ビジネス拡大 = データ量・ユーザー・ワークロード増加 → 処理速度・安定性が低下 😨 • それをカバーするためにスケールアップ

    → コストアップ 😵 • クラスター管理やパフォーマンス・チューニングの運用負荷 → 社内のエンジニアがうまくコントロールできていなかった 😢 10
  2. タイムライン 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
  3. タイムライン 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 へ完全移行完了!
  4. トライアル確認事項の洗い出し 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管理画面の機能・使用感確認
  5. 約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管理画面はシンプルで使いやすい。特にクエリプロファイル(実行計画)が良い
  6. 項目 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
  7. タイムライン 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 へ完全移行完了!
  8. 移行において問題となる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には存在しない その他多数...
  9. 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 ...
  10. SQL結果データ比較ツールも開発 24 SQL結果データ 比較ツール (Python) Redshift 2.SQL実行 4.SQL実行 比較結果 (Excel)

    5.出力 SQLファイル Redshift用 SQLファイル (SELECT) SQLファイル Snowflake用 SQLファイル (SELECT) 1.読み込み 3.読み込み 一致しない! →調査→差異発覚 →SQL変換 の繰り返し
  11. (参考)RedshiftとSnowflakeのSQL実行速度比較 25 当社の実際のSQL43個で比較した結果 Snowflake 39勝 Redshift 4勝 [実行環境] Snowflakeウェアハウス:S(マルチクラスター) Redshiftクラスター:ra3.4xlarge

    7ノード ※Redshiftのチューニングがうまくできてなかった せいかも・・💦 Snowflakeのチューニングは比較的シンプルで分か りやすく、改善しやすいと感じる👍 単位:秒 (合計84CPU、672GBメモリ)
  12. Snowflakeのパフォーマンスチューニングキーワード 26 • ウェアハウス数と振り分け・・当社では開発用含め6個使用 • ウェアハウスサイズ(スケールアップ/ダウン)・・1つだけMでその他はS • マルチクラスターウェアハウス(スケールアウト/イン)・・オン(SQL同時実行性能アップ) • テーブルのクラスタリング・・特に重いテーブルに設定。効果大

    • マテリアライズドビュー・・特に重いSQLで使用 • クエリアクセラレーション・・オフ(検証&検討中) • 各種キャッシュ・・キャッシュ利用されるようSQL組み換え等 • サポート・・チューニングの相談も受けてくれる 参考:https://select.dev/toronto-snowflake-user-group-cost-optimization/index.html
  13. タイムライン 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 へ完全移行完了!
  14. 全データ移行 28 2つの選択肢を検討 • データパイプラインサービス(Fivetran、Airbyte等)の利用 • S3経由でRedshiftからエクスポート・Snowflakeへインポート SQL変換ツールで DDLをSnowflake版 に変換

    Snowflakeにテーブ ル・VIEW作成 Redshiftから Parquet形式でS3に エクスポート (UNLOAD) Snowflakeで外部テ ーブル(中身はS3の ファイル)を作成し、 一括INSERT ParquetはCSVより高速でフ ァイルサイズも軽い 外部ステージを使う方法もある が外部テーブルの方が高速。 ウェアハウスサイズを上げると ある程度までは倍々で高速化。 → 実際に試して手順のシンプルさと速度面から、こちらを採用
  15. 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;
  16. 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
  17. RedshiftとSnowflake並行稼動 32 Redshift用 データ更新系 ETL/ELT Redshift 更新 Snowflake用 データ更新系 ETL/ELT

    更新 データソース 参照 参照 データ一致を 毎日確認 参照 参照系システム 移行データ チェックツール (Python) Snowflakeに常に同等データが入るよう ETL/ELTを新規構築
  18. タイムライン 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 へ完全移行完了!
  19. Snowflake完全移行 35 Redshift用 データ更新系 ETL/ELT Redshift 更新 Snowflake用 データ更新系 ETL/ELT

    更新 データソース 参照 参照 参照 参照系システム 全てのシステム をSnowflakeに 切り替えて移行 完了!
  20. 各職種募集してます! データエンジニア データサイエンティスト Webエンジニア SQL / Snowflake Python データサイエンス Redash

    etc... SQL / Snowflake Python Docker AWS etc... Python TypeScript Docker AWS etc... データアナリスト ドメイン知識 データ分析 Redash etc... 39