🛢️
Amazon RDSやS3にあるデータをBigQueryで分析する方法いろいろ

2025/02/19に公開
BigQuery
RDS
aurora
Database Migration Service
tech
 はじめにBigQuery は大量のデータを手軽かつ高度に分析するのに優れており、私もこれまで複数のプロジェクトで利用してきました。Google Cloud で構成されているプロジェクトであれば困ることは少ないですが、AWS や Azure などで構成されているプロジェクトのデータを BigQuery で分析したいケースも多いです。AWS はもちろん、特に最近では Azure OpenAI Service の存在もあり Azure の利用数が増えてきている印象で、マルチクラウド構成の選択肢をとるケースも増えてきていると感じます。先日の DeNA × AI Day の発表でも Cloud Run + AOAI のマルチクラウド構成 が紹介されていました。

クラウド環境を跨ぐことで分析対象のデータがサイロ化しがちですが、本記事では BigQuery1本化を前提に Amazon RDS のデータを BigQuery で分析する方法をいくつかご紹介します。
ご紹介するアプローチの1,2はどちらも BigQuery Data Transfer Service を使って AWS から Google Cloud にデータを転送する方法です。アプローチ3は BigQuery Omni を使って Google Cloud の外部データソースとして BigQuery から直接参照する方法です。いずれも S3を介しています。

 なぜBigQueryを使いたいかBigQuery に限らず AWS や Azure にもデータ分析に必要なサービスは十分にあると思いますが、その中でも BigQuery の利用を推奨する理由をいくつか記載します（もちろんこれが正解というものではなく、あくまで個人的見解です）。

 1. データ分析に必要な機能が豊富高度な分析が可能な BigQuery Notebook や BigQuery ML はじめ、生成 AI 統合の Gemini in BigQuery、可視化の BigQuery data canvas などデータ分析に必要な機能が広くサポートされておりアップデートが充実
特に BigQuery data canvas は自然言語でクエリが発行でき、非エンジニアも簡単に直接分析できる点に加え、簡単な可視化であれば完結できる点も有用（外部共有などの必要に応じて Locker Studio やスプレッドシートへのシームレスなエクスポートも可能）
ref. BigQuery データ キャンバスのご紹介: AI 中心のエクスペリエンスでデータ分析を刷新 | Google Cloud 公式ブログ

 2. リーズナブルな価格設定
基本的に BigQuery で発生する価格はクエリ処理時に発生する「コンピューティング料金」とデータ保存で発生する「ストレージ料金」の2つ

BigQuery の料金 に記載されていますが、コンピューティング料金は1TB あたり約1,100円（毎月1TB 分無料枠）、ストレージ料金（アクティブ計算）で1GB あたり約3円/月（毎月10GB 分は無料枠）で使えるなど、事業立ち上げ時であれば無料枠で完結可[1]
特に90 日間連続してスキャンされないデータはロングタームストレージへ移動され50％割引になるなどコスパ良し

費用は気になる場合は BigQuery のクエリ利用量制限 でプロジェクトやユーザーごとの1日あたりのクエリ使用量に制限をかけることもでき高額請求の事故も防止可

 3. GA4のユーザー行動履歴との統合
GA4によるユーザー行動履歴（ある程度のデモグラも取得可能）とデータベースにある事業データを組み合わせたクロス分析が可能
ソフトウェア事業であれば toC,toB 問わずユーザーの行動履歴のトラッキングを行う際に、Google Analytics(以下、GA4)を導入することが多い事実（グローバルでの Analytics 用途では GA4が 9割以上のマーケットシェアを占めている デファクトスタンダード）。
一方で GA4コンソールは基本的に統計データの分析で N1分析には不十分（ユーザーエクスプローラは除く）。GA4は BigQuery Export 機能が備わっておりユーザーの行動履歴がすべてレコードとして BigQuery に保存可能

