连接到 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 对等互连；请使用可在 VPC 网络中解析的 IP 地址或主机名进行连接。
    • 如果您通过公共互联网进行连接，请将所有 Google IP 地址范围添加到 PostgreSQL 数据库的防火墙规则中。

所需的角色

如需获得连接到 PostgreSQL 所需的权限，请让您的管理员为您授予以下 IAM 角色：

如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

您也可以通过自定义角色或其他预定义角色来获取所需的权限。

将 BigQuery 连接到 PostgreSQL

您可以在Google Cloud 控制台或 bq 命令行工具中将 BigQuery 连接到 PostgreSQL。

控制台

  1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

    转到 BigQuery

  2. 在浏览器窗格中，点击 添加数据。

    添加数据页面随即会打开。

  3. 在过滤条件窗格中的数据源类型部分，选择数据库。

    或者，在搜索数据源字段中，输入 postgresql。

  4. 在精选数据源部分中，点击 PostgreSQL。

  5. 在就地访问外部数据部分中，点击说明为对 PostgreSQL 的联合查询的 BigQuery 联合解决方案卡片（而不是 Cloud SQL 或 AlloyDB for PostgreSQL 卡片）。

    外部数据源窗格随即打开。

  6. 在外部数据源窗格中，配置以下内容：

    • 在连接 ID 字段中，输入唯一的连接 ID（例如 postgres_prod_conn）。
    • 在位置类型部分中，选择区域，然后选择 BigQuery 数据集所在的区域。使用此连接的查询必须从此区域运行。
    • （可选）在易记名称字段中，输入连接的描述性名称。
    • （可选）在说明字段中，输入此连接资源的说明。
    • 在加密部分中，选择 Google-managed encryption key，或选择 Cloud KMS 密钥以使用客户管理的加密密钥 (CMEK)。
    • 在主机:端口字段中，以 HOST:PORT 格式（例如 10.10.2.2:5432）输入 PostgreSQL 数据库服务器的主机和端口。
    • 在用户名字段中，输入您在前提条件中创建的数据库用户。
    • 在密码字段中，输入数据库用户的密码。
    • 在数据库字段中，输入 PostgreSQL 数据库名称。
    • （可选）在网络连接字段中，如果您使用的是专用连接，请以 projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME 格式输入网络连接的资源路径。
    • 在 TLS 模式列表中，选择一种受支持的 TLS 模式（例如，加密数据，验证 CA 和主机名（推荐））。
    • 在证书授权机构 (CA) 列表中，选择公共 CA (Web PKI)，或选择自定义 CA 并粘贴 PEM 格式的证书。

  7. 点击创建连接。

bq

使用带有 --connection 标志的 bq mk 命令。

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：与您打算与 PostgreSQL 一起查询的数据集匹配的 BigQuery 区域（例如 us-central1）。使用此连接的查询必须从此区域运行。
  • 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：此连接的唯一标识符。
  • （可选）--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 建立的连接，此字段为必填项。

  • 加密数据并仅验证 CA（ENCRYPT_VERIFY_CA 模式）。加密传输中的所有数据，并验证服务器的证书是否由受信任的 CA 签名，但不验证服务器的主机名。如果服务器证书由可信 CA 签名，但您使用与证书 CN 或 SAN 不匹配的 IP 地址或主机名进行连接，请使用此模式。

  • 仅加密（ENCRYPT_VERIFY_NONE 模式）。加密在 BigQuery 与服务器之间传输的所有数据。 不执行证书或主机名验证。仅建议在私有 VPC 中使用。

  • 不加密或验证（DISABLE 模式）。不加密数据，也不执行证书或主机名验证。仅在通过受保护 VPC 网络中的专用网络连接进行连接时才允许。

PEM 格式的可信服务器证书

如果您使用 ENCRYPT_VERIFY_CA_AND_HOST 模式或 ENCRYPT_VERIFY_CA 模式，请执行以下操作：

  • 如果您的数据库服务器证书由公共 CA（例如 Mozilla 根 CA 计划中包含的 CA，包括 Let's Encrypt、DigiCert 或 Google Trust Services）签名，则您无需提供证书。默认情况下，公共 CA 的根证书是受信任的。
  • 如果您使用由组织内的内部或私有 CA 签名的证书，或者使用自签名证书，则必须在连接设置期间，在受信任的 PEM 证书字段中提供隐私增强邮件 (PEM) 格式的完整证书链。

与用户共享连接

您可以授予以下角色，以使用户可以查询数据并管理连接：

  • roles/bigquery.connectionUser：允许用户使用连接与外部数据源建立连接，并对其运行查询。

  • roles/bigquery.connectionAdmin：允许用户管理连接。

如需详细了解 BigQuery 中的 IAM 角色和权限，请参阅预定义的角色和权限。

从下列选项中选择一项：

控制台

  1. 前往 BigQuery 页面。

    转到 BigQuery

    连接列在项目的连接组中。

  2. 在左侧窗格中，点击 探索器：

    突出显示的“探索器”窗格按钮。

    如果您没有看到左侧窗格，请点击 展开左侧窗格以打开该窗格。

  3. 点击您的项目，点击连接，然后选择一个连接。

  4. 在详细信息窗格中，点击共享以共享连接。之后，执行以下操作：

    1. 在连接权限对话框中，通过添加或修改主账号与其他主账号共享连接。

    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");
    }
  }
}

价格

联合查询和连接需支付以下费用：

后续步骤