PostgreSQL データを BigQuery に読み込む

PostgreSQL から BigQuery にデータを読み込むには、PostgreSQL 向け BigQuery Data Transfer Service のコネクタを使用します。このコネクタは、オンプレミス環境、Cloud SQL、および Amazon Web Services（AWS）や Microsoft Azure などの他のパブリック クラウド プロバイダでホストされている PostgreSQL インスタンスをサポートしています。BigQuery Data Transfer Service を使用すると、PostgreSQL から BigQuery に最新のデータを追加する定期的な転送ジョブのスケジュールを設定できます。

制限事項

PostgreSQL のデータ転送には、次の制限があります。

  • 1 つの PostgreSQL データベースへの同時転送実行の最大数は、PostgreSQL データベースでサポートされている同時接続の最大数によって決まります。転送ジョブの同時実行数は、PostgreSQL データベースでサポートされている同時接続の最大数より小さい値に制限する必要があります。
  • 1 つの転送構成でサポートできるデータ転送実行は、一度に 1 つだけです。最初の転送が完了する前に 2 回目のデータ転送が実行されるようにスケジュールされている場合、最初のデータ転送のみが完了し、最初の転送と重複するほかのデータ転送はスキップされます。
    • 1 つの転送構成内で転送がスキップされないようにするには、繰り返し頻度を構成して、大規模なデータ転送間の時間間隔を長くすることをおすすめします。

データの取り込みオプション

以降のセクションでは、PostgreSQL のデータ転送を設定する際のデータの取り込みオプションについて説明します。

TLS 構成

PostgreSQL コネクタは、BigQuery へのデータ転送を暗号化するためのトランスポート レベル セキュリティ（TLS）の構成をサポートしています。PostgreSQL コネクタは、次の TLS 構成をサポートしています。

  • データを暗号化し、CA とホスト名を検証する: このモードでは、TCPS プロトコルを介した TLS を使用してサーバーの完全な検証を行います。転送中のすべてのデータを暗号化し、データベース サーバーの証明書が信頼できる認証局（CA）によって署名されていることを確認します。このモードでは、接続先のホスト名がサーバーの証明書の共通名（CN）またはサブジェクト代替名（SAN）と完全に一致することも確認します。このモードでは、攻撃者が別のドメインの有効な証明書を使用してデータベース サーバーを偽装することを防止できます。
    • ホスト名が証明書の CN または SAN と一致しない場合、接続は失敗します。証明書と一致するように DNS 解決を構成するか、別のセキュリティ モードを使用する必要があります。
    • このモードは、中間者（PITM）攻撃を防ぐための最も安全なオプションです。
  • データを暗号化し、CA のみを検証する: このモードでは、TCPS プロトコルを介した TLS を使用してすべてのデータを暗号化し、サーバーの証明書がクライアントが信頼する CA によって署名されていることを検証します。ただし、このモードではサーバーのホスト名は検証されません。このモードでは、証明書が有効で、信頼できる VA によって発行されていれば、証明書のホスト名が接続先のホスト名と一致するかどうかに関係なく、接続が成功します。
    • このモードは、証明書が信頼できる CA によって署名されているサーバーに接続していることを確認したいものの、ホスト名が検証できない場合や、ホスト名構成を制御できない場合に使用します。
  • 暗号化のみ: このモードでは、クライアントとサーバー間で転送されるすべてのデータが暗号化されます。証明書やホスト名の検証は行いません。
    • このモードでは、転送中のデータを保護することで一定のセキュリティが確保されますが、PITM 攻撃に対して脆弱になる可能性があります。
    • すべてのデータを暗号化する必要があるものの、サーバーの ID を検証できない場合や検証したくない場合は、このモードを使用します。プライベート VPC を使用する場合は、このモードを使用することをおすすめします。
  • 暗号化または検証なし: このモードでは、データは暗号化されず、証明書やホスト名の検証も行われません。すべてのデータは書式なしテキストとして送信されます。
    • 機密データが処理される環境では、このモードを使用しないことをおすすめします。
    • このモードは、セキュリティが問題にならない分離されたネットワークでのテスト目的でのみ使用することをおすすめします。

信頼できるサーバー証明書（PEM）

[データを暗号化し、CA とホスト名を検証する] モードまたは [データを暗号化し、CA を検証する] モードを使用している場合は、1 つ以上の PEM エンコード証明書を指定することもできます。これらの証明書は、BigQuery Data Transfer Service が TLS 接続中にデータベース サーバーの ID を確認する必要があるシナリオで必要になります。

  • 組織内のプライベート CA によって署名された証明書または自己署名証明書を使用している場合は、完全な証明書チェーンまたは単一の自己署名証明書を指定する必要があります。これは、Amazon Relational Database Service（RDS）などのマネージド クラウド プロバイダ サービスの内部 CA によって発行された証明書に必要です。
  • データベース サーバーの証明書が公開 CA（Let's Encrypt、DigiCert、GlobalSign など）によって署名されている場合は、証明書を指定する必要はありません。これらのパブリック CA のルート証明書は、BigQuery Data Transfer Service によってプリインストールされ、信頼されています。