詳細は GA4のBigQueryエクスポートに関するいろいろ のスクラップを参照
上記の理由で、AWS では Amazon RedShift や Athena, QuickSight を組み合わせて AWS 上でデータ分析環境を整えるという選択や、BigQuery と度々比較される Snowflake を採用するという選択もあると思いますが、本記事ではあくまで「データ分析基盤を BigQuery に1本化する前提」で話を進めています。参考までに比較記事を以下に引用します。
https://www.integrate.io/jp/blog/snowflake-vs-bigquery-ja/

https://zenn.dev/koh_satoh/articles/e9f7a36e03f84d

 1. RDS + S3 → BigQuery「RDS の標準エクスポート機能で S3に出力し BigQuery へ転送」する方法です。

ユースケース: データベースの中身をシンプルに BigQuery で分析したい
Pros: RDS の標準機能を使うだけなので簡単にセットアップ可能
Cons: RDS のすべてのデータがスナップショット出力（加工・リージョン跨ぎは不可）
非常にシンプルで RDS のスナップショットから AWS KMS key と S3バケットの作成で完結できます。スナップショットは Apache Parquet 形式で出力されます。以下のドキュメントに手順の記載があります。

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html
2020年時点での新機能なので現在は情報も豊富で特に躓くポイントは少ないと思います。スクショ付きの手順としてクラスメソッドさんの当時の記事を引用します。

https://dev.classmethod.jp/articles/rds-snapshot-s3-export/

 BigQuery Data Transfer Servicehttps://cloud.google.com/bigquery/docs/dts-introduction
S3に出力された Parquet 形式のデータを BigQuery に転送するため、BigQuery Data Transfer Service（DTS）を利用します。これはあらかじめスケジュールされた時刻に基づいて外部データソースから BigQuery へデータの移行を自動化するマネージドサービスです。本記事で扱う Amazon S3の他に Redshift や Azure Blob Storage, 他にもプレビューですが Salesforce などもデータソースとしてサポートされています。

 Amazon S3の転送以下は Amazon S3の転送ガイドです。CSV や JSON、Parquet など計5つのファイル形式がサポートされています。いくつか制限がありますが、中でも後述する BigQuery Omni がサポートしているロケーションにはデータ転送できない点に注意が必要です（Omni が利用できるロケーションはそちらを使用すべき、ということだと思います）。

https://cloud.google.com/bigquery/docs/s3-transfer-intro

 必要な権限ドキュメント記載の通りですが、以下の IAM 権限が必要です。Google Cloud 環境に AWS の IAM を指定するため、最小権限の原則（Principle of Least Privilege）の作成が推奨です。

Google Cloud 側

bigquery.transfers.update: データ転送に必要

bigquery.datasets.get・bigquery.datasets.update: 抽出したデータの書き込みに必要
事前定義ロール BigQuery Admin(roles/bigquery.admin) に上記の権限はすべて含まれているためそれが楽です（気になる場合はカスタムロールの作成が必要です）


AWS 側

AmazonS3ReadOnlyAccess ポリシーを持つ IAM ユーザーの作成: S3ソースデータの取得のため
BigQuery Data Transfer Service にアクセスキーの入力が必要

上記の AmazonS3ReadOnlyAccess のままでも良いですが転送するバケットを指定したポリシーに変更する方がベター
RDS スナップショット作成時に KMS キーで暗号化しているため KMS キーポリシーの追記も忘れずに必要

