ALTER TABLE change external table structure - AnalyticDB

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

Important

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

Important

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`	Table name.
`(partition_key=value[,...])`	The partition that you want to add. When adding a multi-level partition, configure multiple equations (`paritition_key=value`) separated by commas (,). For example: `ADD PARTITION (date='20230613',city='hangzhou')`.
`location`	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 `LOCATION 'oss://testBucketname/testfolder/'`.

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

Table name.

(partition_key=value[,...])

The partitions that you want to delete.

When deleting a multi-level partition, configure multiple equations (paritition_key=value) separated by commas (,). Example of deleting a multi-level partition: DROP PARTITION (date='20230613',city='hangzhou').

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

References

Create External Table