将 BigQuery 数据同步到 AlloyDB

如需让运营应用以低延迟、事务性方式访问分析数据,请将 BigQuery 中的表同步到 AlloyDB for PostgreSQL 实例。与查询 BigQuery 中数据的外部数据封装容器 (FDW) 不同,同步功能会将数据复制到 AlloyDB 存储空间中,以便您的查询从本地表读取数据,从而提高性能。

AlloyDB 提供了以下方法,可将 BigQuery 数据移入实例:

  • 一次性同步:创建 BigQuery 表的可写独立副本。

  • 定期同步或镜像:创建一个只读本地表,该表会按计划(例如每 6 小时或每天)自动刷新。

性能和运营注意事项

使用 BigQuery 同步表时,请注意以下事项:

  • 资源用量:数据移动会消耗 CPU 和内存。对于非常大的表,请考虑在非高峰时段安排同步,以免影响主事务工作负载。
  • 数据可见性:在替换操作期间,系统会预先删除并重新创建现有目标表。在导入期间,查询最初会看到一个空表,然后随着批量事务提交,新导入的数据会逐渐显示。

准备工作

  1. 在 AlloyDB 实例上设置 alloydb_sync.enabled=on 数据库标志。如需了解详情,请参阅配置实例的数据库标志。
  2. 熟悉 bigquery_fdw 如何处理 BigQuery 数据类型和列映射,因为 alloydb_sync 扩展程序使用 bigquery_fdw 连接到 BigQuery。
  3. 登录您的 Google Cloud 账号。如果您是 Google Cloud新手,请创建一个账号来评估我们的产品在实际场景中的表现。新客户还可获享 $300 赠金,用于运行、测试和部署工作负载。
  4. 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

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

  6. 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

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

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

    启用 API

    1. 如需确认您要更改的项目的名称,请在确认项目步骤中点击下一步。

    2. 在启用 API 步骤中,点击启用以启用以下内容:

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

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

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

  9. 确保您有一个现有的 BigQuery 表,可从中同步数据。如需了解详情,请参阅创建和使用 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 权限的任何自定义角色。提供创建和运行作业(包括查询作业)的能力。如果您提供单独的运行时项目,则作业会在该项目中运行,而不是在数据项目中运行。这意味着您必须在运行时项目中授予 Job User 角色。

配置扩展程序

在从 BigQuery 同步表之前,请启用所需的扩展程序并配置与 BigQuery 的连接。如果您使用 Google Cloud 控制台,AlloyDB 会自动执行这些步骤。

  1. 创建 扩展程序:

    1. 按照将 psql 客户端连接到实例中的说明,使用 psql 客户端连接到 AlloyDB 实例。
    2. 运行以下命令:

      CREATE EXTENSION IF NOT EXISTS alloydb_sync;
      
  2. 如需让 AlloyDB 向 BigQuery 进行身份验证,请创建外部服务器和用户映射:

    CREATE EXTENSION IF NOT EXISTS bigquery_fdw;
    CREATE SERVER IF NOT EXISTS bigquery_server FOREIGN DATA WRAPPER bigquery_fdw
      OPTIONS (runtime_project 'RUNTIME_PROJECT_ID');
    CREATE USER MAPPING IF NOT EXISTS FOR USER SERVER bigquery_server;
    

    替换以下内容:

    • RUNTIME_PROJECT_ID:可选。处理查询并产生计算费用的Google Cloud 项目的 ID。如果省略,则查询费用将计入存储数据的项目。
    • USER:访问 BigQuery 表的数据库用户名或 IAM 用户。

同步 BigQuery 表以进行一次性导出

您可以使用Google Cloud 控制台或 psql 同步 BigQuery 表,以进行一次性导出。

使用 Google Cloud 控制台

