AWS Aurora S3 Export を利用した、負荷をかけない GCP BigQuery へのデータ連携

GO TechTalk #19 タクシーアプリ『GO』事業成長を支えるデータ分析基盤の継続的改善！ で発表した資料です。

■ YouTube
https://www.youtube.com/live/sD8IpwoIkaw?feature=share&t=1698

■ connpass
https://jtx.connpass.com/event/282134/

Transcript

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

   View Slide

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

   View Slide

3. © 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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

7. © 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に出力できるようになった
   採用
   

   View Slide

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

   View Slide

9. © 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秒
   

   View Slide

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

    View Slide

11. © 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)
    

    View Slide

12. © 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回の処理コストもリーズナブル
    

    View Slide

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

    View Slide

14. © 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 (マネージドサービス)
    変換リクエスト
    

    View Slide

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

    View Slide

16. © 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
    まとめ
    

    View Slide

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

    View Slide