如何在Kusto(Azure Data Explorer)中基于动态correlationId连接两个数据集？

问题背景 #

我在Azure Data Explorer中有两个数据集：requests和exceptions，希望基于结合operation_ParentId与operation_Id生成的动态correlationId完成两表连接。

尝试的查询示例：

requests
| extend correlationId = strcat('c3172da7e4803fc2', '-', operation_Id) 
| join kind=inner (exceptions
                   | extend correlationId = strcat('c3172da7e4803fc2', '-', operation_Id) 
                  ) on correlationId
| project ProcessingDate, OpCo, Cosmos_OrderId, ReasonMessage, data, operation_ParentId, operation_Id, correlationId
| where Cosmos_OrderId contains "1983"

使用固定值作为correlationId前缀时查询正常，但改用correlationId = strcat(operation_ParentId, '-', operation_Id)时无结果返回。核心问题：

• operation_ParentId在两个数据集中值重复，无法作为唯一标识
• Cosmos_OrderId和OpCo仅存在于requests，无法用于跨表连接

需求：实现基于operation_ParentId和operation_Id的动态关联，同时避免高资源消耗的笛卡尔连接。

解决方案 #

1. 基于APM层级逻辑的直接关联 #

通常在APM场景中，exceptions的operation_ParentId对应requests的operation_Id（异常属于某请求的子操作），无需拼接新ID，直接通过这两个字段关联即可：

requests
| join kind=inner exceptions on $left.operation_Id == $right.operation_ParentId
| project 
    ProcessingDate, 
    OpCo, 
    Cosmos_OrderId, 
    ReasonMessage, 
    data, 
    请求父操作ID = operation_ParentId,
    请求操作ID = operation_Id,
    异常操作ID = exceptions.operation_Id,
    异常父操作ID = exceptions.operation_ParentId
| where Cosmos_OrderId contains "1983"

2. 若需使用拼接的关联ID #

如果业务场景必须用operation_ParentId + operation_Id作为关联键，需保证两边拼接的是对应链路的字段：

requests
| extend correlationId = operation_Id  // 顶层请求用自身operation_Id作为关联标识
| join kind=inner (
    exceptions
    | extend correlationId = operation_ParentId  // 异常用父操作ID（对应请求的operation_Id）作为关联标识
) on correlationId
| project 
    ProcessingDate, 
    OpCo, 
    Cosmos_OrderId, 
    ReasonMessage, 
    data, 
    operation_ParentId, 
    operation_Id, 
    correlationId,
    异常操作ID = exceptions.operation_Id
| where Cosmos_OrderId contains "1983"

3. 连接效率优化建议 #

• 用kind=innerunique替代inner：若requests存在重复operation_Id，innerunique会先去重，减少连接数据量
• 提前过滤缩小数据集：在join前对两表添加时间范围或业务过滤条件，降低参与连接的数据规模

  requests
  | where ProcessingDate between (ago(7d) .. now())
  | where Cosmos_OrderId contains "1983"
  | extend correlationId = operation_Id
  | join kind=innerunique (
      exceptions
      | where ProcessingDate between (ago(7d) .. now())
      | extend correlationId = operation_ParentId
  ) on correlationId
  | project 
      ProcessingDate, 
      OpCo, 
      Cosmos_OrderId, 
      ReasonMessage, 
      data, 
      operation_ParentId, 
      operation_Id, 
      异常操作ID = exceptions.operation_Id
  

• 利用分区键：若表按ProcessingDate分区，确保过滤条件包含该字段，减少扫描的数据范围

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