如需使用Google Cloud 控制台将 BigQuery 表同步到 AlloyDB,请执行以下操作:

  1. 在 Google Cloud 控制台中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 在左侧窗格中,点击 探索器:

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

  3. 在探索器窗格中,展开您的项目,点击数据集,然后点击您的数据集。

  4. 点击概览 > 表,然后选择一个表。

  5. 在详细信息窗格中,点击更多 > 导出或同步到 AlloyDB。

  6. 在选择项目下,为 AlloyDB 集群选择项目。如果您拥有所需权限,则可以选择当前 BigQuery 项目以外的项目。

  7. 在选择目标集群下,选择以下选项之一:

    • 选择使用现有集群,将 BigQuery 表导出到现有 AlloyDB 集群。之后,执行以下操作:

      1. 选择主 AlloyDB 集群。

      2. 选择目标 AlloyDB 数据库。

      3. 选择目标 AlloyDB 表的架构。

      4. 为目标 AlloyDB 表指定名称。

      5. 对于同步频率,选择仅一次以创建 BigQuery 表的副本。

      6. 点击设置导出。

      同步设置完成后,您可以使用提供的 SQL 语句跟踪导入作业并查询导入的表。点击查询,系统会将您定向到 AlloyDB Studio 中导入的表。

    • 选择创建新集群,将 BigQuery 表导出到新的 AlloyDB 集群。之后,执行以下操作:

      1. 点击设置导出。

      2. 在重定向到 AlloyDB 对话框中,选择重定向。

      3. 选择集群类型,免费试用集群或预配的集群。

      4. 点击继续。

      5. 在同步配置中,选择默认的 postgres 目标 AlloyDB 数据库、目标 AlloyDB 表的默认 public 架构,并为目标 AlloyDB 表指定名称。

        对于同步频率,选择仅一次以创建 BigQuery 表的副本。

      6. 点击继续。

      7. 配置集群。 如需详细了解每个字段,请参阅创建新集群和主实例。

      8. 点击创建集群。

      同步设置完成后,前往 AlloyDB Studio 查询导入的表。

使用 psql 一次性同步 BigQuery 表

如需创建 BigQuery 数据的可修改副本,请使用 psql 运行 alloydb_sync.import_bq_table 函数。

SELECT alloydb_sync.import_bq_table(
  'PROJECT_ID.DATASET_ID.TABLE_ID',
  'ALLOYDB_DESTINATION_TABLE_NAME',
  'ON_EXISTS',
  ARRAY['PRIMARY_KEY_COLUMN']
);

替换以下内容:

  • PROJECT_ID:BigQuery 数据集所在项目的 ID。
  • DATASET_ID:表的 BigQuery 数据集的名称。对于名称由 4 部分组成的 Apache Iceberg 表,此部分为 Catalog.Namespace。
  • TABLE_ID:BigQuery 表或视图的名称。
  • ALLOYDB_DESTINATION_TABLE_NAME:AlloyDB 数据库中要创建并导入数据的本地表的名称。您可以添加架构名称,例如 public.local_sales。
  • ON_EXISTS:如果目标表已存在,则要使用的策略。
  • PRIMARY_KEY_COLUMN:要用作主键的列名称的可选列表。

示例

以下示例展示了如何将 BigQuery 数据集中名为 transactions 的表同步到名为 public.local_sales 的新 AlloyDB 表中:

SELECT alloydb_sync.import_bq_table(
    'my-gcp-project.sales_data.transactions',
    'public.local_sales',
    'replace'
);
on_exists 参数

on_exists 参数决定了如果目标表已存在于 AlloyDB 中,函数将如何处理同步:

  • error:默认选项。如果目标表已存在,则停止同步。
  • skip:如果目标表已存在,则跳过同步。
  • replace:将现有本地表替换为来自 BigQuery 的新数据。
主键支持

如果您以文本数组的形式提供可选的 primary_key 参数,AlloyDB 会创建以指定列作为主键的表。

SELECT alloydb_sync.import_bq_table(
    'my-gcp-project.sales_data.transactions',
    'public.local_sales',
    ARRAY['transaction_id']
);

