PostgreSQL 連携クエリを実行する

データ アナリストは、データをコピーまたは移動することなく、PostgreSQL に存在するリレーショナル データを BigQuery から直接リアルタイムでクエリできます。

BigQuery PostgreSQL 連携では、BigQuery Connection API を使用して PostgreSQL データベース エンジンに接続します。その後、EXTERNAL_QUERY 関数を使用して SQL クエリを PostgreSQL に直接送信して実行すると、結果が直接ストリーミングされます。

制限事項

PostgreSQL 連携クエリには、次の制限があります。

  • EXTERNAL_QUERY 関数を使用して実行できるのは、読み取り専用クエリのみです。データ定義言語（DDL）ステートメントとデータ操作言語（DML）ステートメントはサポートされていません。
  • 連携には EXTERNAL_QUERY 関数を介してのみアクセスできます。PostgreSQL テーブルに直接マッピングされる BigQuery 外部テーブルまたは永続データセットの作成はサポートされていません。
  • 連携クエリのレイテンシは、PostgreSQL で直接クエリを実行する場合よりも大幅に長くなる可能性があります。
  • 基盤となる接続サービスが、非アクティブな接続を休止状態にする可能性があります。非アクティブな期間の後に PostgreSQL 接続を使用する最初のクエリは、コネクタの初期化中に実行に 1 分以上かかることがあります。
  • DNS ピアリングはサポートされていません。VPC ネットワーク内の IP アドレスまたは完全に解決可能なホスト名を使用して接続する必要があります。
  • PostgreSQL SQL クエリでは、関数の結果または式を含む列（SELECT COUNT(*) AS total_count など）に明示的な列エイリアスを指定する必要があります。エイリアスが設定されていない式では、BigQuery テーブル スキーマ定義で無効な特殊文字を含む列名が生成されます。
  • クエリ プロジェクトでの Compute Engine API の使用が VPC Service Controls によって制限されている場合、接続でネットワーク アタッチメントが使用されていると、連携クエリは失敗します。適切な VPC Service Controls の上り（内向き）ルールと下り（外向き）ルールが構成されていることを確認します。

  • PostgreSQL では、追加の SQL プッシュダウンはサポートされていません。外部の BigQuery クエリのフィルタと集計は、EXTERNAL_QUERY 関数内の内部クエリにプッシュダウンされません。ネットワーク経由で転送される行と列を制限するには、内部の PostgreSQL SQL ステートメント内で述語と列のプロジェクションを直接指定する必要があります。

  • PostgreSQL データベースのユーザー名とパスワードによる認証のみがサポートされています。サードパーティの ID プロバイダ、Kerberos、クラウド プロバイダの IAM 認証はサポートされていません。

  • クエリで 16 ビット整数列（int2、smallint、smallserial）または単精度浮動小数点数列（float4、real）が選択されている場合、CREATE TABLE ... AS SELECT（CTAS）ステートメントは Unsupported type INT32 または Unsupported type FLOAT エラーで失敗します。クエリ結果を新しい BigQuery テーブルに保存するには、クエリで整数列を INT64 に、浮動小数点数列を FLOAT64 に明示的にキャストします。

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_col,
...
FROM EXTERNAL_QUERY(...);

    INSERT INTO <table> SELECT ... を使用して既存のテーブルに挿入すると、BigQuery が INT32 値と FLOAT 値を自動的に拡張してターゲット テーブルの INT64 列と FLOAT64 列に一致させるため、手動キャストなしで成功します。

  • time [ without time zone ] と time with time zone（timetz）はどちらも、1 秒未満（マイクロ秒）の小数値を秒単位に切り捨てます。マイクロ秒の精度が必要な場合は、EXTERNAL_QUERY ステートメントで列を text または varchar にフォーマットまたはキャストします。

  • すべての PostgreSQL numeric 列と decimal 列は BIGNUMERIC(76, 38) にマッピングされます。スケールが 38 を超える列は小数点以下 38 桁に丸められ、BIGNUMERIC の範囲を超える値（38 桁を超える整数）は範囲外エラーで失敗します。また、特別な値 NaN、Infinity、-Infinity はサポートされておらず、実行時に失敗します。整数部分が大きい数値、小数部分の精度が高い数値、または特殊な値をクエリするには、EXTERNAL_QUERY ステートメントで列を text にキャストします。

  • 固定長の character(n) / char(n)（bpchar）値は、BigQuery STRING にマッピングされるときに、長さ n までの末尾のスペースを保持します。

  • 1582 年 10 月 15 日より前の日付とタイムスタンプは、歴史的なグレゴリオ暦の移行により、10 日間のカレンダー シフトの対象となります。正確な日付を保持するには、EXTERNAL_QUERY ステートメントで過去の日付を text にキャストします。

