Modify the schema of an external table by executing the ALTER TABLE statement - AnalyticDB

AnalyticDB for MySQL allows you to execute the ALTER TABLE statement to modify the schema of an external table. For example, you can modify the table structure, change the names of columns, change the data types of columns, and add partitions to or remove partitions from the external table. This topic describes how to execute the ALTER TABLE statement to modify the schema of external tables.

This topic is applicable only to external tables. For more information about the ALTER TABLE syntax of AnalyticDB for MySQL tables, see ALTER TABLE.

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.

Columns

Important

Only AnalyticDB for MySQL clusters of V3.2.1.0 or later support the following syntax.

To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

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

You cannot add primary key columns.

Examples

Add a column of the VARCHAR type named province to the osstest1 table.

ALTER TABLE osstest1 ADD COLUMN province VARCHAR COMMENT 'Province';

Add two columns to the osstest1 table: a column of the BOOLEAN type named vip and a column of the VARCHAR type named tags.
```
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_name

Usage notes

You cannot remove primary key columns.

Example

Remove the province column from the osstest1 table.

ALTER TABLE osstest1 DROP COLUMN province;

Change the name of a column

Syntax

ALTER TABLE table_name RENAME COLUMN column_name to new_column_name

Example

Change the name of 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_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 the default value of a column

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 the comment of a column

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

Important

The syntax related to partitions is applicable only to Object Storage Service (OSS) partitioned external tables.
Only AnalyticDB for MySQL clusters of V3.1.8.0 or later support the following syntax.
To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

You can use the ALTER TABLE ADD PARTITION statement to manually add a partition or a multi-level partition. For information about how to allow AnalyticDB for MySQL to identify an OSS path and add a partition, see MSCK REPAIR TABLE.

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 that you want to add. To add a multi-level partition, specify multiple `partition_key=value` expressions. Separate multiple expressions with commas (,). Example: `ADD PARTITION (date='20230613',city='hangzhou')`.
`location`	The OSS path of the partition. For example, the OSS path of an object is oss://testBucketName/testfolder/p1=20230613/data.csv and the partition to be added is p1='20230613'. In this case, set the parameter to `LOCATION 'oss://testBucketname/testfolder/'`.

Examples

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';

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 that you want to remove.

To remove a multi-level partition, specify multiple partition_key=value expressions. Separate the multiple expressions with commas (,). Example: DROP PARTITION (date='20230613',city='hangzhou').

Examples

Remove a 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');

References

CREATE EXTERNAL TABLE