ADX-KQL：如何从动态列中按需扩展属性列？

问题背景 #

给定以下两张示例表：

let systems = datatable(traceName: string, trace: string)
[
    'Temp1', 'trace_1_temperature',
    'Temp2', 'trace_2_temperature',
    'Temp3', 'trace_3_temperature'
];
let telemetry = datatable(timestamp: datetime, data: dynamic)
[
    datetime(2023-09-11T10:00:00Z), dynamic({'trace_1_temperature': 35, 'trace_2_temperature': 40, 'voltage':5, 'active':'true'}),
    datetime(2023-09-11T10:05:00Z), dynamic({'trace_1_temperature': 36, 'trace_3_temperature': 42, 'voltage':5, 'active':'true'}),
    datetime(2023-09-11T10:10:00Z), dynamic({'trace_2_temperature': 38, 'trace_3_temperature': 41, 'voltage':5, 'active':'true'})
];

当前通过硬编码方式实现需求输出，但需要改为无需显式命名的动态列扩展，支持两种场景：

1. 基于systems表的trace关联关系生成友好名称列
2. 按属性key的前缀/后缀条件提取属性

解决方案 #

方案1：基于systems表映射动态生成友好列 #

利用bag_unpack拆解动态字段，结合join与pivot实现列映射，无需硬编码列名：

telemetry
// 拆解data字段为独立列
| evaluate bag_unpack(data)
// 保留时间戳和所有拆解出的列
| project timestamp, *
// 关联systems表，匹配trace字段
| join kind=inner (systems) on $left[trace] == $right[trace]
// 按traceName转置列，生成友好命名的温度列
| pivot traceName, any(trace)
// 保留目标列
| project timestamp, Temp1, Temp2, Temp3

方案2：基于前缀/后缀提取并动态重命名 #

针对无映射表的场景，通过前缀/后缀筛选目标字段，并动态重命名为友好名称：

telemetry
// 拆解data字段为独立列
| evaluate bag_unpack(data)
// 筛选出符合前缀(trace_)和后缀(_temperature)的列
| project timestamp, * where column_name startswith "trace_" and column_name endswith "_temperature"
// 动态重命名列：将trace_X_temperature替换为TempX
| evaluate rename_columns(
    array_concat(
        dynamic(['timestamp']),
        array_map(
            x => strcat(x, " = ", replace_string(replace_string(x, "_temperature", ""), "trace_", "Temp")),
            toscalar(
                telemetry 
                | evaluate bag_unpack(data) 
                | getschema 
                | project column_name 
                | where column_name startswith "trace_" and column_name endswith "_temperature" 
                | make_list(column_name)
            )
        )
    )
)

输出结果 #

两种方案均可生成与硬编码一致的输出：

timestamp                   Temp1   Temp2   Temp3
2023-09-11 10:00:00.0000000 35      40       
2023-09-11 10:05:00.0000000 36              42
2023-09-11 10:10:00.0000000         38      41

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