Kusto: Self join table and get values from different rows

user_17506482 0
Viewed 85

Working with a similar dataset as below, I am able to get the desired output by using scan operator, to fill forward strings/bools in test dataset, however it's timing out for larger datasets, as every property has many events and there are millions of property. Looking for an better/faster approach.

let T = datatable(PropertyId:int, Tenant:string, Owner:string, NoisyNeighbour:bool , PropertyTitle:string, EventDate:datetime )
[
   1, "", "", bool(0),"",datetime(2022-08-01 00:00),
   1, "", "abc", bool(null),"",datetime(2022-08-01 01:00),
   1, "X","", bool(null),"Title updated",datetime(2022-08-02 00:00),
   1, "X", "cde",bool(null),"",datetime(2022-08-03 00:00),
   1, "A1", "",bool(null),"",datetime(2022-08-03 00:00),
   1, "A2", "",bool(null),"",datetime(2022-08-03 02:00),
   1, "A2", "def",bool(null),"",datetime(2022-08-03 03:00),
   1, "B", "", bool(null),"",datetime(2022-08-04 00:00),
   1, "C","", bool(1),"",datetime(2022-08-05 00:00),
   1, "D", "xyz",bool(null),"",datetime(2022-08-06 00:00),
]; T

Expected result : Tenant who marked the property with NoisyNeighbor, Date when it was marked noisy, Current owner of the property, property title

kusto-explorer summarize kql azure-data-explorer
2 Answers

The partition operator is used for parallelizing the scan operator work.

let T = datatable(PropertyId:int, Tenant:string, Owner:string, NoisyNeighbour:bool , PropertyTitle:string, EventDate:datetime )
[
    1 ,""   ,""    ,false      ,""              ,datetime(2022-08-01 00:00)
   ,1 ,""   ,"abc" ,bool(null) ,""              ,datetime(2022-08-01 01:00)
   ,1 ,"X"  ,""    ,bool(null) ,"Title updated" ,datetime(2022-08-02 00:00)
   ,1 ,"X"  ,"cde" ,bool(null) ,""              ,datetime(2022-08-03 00:00)
   ,1 ,"A1" ,""    ,bool(null) ,""              ,datetime(2022-08-03 00:00)
   ,1 ,"A2" ,""    ,bool(null) ,""              ,datetime(2022-08-03 02:00)
   ,1 ,"A2" ,"def" ,bool(null) ,""              ,datetime(2022-08-03 03:00)
   ,1 ,"B"  ,""    ,bool(null) ,""              ,datetime(2022-08-04 00:00)
   ,1 ,"C"  ,""    ,true       ,""              ,datetime(2022-08-05 00:00)
   ,1 ,"D"  ,"xyz" ,bool(null) ,""              ,datetime(2022-08-06 00:00)
];
T
|   partition hint.strategy=native by PropertyId 
    (
            order by EventDate asc
        |   scan with 
            (
                step s1 : true =>   Owner           = coalesce(Owner        , s1.Owner)
                                   ,PropertyTitle   = coalesce(PropertyTitle, s1.PropertyTitle);
            )
        |   where NoisyNeighbour
    )
PropertyId Tenant Owner NoisyNeighbour PropertyTitle EventDate
1 C def true Title updated 2022-08-05T00:00:00Z

Fiddle

user_63364790

Based on arg_max()

  • For each property find the last time it was marked with NN
  • For each property take only the events that preceded the NN event (including itself).
  • For each property take the Tenant & NoisyNeighbour of the last event (the one that was marked with NN) and the last Owner & PropertyTitle that are not null
let T = datatable(PropertyId:int, Tenant:string, Owner:string, NoisyNeighbour:bool , PropertyTitle:string, EventDate:datetime )
[
    1 ,""   ,""    ,false      ,""              ,datetime(2022-08-01 00:00)
   ,1 ,""   ,"abc" ,bool(null) ,""              ,datetime(2022-08-01 01:00)
   ,1 ,"X"  ,""    ,bool(null) ,"Title updated" ,datetime(2022-08-02 00:00)
   ,1 ,"X"  ,"cde" ,bool(null) ,""              ,datetime(2022-08-03 00:00)
   ,1 ,"A1" ,""    ,bool(null) ,""              ,datetime(2022-08-03 00:00)
   ,1 ,"A2" ,""    ,bool(null) ,""              ,datetime(2022-08-03 02:00)
   ,1 ,"A2" ,"def" ,bool(null) ,""              ,datetime(2022-08-03 03:00)
   ,1 ,"B"  ,""    ,bool(null) ,""              ,datetime(2022-08-04 00:00)
   ,1 ,"C"  ,""    ,true       ,""              ,datetime(2022-08-05 00:00)
   ,1 ,"D"  ,"xyz" ,bool(null) ,""              ,datetime(2022-08-06 00:00)
];
T
| where         NoisyNeighbour
| summarize     max(EventDate) by PropertyId
| join          kind=inner T on PropertyId
| where         EventDate <= max_EventDate
| summarize     arg_max(EventDate, Tenant, NoisyNeighbour)
               ,arg_max(iff(isnotempty(Owner)         ,EventDate ,datetime(null)) ,Owner) 
               ,arg_max(iff(isnotempty(PropertyTitle) ,EventDate ,datetime(null)) ,PropertyTitle) 
                by PropertyId
| project-away  max*
PropertyId EventDate Tenant NoisyNeighbour Owner PropertyTitle
1 2022-08-05T00:00:00Z C true def Title updated

Fiddle

user_63364790
Related