参考文档

补（额外记录下使用BQ storage api进行cdc的限制 & 优点）：

限制：

  • 官方文档已标注的限制点

    • 有一个很大的问题就是cdc 表无法支持search index

  • 前提条件

    • 补充-启用 BigQuery Storage API：API & Services中搜索BigQuery Storage API启用它

  • 使用需要结合 max_stalenes 来使用，避免不了查询延迟

优点：

背景同步

  • BigQuery可以持有主键或者外键，但并非如传统RDB一样带有强一致性，即默认在存在相同主键时依然会进行数据叠加，而非覆盖。（且不存在唯一键）
  • 在使用 BigQuery （列式数据库大多一样）的时候，通常不建议直接对数据进行频繁的 UPDATE 或 DELETE 操作，因为 BigQuery 的底层架构设计是针对大规模数据的批处理和分析优化的，而不是为频繁小批量的更新操作而设计的
  • 性能问题：
    • 数据更新的开销：BigQuery 背后使用列式存储，更新操作需要重新组织和写入底层存储。这意味着即使只更新一小部分数据，也可能触发大规模的数据重写，影响性能。
    • 资源消耗：更新操作可能会消耗更多的计算资源和时间，特别是当数据量较大时，这些操作可能会变得非常昂贵。
  • 数据架构限制：
    • BigQuery 的表在设计上更适合追加（Append-only）的方式，而不是频繁的修改或删除。
    • 在分区表中，更新操作可能会导致分区的重新计算，从而进一步增加延迟和成本。

方案梳理

传统方案参考图（按照传统大数据计算方式走，学习及架构代价大一点）：

方案一（按照官方文档推荐 - 即BigQuery Storage API CDC）：

  • 使用BigQuery Storage Api来进行加载数据，之后使用变更数据捕获来流式插入表更新CDC（Change Data Capture）

    • 使用变更数据捕获来流式插入表更新
    • Storage API 为需要从自己的应用和工具中扫描大量托管数据的消费者提供高吞吐量数据读取。该 API 支持并行扫描存储并公开利用列项目和过滤等功能的并行机制。

  • 使用分区表，BigQuery 的分区表允许基于日期字段对数据进行分区，然后在 WHERE 子句中包含分区或分桶字段条件，可以显著减少扫描量。

    • RangePartitioning 与 TimePartitioning 的对比:

      特性RangePartitioningTimePartitioning
      分区字段类型必须是 INT64 类型必须是 DATE 或 TIMESTAMP 类型
      分区依据数值范围（如用户 ID、订单编号）时间范围（如日期或时间戳）
      典型场景用户分组、分数范围、订单编号等日志分析、时间序列数据、事件跟踪等
      查询优化按分区字段范围查询按时间范围查询

  • cost相关：在 BigQuery 中，物化视图（Materialized View） 是一种预先计算并存储查询结果的表。它们存储的是预计算的结果，而不是每次查询时动态计算，所以可以对频繁查询的数据集创建物化视图

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE MATERIALIZED VIEW `[project-id].[data-set].materialized_view_name`
    AS
    SELECT
    *
    FROM `[project-id].[data-set].test`;



    SELECT *
    FROM `[project-id].[data-set].materialized_view_name`
    WHERE create_date = '2025-04-20';

方案二（定期Merge）：

  • 使用分层架构（详见步骤）：

    • 批处理层：存储静态的历史数据（包括初始化数据）。
    • 实时层：存储最新或实时更新的数据。

  • 步骤：

    • 将 CDC 记录插入实时表：将每个新的 CDC 记录和相应的操作类型（INSERT、UPDATE、DELETE）实时插入到表中。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      INSERT INTO `[project-id].[data-set].temporary_item` (
      id,
      category_id,
      category_log,
      create_date,
      operation_type,
      ......
      )
      VALUES (
      12345,
      12345,
      '',
      CURRENT_TIMESTAMP(),
      'UPDATE' ...
      )

    • 然后定期使用 DML MERGE 同时执行多个 DML 操作（INSERT、UPDATE、DELETE）。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      MERGE `[project-id].[data-set].item` AS T
      USING `[project-id].[data-set].temporary_item` AS S
      ON T.id = S.id
      WHEN MATCHED AND S.operation_type = 'UPDATE' THEN
      UPDATE SET T.category_id = S.category_id, T.category_log = S.category_log, T.create_date = S.create_date, ...
      WHEN MATCHED AND S.operation_type = 'DELETE' THEN
      DELETE
      WHEN NOT MATCHED AND S.operation_type = 'INSERT' THEN
      INSERT (id, category_id, category_log, create_date)
      VALUES (S.id, S.category_id, S.category_log, S.create_date)

  • 分区表：略

  • 物化视图：略

方案三（针对小批量更新或不频繁更新的表）：

  • 常规处理 → update/delete/xxx

    1
    2
    3
    4
    5
    UPDATE [project-id].[data-set].item
    SET
    category_id = 234,
    category_log = 'test'
    WHERE id = 1

  • 分区表：略

  • 物化视图：略