![]() For more information, see INSERT OVERWRITE SELECT. PARTITION BY VALUE(DATE_FORMAT(order_time, '%Y%m%d')) LIFECYCLE 90 -Retain list partition settings.Įxecute the INSERT OVERWRITE SELECT statement to import data from the source table to the temporary table. PRIMARY KEY (order_id,customer_id,order_time) -Add the distribution key customer_id and the partition key order_time to the primary key.ĭISTRIBUTED BY HASH(customer_id) -Change the distribution key from order_id to customer_id. Order_time timestamp NOT NULL COMMENT 'Order time', Order_id bigint NOT NULL COMMENT 'Order ID',Ĭustomer_id bigint NOT NULL COMMENT 'Customer ID',Ĭustomer_name varchar NOT NULL COMMENT 'Customer name', Its distribution key is intended to be changed from order_id to customer_id.Ĭreate a temporary table named order_auto_opt_v1 by using the distribution key customer_id. In this example, a table named order is used. If you want to use a different partition or distribution key for your table, perform the following steps. You can change the partition function but not the partition field of a table.Įxample 1 ALTER TABLE adb_demo.test PARTITION BY VALUE(c2) LIFECYCLE 10 Įxample 2 ALTER TABLE adb_demo.test PARTITION BY VALUE(date_format(c2, '%Y%m%d')) LIFECYCLE 10 ĪnalyticDB for MySQL does not allow you to change or add partition or distribution keys. You cannot add a partition field to a non-partitioned table. Syntax ALTER TABLE table_name PARTITION BY VALUE(column_name|date_format(column_name,'xxxxx')) LIFECYCLE N To enable the feature, contact Alibaba Cloud technical support to update the minor version of your cluster.įor information about how to query the minor version of a cluster, see the " How do I query the minor version of an AnalyticDB for MySQL cluster?" section of the Purchase, configuration change, and upgrade topic. Only AnalyticDB for MySQL clusters of V3.1.6 or later support the feature. For information about how to query the state of a BUILD task, see the " Query the state of a BUILD task" section of the BUILD topic.įor information about how to specify a hot and cold data storage policy when you create a table, see CREATE TABLE.Įxample 1: Change the storage policy of a table to COLD ALTER TABLE test_table storage_policy = 'COLD' Įxample 2: Change the storage policy of a table to HOT ALTER TABLE test_table storage_policy = 'HOT' Įxample 3: Change the storage policy of a table to MIXED and set the number of hot partitions to 10 ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10 īy default, the partition function change feature is disabled for AnalyticDB for MySQL clusters. After the execution is complete, the change takes effect. To trigger an immediate change, you can execute the BUILD TABLE db_name.table_name statement. The storage policy is not immediately changed after the ALTER TABLE statement is executed. You can change the storage policy between COLD, HOT, and MIXED. ![]() Syntax ALTER TABLE db_name.table_name MODIFY column_name data_type ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar comment 'The province where the customer is located' Ĭhange the value constraint for a column from NOT NULL to NULL Syntax ALTER TABLE db_name.table_name MODIFY column_name data_type comment 'new_comment' Ĭhange the comment of the province column in the customer table to "The province where the customer is located". ALTER TABLE adb_demo.customer DROP COLUMN province Remove the province column from the customer table. Syntax ALTER TABLE db_name.table_name DROP column_name data_type ![]() ALTER TABLE adb_demo.customer ADD COLUMN province varchar comment 'Province' Syntax ALTER TABLE db_name.table_name ADD column_name data_type Īdd the province column of the VARCHAR type to the customer table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |