Use SQL statements to modify table properties and view table metadata in MaxCompute.
Supported platforms
Run the commands in this topic on any of the following platforms:
Required permissions
| Operation | Required role or permission |
|---|---|
| Change the owner of a table | Project owner or the Super_Administrator role |
| Modify the comment of a table | ALTER permission on the table |
| Modify the last update time of a table | ALTER permission on the table |
| Modify the clustering attributes of a table | ALTER permission on the table |
| Rename a table | ALTER permission on the table |
| Clear data from a non-partitioned table | ALTER permission on the table |
| Clear column data | ALTER permission on the table |
| View table information | DESCRIBE permission on the table |
| View the CREATE TABLE statement | DESCRIBE permission on the table |
| List tables and views in a project | LIST permission on project objects |
| List external tables in a project | LIST permission on project objects |
Related topics
Change the owner of a table
Change the owner of a table to another Alibaba Cloud account or RAM user.
Important
Only the project owner or users with the Super_Administrator role can run this statement.
Syntax
ALTER TABLE <table_name> CHANGEOWNER TO <new_owner>;Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the table. |
| new_owner | Yes | The new owner. To specify a RAM user, use the format RAM$<UID>:<ram_name>, where UID is the Alibaba Cloud account ID and ram_name is the RAM user display name. |
To set the table owner to a RAM user, make sure the RAM user has been added to the project where the table resides.
Examples
The sample data in this section uses the sale_detail table. For more information, see Create a partitioned table.
Change the owner to an Alibaba Cloud account:
ALTER TABLE sale_detail CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';Change the owner to a RAM user named ram_test:
ALTER TABLE sale_detail CHANGEOWNER TO 'RAM$13xxxxxxxxxxx:ram_test';Modify the comment of a table
Update the descriptive comment on a table.
Syntax
ALTER TABLE <table_name> SET COMMENT '<new_comment>';Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the table. |
| new_comment | Yes | The new comment. |
Examples
Set a new comment on the sale_detail table:
ALTER TABLE sale_detail SET COMMENT 'new comment for table sale_detail';Verify the updated comment by running DESC sale_detail;. The output includes the updated TableComment field:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$sant****.aliyunid.com |
| Project: ani**** |
| Schema: default |
| TableComment: new comment for table sale_detail |
+------------------------------------------------------------------------------------+Modify the last update time of a table
Reset the LastModifiedTime of a table to the current time. MaxCompute then treats the table data as changed and restarts the lifecycle calculation.
Syntax
ALTER TABLE <table_name> TOUCH;Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the table. |
Examples
ALTER TABLE sale_detail TOUCH;Modify the clustering attributes of a table
Add or remove clustering attributes on a partitioned table.
The ALTER TABLE clustering statement applies only to partitioned tables. Clustering attributes on a non-partitioned table cannot be changed after creation.Syntax
Add hash clustering attributes:
ALTER TABLE <table_name>
[CLUSTERED BY (<col_name> [, <col_name>, ...])
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
INTO <number_of_buckets> BUCKETS];Remove hash clustering attributes:
ALTER TABLE <table_name> NOT CLUSTERED;Add range clustering attributes:
ALTER TABLE <table_name>
[RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
INTO <number_of_buckets> BUCKETS];The INTO <number_of_buckets> BUCKETS clause is optional for range clustering. If omitted, MaxCompute determines the optimal number based on the data volume.
Remove range clustering attributes from a table or partition:
ALTER TABLE <table_name> NOT CLUSTERED;
ALTER TABLE <table_name> <pt_spec> NOT CLUSTERED;Parameters
For parameter descriptions, see Create and drop tables.
Usage notes
The
ALTER TABLEclustering statement applies to existing tables. After you add clustering attributes, new partitions are stored based on these attributes. The statement affects only new partitions, including partitions generated byINSERT OVERWRITE. Existing partitions retain their original storage layout.To change clustering columns, sort columns, or bucket counts on a table that already has clustering attributes, first remove the existing clustering attributes and then add new ones.
Because clustering changes affect only new partitions, do not specify a partition in the
ALTER TABLEclustering statement.
Rename a table
Change the name of a table without affecting its data.
Syntax
ALTER TABLE <table_name> RENAME TO <new_table_name>;Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The current name of the table. |
| new_table_name | Yes | The new name for the table. An error is returned if a table with this name already exists. |
Examples
ALTER TABLE sale_detail RENAME TO sale_detail_rename;Clear data from a non-partitioned table
Delete all data from a non-partitioned table. To clear data from specific partitions of a partitioned table, see Clear data from a partition.
Syntax
TRUNCATE TABLE <table_name>;Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the non-partitioned table to clear. |
Clear column data
Remove data from specific columns in a standard table. This operation deletes column data from disk, sets column values to NULL, and reduces storage costs.
Syntax
ALTER TABLE <table_name> [partition ( <pt_spec>[, <pt_spec>....] )]
CLEAR COLUMN column1[, column2, column3, ...]
[without touch];Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the table. |
| column | Yes | The name of the column to clear. Separate multiple columns with commas. |
| partition | No | The partition to target. If omitted, the operation affects all partitions. |
| pt_spec | No | The partition specification, in the format (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). |
| without touch | No | If specified, LastDataModifiedTime is not updated. If omitted, LastDataModifiedTime is automatically refreshed. |
Examples
Add partitions and insert data into the sale_detail table, then clear the shop_name column in a specific partition:
-- Add partitions to the sale_detail table
ALTER TABLE sale_detail ADD PARTITION (sale_date='2023', region='china') PARTITION (sale_date='2024', region='shanghai');
-- Insert data into the partitioned table
INSERT INTO sale_detail PARTITION (sale_date='2023', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2024', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
-- Clear data from the shop_name column
ALTER TABLE sale_detail partition(sale_date='2023', region='china') CLEAR COLUMN shop_name;For more information about limits and examples, see Clear data from columns.
View table information
Retrieve metadata for internal tables, external tables, clustered tables, or transactional tables. To query table data, see SELECT syntax.
Syntax
Retrieve basic table information:
DESC <table_name> [PARTITION (<pt_spec>)];Retrieve extended table information, including details for external tables, clustered tables, or transactional tables, such as the non-null attribute of columns:
DESC EXTENDED <table_name>;Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the table to describe. |
| pt_spec | No | The partition to describe, in the format (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). |
Examples
The data in the following examples comes from Examples.
Simple internal table
DESC test1;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2020-11-16 17:47:48 |
| LastDDLTime: 2020-11-16 17:47:48 |
| LastModifiedTime: 2020-11-16 17:47:48 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| key | string | | |
+------------------------------------------------------------------------------------+Partitioned table
DESC sale_detail;Returned result:
+--------------------------------------------------------------------+
| Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name |
| TableComment: |
+--------------------------------------------------------------------+
| CreateTime: 2017-06-28 15:05:17 |
| LastDDLTime: 2017-06-28 15:05:17 |
| LastModifiedTime: 2017-06-28 15:05:17 |
+--------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+--------------------------------------------------------------------+
| Native Columns: |
+--------------------------------------------------------------------+
| Field | Type | Label | Comment |
+--------------------------------------------------------------------+
| shop_name | string | | |
| customer_id | string | | |
| total_price | double | | |
+--------------------------------------------------------------------+
| Partition Columns: |
+--------------------------------------------------------------------+
| sale_date | string | |
| region | string | |
+--------------------------------------------------------------------+Extended table information
DESC extended sale_detail_ctas1;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2021-07-07 15:29:53 |
| LastDDLTime: 2021-07-07 15:29:53 |
| LastModifiedTime: 2021-07-07 15:29:53 |
| Lifecycle: 10 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment |
+------------------------------------------------------------------------------------+
| shop_name | string | | | true | NULL | |
| customer_id | string | | | true | NULL | |
| total_price | double | | | true | NULL | |
| sale_date | string | | | true | NULL | |
| region | string | | | true | NULL | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 98cb8a38733c49eabed4735173818147 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| StoredAs: AliOrc |
| CompressionStrategy: normal |
+------------------------------------------------------------------------------------+In this table, sale_date and region are standard columns rather than partition columns.
Table created with CTAS
DESC sale_detail_ctas2;Returned result:
+--------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name |
| TableComment: |
+--------------------------------------------------------------------+
| CreateTime: 2017-06-28 15:42:17 |
| LastDDLTime: 2017-06-28 15:42:17 |
| LastModifiedTime: 2017-06-28 15:42:17 |
+--------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+--------------------------------------------------------------------+
| Native Columns: |
+--------------------------------------------------------------------+
| Field | Type | Label | Comment |
+--------------------------------------------------------------------+
| shop_name | string | | |
| customer_id | string | | |
| total_price | double | | |
| sale_date | string | | |
| region | string | | |
+--------------------------------------------------------------------+Like sale_detail_ctas1, all columns including sale_date and region are standard columns rather than partition columns.
Table created with LIKE
DESC extended sale_detail_like;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2021-07-07 15:40:38 |
| LastDDLTime: 2021-07-07 15:40:38 |
| LastModifiedTime: 2021-07-07 15:40:38 |
| Lifecycle: 10 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment |
+------------------------------------------------------------------------------------+
| shop_name | string | | | true | NULL | |
| customer_id | string | | | true | NULL | |
| total_price | double | | | true | NULL | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| sale_date | string | |
| region | string | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 61782ff7713f426e9d6f91d5deeac99a |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| StoredAs: AliOrc |
| CompressionStrategy: normal |
+------------------------------------------------------------------------------------+Apart from the lifecycle attribute, all attributes of sale_detail_like, including field types and partition types, match those of sale_detail.
The size reported byDESC <table_name>;includes data in the recycle bin. To exclude recycle bin data, first runPURGE TABLE <table_name>;, then runDESC <table_name>;again. To view the recycle bin contents for the project, runSHOW recyclebin;.
Table with complex data types
DESC test_newtype;Returned result:
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| c1 | tinyint | | |
| c2 | smallint | | |
| c3 | int | | |
| c4 | bigint | | |
| c5 | float | | |
| c6 | double | | |
| c7 | decimal | | |
| c8 | binary | | |
| c9 | timestamp | | |
| c10 | array<map<bigint,bigint>> | | |
| c11 | map<string,array<bigint>> | | |
| c12 | struct<s1:string,s2:bigint> | | |
| c13 | varchar(20) | | |
+------------------------------------------------------------------------------------+
OKHash-clustered table
Non-partitioned hash-clustered table t1:
DESC extended t1;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2020-11-16 18:00:56 |
| LastDDLTime: 2020-11-16 18:00:56 |
| LastModifiedTime: 2020-11-16 18:00:56 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment |
+------------------------------------------------------------------------------------+
| a | string | | | true | NULL | |
| b | string | | | true | NULL | |
| c | bigint | | | true | NULL | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: e6b06f705dc34a36a5b72e5af486cab7 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| StoredAs: AliOrc |
| CompressionStrategy: normal |
| ClusterType: hash |
| BucketNum: 1024 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
OKPartitioned hash-clustered table t2:
DESC EXTENDED t2;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2017-12-25 11:18:26 |
| LastDDLTime: 2017-12-25 11:18:26 |
| LastModifiedTime: 2017-12-25 11:18:26 |
| Lifecycle: 2 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| a | string | | |
| b | string | | |
| c | bigint | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| dt | string | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 91a3395d3ef64b4d9ee1d2852755 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| ClusterType: hash |
| BucketNum: 1024 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
OKRange-clustered table
Non-partitioned range-clustered table t3:
DESC extended t3;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2020-11-16 18:01:05 |
| LastDDLTime: 2020-11-16 18:01:05 |
| LastModifiedTime: 2020-11-16 18:01:05 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment |
+------------------------------------------------------------------------------------+
| a | string | | | true | NULL | |
| b | string | | | true | NULL | |
| c | bigint | | | true | NULL | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 38d170aca2684f4baadbbe1931a6ae1f |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| StoredAs: AliOrc |
| CompressionStrategy: normal |
| ClusterType: range |
| BucketNum: 1024 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
OKPartitioned range-clustered table t4:
DESC extended t4;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2020-11-16 19:17:48 |
| LastDDLTime: 2020-11-16 19:17:48 |
| LastModifiedTime: 2020-11-16 19:17:48 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment |
+------------------------------------------------------------------------------------+
| a | string | | | true | NULL | |
| b | string | | | true | NULL | |
| c | bigint | | | true | NULL | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| dt | string | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 6ebc3432e283449188c861427bcd6ee4 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| StoredAs: AliOrc |
| CompressionStrategy: normal |
| ClusterType: range |
| BucketNum: 0 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
OKCheck whether a table is transactional
Use the MaxCompute client (odpscmd) V0.35.4 or later to check for transactional tables. Older tools may not display transactional information.
Non-partitioned transactional table t5:
DESC extended t5;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@aliyun.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2021-02-18 10:56:27 |
| LastDDLTime: 2021-02-18 10:56:27 |
| LastModifiedTime: 2021-02-18 10:56:27 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment |
+------------------------------------------------------------------------------------+
| id | bigint | | | true | NULL | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------
...
| Transactional: true |
+------------------------------------------------------------------------------------Partitioned transactional table t6:
DESC extended t6;Returned result:
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@test.aliyunid.com | Project: $project_name |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2021-02-18 15:34:54 |
| LastDDLTime: 2021-02-18 15:34:54 |
| LastModifiedTime: 2021-02-18 15:34:54 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| id | bigint | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| ds | string | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------
...
| Transactional: true |
+------------------------------------------------------------------------------------View the CREATE TABLE statement
Display the DDL statement used to create a table. This is useful for replicating the table schema.
Syntax
SHOW CREATE TABLE <table_name>;Parameters
| Parameter | Required | Description |
|---|---|---|
| table_name | Yes | The name of the table. |
Examples
SHOW CREATE TABLE sale_detail;Returned result:
CREATE TABLE IF NOT EXISTS max****.`default`.sale_detail(shop_name STRING, customer_id STRING, total_price DOUBLE)
PARTITIONED BY (sale_date STRING, region STRING) STORED AS ALIORC TBLPROPERTIES ('columnar.nested.type'='true');List tables and views in a project
List all tables, external tables, views, and materialized views in a project, or filter by name.
Syntax
List all tables and views:
SHOW TABLES;Filter tables by name using a pattern:
SHOW TABLES LIKE '<chart>';The asterisk (*) serves as a wildcard that matches any characters.
Examples
List all tables whose names start with sale:
SHOW TABLES LIKE 'sale*';Returned result:
ALIYUN$account_name:sale_detail
......In the output, ALIYUN indicates that the table was created by an Alibaba Cloud account. For tables created by a RAM user, the prefix is RAM.
List external tables in a project
List all external tables in a project, or filter by name.
The SHOW EXTERNAL TABLES command requires MaxCompute client (odpscmd) V0.43.0 or later.Syntax
List all external tables:
SHOW EXTERNAL TABLES;Filter external tables by name using a pattern:
SHOW EXTERNAL TABLES LIKE '<external_chart>';Examples
List all external tables whose names start with a:
SHOW EXTERNAL TABLES LIKE 'a*';Returned result:
ALIYUN$account_name:a_et
......In the output, ALIYUN indicates that the table was created by an Alibaba Cloud account. For tables created by a RAM user, the prefix is RAM.