連線至 PostgreSQL

BigQuery 管理員可以建立連線，存取 PostgreSQL 資料。建立連線後，資料分析師就能查詢 PostgreSQL 中的資料。

支援的 PostgreSQL 環境和版本

BigQuery 聯合查詢支援下列 PostgreSQL 部署環境：

  • 透過 Cloud VPN 或 Cloud Interconnect 連線至 Google Cloud的地端部署 PostgreSQL 執行個體
  • 在 Compute Engine 上自行管理 PostgreSQL 執行個體
  • 可透過公開 IP 位址或網路連結存取的雲端託管 PostgreSQL 執行個體

BigQuery 聯合查詢支援下列 PostgreSQL 版本：

  • PostgreSQL 16 (建議)
  • PostgreSQL 15

事前準備

啟用必要的 API、設定 PostgreSQL 資料庫，並授予 Identity and Access Management 角色，讓使用者具備執行本文件各項工作所需的權限。

  1. 啟用 BigQuery Connection API。

    啟用 API

  2. 如果您打算使用網路連結，透過私人網路轉送流量，也請啟用 Compute Engine API。

    啟用 Compute Engine API

  3. 在 PostgreSQL 資料庫中，建立專用資料庫使用者，並針對要查詢的資料表或結構定義，授予 SELECT 權限：

    CREATE USER USER_NAME WITH PASSWORD 'PASSWORD';
GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;

    更改下列內容：

    • USER_NAME：資料庫使用者的使用者名稱。
    • PASSWORD：資料庫使用者的密碼。
    • SCHEMA_NAME：要查詢的資料表結構定義。

  4. 請採取下列任一做法，設定 PostgreSQL 資料庫執行個體的網路連線：

    • 如果您透過私人網路轉送流量，請在 BigQuery 連線所在的區域建立網路連結。不支援 DNS 對接；請使用可在虛擬私有雲網路中解析的 IP 位址或主機名稱連線。
    • 如果您是透過公開網際網路連線，請將所有 Google IP 位址範圍新增至 PostgreSQL 資料庫的防火牆規則。

必要的角色

如要取得連線至 PostgreSQL 所需的權限，請要求管理員授予您下列 IAM 角色：

如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

您或許也能透過自訂角色或其他預先定義的角色，取得必要權限。

將 BigQuery 連結至 PostgreSQL

您可以在Google Cloud 控制台或 bq 指令列工具中，將 BigQuery 連結至 PostgreSQL。

控制台

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往 BigQuery

  2. 在「Explorer」窗格中，點選「 新增資料」。

    「Add data」頁面隨即開啟。

  3. 在「Filter By」(篩選依據) 窗格的「Data Source Type」(資料來源類型) 部分，選取「Databases」(資料庫)。

    或者，您也可以在「Search for data sources」(搜尋資料來源) 欄位中輸入 postgresql。

  4. 在「精選資料來源」部分，點選「PostgreSQL」。

  5. 在「Access external data in place」(原地存取外部資料) 區段中，點選說明為「Federated queries to PostgreSQL」(PostgreSQL 聯合查詢) 的「BigQuery Federation」(BigQuery 聯合) 解決方案資訊卡 (而非 Cloud SQL 或 AlloyDB for PostgreSQL 資訊卡)。

    「外部資料來源」窗格隨即開啟。

  6. 在「外部資料來源」窗格，設定下列項目：

    • 在「Connection ID」(連線 ID) 欄位中，輸入不重複的連線 ID (例如 postgres_prod_conn)。
    • 在「Location type」(位置類型) 區段中，選取「Region」(區域)，然後選取 BigQuery 資料集所在的區域。使用這個連線的查詢必須從這個區域執行。
    • (選用) 在「Friendly name」(好記名稱) 欄位中，輸入連線的描述性名稱。
    • (選用) 在「Description」(說明) 欄位中，輸入這項連線資源的說明。
    • 在「Encryption」(加密) 部分，選取「Google-managed encryption key」， 或選取「Cloud KMS key」(Cloud KMS 金鑰)，使用 客戶自行管理的加密金鑰 (CMEK)。
    • 在「Host:port」(主機:通訊埠) 欄位中，以 HOST:PORT 格式輸入 PostgreSQL 資料庫伺服器的主機和通訊埠 (例如 10.10.2.2:5432)。
    • 在「Username」(使用者名稱) 欄位中，輸入您在必要條件中建立的資料庫使用者。
    • 在「Password」(密碼) 欄位中，輸入資料庫使用者的密碼。
    • 在「Database」(資料庫) 欄位中，輸入 PostgreSQL 資料庫名稱。
    • (選用) 如果您使用私人連線，請在「Network attachment」(網路連結) 欄位中，以 projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME 格式輸入網路連結的資源路徑。
    • 在「TLS mode」(TLS 模式) 清單中，選取其中一種支援的 TLS 模式 (例如「Encrypt data, verify CA and host name (recommended)」(加密資料，驗證憑證授權單位和主機名稱 (建議)))。
    • 在「Certificate authority (CA)」(憑證授權單位 (CA)) 清單中，選取「Public CA (Web PKI)」(公開 CA (Web PKI))，或選取自訂 CA 並貼上 PEM 格式的憑證。

  7. 點選「建立連線」。

bq

使用 bq mk 指令，並加上 --connection 旗標：

bq mk --connection \
  --location=LOCATION \
  --project_id=PROJECT_ID \
  --connector_configuration '{
    "connector_id": "postgresql",
    "endpoint": {
      "host_port": "HOST_PORT"
    },
    "asset": {
      "database": "DATABASE"
    },
    "authentication": {
      "username_password": {
        "username": "USERNAME",
        "password": {
          "plaintext": "PASSWORD"
        }
      }
    },
    "tls": {
      "mode": "TLS_MODE"
    },
    "network": {
      "private_service_connect": {
        "network_attachment": "projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME"
      }
    }
  }' \
  CONNECTION_ID

