BigQuery定时快照仅首次运行正常，后续执行失败原因排查

报错信息 #

Access Denied: Table PROJECT:BACKUP.TABLE_20230315: Permission bigquery.tables.deleteSnapshot denied on table PROJECT:BACKUP.TABLE_20230315 (or it may not exist). at [1:1]; JobID: 000000000:scheduled_query_000000000000-000-etc

背景与已做配置 #

• 按《Google BigQuery定时快照指南》配置，仅修改区域为EU、调度改为每日执行
• 首次运行正常，后续执行失败，报错提及的表尚未创建（应由定时任务生成）
• 已为服务账号分配：
  • Backup数据集的Data Editor权限
  • 项目级BigQuery User权限

使用的命令 #

创建定时查询命令 #

bq query --use_legacy_sql=false --display_name="Daily snapshots of the TABLE table" \
--location="EU" --schedule="every 24 hours" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

更新凭据命令 #

bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/eu/myConfig/12345

排查原因与解决方案 #

1. Data Editor角色缺失关键权限 #

BigQuery的Data Editor角色（roles/bigquery.dataEditor）默认不包含bigquery.tables.deleteSnapshot权限，该权限属于BigQuery Data Owner角色（roles/bigquery.dataOwner）。即使目标快照不存在，定时查询的调度逻辑可能会先执行预检查/清理操作，触发权限校验。

解决：
给服务账号分配Backup数据集的BigQuery Data Owner权限，或创建包含bigquery.tables.deleteSnapshot权限的自定义角色并绑定。

2. 验证定时查询的服务账号关联 #

检查部署命令中的transfer_config ID是否正确，确保snapshot-bot@PROJECT.iam.gserviceaccount.com确实是该定时查询的执行账号。

验证命令：

bq show --transfer_config projects/12345/locations/eu/myConfig/12345

查看输出中的service_account_name字段，确认与配置的账号一致。

3. 检查区域一致性 #

确认定时查询的区域（EU）与Backup数据集的区域完全一致，跨区域操作可能引发资源访问或权限异常。

4. 时区导致的快照名称不匹配（低概率） #

若@run_date的时区与预期不符，生成的快照名称可能和实际日期错位，导致任务尝试操作不存在的表。

验证时区：
在测试环境运行以下查询，确认输出格式：

SELECT FORMAT_DATETIME("%Y%m%d", @run_date)

若时区错误，可指定时区修正：

FORMAT_DATETIME("%Y%m%d", DATETIME(@run_date, "Europe/London"))

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