CODEWITHSUNDEEP
azure-data-explorerkql
Krumelur
Krumelur

Reputation: 33048

Is it possible to ingest data into Kusto based on a multirow result set?

I want to ingest data from the Food table into a table CurrentFood based on filter criteria (finding the latest date per type). The query to get input data is something like this:

Food
| summarize arg_max(Date, Date) by Type
Type Date
Fruit 06-02-2022
Vegetables 01-01-2022
... ...

Based on the rows returned I want to ingest into CurrentFood. Every row returned needs to be used as a filter where Type and Date match. Essentially, one ingestion per row but I'd like to have it all as one single ingestion.

// Ingest 1st row data
.set-or-replace CurrentFood <| Food
| where Type == "Fruit" and Date == "06-02-2022"

// Ingest 2nd row data
.set-or-append CurrentFood <| Food
| where Type == "Vegetable" and Date == "01-01-2022"

// Ingest 3rd row data
.set-or-append CurrentFood <| Food
...

Pseudo-code below for what I'd like to achieve:

.set-or-replace CurrentFood <| Food
| where
  {for each row in result set find those entries where Type and Date match}

Upvotes: 0

Views: 638

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Demo

.set-or-replace Food <|
datatable (RecordID:int, Name:string, Type:string, Date:datetime)
[
     1 ,"Grapes"   ,"Fruit"      ,"2022-02-04"
    ,2 ,"Orange"   ,"Fruit"      ,"2022-02-06"
    ,3 ,"Grapes"   ,"Fruit"      ,"2022-02-06"
    ,4 ,"Apple"    ,"Fruit"      ,"2022-02-06"
    ,5 ,"Orange"   ,"Fruit"      ,"2022-02-05"
    ,6 ,"Mango"    ,"Fruit"      ,"2022-02-05" 
    ,7 ,"cucumber" ,"Vegetables" ,"2022-01-01"
    ,8 ,"Broccoli" ,"Vegetables" ,"2022-01-01"
]

Based on partition & row_rank()

.set-or-replace CurrentFood <|
Food
|   partition hint.strategy=native by Type
    (
        order by Date 
        |   where row_rank(Date) == 1
    )

CurrentFood
RecordID Name Type Date
7 cucumber Vegetables 2022-01-01T00:00:00Z
8 Broccoli Vegetables 2022-01-01T00:00:00Z
2 Orange Fruit 2022-02-06T00:00:00Z
3 Grapes Fruit 2022-02-06T00:00:00Z
4 Apple Fruit 2022-02-06T00:00:00Z

Or

Based on summarize & join

.set-or-replace CurrentFood <|
let MaxDates = Food | summarize Date = max(Date) by Type;
MaxDates
| join kind=inner Food on Type, Date
| project-away *1

CurrentFood
Type Date RecordID Name
Vegetables 2022-01-01T00:00:00Z 7 cucumber
Vegetables 2022-01-01T00:00:00Z 8 Broccoli
Fruit 2022-02-06T00:00:00Z 2 Orange
Fruit 2022-02-06T00:00:00Z 3 Grapes
Fruit 2022-02-06T00:00:00Z 4 Apple

Upvotes: 1

Related Questions