从 AlloyDB 访问 BigQuery

本页介绍了如何使用 Lakehouse Federation 通过 AlloyDB for PostgreSQL 页面访问使用 BigQuery 存储或可访问的数据。

外部数据封装容器支持各种 BigQuery 资源，可让您查询以下内容：

通过使用此集成，您可以将 BigQuery 数据集视为 PostgreSQL 环境中的本地表，以执行跨引擎分析。如需了解详情，请参阅 AlloyDB 中的 Lakehouse Federation 概览。

本页面假定您已有 AlloyDB 集群和主实例，并且已有 BigQuery 数据集和表。如需了解详情，请参阅创建数据集和创建和使用表。

准备工作

  1. 确保在 AlloyDB for PostgreSQL 实例上配置了 bigquery_fdw.enabled 标志。
  2. 熟悉受支持的 BigQuery 数据类型和列映射。

  3. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  4. Verify that billing is enabled for your Google Cloud project.

  5. Enable the AlloyDB, Compute Engine, Resource Manager, and BigQuery APIs.

    Roles required to enable APIs

    To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

    Enable the APIs

  6. 启用创建和连接到 AlloyDB for PostgreSQL 所需的 Cloud API。

    启用 API

  7. 在确认项目步骤中，点击下一步以确认您要更改的项目的名称。

  8. 在启用 API 步骤中，点击启用以启用以下内容：

    • AlloyDB API
    • Compute Engine API
    • Cloud Resource Manager API
    • Service Networking API
    • BigQuery Storage API
    • BigQuery API

    如果您计划使用与 AlloyDB 位于同一 Google Cloud 项目中的 VPC 网络配置与 AlloyDB 的网络连接，则需要使用 Service Networking API。

    如果您计划使用位于其他 Google Cloud 项目中的 VPC 网络配置与 AlloyDB 的网络连接，则需要使用 Compute Engine API 和 Cloud Resource Manager API。

所需的角色

如需向 AlloyDB 集群服务账号授予对 BigQuery 数据集的读取权限，您需要拥有以下权限。如需了解详情，请参阅为 AlloyDB 授予对 BigQuery 数据集的访问权限。

  • BigQuery Data Viewer (roles/bigquery.dataViewer) 或具有 bigquery.tables.get 和 bigquery.tables.getData 权限的任何自定义角色。针对表或视图授予此角色后，可提供从表或视图中读取数据和元数据的权限。
  • BigQuery Read Session User (roles/bigquery.readSessionUser) 或具有 bigquery.readsessions.create 和 bigquery.readsessions.getData 权限的任何自定义角色。提供创建和使用读取会话的功能。
  • BigQuery Job User (roles/bigquery.jobUser) 或具有 bigquery.jobs.create 权限的任何自定义角色。提供使用 BigQuery API 在项目中运行作业（包括查询）的权限。此角色只能在 Resource Manager 资源（项目、文件夹和组织）上授予。
  • Storage Object Viewer (roles/storage.objectViewer) 或具有 storage.objects.get 权限的任何自定义角色。提供访问 BigQuery 外部表的权限。必须在项目级层或存储桶级层授予。

为 AlloyDB 授予对 BigQuery 数据集的访问权限

在 AlloyDB 集群上启用 Lakehouse 联邦功能后，您必须为 AlloyDB 集群服务账号授予对 BigQuery 数据集的访问权限。

当您使用 AlloyDB Studio 连接 BigQuery 表时， Google Cloud 控制台会自动向集群服务账号授予所需的权限。

如需使用 gcloud CLI 授予访问权限，请按以下步骤操作：

gcloud

如需使用 gcloud CLI，您可以安装并初始化 Google Cloud CLI，也可以使用 Cloud Shell。

  1. 打开 gcloud CLI。如果您未安装 gcloud CLI，请安装并初始化 gcloud CLI，或使用 Cloud Shell。

  2. 运行 gcloud beta alloydb clusters describe 命令：

    gcloud beta alloydb clusters describe CLUSTER --region=REGION

    替换以下内容：

    • CLUSTER：AlloyDB 集群 ID。
    • REGION：AlloyDB 集群的位置，例如 asia-east1、us-east1。如需查看完整的区域列表，请参阅 AlloyDB 位置。

    输出包含 serviceAccountEmail 字段，该字段是此集群的服务账号。您还可以在集群概览页面上找到服务账号。

  3. 授予所需权限。 如需了解详情，请参阅使用 IAM 控制对资源的访问权限。

    如果集群服务账号没有所需的权限，则针对 BigQuery 表执行查询时会显示以下错误：

    • The user does not have bigquery.readsessions.create permissions
    • Permission bigquery.tables.get denied on table
    • Permission bigquery.tables.getData denied on table

配置扩展程序

如需配置扩展程序，请按以下步骤操作：

控制台

  1. 前往集群页面。

    转到集群

  2. 点击要使用的集群的 ID。

  3. 在导航菜单中，点击 AlloyDB Studio。

  4. 登录您的数据库。

  5. 在探索器窗格中，展开相关架构。

  6. 点击 BigQuery 表旁边的 操作菜单，然后点击关联 BigQuery 表。

  7. 在连接 BigQuery 表窗格中，选择源项目、源数据集和表。

  8. 查看并选择列表格会显示所选表格中的列。 选择要映射的列。

  9. 在表名称字段中，输入外部表的名称。

  10. 可选：点击查看 SQL 命令可查看生成的命令。

  11. 点击连接表格。系统会显示一个对话框，其中显示了进度。该进程完成后，您可以像查询 AlloyDB 中的任何表一样查询该表。