同步 BigQuery 表以进行定期导出

您可以使用Google Cloud 控制台或 psql 同步 BigQuery 表以进行定期导出。

使用 Google Cloud 控制台

如需使用Google Cloud 控制台将 BigQuery 表同步到 AlloyDB,请执行以下操作:

  1. 在 Google Cloud 控制台中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 在左侧窗格中,点击 探索器:

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

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

  3. 在探索器窗格中,展开您的项目,点击数据集,然后点击您的数据集。

  4. 点击概览 > 表,然后选择一个表。

  5. 在详细信息窗格中,点击更多 > 导出或同步到 AlloyDB。

  6. 在选择项目下,为 AlloyDB 集群选择项目。如果您拥有所需权限,则可以选择当前 BigQuery 项目以外的项目。

  7. 在选择目标集群下,选择以下选项之一:

    • 选择使用现有集群,将 BigQuery 表导出到现有 AlloyDB 集群。之后,执行以下操作:

      1. 选择主 AlloyDB 集群。

      2. 选择目标 AlloyDB 数据库。

      3. 选择目标 AlloyDB 表的架构。

      4. 为目标 AlloyDB 表指定名称。

      5. 对于同步频率,选择一个时段以创建 BigQuery 表的定期同步,例如每小时、每六小时。

      6. 点击设置导出。

      同步设置完成后,您可以使用提供的 SQL 语句跟踪导入作业并查询导入的表。点击查询,系统会将您定向到 AlloyDB Studio 中导入的表。

    • 选择创建新集群,将 BigQuery 表导出到新的 AlloyDB 集群。之后,执行以下操作:

      1. 点击设置导出。

      2. 在重定向到 AlloyDB 对话框中,选择重定向。

      3. 选择集群类型,免费试用集群或预配的集群。

      4. 点击继续。

      5. 在同步配置中,选择默认的 postgres 目标 AlloyDB 数据库、目标 AlloyDB 表的默认 public 架构,并为目标 AlloyDB 表指定名称。

        对于同步频率,选择仅一次以创建 BigQuery 表的副本。

      6. 点击继续。

      7. 配置集群。 如需详细了解每个字段,请参阅创建新集群和主实例。

      8. 点击创建集群。

      同步设置完成后,前往 AlloyDB Studio 查询导入的表。

创建定期同步

如需维护与 BigQuery 数据保持同步的只读表,请使用 psql 运行 alloydb_sync.create_bq_sync_table 函数。

SELECT alloydb_sync.create_bq_sync_table(
    'PROJECT_ID.DATASET_ID.TABLE_ID',
    'ALLOYDB_DESTINATION_TABLE_NAME',
    'REFRESH_INTERVAL',
    'ON_EXISTS',
    ARRAY['PRIMARY_KEY_COLUMN']
);

替换以下内容:

  • PROJECT_ID.DATASET_ID.TABLE_ID:BigQuery 表或视图的完全限定名称,包括项目 ID、数据集 ID 和表 ID,以英文句点分隔。对于名称由 4 部分组成的 Iceberg 表,DATASET_ID 表示为 Catalog.Namespace。例如 my-gcp-project.sales_data.transactions。
  • ALLOYDB_DESTINATION_TABLE_NAME:AlloyDB 数据库中要创建并同步数据的本地表的名称。
  • REFRESH_INTERVAL:AlloyDB 定期刷新 BigQuery 中数据的时间间隔,例如 12 hours。
  • ON_EXISTS:如果目标表已存在,则要使用的策略。
  • PRIMARY_KEY_COLUMN:要用作主键的列名称的可选列表。

示例

以下示例展示了如何创建每 12 小时刷新一次的客户资料镜像:

SELECT alloydb_sync.create_bq_sync_table(
    'my-gcp-project.crm_data.profiles',
    'public.customer_mirror',
    '12 hours',
    'replace'
);

监控和管理作业

启动同步后,您可以监控其进度并管理作业。

检查作业状态

