Azure ADX - UpdatePolicy fails to insert data

Question

Azure ADX - UpdatePolicy fails to insert data

Krishna Kumar, Sai Hariharan 5

Hi Everyone,

I believe everyone is doing good and safe.

I am facing challenge with ADX. Please find the problem details below.

Problem statement: We are unable to insert a result data into a target table from source table using an UpdatePolicy.

Description: We have written an UpdatePolicy on a table. This UpdatePolicy will accept query parameters as an ADX function. This function returns output result in the form of table. Further, This table output result received should be inserted into target table.

Additional Details:

UpdatePolicy helps to update the data into a target table from source table dynamically/automatically.
UpdatePolicy is almost equivalent to Triggers in SQL Server to do dynamic insert into a target table.
Syntax of UpdatePolicy is as below

.alter table TargetTable policy update


[

   {

       "IsEnabled": true,

       "Source": "SourceTable",

       "Query": "SourceTable                     

                 | extend Result = G3MS_ClearAlarm(Id, CountryCode, OccuredTime)

                 | project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, ClearTime = Result.ClearTime",

       "IsTransactional": true,

       "PropagateIngestionProperties": false

   }

]

Error Received when executed

Error during execution of a policy operation: Request is invalid and cannot be processed: Semantic error: SEM0085: Tabular expression is not expected in the current context.

Error - snapshot

If anyone has any suggestions/thoughts on this will be very beneficial to complete the requirement.

J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-01T07:44:52.9033333+00:00
Hi @Krishna Kumar, Sai Hariharan

While executing the query your facing this error "Semantic error: SEM0085: Tabular expression is not expected in the current context" because the extend operator in your Update Policy query is being used with a function (G3MS_ClearAlarm) that returns a tabular result. In Kusto Query Language (KQL), the extend operator expects scalar expressions, not tabular ones.

To resolve this issue, you should modify your query to handle the tabular output appropriately.

.alter table TargetTable policy update @'[{ "IsEnabled": true, "Source": "SourceTable", "Query": " SourceTable | join kind=inner (G3MS_ClearAlarm(SourceTable.Id, SourceTable.CountryCode, SourceTable.OccuredTime)) on CommonColumn | project AlarmId, ClearAlarmId, ClearTime ", "IsTransactional": true, "PropagateIngestionProperties": false }]'

In this modified query:

G3MS_ClearAlarm is invoked separately, and its result is joined with SourceTable on a common column. Ensure that CommonColumn is a field that exists in both SourceTable and the output of G3MS_ClearAlarm to facilitate the join.

The project operator is then used to select the desired columns for insertion into the target table.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-01T10:30:46.3433333+00:00
Firstly, I would like to thank you for your time and your support for the problem.

I have tried the same as you have mentioned. Though the Id column is same in both the tables i get the error response and still it fails to execute.

Error i get after execution

Below is the actual statement i am trying to execute

.alter table AlarmClearDetails policy update