更改下列內容：

  • LOCATION：BigQuery 區域 (例如 us-central1)，與您要和 PostgreSQL 一起查詢的資料集相符。使用這個連線的查詢必須從這個區域執行。
  • PROJECT_ID：您的 Google Cloud 專案 ID。
  • HOST_PORT：PostgreSQL 資料庫伺服器的主機和通訊埠，格式為 HOST:PORT (例如 10.10.2.2:5432)。
  • DATABASE：PostgreSQL 資料庫名稱。
  • USERNAME：PostgreSQL 資料庫使用者。
  • PASSWORD：資料庫使用者的密碼。
  • TLS_MODE：TLS 加密模式 (ENCRYPT_VERIFY_CA_AND_HOST、ENCRYPT_VERIFY_CA、ENCRYPT_VERIFY_NONE 或 DISABLE)。如果您使用自訂 CA 憑證搭配 ENCRYPT_VERIFY_CA_AND_HOST 或 ENCRYPT_VERIFY_CA，請在 "tls" 下方加入 "private_pki": { "trusted_certificates_pem": "CERT_CONTENT" } 物件。
  • NA_REGION：網路連結所在的區域。
  • ATTACHMENT_NAME：網路連結的名稱。如果透過公開 IP 連線，請省略 "network" 物件。
  • CONNECTION_ID：這個連線的專屬 ID。
  • (選用) --kms_key_name=KMS_KEY_NAME：客戶自行管理的加密金鑰 (CMEK)。如果省略，憑證會受到預設 Google-owned and Google-managed encryption key保護。

傳輸層安全標準 (TLS) 設定

PostgreSQL 連接器支援傳輸層安全標準 (TLS)，可加密 BigQuery 和 PostgreSQL 資料庫之間傳輸的查詢流量。

將 BigQuery 連結至 PostgreSQL 時，請選取下列其中一種傳輸層安全標準 (TLS) 模式：

  • 加密資料，並驗證憑證授權單位 (CA) 和主機名稱 (ENCRYPT_VERIFY_CA_AND_HOST 模式)。使用 TLS 執行完整的伺服器驗證。加密所有傳輸中的資料、驗證資料庫伺服器的憑證是否由信任的 CA 簽署，並檢查您連線的主機名稱是否與憑證中的一般名稱 (CN) 或主體別名 (SAN) 相符。透過公開 IP 連線時，必須提供這項資訊。

  • 加密資料，且僅驗證憑證授權單位 (ENCRYPT_VERIFY_CA 模式)。加密所有傳輸中的資料，並驗證伺服器的憑證是否由受信任的 CA 簽署，但不會驗證伺服器的主機名稱。如果伺服器憑證是由信任的 CA 簽署，但您使用與憑證 CN 或 SAN 不符的 IP 位址或主機名稱連線，請使用這個模式。

  • 僅加密 (ENCRYPT_VERIFY_NONE 模式)。加密 BigQuery 和伺服器之間傳輸的所有資料。不會執行憑證或主機名稱驗證。建議僅在私人虛擬私有雲中使用。

  • 不加密或驗證 (DISABLE 模式)。不加密資料，也不會執行憑證或主機名稱驗證。只有在透過受保護虛擬私有雲網路中的私人網路連結連線時，才允許這項操作。

PEM 格式的信任伺服器憑證

如果您使用 ENCRYPT_VERIFY_CA_AND_HOST 模式或 ENCRYPT_VERIFY_CA 模式，請執行下列操作：

  • 如果資料庫伺服器憑證是由公開 CA 簽署 (例如 Mozilla 根 CA 計畫中的憑證，包括 Let's Encrypt、DigiCert 或 Google Trust Services)，則不需提供憑證。公開 CA 的根憑證預設為受信任。
  • 如果您使用由貴組織內部或私人 CA 簽署的憑證，或自行簽署的憑證，則必須在連線設定期間，於「Trusted PEM certificate」(信任的 PEM 憑證) 欄位中，提供隱私強化郵件 (PEM) 格式的完整憑證鏈結。

與使用者共用連線

您可以授予下列角色，讓使用者查詢資料及管理連結：

  • roles/bigquery.connectionUser：可讓使用者透過連線連結外部資料來源，並對這些來源執行查詢。

  • roles/bigquery.connectionAdmin：可讓使用者管理連線。

如要進一步瞭解 BigQuery 的 IAM 角色和權限，請參閱「預先定義的角色和權限」。

選取下列選項之一：

控制台

  1. 前往「BigQuery」頁面。

    前往「BigQuery」

    連線會列在專案中名為「連線」的群組。

  2. 點選左側窗格中的 「Explorer」。

    醒目顯示的「Explorer」窗格按鈕。

    如果沒有看到左側窗格，請按一下 「Expand left pane」(展開左側窗格)，開啟窗格。

  3. 依序點選專案和「連線」，然後選取連線。

  4. 在「詳細資料」窗格中，按一下「共用」即可共用連線。接著，按照下列步驟操作：

    1. 在「Connection permissions」(連線權限) 對話方塊中，新增或編輯主體，與其他主體共用連線。

    2. 按一下「儲存」。

bq

您無法使用 bq 指令列工具共用連線。如要共用連線，請使用 Google Cloud 控制台或 BigQuery Connections API 方法。

API

使用 BigQuery Connections REST API 參考資料部分的 projects.locations.connections.setIAM 方法，並提供 policy 資源的執行個體。

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:example-analyst-group@google.com")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}

定價

聯合查詢和連線會產生下列費用：

後續步驟