psql

  1. 创建 扩展程序：

    1. 按照将 psql 客户端连接到实例中的说明，使用 psql 客户端连接到 AlloyDB 实例。 或者，您也可以使用 AlloyDB Studio。如需了解详情，请参阅使用 Google Cloud 控制台管理您的数据。

    2. 运行以下命令：

      CREATE EXTENSION bigquery_fdw;

  2. 创建外部服务器以定义远程 BigQuery 数据集的连接参数。

    CREATE SERVER BIGQUERY_SERVER_NAME FOREIGN DATA WRAPPER bigquery_fdw;

    替换以下内容：

    • BIGQUERY_SERVER_NAME：外部服务器的唯一标识符。在给定数据库中定义一次。您可以将 BIGQUERY_SERVER_NAME 替换为您的服务器名称。

  3. 运行 CREATE USER MAPPING 命令创建用户映射，该命令用于映射要连接到外部服务器的本地 PostgreSQL 用户。

    CREATE USER MAPPING FOR USERNAME SERVER BIGQUERY_SERVER_NAME ;

    替换以下内容：

    • USERNAME：数据库用户名或访问外部表的 IAM 用户。
    • BIGQUERY_SERVER_NAME：您创建的外部服务器的唯一标识符。

  4. 使用 CREATE FOREIGN TABLE 命令定义与您要在 BigQuery 中访问的表对应的外部表。此命令可让您定义远程表的结构。外部表可以包含 BigQuery 中源表的所有列或部分列。

    CREATE FOREIGN TABLE TABLENAME (
COLUMN1_NAME DATA_TYPE,
COLUMN2_NAME DATA_TYPE,
... ) SERVER BIGQUERY_SERVER_NAME OPTIONS (project BIGQUERY_PROJECT_ID,
dataset BIGQUERY_DATASET_NAME,
table BIGQUERY_TABLE_NAME
[, mode EXECUTION_MODE]);

    替换以下内容：

    • TABLENAME：本地 AlloyDB 数据库中外部表的名称。
    • COLUMNX_NAME：AlloyDB 列名称。列名称必须与 BigQuery 源表中的相应列名称完全一致。 X 表示可以创建包含多列的表格。 名称还必须与 BigQuery 列的大小写完全一致。如果 BigQuery 列名称包含大写字母（例如 employeeID），则必须将 AlloyDB 标识符用双引号括起来（例如 "employeeID"），以保留大小写混合或大写字母。
    • DATA_TYPE：列的数据类型。
    • BIGQUERY_SERVER_NAME：您创建的外部服务器的唯一标识符。
    • BIGQUERY_PROJECT_ID：BigQuery 数据集所在项目的 ID。
    • BIGQUERY_DATASET_NAME：表的 BigQuery 数据集的名称。
    • BIGQUERY_TABLE_NAME：BigQuery 表的名称。
    • EXECUTION_MODE：可选。mode 选项可以是 query（使用 BigQuery API 进行复杂查询）、storage（使用 BigQuery Storage API 进行更快的批量读取）或 auto（在模式之间自动选择）；默认值为 auto。如需了解详情，请参阅 BigQuery 外部数据封装容器执行模式。

    创建外部表后，您可以按照与查询 AlloyDB 中的任何表相同的方式查询此表。

BigQuery 外部数据封装容器执行模式

执行模式决定了 AlloyDB for PostgreSQL 如何与 BigQuery 交互以检索数据。BigQuery 外部数据封装容器支持两种执行模式：query 和 storage。选择合适的模式至关重要，因为每种模式都具有不同的性能特征和价格。如需了解详情，请参阅 BigQuery 价格。

查询模式

此模式使用 BigQuery API 从 BigQuery 检索数据。它使用 BigQuery 的计算引擎通过下推过滤条件和聚合来运行复杂的查询。也就是说，WHERE 子句、GROUP BY 子句和聚合在 BigQuery 上执行，然后再将数据发送回 PostgreSQL。此模式还支持查询 BigQuery 视图和外部表。

由于此 API 提供适合小型结果集的结构化分页行响应，因此与 BigQuery Storage API 的流式传输替代方案相比，读取大型数据集时存在吞吐量限制，且延迟时间更长。

存储模式

此模式使用 BigQuery Storage API 从 BigQuery 检索数据。它通过以二进制序列化格式通过网络发送结构化数据，实现高吞吐量读取。这是在 BigQuery 中扫描大型表的首选模式。

不过，此模式存在一些限制。并非所有复杂的 SQL 操作都可以下推到 BigQuery Storage API。例如，聚合无法下推到 BigQuery，必须在 AlloyDB 中执行。此模式也不支持查询 BigQuery 视图和外部表。

自动模式

如果您未在 CREATE FOREIGN TABLE 命令中设置模式，则默认模式会设置为 auto。当您使用 auto 模式时，AlloyDB 会选择底层 API 来平衡性能，并尽可能将 SQL 操作下推到 BigQuery。

后续步骤