AWS IAM の例）BigQueryS3ReadOnlyAccessUser Permission
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:Get*",
                "s3:List*",
                "kms:Decrypt"
            ],
            "Resource": [
                "arn:aws:s3:::your-transfer-target-bucket",
                "arn:aws:s3:::your-transfer-target-bucket/*",
                "arn:aws:kms:ap-northeast-1:xxxxxx:key/your-kms-id" # RDSエクスポート時に暗号化で利用したKMSのキーID
            ]
        }
    ]
}
KMS キーポリシーの例 Permission
{
  "Version": "2012-10-17",
  "Id": "key-consolepolicy-3",
  "Statement": [
    {
      "Sid": "Allow BigQuery DTS to decrypt",
      "Effect": "Allow",
      "Principal": {
        "AWS": [
          "arn:aws:iam::xxxxxx:user/your-transfer-target-bucket"
        ]
      },
      "Action": "kms:Decrypt",
      "Resource": "*"
    }
  ]
}
ここまでは準備は完了ですので、あとは ドキュメント 記載の通りコンソールや BigQuery CLI などを使って転送スケジュールを組んで完了です。事前に BigQuery データセットおよび転送先のテーブルの設定が必要となります。
スキーマに関しては 自動検出 が機能しますが、列内のすべての行がからの場合は STRING になる仕様や、意図した型で検出されない場合は、 スキーマを手動で事前に作成しておく必要があります。

 2. RDS + DMS + S3 → BigQuery「RDS から DMS 経由で一部データを加工後 S3に出力し BigQuery へ転送」する方法です。
ユースケース: RDS 内のセンシティブデータのマスキングや一部データ加工した上で BigQuery で分析したい
Pros:
DMS を使うことで柔軟なデータ加工が可能
不要なデータをあらかじめ削ることによる BigQuery ストレージ料金の節約

SELECT * FROM クエリによる余計なスキャンの抑制

Cons: DMS のセットアップや各種 IAM 作成の手間

 Database Migration Service(DMS)https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html
Database Migration Service(以下、DMS) はいわゆる ETL（Extract・Transformation・Load）を実現するマネージドサービスです。オンデマンド方式とサーバレス方式が提供されています。ソース/ターゲットのエンドポイントを自由に設定でき、オンデマンド方式の場合は EC2が裏側で建てられパイプラインを形成してくれます。本記事では RDS→S3の転送に利用しますが、名称の通りデータベースのマイグレーション用途としても優秀なツールです。
本記事のアプローチ1でご紹介した単純な RDS スナップショットの S3へのエクスポートと違う点は、エクスポート前にデータの加工が柔軟にできる点です。分析に不要なデータやプライベートサブネットの外に持ち出したくないデータなどは予め削除やマスキング処理をすることで安全にエクスポートできます。出力形式はデフォルト CSV ですが、JSON や Parquet など様々な形式がサポートされています。

 レプリケーションインスタンスの作成今回はレプリケーションインスタンスを使ったオンデマンド形式で実行します。以下がドキュメントです。

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.html
転送するデータ量に応じてレプリケーションインスタンスのサイズやエンジンのバージョンを選定します。また、レプリケーションインスタンスを配置するサブネットグループも事前に作成が必要です。ソースエンドポイント（今回は RDS）と同じプライベートサブネットを指定すると良いと思います。
また、AWS DMS の IAM ロール（dms-vpc-role）の作成も必要です（未作成の場合コンソールで警告が出ると思います）。

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_DMS_migration-IAM.dms-vpc-role.html
IAM ロール `dms-vpc-role` の例Trust Relationship
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AWSDMSVPCPolicyTemplate",
      "Effect": "Allow",
      "Principal": {
        "Service": "dms.amazonaws.com"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "aws:SourceAccount": "072651350417"
        }
      }
    }
  ]
}
Permission Policy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "CreateNetworkInterface",
      "Effect": "Allow",
      "Action": [
        "ec2:CreateNetworkInterface",
        "ec2:DeleteNetworkInterface",
        "ec2:ModifyNetworkInterfaceAttribute"
      ],
      "Resource": [
        "arn:aws:ec2:*:072651350417:network-interface/*",
        "arn:aws:ec2:*:072651350417:instance/*",
        "arn:aws:ec2:*:072651350417:subnet/*",
        "arn:aws:ec2:*:072651350417:security-group/*"
      ]
    },
    {
      "Sid": "DescribeVPC",
      "Effect": "Allow",
      "Action": [
        "ec2:DescribeAvailabilityZones",
        "ec2:DescribeInternetGateways",
        "ec2:DescribeSubnets",
        "ec2:DescribeVpcs",
        "ec2:DescribeSecurityGroups",
        "ec2:DescribeDhcpOptions",
        "ec2:DescribeNetworkInterfaces"
      ],
      "Resource": "*"
    }
  ]
}

 ソースエンドポイントの指定 (Extract)データを抽出する対象を指定します。今回のケースでは RDS から Extract する部分です。RDS の場合はプルダウンが用意されているので選択できます。RDS 以外の場合はソースエンジンを選択し必要情報を入力します。