PostgreSQL 転送構成を作成するときに、次の要件を満たす PEM エンコード証明書を [信頼できる PEM 証明書] フィールドに指定できます。

  • 証明書は、有効な PEM エンコード証明書チェーンである必要があります。
  • 証明書は完全に正しいものでなければなりません。チェーン内の証明書が欠落している場合や、内容が正しくない場合、TLS 接続は失敗します。
  • 単一の証明書の場合は、データベース サーバーから単一の自己署名証明書を指定できます。
  • プライベート CA が発行した完全な証明書チェーンの場合は、完全な信頼チェーンを指定する必要があります。これには、データベース サーバーの証明書と、中間 CA 証明書とルート CA 証明書が含まれます。

始める前に

必要なロール

Pub/Sub の転送実行通知を設定する場合は、Identity and Access Management（IAM）権限の pubsub.topics.setIamPolicy が付与されていることを確認します。メール通知のみを設定する場合は、Pub/Sub の権限は必要ありません。詳細については、BigQuery Data Transfer Service の実行通知をご覧ください。

BigQuery Data Transfer Service データ転送の作成に必要な権限を取得するには、プロジェクトに対する BigQuery 管理者 （roles/bigquery.admin）IAM ロールを付与するよう管理者に依頼します。ロールの付与については、プロジェクト、フォルダ、組織に対するアクセス権の管理をご覧ください。

この事前定義ロールには、BigQuery Data Transfer Service データ転送の作成に必要な権限が含まれています。必要とされる正確な権限については、「必要な権限」セクションを開いてご確認ください。

必要な権限

BigQuery Data Transfer Service データ転送を作成するには、次の権限が必要です。

  • BigQuery Data Transfer Service の権限:
    • bigquery.transfers.update
    • bigquery.transfers.get
  • BigQuery の権限:
    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy
    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy
    • bigquery.jobs.create

カスタムロールや他の事前定義ロールを使用して、これらの権限を取得することもできます。

詳細については、bigquery.admin アクセス権を付与するをご覧ください。

ネットワーク接続

PostgreSQL データベース接続にパブリック IP アドレスを使用できない場合は、ネットワーク アタッチメントを設定する必要があります。

必要なネットワーク設定の詳細な手順については、次のドキュメントをご覧ください。

PostgreSQL のデータ転送を設定する

次のいずれかのオプションを使用して転送構成を設定し、PostgreSQL データを BigQuery に追加します。

コンソール

  1. [データ転送] ページに移動します。

    [データ転送] に移動

  2. [転送を作成] をクリックします。

  3. [ソースタイプ] セクションの [ソース] で、[PostgreSQL] を選択します。

  4. [転送構成名] セクションの [表示名] に、転送名を入力します。転送名には、後で修正が必要になった場合にその転送を特定できる任意の名前を使用できます。

  5. [スケジュール オプション] セクションで、次の操作を行います。

    • [繰り返しの頻度] を選択します。[時間]、[日]（デフォルト）、[週]、[月] のいずれかを選択する場合は、頻度も指定する必要があります。[カスタム] オプションを選択して、より詳細な繰り返し頻度を作成することもできます。[オンデマンド] オプションを選択すると、このデータ転送は手動で転送をトリガーした場合にのみ実行されます。
    • 必要に応じて、[すぐに開始] を選択するか、[設定した時刻に開始] オプションを選択して開始日と実行時間を指定します。

  6. [転送先の設定] セクションの [データセット] で、データを保存するために作成したデータセットを選択するか、[新しいデータセットの作成] をクリックして、転送先データセットとして使用するデータセットを作成します。

  7. [データソースの詳細] セクションで、次のようにします。

    • [ネットワーク アタッチメント] で、既存のネットワーク アタッチメントを選択するか、[ネットワーク アタッチメントの作成] をクリックします。詳細については、このドキュメントの「ネットワーク接続」セクションをご覧ください。
    • [ホスト] に、PostgreSQL データベース サーバーのホスト名または IP アドレスを入力します。
    • [ポート番号] に、PostgreSQL データベース サーバーのポート番号を入力します。
    • [データベース名] に、PostgreSQL データベースの名前を入力します。
    • [ユーザー名] に、PostgreSQL データベース接続を開始する PostgreSQL ユーザーのユーザー名を入力します。
    • [パスワード] に、PostgreSQL データベース接続を開始する PostgreSQL ユーザーのパスワードを入力します。
    • [TLS モード] で、メニューからオプションを選択します。TLS モードの詳細については、TLS 構成をご覧ください。
    • [信頼できる PEM 証明書] に、データベース サーバーの TLS 証明書を発行した認証局（CA）の公開証明書を入力します。詳細については、信頼できるサーバー証明書（PEM）をご覧ください。

    • [転送する PostgreSQL オブジェクト] では、次のいずれかを行います。

      • [参照] をクリックして、転送に必要な PostgreSQL テーブルを選択し、[選択] をクリックします。
      • 転送する PostgreSQL オブジェクトのテーブル名を手動で入力します。

  8. 省略可: [通知オプション] セクションで、次のようにします。

    • メール通知を有効にするには、[メール通知] の切り替えをクリックしてオンにします。このオプションを有効にすると、転送の実行が失敗した場合、転送管理者にメール通知が送信されます。
    • 転送の Pub/Sub 実行通知を構成するには、[Pub/Sub 通知] の切り替えをクリックしてオンにします。トピック名を選択するか、[トピックを作成する] をクリックしてトピックを作成します。

  9. [保存] をクリックします。

