AWS Aurora S3 Export を利用した、 負荷をかけない GCP BigQuery へのデータ連携 2023.05.31 伊田 正寿 GO株式会社

© GO Inc. GO株式会社 データエンジニア ／ 伊田 正寿 家庭菜園でプチトマトを作りたく調査中 2 自己紹介 プロフィール写真 正方形にトリミングした写 真を「図形に合わせてトリ ミング」で円形にすると真 円になる

© GO Inc. AWS Aurora から GCP BigQuery へのデータ連携のシステム構成 ● 試しに CDC (Change Data Capture) を試しに導入した ● プライベートサブネットにある DB に穴を開けられない制約があるため内部からプッシュする方式とした ● 詳細は Tech Talk Vol.12 参照 3 背景 復元した テーブル 復元した テーブル 更新ログ (JSON) CDC ツール 更新 ログ 格納 サービスDB テーブルN テーブルN テーブル テーブル 復元処理 GCP 分析環境 AWS サービスDB BigQuery BigQuery Aurora MySQL BIN ログ ワークフローエンジン Cloud Pub/Sub 分散 キュー Cloud Dataflow GKE Cloud Composer

© GO Inc. 4 課題 ● 本方式によるデータ同期は運用負荷が高い ○ 障害発生時の復旧手順が複雑 ■ 詳細は Tech Talk Vol.12 参照 ○ テーブルリネーム・カラムリネームが行われると、更新ログからレプ リカを再現できない (別途対応が必要) ○ GCP だけでなく AWS 側での構築、運用も発生する このことから運用負荷が下がる方法がないか検討を開始した

© GO Inc. ● 方針 ○ 要件は下記に限定する ■ 更新頻度は日次 ○ ゴール ■ CDC 方式より運用負荷が下がること ○ 撤退条件 ■ CDC 方式より運用負荷が下がらない時 ○ 運用負荷が下がるとは ■ 障害復旧がシンプルにできること ■ スキーマ変更に対して簡単に追従できること ■ 他の運用との兼ね合いで GCP Cloud Composer 起点でジョブ実行 やエラー対応ができること ■ GCP と AWS にまたがる開発となるが、作り込みは GCP 側に寄せ られること 5 方式の検討

© GO Inc. 6 方式の検討 (1/2) パイプライン サービスDB テーブルN テーブルN テーブル 各種変換処理 GCP 分析環境 AWS サービスDB 日次 BigQuery Aurora MySQL / Postgresql ワークフローエンジン GKE Cloud Composer データ S3 データ GCS 復元した テーブル レプリカ テーブル 復元した テーブル レプリカ テーブル BigQuery ??? 抽出処理 S3 にデータを抽出できれば後工程の構築は知見があるため、 プライベートサブネットにある DB からどうやってデータを S3 に抽出するか検討した

© GO Inc. 案 処理方法 メリット デメリット 1. Aurora S3 Export Aurora S3 Export 機能(※)を用いて、DB の全データを S3 に抽出する DB に負荷をかけ ない 前処理を挟めない。 データ量に対してコストが 掛かる。 2. Redshift 経由 (Athena 経由でも可) Redshift を用意し、Redshift から Aurora に Federated Query で問い合わせて S3 に抽出する (Redshift Data API で SQL を発行) SQLで前処理を 挟める 構成が複雑で、トラブル シュートが大変。 Redshift はクラスタ or サー バーレスなどの選定も必要 3. スクラッチ実装 EC2 / ECS / Fargate / EKS / Glue など を用いてスクラッチ開発して、 Aurora から S3 に抽出する 自由度が高い 開発コストが掛かる 7 方式の検討 (2/2) プライベートサブネットにある DB に外から穴を開けずに S3 に出力する方法 ※)2022年10月に直接S3に出力できるようになった 採用

© GO Inc. 8 実験 ● 実験対象の方式 ○ Aurora S3 Export ● 観点 ○ 日次処理を2時間程度に収められるか ■ 0〜1時に処理起動、5〜6時にレポート配信。1回のリトライを考慮 して1回の処理時間を2時間程度に収めたい ○ 2時間の処理時間に収まるデータ量はいくらが限界か ○ コストが許容範囲内か ● 実験対象のDB ○ Large DB ■ Aurora スナップショットのサイズ約500GB ○ Medium DB ■ Aurora スナップショットのサイズ約100GB

© GO Inc. 9 実験 ● 実験結果 Large DB (約500GB) Medium DB (約100GB) 合計 31分 21分20秒 データ抽出 Aurora to S3 25分 (500GB → 74GB) ※ gz.parquet に圧縮 20分 (100GB → 12GB) (内訳) DB Clone 17分 16分 (内訳) S3 Export 8分 4分 データ転送 S3(Tokyo) to GCS(US) 3分 40秒 データ取込 GCS to BigQuery 3分 40秒

© GO Inc. ● 考察 ○ Large DB の全体の処理時間約30分のうち、8割がデータ抽出、1割がデー タ転送、1割がデータ取込となっている (Medium DB に至ってはデータ抽 出に9割を占める) ○ Medium DB に対してサイズが約5倍の Large DB になっても、処理時間が 単純に5倍になるわけではない ○ その理由は、データ抽出はサイズによって処理時間が変動するが、DB ク ローンはサイズに関わらず一定の時間であるため (変動時間+固定時間で 構成されているから) ■ DB クローンについては次のページで詳細を説明 10 実験の考察

