G-gen の杉村です。BigQuery Data Transfer Service で Cloud SQL にホストされた PostgreSQL のデータを転送する検証を行いましたので、共有します。

はじめに
- BigQuery Data Transfer Service とは
- 当記事について
環境のセットアップ
検証
挙動のまとめ

はじめに

BigQuery Data Transfer Service とは

BigQuery Data Transfer Service は BigQuery に備え付きの、フルマネージドなデータ転送サービスです。設定したスケジュールに基づいて、各種取得元からデータを取得して BigQuery のテーブルにデータを取り込みます。容易に実装することができるうえ、料金も一部のコネクタを除いて無料です。

参考 : What is BigQuery Data Transfer Service?

以下の記事も参考にしてください。

参考 : BigQueryを徹底解説！(基本編) - BigQuery Data Transfer Service

当記事について

2025年1月に、BigQuery Data Transfer Service はデータの転送元として PostgreSQL や MySQL に対応しました（2025年1月現在、Preview）。

2025年1月現在、公式ドキュメントには、これらのデータベースからの転送を繰り返しジョブとして実行した際に、レコードの追加に対する差分転送やレコードの更新、削除がどのように転送先に反映されるかについての記述がありませんでした。これを検証するため、当記事では Cloud SQL にホストした PostgreSQL データベースから BigQuery データセットへのデータ転送を検証します。

結論からいうと、転送ジョブを複数回実行すると、レコードの追加、更新、削除はいずれも転送先の BigQuery テーブルに反映されました。内部的には WRITE_TRUNCATE ジョブが実行され、毎回データの全件洗い替えが行われます。

参考 : PostgreSQL transfers

環境のセットアップ

Cloud SQL インスタンスの起動

Cloud SQL for PostgreSQL インスタンスを起動します。

スペックは最小限とし、Public IP アドレスを付与、また許可する接続元 IP アドレス範囲である「承認済みネットワーク」に 0.0.0.0/0 を追加します。

今回は簡易的な検証目的であるためこのように設定しましたが、インターネットのどのような場所からでもアクセスできるような設定は、本来は推奨されません。BigQuery Data Transfser Service から Cloud SQL に接続させる場合は、本来は以下のような手順を踏みます。

Cloud SQL インスタンスと Private Service Connect で接続された VPC ネットワークに踏み台 VM を作成する
踏み台 VM に Cloud SQL プロキシを起動する
BigQuery Data Transfer Service のプロジェクトにネットワークアタッチメントを作成する
BigQuery Data Transfer Service の転送でネットワークアタッチメントを紐づける

つまり BigQuery Data Transfer Service から直接、Cloud SQL インスタンスにプライベート接続することは現時点ではできません。Cloud SQL プロキシを起動した VPC ネットワーク内の Compute Engine インスタンスを踏み台とする必要があります。

参考 : Configure Cloud SQL instance access

テーブルとデータの準備

Cloud SQL のデータベースには、Google Cloud コンソールの Cloud SQL Studio と呼ばれる Web コンソール画面から接続し、直接 SQL を実行することができます。

以下のように、サンプルテーブルとデータを準備しました。

CREATE TABLE "public".my_table
(
  id INT,
  data TEXT
);

INSERT INTO "public".my_table (id, data) VALUES
  (1, 'this is a message.'),
  (2, 'this is the second message.');

一方で BigQuery 側には、転送先のデータセットのみを Cloud SQL インスタンスと同じリージョンに用意しました。テーブルは作成しません。また、後述しますが転送先のデータセットは、Cloud SQL インスタンスと同じリージョンである必要はありません。

転送の作成

BigQuery Data Transfer Service で転送ジョブを作成します。Cloud SQL インスタンスのパブリック IP アドレス、ポート番号、データベース名、スキーマ名などを指定します。また転送先の BigQuery データセットや、転送元の PostgreSQL のテーブルも指定しました（postgres/public/my_table）。

実行頻度として、検証のため定期実行ではなく、指示したときに起動するオンデマンドとしました。

検証

初回転送

初回の転送を実施してみます。

作成した転送ジョブの画面で「今すぐ転送を実行」を実行しました。

以下のように、データセットにテーブルが生成されました。あらかじめ BigQuery データセットにテーブルを作成していたわけではありませんが、自動的に適切なスキーマ（id INTEGER, data STRING）を持つテーブルが生成されました。ジョブ開始のログから終了ログの出力まで、約2分30秒ほどかかりました。

レコードの追加（差分転送）

PostgreSQL のテーブルに、以下のように新しいレコードを挿入します。

INSERT INTO "public".my_table (id, data) VALUES
  (3, 'this is the third message.'),
  (4, 'may the force be with you.');

その後、転送ジョブを再度実行します。以下のように、差分のみが BigQuery テーブルに転送されました。

レコードの更新

PostgreSQL のテーブルのレコードを以下のように更新します。

UPDATE "public".my_table
SET data = 'you were the chosen one!'
WHERE id = 1;

その後、転送ジョブを再度実行します。以下のように、更新されたレコードが BigQuery テーブルに反映されました。

レコードの削除

PostgreSQL のテーブルから、以下のようにレコードを削除し、さらに別のレコードを追加します。

DELETE FROM "public".my_table
WHERE id = 2;

INSERT INTO "public".my_table (id, data) VALUES
  (5, 'this is the fifth element.');

その後、転送ジョブを再度実行します。以下のように、レコードの削除と追加がともに BigQuery テーブルに反映されました。

挙動のまとめ

非常に簡単ではありますが、初回転送、レコードの追加、更新、削除を検証しました。いずれも BigtQuery Data Tranfser Service の転送ジョブが実行されるたびに、BigQuery テーブルに反映されました。

なお、転送ジョブによって実行された BigQuery ジョブのログを Cloud Logging で確認すると、"writeDisposition": "WRITE_TRUNCATE" になっています。また転送終了後には次のようなログが出力されます。

Job 1234567890:postgresql_xxxxx-0000-xxxx-xxxx-xxxx (table my_table) completed successfully. Number of records: 4, with errors: 0.

転送ジョブは、毎回テーブルを TRUNCATE してデータを全件転送しなおしていることがわかります。もし転送元データベースが Google Cloud の外部にある場合、ネットワーク帯域やクラウドの Egress データ転送料金などに注意が必要です。

今回はいずれも少量のレコードの転送でした。いずれの実行の際も、ジョブ実行が開始されてから概ね1分後に実際の転送が開始された旨のログが出力され、その1分後に転送が完了した旨のログが出力されました。完了のログの約30秒後に、ジョブの完了を示す最終的なログが出力されました。

なお、転送先の BigQuery データセットは、転送元の Cloud SQL インスタンスと同じリージョンである必要はありません。異なるリージョンのデータセットを指定しても、転送は成功しました。