Forum Discussion
KQL update policy
I have 3 tables, src_table (data inserted from eventstream), target_table (transformed data). reference table. How a join can be applied in update policy query parameters for src_table and reference_table , and output of the join should stored in target_table. Also which table should be in SourceTable parameter.
Example for Update policy,
Hi chetanhiwale ,
Please try the following alternatives to see if they work:
1. Intermediate Table Approach
(1)Continue using `src_table` with streaming ingestion enabled to receive real-time data.
(2)Create an intermediate table (e.g., `intermediate_table`) without streaming ingestion.
(3)Set up an update policy on `intermediate_table` to transform and join data from `src_table` and `reference_table`, and then store the result in `target_table`..alter table intermediate_table policy update @'[{"IsEnabled": true, "Source": "src_table", "Query": "src_table | join kind=inner (reference_table) on id | project src_table.id, src_table.name, reference_table.additional_info", "IsTransactional": true}]'2.Use a scheduled query to periodically join `src_table` and `reference_table`, and insert the results into `target_table`. This way, you can keep streaming ingestion enabled on `src_table`.
```kql .create-or-alter function UpdateTargetTable() { src_table | join kind=inner (reference_table) on id | project src_table.id, src_table.name, reference_table.additional_info | into target_table } .create-or-alter function ScheduledUpdate() { UpdateTargetTable() } .create-or-alter function ScheduledUpdatePolicy() { .set-or-append target_table <| ScheduledUpdate() } .schedule function ScheduledUpdatePolicy() every 5m ```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.
6 Replies
Hi chetanhiwale ,
The SourceTable parameter should be set to src_table since it is the table where data is being inserted from the event stream.
Here's how you can define the update policy:
```kusto .alter table target_table policy update @'[{"IsEnabled": true, "Source": "src_table", "Query": "src_table | join kind=inner (reference_table) on id | project id, name, additional_info", "IsTransactional": true}]' ```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.
Thanks Anonymous for quick reply,
Is there any condition for source table, that its streaming ingestion should be disabled. I have tried the join but, got the following error
Error during execution of a policy operation: Referencing additional tables from update policy is not allowed when the source table has a StreamingIngestion policy defined.Hi chetanhiwale ,
I apologize for ignoring some of the limitations.
I found some information for your reference:.alter table src_table policy streamingingestion '{"IsEnabled": false}'For more details, please refer:
Update policy overview - Kusto | Microsoft Learn
.alter table policy streamingingestion command - 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.
