.alter materialized-view

Altering the materialized view can be used for changing the query of a materialized view, while preserving the existing data in the view.

Warning

Be extra cautious when altering a materialized view. Incorrect use may lead to data loss.

Permissions

You must have at least Materialized View Admin permissions to run this command.

Syntax

.alter materialized-view [ with (PropertyName = PropertyValue,...)] MaterializedViewName on table SourceTableName { Query }

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
PropertyName, PropertyValue	string		List of properties in the form of name and value pairs, from the list of supported properties.
MaterializedViewName	string	✔️	Name of the materialized view.
SourceTableName	string	✔️	Name of source table on which the view is defined.
Query	string	✔️	Query definition of the materialized view.

Supported properties

The following properties are supported in the with (PropertyName = PropertyValue) clause. All properties are optional.

Name	Type	Description
lookback	timespan	Valid only for arg_max/arg_min/take_any materialized views. It limits the period of time in which duplicates are expected. For example, if a lookback of 6 hours is specified on an arg_max view, the deduplication between newly ingested records and existing ones will take into consideration only records that were ingested up to 6 hours ago. Lookback is relative to ingestion_time. Defining the lookback period incorrectly might lead to duplicates in the materialized view. For example, if a record for a specific key is ingested 10 hours after a record for the same key was ingested, and the lookback is set to 6 hours, that key will be a duplicate in the view. The lookback period is applied during both materialization time and query time.
autoUpdateSchema	bool	Whether to automatically update the view on source table changes. Default is false. This option is valid only for views of type arg_max(Timestamp, *)/arg_min(Timestamp, *)/take_any(*) (only when the column's argument is *). If this option is set to true, changes to the source table will be automatically reflected in the materialized view.
dimensionTables	array	A dynamic argument that includes an array of dimension tables in the view. See Query parameter.
folder	string	The materialized view's folder.
docString	string	A string that documents the materialized view.

Returns

Output schema:

Name	Type	Description
Name	string	Name of the materialized view.
SourceTable	string	Name of source table on which the view is defined.
Query	string	Query definition of the materialized view.
MaterializedTo	datetime	Maximum materialized ingestion_time() timestamp in source table. For more information, see how materialized views work.
LastRun	datetime	Last time materialization was run.
LastRunResult	string	Result of last run. Returns Completed for successful runs, otherwise Failed.
IsHealthy	bool	true when view is considered healthy, false otherwise. View is considered healthy if it was successfully materialized up to the last hour (MaterializedTo is greater than ago(1h)).
IsEnabled	bool	true when view is enabled (see Disable or enable materialized view).
Folder	string	Folder under which the materialized view is created.
DocString	string	Description assigned to the materialized view.
AutoUpdateSchema	bool	Whether the view is enabled for auto updates.
EffectiveDateTime	datetime	Effective date time of the view, determined during creation time (see .create materialized-view).
Lookback	timespan	Time span limiting the period of time in which duplicates are expected.

Examples

Modify the query definition of a materialized view

The following command modifies the query definition of materialized view MyView:

.alter materialized-view MyView on table MyTable
{
    MyTable | summarize arg_max(Column3, *) by Column1
}

Output

Name	SourceTable	Query	MaterializedTo	LastRun	LastRunResult	IsHealthy	IsEnabled	Folder	DocString	AutoUpdateSchema	EffectiveDateTime	Lookback
MyView	MyTable	MyTable | summarize arg_max(Column3, *) by Column1	2023-02-26T16:40:03.3345704Z	2023-02-26T16:44:15.9033667Z	Completed	true	true			false	2023-02-23T14:01:42.5172342Z

Use cases

• Add aggregations to the view - for example, add avg aggregation to T | summarize count(), min(Value) by Id, by altering view query to T | summarize count(), min(Value), avg(Value) by Id.
• Change operators other than the summarize operator. For example, filter out some records by altering T | summarize arg_max(Timestamp, *) by User to T | where User != 'someone' | summarize arg_max(Timestamp, *) by User.
• Alter with no change to the query because of a change in source table. For example, assume a view of T | summarize arg_max(Timestamp, *) by Id, which isn't set to autoUpdateSchema (see .create materialized-view command). If a column is added or removed from the source table of the view, the view is automatically disabled. Execute the alter command, with the exact same query, to change the materialized view's schema to align with new table schema. The view still must be explicitly enabled following the change, using the enable materialized view command.

Limitations

• Changes not supported:

  • Changes to the materialized view group by expressions.
  • Changing column type.
  • Renaming columns. For example, altering a view of T | summarize count() by Id to T | summarize Count=count() by Id drops column count_ and creates a new column Count, which initially contains nulls only.

• Impact on existing data:

  • Altering the materialized view has no impact on existing data.
  • Adding filters to the query applies only to newly ingested records, and doesn't change records that have already been materialized.
    • New columns receive nulls for all existing records until records ingested after the alter command modify the null values.
    • For example: A view of T | summarize count() by bin(Timestamp, 1d) is altered to T | summarize count(), sum(Value) by bin(Timestamp, 1d). For a particular Timestamp=T for which records have already been processed before altering the view, the sum column contains partial data. This view only includes records processed after the alter execution.