© GO Inc. ● Aurora S3 Exportにおいて、DB のクローンはデータ量に関わらず一定 時間で完了する理由の考察 11 実験の考察 ● Aurora はコンピューティングとストレージが分離 している。 ● DB のクローンはコンピューティング部分のコ ピーであり、データの移動は発生しない。 ● 以上のことから DB のクローンはサイズに関わら ず、どの DB でも一定時間掛かると考えられる AWS の資料から引用 (https://pages.awscloud.com/rs/112-TZM-766/images/01_Amazon%20A urora%20%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82% AF%E3%83%81%E3%83%A3%E6%A6%82%E8%A6%81.pdf)

© GO Inc. 12 コスト Large DB Medium DB 合計 $14.2 $2.7 S3 Export 料金 $5.70 $1.33 S3 転出料金 $8.44 $1.33 S3 → GCS 転送料金 (GCS Storage Transfer Service) $0.00 $0.00 GCS 書き込み料金 $0.06 $0.03 ● 1回の処理コストもリーズナブル

© GO Inc. Aurora S3 Export 方式について 以下の理由から Aurora S3 Export 方式を採用した ● 制約を満たしているか？ ○ Aurora S3 Export はプライベートサブネットにある DB に外から穴を開けずに実行可能 ○ 実験の結果、処理時間、コストともに許容範囲内であることが確認できた ■ 変動部分の処理時間が500GBでも10分程度で完了していることから、単純計算で6TBで処 理時間が2時間程度となり、十分に余裕があると判断 ■ コストはDBのサイズが500GBでも月数万のコストのため許容範囲内と判断 ● 課題を解決しているか？＝運用負荷が高くないか ○ 障害発生時も全件抽出方式のため頭からリトライするだけ済む ○ スキーマ変更時も全件抽出による洗い替えができるので特別な考慮をしなくて済む ○ GCP 側から API を実行できるため、実装を GCP 側に寄せることができ、保守の範囲が広がらな いで済む 13 実験の結果から

© GO Inc. ● コンポーネント一覧 ○ ワークフローエンジン ■ Cloud Composer (Airflow) (マネージドサービス) ○ コンテナ実行環境 ■ GKE (マネージドサービス) Aurora 14 変更後のアーキテクチャ パイプライン サービスDB テーブルN テーブルN テーブル コンテナ 実行環境 GCP 分析環境 AWS サービスDB BigQuery GKE ワークフローエンジン Cloud Composer データ S3 データ GCS 復元した テーブル レプリカ テーブル 復元した テーブル レプリカ テーブル BigQuery クローン クローンDB テーブルN テーブルN テーブル エクスポート Aurora S3 Export 抽出リクエスト (アウトバウンド通信のIP固定) 転送 リクエスト 取込 リクエスト ○ データ抽出 ■ Aurora S3 Export (マネージドサービス) ○ データ転送 ■ GCS Storage Transfer Service (マネージドサービス) ○ データ取込 ■ BigQuery Load Job (マネージドサービス) 変換リクエスト

© GO Inc. 15 本番運用した結果 ● 同期対象のDB数 ○ 11個のDB (2023年5月現在) ■ 本番環境 5個 ■ QA環境 5個 ■ 開発環境 1個 ● 障害発生件数 ○ 期間: 2023年1月〜2023年5月現在 ○ 件数: 0 ○ 考察: CDC より仕組みがシンプルであるため障害になりにくいと推察している ■ 全件抽出方式というシンプルなパイプラインのため ■ 大半がマネージドサービスで構成されており障害になりづらいため ■ 経験上、DB に負荷が掛かる時にデータ連携は失敗しやすいが、この方式はDBに 負荷を掛けないため ● 問題点 ○ S3 Export の並列実行数の上限が5となっており、同期対象の DB が増えた 時に実行順序の考慮が必要になる

© GO Inc. ● 背景 ○ 試しに CDC (Change Data Capture) を試験導入した ○ プライベートサブネットにある DB に穴を開けられない制約があるため内部からプッシュする方式だった ● 課題 ○ 障害復旧の手順が複雑 ○ テーブルやカラムがリネームされると破綻する ○ GCP だけでなく AWS 側での構築、運用も発生する ● 検討 および 実験 ○ プライベートサブネットにある DB に穴を開けず、CDC方式より運用が楽なものに置き換える ■ 案1 Aurora S3 Export ■ 案2 Redshift (Federated Query & Redshift Data API) ■ 案3 スクラッチ実装 (EC2/ ECS / Fargate / EKS / Glue など) ○ 机上では案1が有力だったため課題がないかどうか実験した ○ 実験により問題がないことを確認し、コストも許容範囲内のため本実装を進めた ● 本番運用した結果 ○ CDC 方式より安定し、運用負荷が下がった 16 まとめ

文章・画像等の内容の無断転載及び複製等の行為はご遠慮ください。 © GO Inc.