bq

bq mk コマンドを入力して、転送作成フラグ --transfer_config を指定します。

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

次のように置き換えます。

  • PROJECT_ID（省略可）: Google Cloud プロジェクト ID。--project_id フラグで特定のプロジェクトを指定しない場合は、デフォルトのプロジェクトが使用されます。
  • DATA_SOURCE: データソース（postgresql）。
  • DISPLAY_NAME: データ転送構成の表示名。転送名には、後で修正が必要になった場合に識別できる任意の名前を使用できます。
  • DATASET: データ転送構成のターゲット データセット。

  • PARAMETERS: 作成される転送構成のパラメータを JSON 形式で指定します。例: --params='{"param":"param_value"}'。PostgreSQL 転送のパラメータは次のとおりです。

    • connector.networkAttachment（省略可）: PostgreSQL データベースに接続するネットワーク アタッチメントの名前。
    • connector.database: PostgreSQL データベースの名前。
    • connector.endpoint.host: データベースのホスト名または IP アドレス。
    • connector.endpoint.port: データベースのポート番号。
    • connector.authentication.username: データベース ユーザーのユーザー名
    • connector.authentication.password: データベース ユーザーのパスワード。
    • connector.tls.mode: この転送で使用する TLS 構成を指定します。
      • ENCRYPT_VERIFY_CA_AND_HOST: データを暗号化し、CA とホスト名を検証する
      • ENCRYPT_VERIFY_CA: データを暗号化し、CA のみを検証する
      • ENCRYPT_VERIFY_NONE: データの暗号化のみ行う
      • DISABLE: 暗号化や検証を行わない場合に指定
    • connector.tls.trustedServerCertificate: （省略可）1 つ以上の PEM エンコード証明書を指定します。connector.tls.mode が ENCRYPT_VERIFY_CA_AND_HOST または ENCRYPT_VERIFY_CA の場合にのみ必須です。
    • assets: 転送の一部として PostgreSQL データベースから転送される PostgreSQL のテーブル名のリスト。

たとえば、次のコマンドは My Transfer という名前の PostgreSQL 転送を作成します。

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

API

projects.locations.transferConfigs.create メソッドを使用して、TransferConfig リソースのインスタンスを指定します。

転送構成を保存すると、PostgreSQL コネクタはスケジュール オプションに従って転送実行を自動的にトリガーします。転送を実行するたびに、PostgreSQL コネクタは PostgreSQL から BigQuery に使用可能なすべてのデータを転送します。

定期的なスケジュール外でデータ転送を手動で実行するには、バックフィル実行を開始します。

データ型マッピング

次の表では、PostgreSQL のデータ型を対応する BigQuery のデータ型にマッピングしています。

PostgreSQL のデータ型 BigQuery のデータ型
bigint INTEGER
bigserial INTEGER
bit(n) STRING
bit varying(n) STRING
boolean BOOLEAN
bytea BYTES
character STRING
character varying STRING
double precision FLOAT
integer INTEGER
numeric(precision, scale)/decimal(precision, scale) NUMERIC
real FLOAT
smallint INTEGER
smallserial INTEGER
serial INTEGER
text STRING
date DATE
time [ (p) ] [ without timezone ] TIMESTAMP
time [ (p) ] with time zone TIMESTAMP
timestamp [ (p) ] [ without timezone ] TIMESTAMP
timestamp [ (p) ] with time zone TIMESTAMP
xml STRING
tsquery STRING
tsvector STRING
uuid STRING
box STRING
cidr STRING
circle STRING
inet STRING
interval STRING
json STRING
jsonb STRING
line STRING
lseg STRING
macaddr STRING
macaddr8 STRING
money STRING
path STRING
point STRING
polygon STRING

トラブルシューティング

データ転送の設定で問題が発生した場合は、PostgreSQL の転送に関する問題をご覧ください。

次のステップ