AnalyticDB for MySQL
supports modifying the structure of an external table using the ALTER TABLE
statement, which includes renaming columns, changing column data types, and adding or deleting partitions. This topic describes the syntax for modifying an external table with ALTER TABLE
.
This topic is relevant exclusively to external tables. For the ALTER TABLE syntax pertaining to internal tables, see ALTER TABLE.
Prerequisites
An AnalyticDB for MySQL Data Lakehouse Edition cluster is created.
Columns
This syntax is supported by clusters with a kernel version of 3.2.1.0 or later.
To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version();
statement. To update the minor version of a cluster, contact technical support.
Rename columns
Syntax
ALTER TABLE table_name RENAME COLUMN column_name to new_column_name
Example
Rename the city_name
column in the osstest1
table to city
.
ALTER TABLE osstest1 RENAME COLUMN city_name to city;
Change column data types
Syntax
ALTER TABLE table_name MODIFY [COLUMN] column_name new_column_type
Example
Change the data type of the age
column in the osstest1
table from INT to BIGINT.
ALTER TABLE osstest1 MODIFY COLUMN age BIGINT;
Change default values of columns
Syntax
ALTER TABLE table_name MODIFY [COLUMN] column_name column_type DEFAULT constant|CURRENT_TIMESTAMP
Example
Set the default value of the sex
column in the osstest1
table to 0.
ALTER TABLE osstest1 MODIFY COLUMN sex INT DEFAULT 0;
Change column comments
Syntax
ALTER TABLE table_name MODIFY [COLUMN] column_name column_type COMMENT 'new_comment'
Example
Change the COMMENT of the region
column in the osstest1
table to 'region'.
ALTER TABLE osstest1 MODIFY COLUMN region VARCHAR COMMENT 'region';
Partitions
-
The partition-related syntax is applicable only to OSS partitioned external tables. Other external tables are not supported.
-
This syntax is supported by clusters with a kernel version of 3.1.8.0 or later.
To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the
SELECT adb_version();
statement. To update the minor version of a cluster, contact technical support.
You can manually add a single or multi-level partition to a partitioned external table using the ALTER TABLE ADD PARTITION statement. For automatic OSS path detection and partition addition, see MSCK REPAIR TABLE.
Add partitions
Syntax
ALTER TABLE table_name ADD PARTITION (partition_key=value[,...]) LOCATION 'location';
Parameter description
Parameter | Description |
| Table name. |
| The partition that you want to add. When adding a multi-level partition, configure multiple equations ( |
| The OSS path of the partition. For example, the path of the OSS file is oss://testBucketName/testfolder/p1=20230613/data.csv. The partition to be added is p1='20230613'. In this case, specify |
Example
-
Add a partition.
ALTER TABLE adb_external_db.partition_table ADD PARTITION (p1='20230613') LOCATION 'oss://testBucketName/testfolder/p1=20230613/';
-
Add a multi-level partition.
ALTER TABLE adb_external_db.partition_table ADD PARTITION (p1='20230613',p2='hangzhou') LOCATION 'oss://testBucketName/testfolder/p1=20230613/p2=hangzhou';
Delete partitions
Syntax
ALTER TABLE table_name DROP PARTITION (partition_key=value[,...]);
Parameter description
Parameter | Description |
| Table name. |
| The partitions that you want to delete. When deleting a multi-level partition, configure multiple equations ( |
Example
-
Delete a partition.
ALTER TABLE adb_external_db.partition_table DROP PARTITION (p1='20230613');
-
Delete a multi-level partition.
ALTER TABLE adb_external_db.partition_table DROP PARTITION (p1='20230613',p2='hangzhou');