Inserting the last events into a KQL database

[pmscorca]

Hi,

I need to insert many events in a KQL database.

These events come from a Azure Event Hubs, and are mainly composed of an id, version and event datetime fields.

For each the same id I could have different version, and so the corresponding KQL table has to contain only the last version for each event.

In order to achieve a such goal, I could ovewrite the new version over to the old version respect the same id, but I don't know if Kusto supports an update statement, or I could insert all the versions for each events and then create a view to consider the last version for each event, using a KQL query similar to this T-SQL statement:

CREATE VIEW v_last_events
as
SELECT *
FROM
(
SELECT id, [version], event_date, event_descr,
	row_number() over(partition by id order by version desc) as rown
FROM dbo.events
) x
WHERE x.rown = 1

I don't know if a KQL database could have a particular setting in order to contain only the last version of each event.

Any suggests to me, please? Thanks

[v-tangjie-msft · Accepted solution]

Hi @pmscorca ,

Step 1: Ingest All Versions

First, ingest all versions of your events into a KQL table. This ensures you have a complete history of all events.

Step 2: Create a View for the Latest Version

Next, create a view that selects only the latest version for each event.

```kql
.create-or-alter function with (docstring = "Get latest version of events", folder = "Functions") 
v_last_events() {
    events
    | summarize arg_max(version, *) by id
}
```

//This function uses `arg_max` to get the row with the highest version for each `id`.

```kql
v_last_events()
| project id, version, event_date, event_descr
```

Or you can use the update command.

Update records in a Kusto Database (Public Preview) - Microsoft Community Hub

Update policy overview - Kusto | Microsoft Learn

curatedsql.com

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.