如何统计BigQuery中用户按月/按年维度的查询作业次数

你之前用bq ls -j+bq show的方式效率低、且默认只能拉取最近1000条作业，无法满足全量统计需求，直接使用BigQuery内置的INFORMATION_SCHEMA作业元数据视图即可实现需求：

1. 统计每位用户每月发起的查询次数 #

执行以下SQL即可，单项目统计使用JOBS_BY_PROJECT视图，需统计整个组织所有项目数据时替换为JOBS_BY_ORGANIZATION即可：

SELECT
  user_email AS 发起用户邮箱,
  DATE_TRUNC(DATE(creation_time), MONTH) AS 统计月份,
  COUNT(job_id) AS 月查询总次数
FROM
  `region-你的区域名`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  job_type = 'QUERY' -- 只统计查询类作业，排除导入、导出等其他作业
  AND state = 'DONE' -- 只统计执行完成的作业，可按需调整
  -- 可按需加时间范围过滤：AND creation_time BETWEEN '2023-01-01' AND '2024-01-01'
GROUP BY
  user_email,
  统计月份
ORDER BY
  统计月份 DESC,
  月查询总次数 DESC

2. 统计特定用户每年的总查询次数 #

在上述逻辑基础上增加用户过滤条件、按年份聚合即可：

SELECT
  user_email AS 发起用户邮箱,
  DATE_TRUNC(DATE(creation_time), YEAR) AS 统计年份,
  COUNT(job_id) AS 年查询总次数
FROM
  `region-你的区域名`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  job_type = 'QUERY'
  AND state = 'DONE'
  AND user_email = '目标用户邮箱@xxx.com' -- 替换为要统计的特定用户邮箱
GROUP BY
  user_email,
  统计年份
ORDER BY
  统计年份 DESC

注意事项 #

• INFORMATION_SCHEMA作业视图默认仅保留最近180天的作业历史，若需要查询超过180天的统计数据，需提前定期将作业元数据导出到自建表留存
• 统计时增加时间范围过滤条件可以缩小数据扫描量，降低查询成本

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