ALTER-TABLE-PROPERTY

Name

ALTER TABLE PROPERTY

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:

  1. 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

  1. Modify the bloom filter column of the table
  1. 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)

  1. ALTER TABLE example_db.my_table
  2. DROP COLUMN col2
  3. PROPERTIES ("bloom_filter_columns"="k1,k2,k3");
  1. Modify the Colocate property of the table
  1. ALTER TABLE example_db.my_table set ("colocate_with" = "t1");
  1. Change the bucketing method of the table from Hash Distribution to Random Distribution
  1. ALTER TABLE example_db.my_table set ("distribution_type" = "random");
  1. Modify the dynamic partition attribute of the table (support adding dynamic partition attribute to the table without dynamic partition attribute)
  1. 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)

  1. ALTER TABLE example_db.my_table set (
  2.   "dynamic_partition.enable" = "true", 
  3.   "dynamic_partition.time_unit" = "DAY", 
  4.   "dynamic_partition.end" = "3", 
  5.   "dynamic_partition.prefix" = "p", 
  6.   "dynamic_partition. buckets" = "32"
  7. );
  1. Modify the in_memory attribute of the table, only can set value ‘false’
  1. ALTER TABLE example_db.my_table set ("in_memory" = "false");
  1. Enable batch delete function
  1. 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
  1. Enable the function of ensuring the import order according to the value of the sequence column
  1. ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES (
  2.   "function_column.sequence_type" = "Date"
  3. );

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
  1. Change the default number of buckets for the table to 50
  1. 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
  1. Modify table comments
  1. ALTER TABLE example_db.my_table MODIFY COMMENT "new comment";
  1. Modify column comments
  1. ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2 COMMENT "k2";
  1. 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.

  1. ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
  1. Modify the number of copies
  1. ALTER TABLE example_db.mysql_table SET ("replication_num" = "2");
  2. ALTER TABLE example_db.mysql_table SET ("default.replication_num" = "2");
  3. ALTER TABLE example_db.mysql_table SET ("replication_allocation" = "tag.location.default: 1");
  4. ALTER TABLE example_db.mysql_table SET ("default.replication_allocation" = "tag.location.default: 1");

Note:

  1. 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.
  2. 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.

  3. 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.

  4. [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.

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

Example

  1. Modify the bloom filter column of the table
  1. 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)

  1. ALTER TABLE example_db.my_table
  2. DROP COLUMN col2
  3. PROPERTIES ("bloom_filter_columns"="k1,k2,k3");
  1. Modify the Colocate property of the table
  1. ALTER TABLE example_db.my_table set ("colocate_with" = "t1");
  1. Change the bucketing method of the table from Hash Distribution to Random Distribution
  1. ALTER TABLE example_db.my_table set ("distribution_type" = "random");
  1. Modify the dynamic partition attribute of the table (support adding dynamic partition attribute to the table without dynamic partition attribute)
  1. 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)

  1. 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");
  1. Modify the in_memory attribute of the table, only can set value ‘false’
  1. ALTER TABLE example_db.my_table set ("in_memory" = "false");
  1. Enable batch delete function
  1. ALTER TABLE example_db.my_table ENABLE FEATURE "BATCH_DELETE";
  1. Enable the function of ensuring the import order according to the value of the sequence column
  1. ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date");
  1. Change the default number of buckets for the table to 50
  1. ALTER TABLE example_db.my_table MODIFY DISTRIBUTION DISTRIBUTED BY HASH(k1) BUCKETS 50;
  1. Modify table comments
  1. ALTER TABLE example_db.my_table MODIFY COMMENT "new comment";
  1. Modify column comments
  1. ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2 COMMENT "k2";
  1. Modify the engine type
  1. ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
  1. Add a cold and hot separation data migration strategy to the table
  1.  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.

  1. Add a hot and cold data migration strategy to the partition of the table
  1. 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

  1. ALTER, TABLE, PROPERTY, ALTER TABLE

Best Practice