1

I'm writing a function which takes in raw data table (contains multijson telemetry data) and reformat it to a multiple cols. I use .set MyTable <| myfunction|limit 0 to create my target table based off of the function and use update policy to alert my target table.

Here is the code :

.set-or-append MyTargetTable <| 
   myfunction
   | limit 0

.alter table MyTargetTable policy update 
@'[{ "IsEnabled": true, "Source": "raw", "Query": "myfunction()", "IsTransactional": false, "PropagateIngestionProperties": false}]'

But I'm getting ingestion failures: Here is the ingestion failure message :

Failed to invoke update policy. Target Table = 'MyTargetTable', Query = '

let raw = __table("raw", 'All', 'AllButRowStore') 
| where extent_id() in (guid(659e3b3c-6859-426d-9c37-003623834455));
myfunction()': Query schema does not match table schema

I double check the query schema and target table; they are the same . I'm not sure what this error means. Also, I ran count on both the raw and mytarget tables; there are relatively large discrepancies (400 rows for My target and 2000 rows in raw table).

Any advise will be appreciated.

SendETHToThisAddress
  • 2,756
  • 7
  • 29
  • 54
user15186335
  • 23
  • 3

1 Answers1

1

Generally speaking - to find the root of the mismatch between schemas, you can run something along the following lines, and filter for differences:

myfunction
| getschema 
| join kind=leftouter (
    table('MyTargetTable')
    | getschema 
) on ColumnOrdinal, ColumnType

In addition - you should make sure the output schema of the function you use in your update policy is 'stable', i.e. isn't affected by the input data

  • The output schema of some query plugins such as pivot() and bag_unpack() depends on the input data, and therefore it isn't recommended to use those in update policies.
Yoni L.
  • 22,627
  • 2
  • 29
  • 48
  • Thanks Yoni , I ran your suggested lines and that confirmed there is no mismatch between my target table and function. In regards to your 2nd point, I'm using bag_unpack in my function is this what you think causing the issue? who could I solve this problem? Also I added the results of schema comparison to the original message for your reference – user15186335 Feb 10 '21 at 22:15
  • 1
    The problem is that you're using `bag_unpack()` - You need to alter the definition of your function, so its output schema is fixed, and doesn't depend on input data. As previously mentioned: *"it isn't recommended to use those in update policies"*. This is also mentioned in the documentation: (https://learn.microsoft.com/azure/data-explorer/kusto/query/bag-unpackplugin) *"The plugin's output schema depends on the data values, making it as "unpredictable" as the data itself. Multiple executions of the plugin, using different data inputs, may produce different output schema"* – Yoni L. Feb 10 '21 at 23:06
  • @user15186335, if your question was answered, please accept Yoni's answer :) and if you're missing any info, please add a comment specifying what's missing. – Slavik N Feb 18 '21 at 04:23