サポートされている PostgreSQL の環境とバージョン

クエリ連携では、次のデプロイ環境で PostgreSQL データベースがサポートされています。

  • パブリック IP アドレスまたはネットワーク アタッチメント（Cloud VPN または Cloud Interconnect 経由）を介してアクセス可能な、クラウドホスト型またはオンプレミスの PostgreSQL データベース
  • Compute Engine または他のインフラストラクチャ上のセルフホスト型 PostgreSQL インスタンス
  • PostgreSQL 16（推奨）と PostgreSQL 15

始める前に

BigQuery 管理者が PostgreSQL 接続を作成して、共有していることを確認します。

必要なロール

PostgreSQL をクエリするのに必要な権限を取得するには、プロジェクトに対する BigQuery 接続ユーザー（roles/bigquery.connectionUser）の IAM ロールを付与するよう管理者に依頼してください。ロールの付与については、プロジェクト、フォルダ、組織に対するアクセス権の管理をご覧ください。

必要な権限は、カスタムロールや他の事前定義ロールから取得することもできます。

データのクエリ

GoogleSQL クエリから PostgreSQL に連携クエリを送信するには、EXTERNAL_QUERY 関数を使用します。

次の例は、mydataset.customers という名前の BigQuery の顧客レコードと PostgreSQL のトランザクション販売注文を結合する連携クエリです。

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/postgres_prod_conn',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

PostgreSQL テーブル スキーマを表示する

EXTERNAL_QUERY を使用して PostgreSQL の information_schema テーブルをクエリし、データベース メタデータを取得できます。

スキーマ内のすべてのテーブルを一覧表示する

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_name''');

テーブルの列とデータ型を表示する

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, is_nullable
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

データ型マッピング

連携クエリを実行すると、BigQuery は次の表に従って PostgreSQL のデータを GoogleSQL データ型に変換します。

PostgreSQL のデータ型 BigQuery のデータ型
ブール型
bool、boolean BOOL
数値型
int2、smallint、smallserial INT64（インタラクティブ クエリ。CREATE TABLE ... AS SELECT の制限事項をご覧ください）
int4、integer、serial INT64
int8、bigint、bigserial INT64
numeric、decimal BIGNUMERIC(76, 38)
float4、real FLOAT64（インタラクティブ クエリ。CREATE TABLE ... AS SELECT の制限事項をご覧ください）
float8、double precision FLOAT64
日時型
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone、timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone、timetz TIME
文字とテキストのタイプ
character(n)、char(n)、bpchar STRING
character varying(n)、varchar(n) STRING
text STRING

料金

連携クエリの実行にかかる費用は、次の 3 つの要因に基づきます。

  • PostgreSQL でクエリを実行する際のコンピューティング費用（PostgreSQL データベース プロバイダまたはホスティング環境によって課金されます）
  • PostgreSQL から BigQuery にクエリ結果を転送する際の帯域幅費用（データベース ホスティング プロバイダからの下り（外向き）ネットワーク料金）
  • BigQuery でクエリを実行するコンピューティング費用

PostgreSQL のコンピューティング費用と下り（外向き）費用は、Google ではなく、PostgreSQL プロバイダまたはインフラストラクチャ ホストから直接請求されます。帯域幅コストを抑えるため、クエリは、最終結果の計算に不要な列と行をすべて除外するように、EXTERNAL_QUERY ステートメントに記述することをおすすめします。

BigQuery で連携クエリを実行しても追加料金は発生しません。BigQuery の料金の詳細については、料金をご覧ください。

接続で Google Cloud ネットワーキング コンポーネントを使用している場合は、次の料金も適用されることがあります。

次のステップ