Forum Discussion
Post ProdigyInserting the last events into a KQL database
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 = 1I 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
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
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.
1 Reply
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
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.