BigQuery前缀查询含未应用Upsert数据表报错问题求助

1. 手动应用未完成的Upsert数据 #

报错核心是部分同步表存在未合并的CDC变更数据，BigQuery禁止通过通配符前缀查询这类表。你可以手动触发数据合并：

• 针对单个涉事表（如my_table_2022_09），执行以下命令强制应用未完成的upsert：

ALTER TABLE `my_project_id.my_dataset.my_table_2022_09`
SET OPTIONS (
  enable_upsert_merge = TRUE
);

• 若有多个表需要处理，批量生成ALTER TABLE语句：

SELECT CONCAT(
  'ALTER TABLE `', table_catalog, '.', table_schema, '.', table_name, '` SET OPTIONS (enable_upsert_merge = TRUE);'
) AS alter_statement
FROM `my_project_id.my_dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'my_table_%'
  AND REGEXP_CONTAINS(table_name, 'my_table_[0-9]{4}_[0-9]{2}$')

将查询结果的语句批量执行即可。

2. 动态生成显式表列表的视图（替代手动列表） #

如果不想手动维护表列表，可通过INFORMATION_SCHEMA动态获取符合条件的表，自动生成包含UNION ALL的查询来创建视图：

步骤1：生成动态查询语句 #

执行以下SQL获取拼接好的UNION ALL查询：

SELECT STRING_AGG(
  CONCAT(
    'SELECT *, REPLACE("', REPLACE(table_name, 'my_table_', ''), '", "_", "-") AS month FROM `', 
    table_catalog, '.', table_schema, '.', table_name, '`'
  ),
  '\nUNION ALL\n'
) AS union_query
FROM `my_project_id.my_dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'my_table_%'
  AND REGEXP_CONTAINS(table_name, 'my_table_[0-9]{4}_[0-9]{2}$')

步骤2：创建视图 #

将上述查询输出的union_query内容复制，作为创建视图的SQL：

CREATE OR REPLACE VIEW `my_project_id.my_dataset.my_merged_view` AS
-- 粘贴步骤1生成的UNION ALL内容

后续新增同步表时，重新执行步骤1更新视图即可，也可通过Cloud Scheduler定期自动更新视图。

3. 调整Datastream同步配置从根源避免问题 #

修改Datastream的PostgreSQL同步任务配置，确保upsert数据实时应用：

• 打开Datastream控制台对应同步任务，进入目标BigQuery配置页面
• 启用自动合并变更数据类选项，避免积累未应用的upsert记录
• 保存配置并重启同步任务，后续同步的表将不会再出现此类问题

内容的提问来源于stack exchange，提问作者vilozio