.alter-merge table policy mirroring command
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 = bin ( ColumnName , TimeSpan ) |
Read 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. |
Note
When the mirroring policy is enabled, tables can't be renamed.
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
- To check mirroring operations, see .show table mirroring operations command.
- To delete mirroring operations, see .delete table policy mirroring command.
This feature isn't supported.