查询BigQuery高访问高计费表、槽使用及关联用户的方法

以下SQL可直接在BigQuery控制台运行，默认统计当前项目近30天的有效查询作业，覆盖所有需求维度：

WITH job_table_refs AS (
  SELECT
    job_id,
    user_email,
    total_slot_ms,
    (total_bytes_billed / POW(1024,4)) * 5 AS calc_cost_usd,
    ref_table.project_id AS table_project,
    ref_table.dataset_id AS table_dataset,
    ref_table.table_id AS table_name
  FROM
    `region-替换为你的实际区域`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
    UNNEST(referenced_tables) ref_table
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND statement_type = 'SELECT'
    AND error_result IS NULL
)
-- 查询频次Top20表
SELECT
  '查询频次最高表' AS stat_dim,
  CONCAT(table_project, '.', table_dataset, '.', table_name) AS table_full_name,
  COUNT(DISTINCT job_id) AS query_total_count,
  APPROX_TOP_COUNT(user_email, 1)[OFFSET(0)].value AS top_related_user,
  NULL AS total_cost_usd,
  NULL AS slot_ms_value
FROM job_table_refs
GROUP BY table_full_name
ORDER BY query_total_count DESC
LIMIT 20

UNION ALL

-- 计费成本Top20表
SELECT
  '计费成本最高表' AS stat_dim,
  CONCAT(table_project, '.', table_dataset, '.', table_name) AS table_full_name,
  NULL AS query_total_count,
  APPROX_TOP_COUNT(user_email, 1)[OFFSET(0)].value AS top_related_user,
  ROUND(SUM(calc_cost_usd), 2) AS total_cost_usd,
  NULL AS slot_ms_value
FROM job_table_refs
GROUP BY table_full_name
ORDER BY total_cost_usd DESC
LIMIT 20

UNION ALL

-- Slot消耗峰值记录
SELECT
  'Slot消耗峰值记录' AS stat_dim,
  CONCAT(table_project, '.', table_dataset, '.', table_name) AS table_full_name,
  NULL AS query_total_count,
  user_email AS top_related_user,
  NULL AS total_cost_usd,
  total_slot_ms AS slot_ms_value
FROM job_table_refs
ORDER BY slot_ms_value DESC
LIMIT 1

结果集说明：前20条为查询频次最高的表及对应最活跃用户，中间20条为累计计费成本最高的表及对应产生成本最多的用户，最后1条为统计周期内Slot使用量最高的单次查询关联的表和操作用户。

• 运行权限要求：执行账号需要拥有项目级bigquery.jobs.listAll权限，BigQuery Admin、项目Owner等默认角色自带该权限
• 运行前需将region-替换为你的实际区域修改为你项目所在的BigQuery区域，例如美东区填region-us，上海区填region-asia-east2
• 成本计算默认采用BigQuery按需计费公开定价（5美元/TB），如果使用预留槽/扁平定价可自行调整成本计算逻辑
• 如需统计组织下所有项目的数据，可将JOBS_BY_PROJECT替换为JOBS_BY_ORGANIZATION，前提是你的账号拥有组织级作业查看权限
• 调整统计时间范围只需修改INTERVAL 30 DAY中的天数数值即可

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