設定が完了したらエンドポイントごとに接続テストができます。タスク実行前にまずはこの接続が成功するかを先に確認しましょう。IAM やサブネットなどの配置にミスがあると接続が失敗しますので設定を再確認し修正します。

 ターゲットエンドポイントの指定 (Load)ソースエンドポイント同様に出力する対象を指定します。今回のケースでは S3へ Load する部分です。S3の具体的な手順については以下に詳細があるため割愛します。
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html

 出力形式の指定出力形式をターゲットエンドポイントの属性情報（attribute）で指定できます。例えば、BigQuery 側でスキーマ自動検出できるように以下のように AddColumnName を true にしたり、DataFormat の指定ができます（※デフォルトは CSV ですがスキーマ自動検出が機能しなかたったため今回は Parquet 形式で出力しました）。


attribute属性の指定例

 データベース移行タスク (Transformation)レプリケーションインスタンスの作成、ソースエンドポイント・ターゲットエンドポイントの作成までできたら、最後に移行タスクを作成します。実行スケジュールや移行方法などを指定します。この時、CloudWatch Logs の有効化がデフォルトオフになっているので、オンにしておくと良いでしょう。

 テーブルマッピングテーブルマッピングはスキーマのルールやカラムの加工などのルールを指定できます。Transformation はこの処理で実施されるので、ここが肝です。マスキングやカラム削除など自由に設定できます。
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.html

 データ変換データの変換方法は多様で、スキーマやテーブル自体のリネームやカラムの追加・削除などいろいろできます。
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html
例えば、public スキーマの users テーブルにある email カラムを削除したうえで、他のすべてのテーブルを移行するには以下の JSON で記述できます。
テーブルマッピング JSON の例{
    "rules": [
        {
            "rule-type": "transformation",
            "rule-id": "850950362",
            "rule-name": "850950362",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "public",
                "table-name": "users",
                "column-name": "email"
            },
            "rule-action": "remove-column",
            "value": null,
            "old-value": null
        },
        {
            "rule-type": "selection",
            "rule-id": "850887828",
            "rule-name": "850887828",
            "object-locator": {
                "schema-name": "public",
                "table-name": "%"
            },
            "rule-action": "include",
            "filters": []
        }
    ]
}

 マスキングマスキングについては2024年11月にサポートされた機能で「数字のマスキング」「数字のランダム化」「ハッシュマスキング」の 3つが可能です。

https://dev.classmethod.jp/articles/dms-data-masking/
例）C6BGJ566669K という値をマスキングする場合


種類
変換例


Digits Mask（例: 数値を # に変換）
C#BGJ######K

Digits Randomize（例: 数値をランダムに変換）
C1BGJ842170K

Hashing Mask（値全体をハッシュ化）
7CB06784764C9030CCC41E25C15339FEB293FFE9B329A72B5FED564E99900C75

マスキング対象は数字（numeric value）か全体ハッシュ化の2パターンしかないのでそれで要件が満たせるかは確認するべきです。ハッシュは「ソルトなし SHA256」で処理されるようですが、そもそも分析で一切使わないのであれば列ごと削除してしまったほうが良いかもしれません。
以上で S3に DMS で加工したデータ形式でスナップショットが作成されていると思います。後は、アプローチ1で記載した BigQuery Data Transfer Service による Amazon S3の転送 と同様の手順で BigQuery へ転送して完了です。

 3. RDS + DMS + S3(※) ⇔ BigQuery Omni「RDS から DMS 経由で一部データを加工後 "特定リージョン(※)"の S3に出力し BigQuery Omni で直接参照」する方法です。