同步大量数据可能需要一些时间。您可以通过查询 job_status 视图来监控进度,包括已处理的记录和预计完成时间:

SELECT
    import_id,
    status,
    records_processed,
    total_records,
    error_message
FROM alloydb_sync.job_status;

例如,如需取消作业,请运行以下命令:

SELECT alloydb_sync.cancel_import_job('85bb5dfa-dfb9-4017-9153-738f55abe4b1');

停止并删除同步作业

如需停止镜像 BigQuery 表并删除本地表,请使用 alloydb_sync.delete_bq_sync_table 函数:

SELECT alloydb_sync.delete_bq_sync_table('public.customer_mirror');

数据类型映射

使用 alloydb_sync 扩展程序将数据从 BigQuery 同步或导入到 AlloyDB 时,AlloyDB 会将 BigQuery 数据类型映射到目标表中的相应 PostgreSQL 数据类型。

验证源 BigQuery 表列是否使用下表中列出的受支持数据类型:

BigQuery 表数据类型 PostgreSQL 数据类型
BOOLEAN BOOLEAN
INTEGER (INT64) BIGINT
FLOAT (FLOAT64) DOUBLE PRECISION
STRING TEXT
NUMERIC NUMERIC
BIGNUMERIC NUMERIC
DATE DATE
TIMESTAMP TIMESTAMPTZ
TIME TIME
DATETIME TIMESTAMP
JSON JSONB
BYTES BYTEA

限制

从 BigQuery 同步表时,存在以下限制:

  • 仅 PostgreSQL 18 版支持此功能。
  • 此功能需要在实例上设置 alloydb_sync.enabled=on 数据库标志。
  • 在数据库中配置 bigquery_fdw 外部服务器和用户映射时,必须将外部服务器命名为 bigquery_server。
  • 不支持同步复杂的 BigQuery 数据类型,例如 ARRAY、STRUCT、VECTOR、GEOGRAPHY、RANGE 和 INTERVAL。参数化 BigQuery 数据类型(例如 STRING(L)、BYTES(L)、NUMERIC(P, S) 和 BIGNUMERIC(P, S))会映射到 TEXT。如需了解详情,请参阅受支持的 BigQuery 数据类型和列映射。
  • 指定 primary_key 参数时,主键列名称的逗号分隔列表不得超过 1,023 个字节。此外,行的 JSON 序列化主键列名称和值不得超过 255 个字节。
  • 对于使用 alloydb_sync.create_bq_sync_table 函数创建的定期同步表,refresh_interval 参数不支持月或年。以整数天数、小于 24 的小时数、小于 60 的分钟数或小于 60 的秒数指定间隔。混合单位间隔(例如 1 hour 30 minutes 或 90 minutes)会被截断为最大时间单位的整数,例如 1 hour。
  • 只有发起同步作业或创建定期同步表的用户才能执行以下操作:

    • 在 alloydb_sync.job_status 视图中查看作业
    • 使用 alloydb_sync.cancel_import_job 函数取消作业
    • 使用 alloydb_sync.delete_bq_sync_table 函数删除同步表
  • 同步作业以批量方式(而不是在单个事务中)增量提交数据。在 replace 操作期间,目标表会在数据加载之前被删除并重新创建,因此并发查询会看到空表或部分填充的表,直到作业完成。如果导入作业失败或在完成之前被取消,则部分导入的行和内部外部表定义会保留在数据库中。如需恢复,请将 on_exists 参数设置为 replace,然后重新运行导入。

  • 如果新注册的定期同步表在初始后台导入期间发生中断,则该表在下一个预定刷新间隔之前会保持不完整状态。

  • 当后台作业跟踪器工作器连接到在过去 10 秒内具有定期同步表或同步活动的数据库时,标准 DROP DATABASE 命令会失败。

  • 在未使用 alloydb_sync.create_bq_sync_table 函数配置定期同步表的数据库中,后台作业跟踪器工作器会在空闲 10 秒后关闭。工作器会在下次调用 alloydb_sync.import_bq_table 函数时或在删除并重新创建 alloydb_sync 扩展程序时分配新的动态共享内存段。在没有活跃定期同步表的情况下,运行大量由空闲期分隔的孤立一次性导入可能会耗尽共享内存槽,从而需要重启实例。

