.alter-merge table policy mirroring command

  • Article

Changes the tables's mirroring policy. The mirroring policy creates a logical copy of tables in your database in delta parquet format and allows you to partition your files to improve query speed. Each partition is represented as a separate column using the PartitionName listed in the Partitions list. This means there are more columns in the target than in your source table.

Syntax

(.alter | .alter-merge) table TableName policy mirroring [partition by (Partitions)] dataformat = parquet
[with (IsEnabled=IsEnabledValue)]

Learn more about syntax conventions.

Parameters

Name Type Required Description
TableName string ✔️ A table name that adheres to the Entity names rules.
Partitions string A comma-separated list of columns used to divide the data into smaller partitions. See Partitions formatting.

Partitions formatting

The partitions list is any combination of partition columns, specified using one of the forms shown in the following table.

Partition Type Syntax Notes
Virtual column PartitionName : (datetime | string) Read more on Virtual columns.
String column value PartitionName : string = ColumnName
String column value hash() PartitionName : long = hash(ColumnName, Number) The hash is modulo Number.
Truncated datetime column (value) PartitionName : datetime = (startofyear | startofmonth | startofweek | startofday) ( ColumnName ) See documentation on startofyear, startofmonth, startofweek, or startofday functions.
Truncated datetime column (value) PartitionName : datetime = bin ( ColumnName , TimeSpan ) Learn more about the bin function.

Note

Each partition is represented as a separate column using the PartitionName listed in the Partitions list. PartitionName must be a case insensitive unique string, both among other partition names and the column names of the mirrored table.

Properties

Name Type Description
IsEnabled bool A Boolean value that determines whether the mirroring policy is enabled. Default is true. When the mirroring policy is disabled and set to false, the underlying mirroring data is soft-deleted and retained in the database.

Examples

.alter table policy mirroring

In the following example, a table called myTable is mirrored. The data is partitioned first by name and then by date.

.alter table myTable policy mirroring
  partition by (Name: string=Name, Date: datetime= startofday(timestamp))
  dataformat=parquet
  with
  (IsEnabled=true)

Related content

This feature isn't supported.