!執筆時点(2025年2月)では BigQuery Omni の対応リージョンに制限があり、東京リージョン(asia-northeast1)では利用できません。S3と BigQuery のリージョンを合わせる必要があるため、この手法を取る場合は BigQuery 側のリージョンも東京とできない点に注意してください。
ユースケース: RDS データを AWS 外へ持ち出さずに BigQuery で分析したい
Pros:
BigQuery ストレージ料金の大幅な節約（⇔BigQuery Omni の費用あり）
転送スケジュールを組む必要がなくワークフロー簡素化
RDS のデータが外部に持ち出されず他の方法と比べて安心

Cons:
ロケーションを AWS と Google Cloud で合わせる必要あり（東京は利用不可）
JavaScript UDF が使えないなど BigQuery Omniの制限事項 あり


このアプローチでも DMS を利用します。理由はアプローチ1の RDS スナップショットではリージョン跨ぎができないためです。RDS と S3がどちらも同じリージョンにあれば良いですが、そうではないケースを想定しています（例えば、RDS が東京リージョンで S3がバージニア北部など）。DMS はデータの加工はもちろん、異なるリージョンへのエクスポートもできるため搭載しています。DMS については Database Migration Service(DMS) で触れていますので割愛します。

 BigQuery Omnihttps://cloud.google.com/bigquery/docs/omni-introduction?hl=ja
BigQuery Omni は Amazon S3や Azure Blob Storage など外部環境に保存されたデータに対して BigLake テーブルを使用してクエリを発行できる機能です。BigQuery にわざわざ分析対象データを移行する必要がありません。
注意書きとして上記に記載しましたが、東京リージョンに対応していないという点に注意です。BigQuery はデータそのものをわざわざ転送せずとも外部データソースを直接参照できる部分も特徴の1つであり、BigQuery Omni もその1つなのですが惜しいです。ちなみに2022年5月時点でクラウドエース社から公開されている BigQuery OmniでAmazon S3のデータをクエリしてみた のトライアル時点の記事では「米国東部（バージニア北部）us-east-1」のみ対応であり、2年半経って対応リージョンも少し増えましたが東京はなく、サポートはもう少し先になりそうな雰囲気です。


2025年2月時点での BigQuery Omni 対応ロケーション
また、BigQuery では、異なるロケーション（リージョンまたはマルチリージョン）にある2つのデータセット間で直接結合（JOIN）クエリを実行することはサポートされていません。これは、BigQuery がデータセット間のクエリ実行において、同一ロケーション内での操作を前提としているためです。そのため、せっかく GA4のユーザー行動履歴のデータとクロス分析しようとしても、片方が東京リージョンに配置されていたりすると一度データコピーするなど手間が発生してしまいます。

 BigQuery OmniでAmazon S3に接続詳細は割愛しますが、公式ドキュメントと以下のサイトが参考になります。
https://cloud.google.com/bigquery/docs/omni-aws-create-connection?hl=ja

https://www.lac.co.jp/lacwatch/people/20240131_003662.html
ほぼ再掲ですが手順は以下です:
AWS 側
BigQuery Omni 対象リージョンに分析対象をとなるバケットを作成
IAM ポリシー（AmazonS3ReadOnlyAccess の最小権限）と IAM ロールの作成

- IAMロールがやや特殊でIdentity Providerから作成します
IAM ロールの信頼関係に Google Cloud 接続を明記（BigQuery Google identity を指定）

BigQuery 側
BigQuery Omni のコネクションを作成

roles/bigquery.connectionUser または roles/bigquery.connectionAdmin 権限をクエリ発行ユーザーへ付与
データセットの作成（ロケーションを手順1のコネクションや S3と合わせる）
例）aws-us-east-1

