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.
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.
If anyone has any suggestions/thoughts on this will be very beneficial to complete the requirement.
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.
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.
If anyone has any suggestions/thoughts on this will be very beneficial to complete the requirement.
Share Improve this question asked Apr 1 at 12:27 SaihariharanSaihariharan 1392 silver badges7 bronze badges1 Answer
Reset to default 0one (preferred) option would be to define the logic that is run by the update policy in a stored function, then reference that function in the "Query" property of the policy.
another option is to properly format your command:
.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
}
]```