如何通过INFORMATION_SCHEMA.JOBS_BY_PROJECT识别Looker Studio触发的BigQuery查询并统计消耗？

要按Looker Studio、Tableau、即席查询、计划查询分类统计BigQuery的资源消耗，核心是通过INFORMATION_SCHEMA.JOBS_BY_PROJECT表的字段特征区分不同执行类型，再聚合统计资源指标。

关键识别规则 #

不同工具/类型的查询在表中有明显特征：

• Looker Studio：查询发起账号固定为looker-studio@system.gserviceaccount.com，或user_agent包含data-studio/looker-studio关键词；部分场景可通过labels字段的source标签判断（值为data-studio）。
• Tableau：user_agent字段包含Tableau关键词（适配Desktop、Server等全场景）。
• 计划查询：creation_reason字段值为SCHEDULED_QUERY，或schedule_name不为空（计划查询专属字段）。
• 即席查询：排除以上三类的普通查询，通常是用户通过BigQuery控制台、bq命令行、自定义API直接发起的请求。

统计SQL示例 #

替换your-project-id为目标项目ID，调整时间范围后即可执行：

SELECT
  CASE
    -- 识别Looker Studio查询
    WHEN user_email = 'looker-studio@system.gserviceaccount.com' 
         OR user_agent LIKE '%data-studio%' 
         OR user_agent LIKE '%looker-studio%'
         OR JSON_EXTRACT_SCALAR(labels, '$.source') = 'data-studio' THEN 'Looker Studio'
    -- 识别Tableau查询
    WHEN user_agent LIKE '%Tableau%' THEN 'Tableau'
    -- 识别计划查询
    WHEN creation_reason = 'SCHEDULED_QUERY' THEN '计划查询'
    -- 剩余查询归为即席查询
    WHEN job_type = 'QUERY' THEN '即席查询'
    ELSE '其他'
  END AS 执行类型,
  COUNT(*) AS 查询次数,
  -- 转换为TB便于阅读
  SUM(total_bytes_processed) / POWER(1024, 4) AS 总处理量_TB,
  -- 转换为小时统计槽时消耗
  SUM(total_slot_ms) / 3600000 AS 总占用槽时_小时,
  AVG(total_bytes_processed) / POWER(1024, 3) AS 平均处理量_GB,
  AVG(total_slot_ms) / 1000 AS 平均耗时_秒
FROM
  `your-project-id.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
  job_type = 'QUERY' -- 仅统计查询类作业，排除LOAD/COPY等操作
  AND start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) -- 统计最近30天数据
GROUP BY
  执行类型
ORDER BY
  总处理量_TB DESC;

注意事项 #

• 若需更精准的Looker Studio识别，优先用user_email判断，服务账号不会随工具版本变更；
• Tableau的user_agent可能包含不同后缀，用通配符匹配更可靠；
• 资源指标可按需扩展，比如total_bytes_billed（计费字节数）、elapsed_time（总耗时）等；
• 时间范围可根据业务需求调整，比如改为INTERVAL 7 DAY统计最近一周数据。

内容的提问来源于stack exchange，提问作者Nicolás Rodríguez Celis