[ { "IsEnabled": true, "Source": "Alarm", "Query": "Alarm
| join kind=rightanti (G3MS_ClearAlarm(Alarm.AlarmId, Alarm.CountryCode, Alarm.ElevatorUnitNumber, Alarm.PlatformUuid, Alarm.SourceApplicationId, Alarm.AttributeId, Alarm.AlarmClearAttributes, Alarm.OccuredTime)) on AlarmId | project AlarmId, ClearAlarmId, ClearTime", "IsTransactional": true, "PropagateIngestionProperties": false } ]

Table Details
J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-02T05:53:28.2833333+00:00
@Krishna Kumar, Sai Hariharan

Rightanti joins are not allowed in update policies because they remove matches instead of inserting new data.

ADX Update Policies expect only new records to be inserted, but rightanti join excludes rows present in Alarm, which is incompatible.

Instead of rightanti join use inner join , run the updated query

Please check this modify your query

.alter table AlarmClearDetails policy update [ { "IsEnabled": true, "Source": "Alarm", "Query": " Alarm | join kind=inner (G3MS_ClearAlarm(Alarm.AlarmId, Alarm.CountryCode, Alarm.ElevatorUnitNumber, Alarm.PlatformUuid, Alarm.SourceApplicationId, Alarm.AttributeId, Alarm.AlarmClearAttributes, Alarm.OccuredTime)) on AlarmId | project AlarmId, ClearAlarmId, ClearTime ", "IsTransactional": true, "PropagateIngestionProperties": false } ]

please let me know if the issue still persists. I am happy to help further.
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-02T07:50:56.41+00:00

I have tried both inner and rightanti ... but i could not execute the uploadpolicy against the table .. if you can further refine the updatepolicy query it would be good.
J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-03T03:08:31.53+00:00

@Krishna Kumar, Sai Hariharan
Please help answer these questions I am happy to help further

Does G3MS_ClearAlarm return a single row per input row or multiple rows?

Have you tested G3MS_ClearAlarm separately to verify its output?

What are the schemas of Alarm and AlarmClearDetails tables?

Are you trying to insert only new records, or do you also want to update existing ones?

Have you tried materializing G3MS_ClearAlarm results into a staging table before using it in the UpdatePolicy?
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-03T06:00:15.8266667+00:00

Does G3MS_ClearAlarm return a single row per input row or multiple rows?

G3MS_ClearAlarm will return multiple rows.

Have you tested G3MS_ClearAlarm separately to verify its output?

Yes

What are the schemas of Alarm and AlarmClearDetails tables?

They are different.

Are you trying to insert only new records, or do you also want to update existing ones?

This will insert fresh records. No updates will happen.

Have you tried materializing G3MS_ClearAlarm results into a staging table before using it in the UpdatePolicy?

No. What are the advantages of materializing G3MS_ClearAlarm. Can you share some insights on this. I will work on this if there is any possibility.
J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-04T05:21:55.1933333+00:00
@Krishna Kumar, Sai Hariharan

Since G3MS_ClearAlarm returns multiple rows, we need to expand the tabular result into multiple rows.
Using mv-expand

.set-or-append AlarmClearStaging <| Alarm | extend Result = G3MS_ClearAlarm(AlarmId, CountryCode, ElevatorUnitNumber, PlatformUuid, SourceApplicationId, AttributeId, AlarmClearAttributes, OccuredTime) | mv-expand Result | project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, AlarmClearTime = Result.ClearTime

Please try this query. If it doesn't help, I will reach out to the internal team for assistance.
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-04T05:31:06.8533333+00:00

@J N S S Kasyap Appreciate your support

.set-or-append is a control command that cannot be executed in a query or a function in ADX Updatepolicy. Hence this solution also not working.

If this command is executed plainly not in updatepolicy i hope that is absolute one but i have a use case that is completely on other side.

Error

Error during execution of a policy operation: Request is invalid and cannot be processed: Syntax error: SYN0002: Unexpected control command [line:position=1:1].

2 answers

Your answer

J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-01T07:44:52.9033333+00:00

Hi @Krishna Kumar, Sai Hariharan

While executing the query your facing this error "Semantic error: SEM0085: Tabular expression is not expected in the current context" because the extend operator in your Update Policy query is being used with a function (G3MS_ClearAlarm) that returns a tabular result. In Kusto Query Language (KQL), the extend operator expects scalar expressions, not tabular ones.

To resolve this issue, you should modify your query to handle the tabular output appropriately.

.alter table TargetTable policy update @'[{ "IsEnabled": true, "Source": "SourceTable", "Query": " SourceTable | join kind=inner (G3MS_ClearAlarm(SourceTable.Id, SourceTable.CountryCode, SourceTable.OccuredTime)) on CommonColumn | project AlarmId, ClearAlarmId, ClearTime ", "IsTransactional": true, "PropagateIngestionProperties": false }]'

In this modified query:

G3MS_ClearAlarm is invoked separately, and its result is joined with SourceTable on a common column. Ensure that CommonColumn is a field that exists in both SourceTable and the output of G3MS_ClearAlarm to facilitate the join.

The project operator is then used to select the desired columns for insertion into the target table.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-01T10:30:46.3433333+00:00

Firstly, I would like to thank you for your time and your support for the problem.

I have tried the same as you have mentioned. Though the Id column is same in both the tables i get the error response and still it fails to execute.

Error i get after execution

Below is the actual statement i am trying to execute

.alter table AlarmClearDetails policy update

[ { "IsEnabled": true, "Source": "Alarm", "Query": "Alarm
| join kind=rightanti (G3MS_ClearAlarm(Alarm.AlarmId, Alarm.CountryCode, Alarm.ElevatorUnitNumber, Alarm.PlatformUuid, Alarm.SourceApplicationId, Alarm.AttributeId, Alarm.AlarmClearAttributes, Alarm.OccuredTime)) on AlarmId | project AlarmId, ClearAlarmId, ClearTime", "IsTransactional": true, "PropagateIngestionProperties": false } ]

Table Details
J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-02T05:53:28.2833333+00:00

@Krishna Kumar, Sai Hariharan

Rightanti joins are not allowed in update policies because they remove matches instead of inserting new data.

ADX Update Policies expect only new records to be inserted, but rightanti join excludes rows present in Alarm, which is incompatible.

Instead of rightanti join use inner join , run the updated query

Please check this modify your query

.alter table AlarmClearDetails policy update [ { "IsEnabled": true, "Source": "Alarm", "Query": " Alarm | join kind=inner (G3MS_ClearAlarm(Alarm.AlarmId, Alarm.CountryCode, Alarm.ElevatorUnitNumber, Alarm.PlatformUuid, Alarm.SourceApplicationId, Alarm.AttributeId, Alarm.AlarmClearAttributes, Alarm.OccuredTime)) on AlarmId | project AlarmId, ClearAlarmId, ClearTime ", "IsTransactional": true, "PropagateIngestionProperties": false } ]

please let me know if the issue still persists. I am happy to help further.
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-02T07:50:56.41+00:00

I have tried both inner and rightanti ... but i could not execute the uploadpolicy against the table .. if you can further refine the updatepolicy query it would be good.
J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-03T03:08:31.53+00:00

@Krishna Kumar, Sai Hariharan
Please help answer these questions I am happy to help further

Does G3MS_ClearAlarm return a single row per input row or multiple rows?

Have you tested G3MS_ClearAlarm separately to verify its output?

What are the schemas of Alarm and AlarmClearDetails tables?

Are you trying to insert only new records, or do you also want to update existing ones?

Have you tried materializing G3MS_ClearAlarm results into a staging table before using it in the UpdatePolicy?
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-03T06:00:15.8266667+00:00

Does G3MS_ClearAlarm return a single row per input row or multiple rows?

G3MS_ClearAlarm will return multiple rows.

Have you tested G3MS_ClearAlarm separately to verify its output?

Yes

What are the schemas of Alarm and AlarmClearDetails tables?

They are different.

Are you trying to insert only new records, or do you also want to update existing ones?

This will insert fresh records. No updates will happen.

Have you tried materializing G3MS_ClearAlarm results into a staging table before using it in the UpdatePolicy?

No. What are the advantages of materializing G3MS_ClearAlarm. Can you share some insights on this. I will work on this if there is any possibility.
J N S S Kasyap 1,715 Reputation points Microsoft External Staff

2025-04-04T05:21:55.1933333+00:00

@Krishna Kumar, Sai Hariharan

Since G3MS_ClearAlarm returns multiple rows, we need to expand the tabular result into multiple rows.
Using mv-expand

.set-or-append AlarmClearStaging <| Alarm | extend Result = G3MS_ClearAlarm(AlarmId, CountryCode, ElevatorUnitNumber, PlatformUuid, SourceApplicationId, AttributeId, AlarmClearAttributes, OccuredTime) | mv-expand Result | project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, AlarmClearTime = Result.ClearTime

Please try this query. If it doesn't help, I will reach out to the internal team for assistance.
Krishna Kumar, Sai Hariharan 5 Reputation points

2025-04-04T05:31:06.8533333+00:00

@J N S S Kasyap Appreciate your support

.set-or-append is a control command that cannot be executed in a query or a function in ADX Updatepolicy. Hence this solution also not working.

If this command is executed plainly not in updatepolicy i hope that is absolute one but i have a use case that is completely on other side.

Error

Error during execution of a policy operation: Request is invalid and cannot be processed: Syntax error: SYN0002: Unexpected control command [line:position=1:1].

Answer 1

Atlast i could get the solution from my colleague who was expert in finding solutions. Below is the way i could resolve this problem

ADX Tables

.create table ABC(CountryCode: string,  PlatformDeviceType: string, UnitState: string, SystemType: string, AttributeId: int, OccuredTime: datetime, ReceivedTime: datetime, SourceApplicationId: int,AlarmClearAttributes: string)

.create table AlarmClearDetails (AlarmId: string, ClearAlarmId: string, AlarmClearTime: datetime)

ADX Function:

We need to mention all columns and below is syntax of the function

.create-or-alter function with (
    docstring = "<Details abouve the function>",
    folder = "<FolderNAME>"
)
G3MS_ClearAlarm(input: (
CountryCode: string,  PlatformDeviceType: string, UnitState: string, SystemType: string, AttributeId: int, OccuredTime: datetime, ReceivedTime: datetime, SourceApplicationId: int,AlarmClearAttributes: string
))
{

//WRITE logic you wanted to do as part of business.

}

ADX Update Policy

.alter table AlarmClearDetails policy update
@'[{"Source": "Alarm", "Query": "G3MS_ClearAlarm(ABC)", "IsEnabled": true}]'

In this way we can solve the problem statement i had.

Rakesh Govindula 965 Reputation points Microsoft External Staff

2025-04-08T11:46:41.9033333+00:00

Hi @Krishna Kumar, Sai Hariharan,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this!

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I have reposted your solution in case you'd like to "Accept " the answer.

Answer 2

Hi @Krishna Kumar, Sai Hariharan,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this!

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

Unable to insert a result data into a target table from source table using an UpdatePolicy.

When using the below code:


alter table TargetTable policy update

Copy

[

   {

       "IsEnabled": true,

       "Source": "SourceTable",

       "Query": "SourceTable                     

                 | extend Result = G3MS_ClearAlarm(Id, CountryCode, OccuredTime)

                 | project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, ClearTime = Result.ClearTime",

       "IsTransactional": true,

       "PropagateIngestionProperties": false

   }

]

Getting below error while trying the above query:


Error during execution of a policy operation: Request is invalid and cannot be processed: Semantic error: SEM0085: Tabular expression is not expected in the current context.

Solution:

We need to mention all columns and below is syntax of the function

Creation of ADX Tables:


.create table ABC(CountryCode: string,  PlatformDeviceType: string, UnitState: string, SystemType: string, AttributeId: int, OccuredTime: datetime, ReceivedTime: datetime, SourceApplicationId: int,AlarmClearAttributes: string)


.create table AlarmClearDetails (AlarmId: string, ClearAlarmId: string, AlarmClearTime: datetime)

ADX Function:

We need to mention all columns and below is syntax of the function


.create-or-alter function with (

    docstring = "<Details abouve the function>",

    folder = "<FolderNAME>"

)

G3MS_ClearAlarm(input: (

CountryCode: string,  PlatformDeviceType: string, UnitState: string, SystemType: string, AttributeId: int, OccuredTime: datetime, ReceivedTime: datetime, SourceApplicationId: int,AlarmClearAttributes: string

))

{

//WRITE logic you wanted to do as part of business.

}

ADX Update Policy:


.alter table AlarmClearDetails policy update

@'[{"Source": "Alarm", "Query": "G3MS_ClearAlarm(ABC)", "IsEnabled": true}]'

Please click Accept Answer and kindly upvote it so that other people who faces similar issue may get benefitted from it.

Share via

Azure ADX - UpdatePolicy fails to insert data

2 answers

Your answer