Kusto Materialized view on Pivoted data

Question

Hi

I have some sample data mentioned on below in my Test table.

i am converting the rows into columns using the below query.

Test|where ParameterName in ("TotalNumberOfWagons","StateOfWeigh","NumberOfWeigh")
| project EventUTCDateTime,FunctionalLocation,ParameterName,RawValue
| summarize d = make_bag(pack(ParameterName,RawValue)) by EventUTCDateTime,FunctionalLocation
| evaluate bag_unpack(d)

and when i am creating the MV its throwing's an error because we can't use any code after the summarize function with in the MV.

.create async materialized-view with (backfill=true) MyMaterialisedView on table Test
{
Test|where ParameterName in ("TotalNumberOfWagons","StateOfWeigh","NumberOfWeigh")
| project EventUTCDateTime,FunctionalLocation,ParameterName,RawValue
| summarize d = make_bag(pack(ParameterName,RawValue)) by EventUTCDateTime,FunctionalLocation
| evaluate bag_unpack(d)
};

Could you please assist me is there any other way we can work on this.

Also can we apply some filter condition on the sourcetable while creating the Materialized view like below

.create async materialized-view with (backfill=true) MyMaterialisedView on table (Test|where System=='ABC')

Answer 1

Hello @ADX ,

Thanks for the question and using MS Q&A platform.

The summarize operator must always be the last operator in the query for a MV as documented here - Create materialized view - Azure Data Explorer | Microsoft Learn

So you cannot write bag_unpack() after summarize in MV.

Regarding filter conditions on a source table in MV - yes thats very much possible but make sure last statement should have summarize operator and the example above has wrong syntax so check that from the documentation. It would be like -

.create async materialized-view with (backfill=true) MyMaterialisedView on table Test  
{  
Test  
|where System=='ABC'  
| summarize ....  
}  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators