如何通过编程获取BigQuery中所有查询的数量及状态

为什么现有SQL和Python结果不一致 #

• 你用的INFORMATION_SCHEMA.JOBS视图仅返回当前执行查询的用户所提交的作业，而BigQuery UI的Jobs Explorer会展示所有有权限查看的作业（包括服务账号提交的）。
• 你的Python代码用了all_users=True参数，会拉取项目内所有用户的作业，所以结果和SQL自然不匹配。

最佳编程实现方式 #

方式1：使用SQL查询全量作业（推荐） #

改用JOBS_BY_PROJECT视图，这个视图会返回项目内所有用户的作业（前提是你有足够权限）：

SELECT 
  COUNT(job_id) AS job_count, 
  state
FROM `MY_PROJECT`.`region-MY_REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE 
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND CURRENT_TIMESTAMP()
  AND project_id = 'MY_PROJECT'
  -- 排除当前统计查询本身，避免重复计数
  AND query NOT LIKE '%COUNT(job_id) AS job_count, state%'
GROUP BY state;

注意：需要确保你的账号拥有bigquery.jobs.list权限或者项目级的Viewer/Editor权限。

方式2：优化Python客户端代码 #

原有Python代码逻辑没问题，但可以优化统计逻辑，同时注意时间参数的时区问题（BigQuery用UTC时间，建议统一用UTC避免偏差）：

from google.cloud import bigquery
from datetime import datetime, timedelta
import pytz

project_id = 'MY_PROJECT'
client = bigquery.Client(project=project_id)

# 使用UTC时间，避免本地时区偏差
utc_now = datetime.now(pytz.utc)
min_create_time = utc_now - timedelta(hours=1)

# 拉取项目内所有用户最近1小时的作业
jobs = client.list_jobs(
    project=project_id,
    all_users=True,
    min_creation_time=min_create_time,
    max_creation_time=utc_now,
    # 可选：指定区域，和你作业所在区域一致
    location="MY_REGION"
)

# 统计状态，用字典直接计数更简洁
job_counts = {}
for job in jobs:
    state = job.state or "UNKNOWN"  # 处理状态为空的情况
    job_counts[state] = job_counts.get(state, 0) + 1

# 输出结果
for state, count in job_counts.items():
    print(f"状态 {state} 的作业数量：{count}")

优化点说明：

• 加入时区处理，避免本地时间和BigQuery的UTC时间不一致导致统计偏差
• 简化计数逻辑，用dict.get()更高效
• 处理作业状态为空的边缘情况
• 显式指定区域，提升拉取效率

关键注意事项 #

• 权限：无论用哪种方式，你的账号必须拥有bigquery.jobs.list权限，或者项目的roles/bigquery.viewer、roles/bigquery.editor等角色
• 区域：确保SQL视图和Python代码指定的区域和你的作业所在区域一致，BigQuery作业是区域级资源
• 排除自查询：如果需要避免统计当前查询本身，SQL里要加过滤条件，Python代码可以通过检查job的query内容跳过

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