KQL update policy

[chetanhiwale]

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,

.alter table TargetTable policy update

@'[{"IsEnabled": true, "Source": "SourceTable", "Query": "SourceTable | join kind=inner (ReferenceTable) on id | project id, name, additional_info", "IsTransactional": true}]'

[v-tangjie-msft]

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. 

[chetanhiwale]

Thanks @v-tangjie-msft  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.

[v-tangjie-msft]

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. 

[chetanhiwale]

By disabling streaming ingestion , we cant use src_table , as we will not get any data in src_table. Please share your thoughts on it. 

[v-tangjie-msft · Accepted solution]

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. 

[JuanMahecha]

I have error when i try: 

 .create-or-alter function ScheduledUpdatePolicy() {
       .set-or-append target_table <| ScheduledUpdate()
   }

Also, ".schedule function ScheduledUpdate() every 5m" also doesn't work
I think .schedule is not a KQL command, also create an intermediate table that you never use, this solution doesn't work, I don't understand why it was marked correctly