最佳做法

在配置和管理 BigQuery 中的表同步时,请遵循以下最佳实践:

  • 在调用任何同步函数之前,请在自己的已提交事务中运行 CREATE EXTENSION IF NOT EXISTS alloydb_sync CASCADE; 语句。
  • 在导入过程中,请避免修改源 BigQuery 表。如果源表同时更新,则同步作业可能会导入更新后的数据。
  • 调用 alloydb_sync.import_bq_table、alloydb_sync.create_bq_sync_table 或 alloydb_sync.delete_bq_sync_table 函数时,请指定一致的、架构限定的目标表名称,例如 public.my_table。请勿同时为同一目标表启动多个同步作业。如果您在同一集群上的多个数据库中同步表,请在各个数据库中使用不同的架构或表名称。
  • 使用 alloydb_sync.create_bq_sync_table 函数创建定期同步表时,请将 on_exists 参数设置为 replace,以便通过定期刷新来更新目标表。将 refresh_interval 参数指定为单个完整单位:天、小时(小于 24)、分钟(小于 60)或秒(小于 60)。
  • 导入非常大的 BigQuery 表时,请增加实例上的 temp_file_limit 数据库标志的值,并视需要增加 work_mem 标志的值,以便导入操作不会超出临时文件大小限制。在导入期间,传入的行会在内存中缓冲,直到达到 work_mem 限制,然后在批量提交完成之前暂存在本地 PostgreSQL 临时文件中。
  • 请勿在复制的同步表或具有活跃导入的表上手动运行 DROP TABLE 语句。如需停止正在进行的导入,请先以启动作业的数据库用户身份运行 alloydb_sync.cancel_import_job 函数。如需移除定期同步表及其时间表,请使用 alloydb_sync.delete_bq_sync_table 函数,并传入您在创建同步表时使用的确切目标表名称。
  • 如果一次性导入失败或在完成之前被取消,请重新运行 alloydb_sync.import_bq_table 函数,并将 on_exists 参数设置为 replace。如果新注册的定期同步表的初始后台导入被中断,并且您希望立即解决此问题,而不是等待下一次预定的刷新,请使用 alloydb_sync.delete_bq_sync_table 函数删除该同步表,然后重新创建。
  • 如果作业启动时 max_worker_processes 中的后台工作器槽已用尽,或者在导入排队或运行期间数据库崩溃,则作业元数据可能会停留在 QUEUED 或 RUNNING 状态,并阻止将来导入到该表。如需解除对表的锁定,请以启动作业的数据库用户身份运行以下命令,更新作业状态:

    UPDATE alloydb_sync.import_job_status
    SET status = 'FAILED'
    WHERE status IN ('QUEUED', 'RUNNING');
    
  • 如需删除具有定期同步表或近期同步活动的数据库,请运行 DROP DATABASE DATABASE_NAME WITH (FORCE),或先删除所有同步表,然后等待至少 10 秒,让后台跟踪器工作器退出。您可以通过查询 pg_stat_activity 视图来验证工作器是否仍连接到数据库:

    SELECT pid, datname, backend_type
    FROM pg_stat_activity
    WHERE datname = 'DATABASE_NAME'
      AND backend_type = 'job_tracker';
    

    将 DATABASE_NAME 替换为您的数据库名称。

价格

将数据从 BigQuery 同步到 AlloyDB 时,系统会按照 BigQuery 流式读取(Storage Read API)价格向您收费。

导出数据后,如果您将数据存储在 AlloyDB 中,则需要为此付费。如需了解详情,请参阅 AlloyDB for PostgreSQL 价格。

后续步骤