.create-or-alter materialized-view

Article
03/23/2023

Creates a materialized view or alters an existing materialized view.

Permissions

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

Syntax

.create-or-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. The view name can't conflict with table or function names in the same database and must adhere to the identifier naming rules.
SourceTableName	`string`	✔️	Name of source table on which the view is defined.
Query	`string`	✔️	Query definition of the materialized view.

Supported properties

New table

If the table is new, the following properties are supported in the with(PropertyName = PropertyValue ). All properties are optional.

Name	Type	Description
backfill	`bool`	Whether to create the view based on all records currently in `SourceTable` (`true`), or to create it from now on (`false`). Default is `false`. For more information, see Backfill a materialized view.
effectiveDateTime	`datetime`	Relevant only when you're using `backfill`. If it's set, creation backfills only with records ingested after the datetime. `backfill` must also be set to `true`. This property expects a datetime literal; for example, `effectiveDateTime=datetime(2019-05-01)`.
updateExtentsCreationTime	`bool`	Relevant only when you're using `backfill`. If it's set to `true`, Extent Creation time is assigned based on the datetime group-by key during the backfill process. For more information, see Backfill a materialized view.
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.
allowMaterializedViewsWithoutRowLevelSecurity	`bool`	Allows creating a materialized view over a table with row level security policy enabled.

Existing table

If the table already exists, only the following subset of properties are supported in the with(PropertyName = PropertyValue ). 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

Create or alter a materialized view

The following command creates a new or alters an existing materialized view called ArgMax:

.create-or-alter materialized-view ArgMax on table T
{
    T | summarize arg_max(Timestamp, *) by User
}

Output

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

Remarks

If the materialized view does not exist, this command behaves just like .create materialized-view.

For more information, see the Query parameter and Properties sections.

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.