テーブルを作成時の作成元に「Amazon S3」を選択しテーブル名を指定

ポイントとしては AWS IAM ロールに BigQuery Google identity を指定する点と、BigQuery Omni のコネクションを作成するだけではなく、データセット・BigLake テーブルの作成も必要な点です。


BigQuery Omniの接続
BigLakeテーブル





以上の手順が完了すると通常の BigQuery の使い方でクエリ発行ができるようになります。クエリ結果が返ってくるまでやや遅い体感はありますが気になるレベルではないです（データ量が多くなるとまた違うかもしれません）。

 まとめ本記事では、3つのアプローチについてご紹介しました。DMS や BigQuery Omni にも触れたことで少し内容が多くなってしまったため、改めて比較のために記載します。

RDS + S3 → BigQuery
ユースケース: データベースの中身をシンプルに BigQuery で分析したい
Pros: RDS の標準機能を使うだけなので簡単にセットアップ可能
Cons: RDS のすべてのデータがスナップショット出力（加工・リージョン跨ぎは不可）


RDS + DMS + S3 → BigQuery
ユースケース: RDS 内のセンシティブデータのマスキングや一部データ加工した上で BigQuery で分析したい
Pros:
DMS を使うことで柔軟なデータ加工が可能
不要なデータをあらかじめ削ることによる BigQuery ストレージ料金の節約

SELECT * FROM クエリによる余計なスキャンの抑制

Cons: DMS のセットアップや各種 IAM 作成の手間


RDS + DMS + S3(※) ⇔ BigQuery Omni
ユースケース: RDS データを AWS 外へ持ち出さずに BigQuery で分析したい
Pros:
BigQuery ストレージ料金の大幅な節約（⇔BigQuery Omni の費用あり）
転送スケジュールを組む必要がなくワークフロー簡素化
RDS のデータが外部に持ち出されず他の方法と比べて安心

Cons:
ロケーションを AWS と Google Cloud で合わせる必要あり（東京は利用不可）
JavaScript UDF が使えないなど BigQuery Omni の制限事項あり


また、今回は検証用途も込みでコンソール操作が中心でしたが、特に IAM ロール作成周りの手間は面倒なので Cloud Formation などで IaC にするべきだと思います。本記事では触れませんでしたが、実際にはデータ更新のスケジューリングとして EventBridge で DMS タスクを実行[2]する必要があり、BigQuery 側の追加もパーティション分割にするべきかサービスに合わせて判断が必要です。記事中で誤っている箇所や他にもベターな方法がありましたらコメントいただけますと幸いです。

 参考BigQuery Data Transfer Serviceのデータセットコピーを解説 - G-gen Tech Blog
BigQuery OmniでAmazon S3のデータをクエリしてみた
BigQuery Omni を使用してマルチクラウド環境におけるログの取り込みと分析の費用を削減する | Google Cloud 公式ブログ
S3からBigQueryへ連携する方法いろいろ（Omni/Transfer Service） | DevelopersIO
Snowflake vs. BigQuery 選択ガイド | Integrate.io
AWS（S3）にあるデータをBigQuery Omniで処理してみた | LAC WATCH
特定の時間間隔で AWS DMS を自動化する | AWS re:Post

脚注
いずれも Tokyo(asia-northeast1)の価格を引用しています。 ↩︎
Lambda を介さずとも DMS の StartReplicationTask の API を直接 EventBridge から叩くことができます。 ↩︎
種類	変換例
Digits Mask（例: 数値を `#` に変換）	`C#BGJ######K`
Digits Randomize（例: 数値をランダムに変換）	`C1BGJ842170K`
Hashing Mask（値全体をハッシュ化）	`7CB06784764C9030CCC41E25C15339FEB293FFE9B329A72B5FED564E99900C75`
Discussion

ログインするとコメントできます