Use ALTER TABLE to modify the schema of an external table in AnalyticDB for MySQL. Supported operations include adding, removing, renaming, and modifying columns, as well as adding and removing partitions on Object Storage Service (OSS) partitioned external tables.
This topic applies only to external tables. For the ALTER TABLE syntax for AnalyticDB for MySQL internal tables, see ALTER TABLE.
Prerequisites
Before you begin, ensure that you have:
An AnalyticDB for MySQL cluster of Enterprise Edition, Basic Edition, or Data Lakehouse Edition
Columns
Cluster version V3.2.1.0 or later is required for all column operations. To view and update the minor version of your cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page. For instructions, see Update the minor version of a cluster.
The following table summarizes the supported column operations and their constraints:
| Operation | Supported | Notes |
|---|---|---|
| Add a non-primary-key column | Yes | Use ADD COLUMN. |
| Add a primary key column | No | Primary key columns cannot be added to an existing external table. |
| Remove a non-primary-key column | Yes | Use DROP COLUMN. |
| Remove a primary key column | No | Primary key columns cannot be removed from an existing external table. |
| Rename a column | Yes | Use RENAME COLUMN. |
| Change a column data type | Yes | Use MODIFY COLUMN. |
| Change a column default value | Yes | Use MODIFY COLUMN ... DEFAULT. |
| Change a column comment | Yes | Use MODIFY COLUMN ... COMMENT. |
Add a column
Syntax
ALTER TABLE db_name.table_name ADD [COLUMN]
{column_name column_type [DEFAULT {constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT] [NULL|NOT NULL] [COMMENT 'comment']
| (column column_type [DEFAULT {constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT] [NULL|NOT NULL] [COMMENT 'comment'],...)}Usage notes
Primary key columns cannot be added.
Examples
Add a VARCHAR column named province to the osstest1 table:
ALTER TABLE osstest1 ADD COLUMN province VARCHAR COMMENT 'Province';Add two columns at once to the osstest1 table—a Boolean column named vip and a VARCHAR column named tags with a default value:
ALTER TABLE osstest1 ADD COLUMN (vip BOOLEAN COMMENT 'VIP', tags VARCHAR DEFAULT 'None' COMMENT 'Tags');Remove a column
Syntax
ALTER TABLE db_name.table_name DROP [COLUMN] column_nameUsage notes
Primary key columns cannot be removed.
Example
Remove the province column from the osstest1 table:
ALTER TABLE osstest1 DROP COLUMN province;Rename a column
Syntax
ALTER TABLE table_name RENAME COLUMN column_name to new_column_nameExample
Rename the city_name column in the osstest1 table to city:
ALTER TABLE osstest1 RENAME COLUMN city_name to city;Change the data type of a column
Syntax
ALTER TABLE table_name MODIFY [COLUMN] column_name new_column_typeExample
Change the data type of the age column in the osstest1 table from INT to BIGINT:
ALTER TABLE osstest1 MODIFY COLUMN age BIGINT;Change the default value of a column
Syntax
ALTER TABLE table_name MODIFY [COLUMN] column_name column_type DEFAULT constant|CURRENT_TIMESTAMPExample
Set the default value of the sex column in the osstest1 table to 0:
ALTER TABLE osstest1 MODIFY COLUMN sex INT DEFAULT 0;Change the comment of a column
Syntax
ALTER TABLE table_name MODIFY [COLUMN] column_name column_type COMMENT 'new_comment'Example
Update the comment on the region column in the osstest1 table:
ALTER TABLE osstest1 MODIFY COLUMN region VARCHAR COMMENT 'region';Partitions
The partition operations below apply only to OSS partitioned external tables. Cluster version V3.1.8.0 or later is required. To view and update the minor version of your cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page. For instructions, see Update the minor version of a cluster.
Use ALTER TABLE ADD PARTITION to manually add a partition or a multi-level partition. To let AnalyticDB for MySQL scan an OSS path and register partitions automatically, use MSCK REPAIR TABLE instead.
Add a partition
Syntax
ALTER TABLE table_name ADD PARTITION (partition_key=value[,...]) LOCATION 'location';Parameters
| Parameter | Description |
|---|---|
table_name | The name of the external table. |
(partition_key=value[,...]) | The partition to register. To add a multi-level partition, specify multiple partition_key=value pairs separated by commas. Example: (date='20230613', city='hangzhou'). |
location | The OSS path for the partition data. For example, if the partition is p1='20230613' and the object path is oss://testBucketName/testfolder/p1=20230613/data.csv, set this to oss://testBucketName/testfolder/. |
Examples
Add a single 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';Remove a partition
Syntax
ALTER TABLE table_name DROP PARTITION (partition_key=value[,...]);Parameters
| Parameter | Description |
|---|---|
table_name | The name of the external table. |
(partition_key=value[,...]) | The partition to remove. To drop a multi-level partition, specify multiple partition_key=value pairs separated by commas. Example: (date='20230613', city='hangzhou'). |
Examples
Remove a single partition:
ALTER TABLE adb_external_db.partition_table DROP PARTITION (p1='20230613');Remove a multi-level partition:
ALTER TABLE adb_external_db.partition_table DROP PARTITION (p1='20230613', p2='hangzhou');