如何用BigQuery在Grafana中构建分环境部署频次热力图/时序图

1. 单日维度：按小时统计部署频次 #

核心是确保每个小时区间都有数据（即使部署次数为0），避免图表出现断层。以下是BigQuery查询语句：

WITH hourly_time_range AS (
  -- 生成当日0-23点的完整小时区间
  SELECT 
    TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_DATE(), DAY), INTERVAL hour HOUR) AS hour_start
  FROM UNNEST(GENERATE_ARRAY(0, 23)) AS hour
),
deploy_counts AS (
  -- 统计各环境每小时部署次数
  SELECT
    environment,
    TIMESTAMP_TRUNC(`deployed time`, HOUR) AS hour_start,
    COUNT(*) AS deploy_count
  FROM `your-project.your-dataset.your-table`
  WHERE DATE(`deployed time`) = CURRENT_DATE()
  GROUP BY environment, hour_start
)
-- 关联时间区间，补全无部署时段的0值
SELECT
  h.hour_start,
  COALESCE(d.environment, '无部署') AS environment,
  COALESCE(d.deploy_count, 0) AS deploy_count
FROM hourly_time_range h
LEFT JOIN deploy_counts d
ON h.hour_start = d.hour_start
ORDER BY h.hour_start, environment

Grafana配置要点 #

• 数据源选择BigQuery，导入上述查询
• 热力图：将hour_start设为X轴（时间粒度选小时），environment设为Y轴，deploy_count设为颜色映射值
• 时序图：将hour_start设为时间轴，environment设为系列字段，deploy_count设为Y轴数值

2. 月度维度：按日期统计部署频次 #

同样需要生成当月完整日期区间，补全无部署日期的0值：

WITH daily_time_range AS (
  -- 生成当月所有日期
  SELECT 
    DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL day DAY) AS date_start
  FROM UNNEST(GENERATE_ARRAY(0, DATE_DIFF(LAST_DAY(CURRENT_DATE()), DATE_TRUNC(CURRENT_DATE(), MONTH), DAY))) AS day
),
deploy_counts AS (
  -- 统计各环境每日部署次数
  SELECT
    environment,
    DATE(`deployed time`) AS date_start,
    COUNT(*) AS deploy_count
  FROM `your-project.your-dataset.your-table`
  WHERE DATE(`deployed time`) BETWEEN DATE_TRUNC(CURRENT_DATE(), MONTH) AND LAST_DAY(CURRENT_DATE())
  GROUP BY environment, date_start
)
-- 关联日期区间，补全无部署日期的0值
SELECT
  d.date_start,
  COALESCE(de.environment, '无部署') AS environment,
  COALESCE(de.deploy_count, 0) AS deploy_count
FROM daily_time_range d
LEFT JOIN deploy_counts de
ON d.date_start = de.date_start
ORDER BY d.date_start, environment

Grafana配置要点 #

• 数据源选择BigQuery，导入上述查询
• 热力图：将date_start设为X轴（时间粒度选日期），environment设为Y轴，deploy_count设为颜色映射值
• 时序图：将date_start设为时间轴，environment设为系列字段，deploy_count设为Y轴数值

常见问题排查 #

• 数据断层：简单COUNT+GROUP BY仅返回有部署的时间点，无部署时段会缺失，用时间区间CTE可补全0值
• 时间格式异常：确保deployed time为BigQuery的TIMESTAMP/DATE类型，Grafana才能正确识别为时间字段
• 分组显示异常：用COALESCE替换NULL环境值，避免图表出现空白或异常分类

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