如何优化KQL查询从超大规模数据集提取列唯一值

问题背景 #

• 70亿+记录的Customers表存储在60节点集群的热缓存中
• 需基于动态生成的IdCodes权限表过滤数据，最终输出指定列的唯一值JSON属性包
• 当前查询需返回3亿+中间记录，经pack_all()等处理后耗时8-10秒，内存占用高易引发集群瓶颈

当前查询的核心痛点 #

1. 全量数据做代码归一化：对Customers所有记录执行toupper(Code)，内存开销大
2. 重复扫描中间数据集：分四次扫描myData生成各列唯一值，浪费计算资源
3. lookup+shuffle策略效率低：未利用小表广播的优化，且多次shuffle增加集群负载

优化方案 #

1. 反转代码归一化逻辑，避免全量转换 #

将动态生成的IdCodes中的Code转为小写（匹配Customers表的存储格式），无需对70亿条记录执行toupper操作，直接通过Id+Code匹配过滤，大幅降低内存消耗。

2. 一次性聚合所有目标列的唯一值 #

通过单次扫描过滤后的数据集，用make_set（自动去重）收集各列的唯一值，避免多次重复扫描中间数据，减少IO和计算开销。

3. 使用高效的Hash Join策略 #

IdCodes是动态生成的小表，采用kind=inner hash join，将小表广播到各节点，替代lookup操作，提升过滤效率。

4. 移除不必要的Shuffle策略 #

利用Kusto的自动聚合策略（hint.strategy=auto），无需手动指定shuffle，让集群根据数据分布选择最优执行计划。

优化后的完整查询 #

// 70亿+记录的Customers表（示例结构）
let Customers = datatable(Id:string, Code:string, Name:string, Area:string, Role:string, Type:string, DateField:datetime )
['1','a','John','Seattle','MD','12',datetime("2020-12-01"),
'2','a','Mark','Chicago','CC','13',datetime("2020-12-01"),
'2','b','Mark','Seattle','SL','14',datetime("2020-12-01"),
'3','a','Mike','NY','AB','12',datetime("2023-12-01"),
'3','b','Mike','CA','CD','15',datetime("2023-12-01"),
'3','c','Mike','Seattle','MD','12',datetime("2023-12-01")];

// 动态生成的权限表：将Code转为小写，匹配Customers的存储格式
let IdCodes = datatable(Id:string, Code:string)['1','A','1','B','2','A', '2','B','3','A','3','B','3','C']
| extend Code = tolower(Code);

// 核心过滤逻辑：Hash Join替代lookup，仅保留符合条件的记录
let filteredData = Customers
| where DateField between (datetime(2019-12-01 22:50:15.5050000) .. datetime(2023-12-01 22:50:15.5050000))
| join kind=inner hash (IdCodes) on Id, Code;

// 单次聚合所有目标列的唯一值，打包为JSON结构
filteredData
| summarize 
    IdName = make_set(pack("Id", Id, "Name", Name)),
    Code = make_set(pack("Code", Code)),
    Area = make_set(pack("Area", Area)),
    Type = make_set(pack("Type", Type))
| project 
    IdName, 
    Code, 
    Area, 
    Type

优化效果说明 #

• 内存消耗降低：避免全量Code字段的大小写转换，减少中间数据量
• 查询耗时缩短：单次扫描聚合替代四次重复扫描，Hash Join提升过滤效率
• 集群负载减轻：减少不必要的shuffle操作，优化资源分配

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