ALTER-TABLE-PROPERTY

Name

ALTER TABLE PROPERTY

:::caution Differences between Partition Attributes and Table Attributes

Partition attributes generally focus on the number of buckets (buckets), storage medium (storage_medium), replication num (replication_num), and hot/cold data separation policies (storage_policy).
- For existing partitions, you can use ALTER TABLE {tableName} MODIFY PARTITION ({partitionName}) SET ({key}={value}) to modify them, but the number of buckets (buckets) cannot be changed.
- For not-created dynamic partitions, you can use ALTER TABLE {tableName} SET (dynamic_partition.{key} = {value}) to modify their attributes.
- For not-created auto partitions, you can use ALTER TABLE {tableName} SET ({key} = {value}) to modify their attributes.
- If users want to modify partition attributes, they need to modify the attributes of the already created partitions, as well as the attributes of not-created partitions.
Aside from the above attributes, all others are at the table level.
For the specific attributes, please refer to create table attributes :::

Description

This statement is used to modify the properties of an existing table. This operation is synchronous, and the return of the command indicates the completion of the execution.

Modify the properties of the table, currently supports modifying the bloom filter column, the colocate_with attribute and the dynamic_partition attribute, the replication_num and default.replication_num.

grammar:

ALTER TABLE [database.]table alter_clause;

The alter_clause of property supports the following modification methods.

Note:

Can also be merged into the above schema change operation to modify, see the example below

Modify the bloom filter column of the table

ALTER TABLE example_db.my_table SET ("bloom_filter_columns"="k1,k2,k3");

Can also be incorporated into the schema change operation above (note that the syntax for multiple clauses is slightly different)

ALTER TABLE example_db.my_table
DROP COLUMN col2
PROPERTIES ("bloom_filter_columns"="k1,k2,k3");

Modify the Colocate property of the table

ALTER TABLE example_db.my_table set ("colocate_with" = "t1");

Change the bucketing method of the table from Hash Distribution to Random Distribution

ALTER TABLE example_db.my_table set ("distribution_type" = "random");

Modify the dynamic partition attribute of the table (support adding dynamic partition attribute to the table without dynamic partition attribute)

ALTER TABLE example_db.my_table set ("dynamic_partition.enable" = "false");

If you need to add dynamic partition attributes to tables without dynamic partition attributes, you need to specify all dynamic partition attributes (Note: adding dynamic partition attributes is not supported for non-partitioned tables)

ALTER TABLE example_db.my_table set (
  "dynamic_partition.enable" = "true", 
  "dynamic_partition.time_unit" = "DAY", 
  "dynamic_partition.end" = "3", 
  "dynamic_partition.prefix" = "p", 
  "dynamic_partition. buckets" = "32"
);

Modify the in_memory attribute of the table, only can set value 'false'

ALTER TABLE example_db.my_table set ("in_memory" = "false");

Enable batch delete function

ALTER TABLE example_db.my_table ENABLE FEATURE "BATCH_DELETE";

Note:

Only support unique tables
Batch deletion is supported for old tables, while new tables are already supported when they are created

Enable the function of ensuring the import order according to the value of the sequence column

ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES (
  "function_column.sequence_type" = "Date"
);

Note:

Only support unique tables
The sequence_type is used to specify the type of the sequence column, which can be integral and time type
Only the orderliness of newly imported data is supported. Historical data cannot be changed

Change the default number of buckets for the table to 50

ALTER TABLE example_db.my_table MODIFY DISTRIBUTION DISTRIBUTED BY HASH(k1) BUCKETS 50;

Note:

Only support non colocate table with RANGE partition and HASH distribution

Modify table comments

ALTER TABLE example_db.my_table MODIFY COMMENT "new comment";

Modify column comments

ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2 COMMENT "k2";

Modify the engine type

Only the MySQL type can be changed to the ODBC type. The value of driver is the name of the driver in the odbc.init configuration.

ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");

Modify the number of copies

ALTER TABLE example_db.mysql_table SET ("replication_num" = "2");
ALTER TABLE example_db.mysql_table SET ("default.replication_num" = "2");
ALTER TABLE example_db.mysql_table SET ("replication_allocation" = "tag.location.default: 1");
ALTER TABLE example_db.mysql_table SET ("default.replication_allocation" = "tag.location.default: 1");

Note:

The property with the default prefix indicates the default replica distribution for the modified table. This modification does not modify the current actual replica distribution of the table, but only affects the replica distribution of newly created partitions on the partitioned table.
For non-partitioned tables, modifying the replica distribution property without the default prefix will modify both the default replica distribution and the actual replica distribution of the table. That is, after the modification, through the show create table and show partitions from tbl statements, you can see that the replica distribution has been modified. changed.
For partitioned tables, the actual replica distribution of the table is at the partition level, that is, each partition has its own replica distribution, which can be viewed through the show partitions from tbl statement. If you want to modify the actual replica distribution, see ALTER TABLE PARTITION.

13. [Experimental] turn on light_schema_change

For tables that were not created with light_schema_change enabled, you can enable it by using the following statement.

ALTER TABLE example_db.mysql_table SET ("light_schema_change" = "true");

Example

Modify the bloom filter column of the table

ALTER TABLE example_db.my_table SET ("bloom_filter_columns"="k1,k2,k3");

Can also be incorporated into the schema change operation above (note that the syntax for multiple clauses is slightly different)

ALTER TABLE example_db.my_table
DROP COLUMN col2
PROPERTIES ("bloom_filter_columns"="k1,k2,k3");

Modify the Colocate property of the table

ALTER TABLE example_db.my_table set ("colocate_with" = "t1");

Change the bucketing method of the table from Hash Distribution to Random Distribution

ALTER TABLE example_db.my_table set ("distribution_type" = "random");

Modify the dynamic partition attribute of the table (support adding dynamic partition attribute to the table without dynamic partition attribute)

ALTER TABLE example_db.my_table set ("dynamic_partition.enable" = "false");

ALTER TABLE example_db.my_table set ("dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p", "dynamic_partition. buckets" = "32");

Modify the in_memory attribute of the table, only can set value 'false'

ALTER TABLE example_db.my_table set ("in_memory" = "false");

Enable batch delete function

ALTER TABLE example_db.my_table ENABLE FEATURE "BATCH_DELETE";

Enable the function of ensuring the import order according to the value of the sequence column

ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date");

Change the default number of buckets for the table to 50

ALTER TABLE example_db.my_table MODIFY DISTRIBUTION DISTRIBUTED BY HASH(k1) BUCKETS 50;

Modify table comments

ALTER TABLE example_db.my_table MODIFY COMMENT "new comment";

Modify column comments

ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2 COMMENT "k2";

Modify the engine type

ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");

Add a cold and hot separation data migration strategy to the table

 ALTER TABLE create_table_not_have_policy set ("storage_policy" = "created_create_table_alter_policy");

NOTE：The table can be successfully added only if it hasn't been associated with a storage policy. A table just can have one storage policy.

Add a hot and cold data migration strategy to the partition of the table

ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="created_create_table_partition_alter_policy");

NOTE：The table's partition can be successfully added only if it hasn't been associated with a storage policy. A table just can have one storage policy.

Keywords

ALTER, TABLE, PROPERTY, ALTER TABLE

Best Practice

ALTER-TABLE-PARTITION ALTER-TABLE-RENAME