如何在BigQuery中查询项目每日全量查询的大小与成本数据？

问题原因 #

你使用的region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT视图仅返回当前执行查询的身份（你的个人账号）发起的作业，通过服务账号密钥认证运行的DBT、数据传输等作业属于独立身份，因此不会出现在你的查询结果中。这就是为什么你只能看到少量作业，而项目实际有大量作业的核心原因。

解决方案 #

方案1：使用项目级权限身份查询全量作业 #

如果你拥有项目的bigquery.jobs.list权限（或管理员权限），直接在查询中添加日期过滤，即可获取单日所有身份（包括服务账号）发起的作业：

SELECT 
  creation_time,
  user_email, -- 会显示服务账号邮箱，如xxx@your-project.iam.gserviceaccount.com
  ROUND(total_bytes_processed/POWER(1024, 3), 2) as gb_processed,
  ROUND(total_bytes_billed/POWER(1024, 3), 2) as gb_billed,
  ROUND((total_bytes_billed/1099511627776)*5, 4) as estimated_cost_usd,
  cache_hit,
  state,
  labels,
  SUBSTR(query, 0, 100) as query_preview
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE DATE(creation_time) = '2024-05-20' -- 替换为目标日期
ORDER BY creation_time DESC

注意：如果作业分布在多个区域，需将region-us替换为对应区域（如region-eu），或针对每个区域分别查询。

方案2：通过Cloud Audit Logs长期收集全量作业数据 #

如果权限受限，或需要长期保留作业数据用于分析，建议启用BigQuery Audit Logs并导出到BigQuery数据集：

1. 进入Google Cloud Console的IAM与管理 > 日志记录 > 日志导出
2. 创建导出任务，选择bigquery.googleapis.com%2Fjobs日志类型，指定导出到你的BigQuery数据集
3. 查询导出的日志表获取单日作业数据：

SELECT
  TIMESTAMP(JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobCreationTime')) as creation_time,
  JSON_VALUE(protopayload_auditlog.authenticationInfo.principalEmail) as user_email,
  ROUND(SAFE_CAST(JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobStatistics.totalBytesProcessed') AS INT64)/POWER(1024,3),2) as gb_processed,
  ROUND(SAFE_CAST(JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobStatistics.totalBytesBilled') AS INT64)/POWER(1024,3),2) as gb_billed,
  ROUND((SAFE_CAST(JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobStatistics.totalBytesBilled') AS INT64)/1099511627776)*5,4) as estimated_cost_usd,
  JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobStatistics.cacheHit') as cache_hit,
  JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobStatus.state') as state,
  JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobConfiguration.labels') as labels,
  SUBSTR(JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobConfiguration.query.query'),0,100) as query_preview
FROM `your-project.your-dataset.cloudaudit_googleapis_com_data_access_*` -- 替换为你的导出表前缀
WHERE DATE(TIMESTAMP(JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobCreationTime'))) = '2024-05-20'
  AND JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobConfiguration.type') = 'QUERY'
ORDER BY creation_time DESC

补充提示 #

• 服务账号发起的作业会在user_email字段显示为[账号名]@[项目ID].iam.gserviceaccount.com，可通过此字段区分DBT、数据传输等不同来源的作业
• 若项目使用多区域资源，审计日志方案可统一收集所有区域的作业数据，无需逐个区域查询

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