This topic describes how to use SQL statements to modify and view tables.
Commands
Command category | Type | Description | Role | Operation platform |
Modify Table | Change the owner of a table. | Project owner | The commands in this topic can be executed using the following platforms: | |
Modify the comment of a table. | Users with the ALTER permission on tables | |||
Change the | ||||
Add or remove clustering attributes to or from a table. | ||||
Change the name of a table. | ||||
Clear data from a specified non-partitioned table. | ||||
Clear columns of a standard table. | ||||
View Table | View information about internal tables, foreign tables, clustered tables, or transactional tables in MaxCompute. | Users with the DESCRIBE permission to read table metadata | ||
View the DDL statement of a table. | ||||
List all internal and foreign tables in a project, or tables that meet specific criteria. | Users with the LIST permission on project objects | |||
List all foreign tables in a project, or ones that meet specific criteria. |
Change the owner of a table
Only the project owner or users with the Super_Administrator role can execute a statement to change the owner of a table.
Syntax
ALTER TABLE <table_name> CHANGEOWNER TO <new_owner>;Parameters
Parameter | Required | Description |
table_name | Yes | The name of the table whose owner you want to modify. |
new_owner | Yes | The new owner of the table. If you want to set the owner to a RAM user, use the format Note To set the table owner to a RAM user, ensure the RAM user has been added to the project where the table resides. |
Usage examples
The sample data in this topic is created in the sale_detail table. For more information, see Create a partitioned table.
Change the owner of the sale_detail table to ALIYUN$xxx@aliyun.com.
ALTER TABLE sale_detail CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';Change the owner of the sale_detail table to a RAM user named
ram_test.ALTER TABLE sale_detail CHANGEOWNER TO 'RAM$13xxxxxxxxxxx:ram_test';
Modify the comment of a table
Syntax
ALTER TABLE <table_name> SET COMMENT '<new_comment>';Parameters
Parameter | Required | Description |
table_name | Yes | The name of the table whose comment you want to modify. |
new_comment | Yes | The new comment name. |
Usage examples
ALTER TABLE sale_detail SET COMMENT 'new comment for table sale_detail';You can use the DESC <table_name> command in MaxCompute to view the updated comment for the table.
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$sant****.aliyunid.com |
| Project: ani**** |
| Schema: default |
| TableComment: new comment for table sale_detail |
+------------------------------------------------------------------------------------+Modify the last update time of a table
MaxCompute SQL provides the TOUCH operation to modify the LastModifiedTime of a table. You can change the LastModifiedTime of the table to the current time. MaxCompute will consider that the data in the table has changed, and the calculation of the lifecycle will restart.
Syntax
ALTER TABLE <table_name> TOUCH;Parameters
table_name: Required. The name of the table whose LastModifiedTime you want to modify.
Usage examples
ALTER TABLE sale_detail TOUCH;Modify the clustering attributes of a table
For partitioned tables, MaxCompute supports adding or removing clustering attributes by using the ALTER TABLE statement.
Syntax
Add hash clustering attributes to a table.
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 from a table.
ALTER TABLE <table_name> NOT CLUSTERED;Add range clustering attributes to a table. The INTO <number_of_buckets> BUCKETS clause is optional. If you do not specify the number of buckets, MaxCompute automatically determines the optimal number based on the data volume.
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];Remove range clustering attributes from a table or partition.
ALTER TABLE <table_name> NOT CLUSTERED; ALTER TABLE <table_name> <pt_spec> NOT CLUSTERED;NoteThe
ALTER TABLEstatement is only valid for partitioned tables. Once clustering attributes are set for non-partitioned tables, they cannot be changed. TheALTER TABLEstatement applies to existing tables. After adding new clustering attributes, new partitions will be stored based on these attributes.
The
ALTER TABLEstatement affects only new partitions of a partitioned table, including those generated byINSERT OVERWRITE. New partitions are stored based on the new clustering attributes, while the storage of old data partitions remains unchanged. Specifically, if you want to configure different clustering columns, sort columns, and numbers of buckets for new partitions on a table with existing clustering attributes, you should first remove the existing clustering attributes, then add the new ones.
Because the
ALTER TABLEstatement affects only new partitions, you cannot specify a partition in the statement.
Parameters
For more information, see Create and drop tables.
Rename a table
Changes only the name of a table. The data remains unaffected.
Syntax
ALTER TABLE <table_name> RENAME TO <new_table_name>;Parameters
Parameter | Required | Description |
table_name | Yes | The table whose name you want to modify. |
new_table_name | Yes | The new name for the table. If a table with the same name as new_table_name already exists, an error will be returned. |
Usage examples
ALTER TABLE sale_detail RENAME TO sale_detail_rename;View table information
Retrieve information about internal, external, clustered, or transactional tables in MaxCompute. For more information about a table, see SELECT syntax.
Syntax
Retrieve table information.
DESC <table_name> [PARTITION (<pt_spec>)];Retrieve information about external, clustered, or transactional tables, or extended information about an internal table, such as the non-null attribute of columns.
-- Retrieve the information about an external table, a clustered table, or a transactional table. You can also execute this statement to Retrieve the extended information about an internal table. DESC EXTENDED <table_name>;
Parameters
Parameter | Required | Description |
table_name | Yes | The name of the table to view. |
pt_spec | No | The specified partition of the partitioned table to view, formatted as |
Usage examples
The data in the following examples is from Examples.
Retrieve information about the test1 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 | | | +------------------------------------------------------------------------------------+Retrieve information about the sale_detail 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 | | +--------------------------------------------------------------------+Retrieve detailed information about the sale_detail_ctas1 table.
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 | +------------------------------------------------------------------------------------+The
sale_dateandregionfields serve as standard columns rather than partitions within the table.Retrieve information about the sale_detail_ctas2 table.
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 | | | +--------------------------------------------------------------------Retrieve detailed information about the sale_detail_like table.
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 other attributes of
sale_detail_like, including field and partition types, match those ofsale_detailprecisely.NoteThe size indicated by
DESC <table_name>;includes the data within the recycle bin. To empty the recycle bin, first executePURGE TABLE <table_name>;. Afterwards, runDESC <table_name>;again to check the data size without the recycle bin contents. To see the contents of the recycle bin for the project, useSHOW recyclebin;.Retrieve information about the test_newtype table.
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) | | | +------------------------------------------------------------------------------------ OKRetrieve information about the hash-clustered table t1, with clustering attributes in Extended Info.
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] | +------------------------------------------------------------------------------------ OKRetrieve information about the hash-clustered partitioned table t2, with clustering attributes in Extended Info.
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] | +------------------------------------------------------------------------------------ OKRetrieve information about the range-clustered table t3, with clustering attributes in Extended Info.
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] | +------------------------------------------------------------------------------------ OKRetrieve information about the range-clustered partitioned table t4, with clustering attributes in Extended Info.
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] | +------------------------------------------------------------------------------------ OKVerify if the non-partitioned table t5 is transactional.
NoteWe recommend that you use the MaxCompute client (odpscmd) to check for transactional tables. Ensure the client is updated to V0.35.4 or later, as older tools may not display transactional information.
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 | +------------------------------------------------------------------------------------Verify if the partitioned table t6 is transactional.
NoteWe recommend that you use the MaxCompute client (odpscmd) to check for transactional tables. Ensure the client is updated to V0.35.4 or later, as older tools may not display transactional information.
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
This feature displays the SQL statement used to create a table, enabling you to replicate the table's schema.
Syntax
SHOW CREATE TABLE <table_name>;Parameters
table_name: Required. Specifies the name of the table whose CREATE TABLE statement you want to view.
Usage examples
-- View the CREATE TABLE statement of the sale_detail table.
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
Retrieve a list of all tables, external tables, views, and materialized views within a project, or those that meet specific criteria.
Syntax
-- List all tables and views in a project.
SHOW TABLES;
-- List the tables or views whose names contain the chart keyword in a project.
SHOW TABLES LIKE '<chart>';Usage examples
-- List the tables whose names contain the sale* keyword in a project. The asterisk (*) indicates any character.
SHOW TABLES LIKE 'sale*'; Returned result:
ALIYUN$account_name:sale_detail
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If you are a RAM user, the system prompt is RAM.List external tables in a project
Retrieve a list of all external tables within a project, or those that meet specific criteria.
To use the SHOW EXTERNAL TABLES command, ensure you are operating on MaxCompute client (odpscmd) V0.43.0 or higher.
Syntax
-- List all external tables in a project.
SHOW EXTERNAL TABLES;
-- View the external tables whose names match the condition specified by external_chart in a project.
SHOW EXTERNAL TABLES LIKE '<external_chart>';Usage examples
-- View the external tables whose names match the condition specified by a* in a project. The asterisk (*) indicates any character.
SHOW EXTERNAL TABLES LIKE 'a*'; Returned result:
ALIYUN$account_name:a_et
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If you are a RAM user, the system prompt is RAM.Clear data from a non-partitioned table
Clears data from a specified non-partitioned table. To clear data from a single partition or multiple partitions of a partitioned table, see Clear data from a partition.
Syntax
TRUNCATE TABLE <table_name>;Parameters
table_name: Required. The name of the non-partitioned table you want to clear.
Clear column data
The clear column command is used to remove data from specific columns in a standard table. By deleting unused column data from the disk and setting the columns to NULL, storage costs can be reduced.
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 from which you want to clear column data. |
column | Yes | The name of the column you want to clear. |
partition | No | Specifies a partition. If not specified, the operation affects all partitions. |
pt_spec | No | The partition specification, in the format |
without touch | No | Specifies that |
Usage examples
-- 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 the limits and examples of clearing column data, see Clear data from columns.
References
For more information on creating and deleting tables, see Create and drop tables.
For more information on managing partitions in existing tables, see Partition operations.
For more information on managing columns in existing tables, see Column operations.
For more information on updating or deleting data in transactional tables, see Update or delete data (UPDATE | DELETE).