如何使用Kusto将数据转换为Grafana热力图所需格式？

Grafana热力图需要明确的二维维度+数值结构，你的原始数据已经具备基础字段，只需按需求调整聚合/格式即可，以下是两种常用转换方案：

方案1：长表格式（推荐，兼容性更强） #

这种格式保留三列结构：时间桶、设备变量ID、数值，是Grafana热力图最常支持的输入格式。

Kusto查询语句 #

let test = datatable (Timestamp: datetime, Id: string, Value: decimal)
[
  datetime(2022-11-30 11:40:00), "machineA_var01", 1,
  datetime(2022-11-30 11:40:00), "machineA_var02", 1.5,
  datetime(2022-11-30 11:40:00), "machineA_var03", 2,
  datetime(2022-11-30 11:40:00), "machineA_var04", 2.5,
  datetime(2022-11-30 11:40:00), "machineA_var05", 5,
  datetime(2022-11-30 11:40:00), "machineA_var06", 3,
  datetime(2022-11-30 11:40:00), "machineA_var07", 2,
  datetime(2022-11-30 11:40:00), "machineA_var08", 6,
  datetime(2022-11-30 11:40:00), "machineA_var09", 3,
  datetime(2022-11-30 11:40:00), "machineA_var10", 4,
  datetime(2022-11-30 11:40:00), "machineA_var11", 5,
  datetime(2022-11-30 11:40:00), "machineA_var12", 6,
  datetime(2022-11-30 11:40:00), "machineA_var13", 1,
  datetime(2022-11-30 11:40:00), "machineA_var14", 13,
  datetime(2022-11-30 11:40:00), "machineA_var15", 3,
  datetime(2022-11-30 11:40:00), "machineA_var16", 5,
  datetime(2022-11-30 11:40:15), "machineB_var01", 3,
  datetime(2022-11-30 11:40:15), "machineB_var02", 7,
  datetime(2022-11-30 11:40:15), "machineB_var03", 8,
  datetime(2022-11-30 11:40:15), "machineB_var04", 6,
  datetime(2022-11-30 11:40:15), "machineB_var05", 8,
  datetime(2022-11-30 11:40:15), "machineB_var06", 2,
  datetime(2022-11-30 11:40:15), "machineB_var07", 6,
  datetime(2022-11-30 11:40:15), "machineB_var08", 2,
  datetime(2022-11-30 11:40:15), "machineB_var09", 3,
  datetime(2022-11-30 11:40:15), "machineB_var10", 3,
  datetime(2022-11-30 11:40:15), "machineB_var11", 3,
  datetime(2022-11-30 11:40:15), "machineB_var12", 4,
  datetime(2022-11-30 11:40:15), "machineB_var13", 6,
  datetime(2022-11-30 11:40:15), "machineB_var14", 2,
  datetime(2022-11-30 11:40:15), "machineB_var15", 8,
  datetime(2022-11-30 11:40:15), "machineB_var16", 5,
  datetime(2022-11-30 11:40:30), "machineC_var01", 6,
  datetime(2022-11-30 11:40:30), "machineC_var02", 3,
  datetime(2022-11-30 11:40:30), "machineC_var03", 8,
  datetime(2022-11-30 11:40:30), "machineC_var04", 9,
  datetime(2022-11-30 11:40:30), "machineC_var05", 3,
  datetime(2022-11-30 11:40:30), "machineC_var06", 6,
  datetime(2022-11-30 11:40:30), "machineC_var07", 5,
  datetime(2022-11-30 11:40:30), "machineC_var08", 6,
  datetime(2022-11-30 11:40:30), "machineC_var09", 4,
  datetime(2022-11-30 11:40:30), "machineC_var10", 7,
  datetime(2022-11-30 11:40:30), "machineC_var11", 1,
  datetime(2022-11-30 11:40:30), "machineC_var12", 3,
  datetime(2022-11-30 11:40:30), "machineC_var13", 5.5,
  datetime(2022-11-30 11:40:30), "machineC_var14", 6,
  datetime(2022-11-30 11:40:30), "machineC_var15", 7,
  datetime(2022-11-30 11:40:30), "machineC_var16", 8,
];
// 若需自定义时间分桶，将Timestamp替换为 bin(Timestamp, 15s) 这类表达式
test
| summarize Value = take_any(Value) by Timestamp, Id
| order by Timestamp asc, Id asc

关键说明 #

• 如果原始数据中同一时间+同一ID存在多条记录，将take_any(Value)替换为业务需要的聚合函数（如avg(Value)、max(Value)）
• 自定义时间分桶示例：按1分钟聚合时间维度，使用bin(Timestamp, 1m)替代原始Timestamp

方案2：宽表格式（适合固定变量场景） #

将每个设备变量ID转为单独的列，每行对应一个时间桶，适合变量数量固定的场景。

Kusto查询语句 #

let test = datatable (Timestamp: datetime, Id: string, Value: decimal)
[
  // 示例数据同上
];
test
| summarize Value = take_any(Value) by Timestamp, Id
| pivot(Id, take_any(Value))
| order by Timestamp asc

Grafana面板配置指引 #

• 长表格式配置：在热力图面板中，将Timestamp设为X轴（时间维度），Id设为Y轴（类别维度），Value设为颜色映射的数值字段
• 宽表格式配置：将Timestamp设为X轴，选择所有设备变量对应的列作为数据系列，Grafana会自动映射为Y轴类别

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