时间范围联接

适用于：✅Azure 数据资源管理器✅Azure Monitor✅Microsoft Sentinel

基于某个高基数键（例如操作 ID 或会话 ID）在两个大型数据集之间进行联接，并通过对左右两侧 datetime 列之间的“时间距离”添加限制来进一步限制需要与每个左侧 ($left) 记录向匹配的右侧 ($right) 记录通常是有用的。

上述操作不同于一般的 Kusto 联接操作，因为对于在左右两侧数据集之间匹配高基数键的 equi-join 部分，系统还可以应用距离函数并使用它大大加快联接速度。

例如，如果你希望标识相对较小的时间范围内的事件序列，假设你有一个具有以下架构的表 T：

• SessionId：一个 string 类型的列，其中包含相关 ID。
• EventType：一个 string 类型的列，用于标识记录的事件类型。
• Timestamp：一个 datetime 类型的列，用于指示记录描述的事件是何时发生的。

let T = datatable(SessionId:string, EventType:string, Timestamp:datetime)
[
    '0', 'A', datetime(2017-10-01 00:00:00),
    '0', 'B', datetime(2017-10-01 00:01:00),
    '1', 'B', datetime(2017-10-01 00:02:00),
    '1', 'A', datetime(2017-10-01 00:03:00),
    '3', 'A', datetime(2017-10-01 00:04:00),
    '3', 'B', datetime(2017-10-01 00:10:00),
];
T

输出

问题陈述

我们的查询应当回答以下问题：

查找 1min 时间范围内在事件类型 A 后跟有事件类型 B 的所有会话 ID。

从语义上讲，以下查询可以回答此问题，尽管效率低下。

T 
| where EventType == 'A'
| project SessionId, Start=Timestamp
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp
    ) on SessionId
| where (End - Start) between (0min .. 1min)
| project SessionId, Start, End 

输出

为了优化此查询，我们可以进行重写，将时间范围表示为联接键，如下所述。

重写查询以考虑时间范围

重写查询，将 datetime 值“离散化”为大小为时间范围大小一半的 Bucket。 使用 Kusto 的 equi-join 来比较这些 Bucket ID。

let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0; // lookup bin = equal to 1/2 of the lookup window
T 
| where EventType == 'A'
| project SessionId, Start=Timestamp,
          // TimeKey on the left side of the join is mapped to a discrete time axis for the join purpose
          TimeKey = bin(Timestamp, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp,
              // TimeKey on the right side of the join - emulates event 'B' appearing several times
              // as if it was 'replicated'
              TimeKey = range(bin(Timestamp-lookupWindow, lookupBin),
                              bin(Timestamp, lookupBin),
                              lookupBin)
    // 'mv-expand' translates the TimeKey array range into a column
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End 

可运行的查询引用（包含内联表）

let T = datatable(SessionId:string, EventType:string, Timestamp:datetime)
[
    '0', 'A', datetime(2017-10-01 00:00:00),
    '0', 'B', datetime(2017-10-01 00:01:00),
    '1', 'B', datetime(2017-10-01 00:02:00),
    '1', 'A', datetime(2017-10-01 00:03:00),
    '3', 'A', datetime(2017-10-01 00:04:00),
    '3', 'B', datetime(2017-10-01 00:10:00),
];
let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0;
T 
| where EventType == 'A'
| project SessionId, Start=Timestamp, TimeKey = bin(Timestamp, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp,
              TimeKey = range(bin(Timestamp-lookupWindow, lookupBin),
                              bin(Timestamp, lookupBin),
                              lookupBin)
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End 

输出

5M 数据查询

下一个查询模拟包含 5M 记录和大约 1M ID 的一个数据集，运行该查询的方法如上所述。

let T = range x from 1 to 5000000 step 1
| extend SessionId = rand(1000000), EventType = rand(3), Time=datetime(2017-01-01)+(x * 10ms)
| extend EventType = case(EventType < 1, "A",
                          EventType < 2, "B",
                          "C");
let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0;
T 
| where EventType == 'A'
| project SessionId, Start=Time, TimeKey = bin(Time, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Time, 
              TimeKey = range(bin(Time-lookupWindow, lookupBin), 
                              bin(Time, lookupBin),
                              lookupBin)
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End 
| count 

输出