Use SQL statements to modify table properties, rename tables, clear data, and view table metadata in MaxCompute.
The following table lists the commands for modifying and viewing tables.
Change the table owner
Changes the owner of a table to a different Alibaba Cloud or RAM user account.
Important Only a project owner or a user with the Super_Administrator role can run this command.
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's account. To change the owner to a RAM user, use the format: RAM$<UID>:<ram_name>, where UID is the account ID of the Alibaba Cloud account, and ram_name is the display name of the RAM user.
Note To change the table owner to a RAM user, ensure the RAM user is added to the project that contains the table. |
Examples
The examples in this section use the sale_detail table. To create it, see Create the sale_detail 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 the RAM user named ram_test.
ALTER TABLE sale_detail CHANGEOWNER TO 'RAM$13xxxxxxxxxxx:ram_test';
Modify a table comment
Modifies a table's comment.
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
ALTER TABLE sale_detail SET COMMENT 'new comment for table sale_detail';
Run DESC <table_name> to verify the updated comment.
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$sant****.aliyunid.com |
| Project: ani**** |
| Schema: default |
| TableComment: new comment for table sale_detail |
+------------------------------------------------------------------------------------+
Update a table's last modified time
The TOUCH operation sets a table's LastModifiedTime to the current time. This changes the table's LastModifiedTime, so MaxCompute considers the table data to be modified and the LastModifiedTime lifecycle calculation restarts.
Syntax
ALTER TABLE <table_name> TOUCH;
Parameters
table_name: Required. The name of the table to update.
Examples
ALTER TABLE sale_detail TOUCH;
Modify table cluster properties
Use ALTER TABLE to add or remove cluster properties on partitioned tables.
Syntax
Add a hash cluster property 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 the hash cluster property from a table:
ALTER TABLE <table_name> NOT CLUSTERED;
Add a range cluster property to a table. The number of buckets is optional. If omitted, the system determines the optimal count based on 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 the range cluster property from a table or partition:
ALTER TABLE <table_name> NOT CLUSTERED;
ALTER TABLE <table_name> PARTITION <pt_spec> NOT CLUSTERED;
Note ALTER TABLE changes clustering properties for partitioned tables only. For non-partitioned tables, clustering cannot be changed after creation. The ALTER TABLE statement applies to existing tables. After a new clustering property is added, new partitions are stored with the specified clustering.
ALTER TABLE affects only new partitions, including those generated by INSERT OVERWRITE. Existing partitions remain unchanged. You can disable and re-enable clustering with different configurations for new partitions.
ALTER TABLE affects only new partitions. You cannot specify a particular partition when adding or modifying a cluster property.
Rename a table
Renames a table without modifying data.
Syntax
ALTER TABLE <table_name> RENAME TO <new_table_name>;
Parameters
Parameter | Required | Description |
table_name | Yes | The name of the table to rename. |
new_table_name | Yes | The new table name. An error occurs if a table named new_table_name already exists. |
Examples
ALTER TABLE sale_detail RENAME TO sale_detail_rename;
Clear a non-partitioned table
Removes all data from a non-partitioned table. To clear partition data, see Clear partition data.
Command format
TRUNCATE TABLE <table_name>;
Parameters
table_name: Required. The name of the non-partitioned table.
Clear column data
The clear column command clears data from columns in a regular table by setting values to NULL. This removes unused data from disk 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 containing the columns to clear. |
column | Yes | The name of one or more columns to clear. |
partition | No | The partition to clear. If omitted, the operation applies to 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, the LastDataModifiedTime is not updated. By default, the LastDataModifiedTime is updated. |
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 the shop_name column
ALTER TABLE sale_detail partition(sale_date='2023', region='china') CLEAR COLUMN shop_name;
Clear column data for limitations and additional examples.
Table information
View information about internal, external, clustered, or transactional tables. To view table data, use SELECT syntax.
Command syntax
View basic table information:
DESC <table_name> [PARTITION (<pt_spec>)];
View extended information, including column nullability, for external, clustered, transactional, or internal tables:
-- View extended information for external, clustered, transactional, or internal tables.
DESC EXTENDED <table_name>;
Parameters
Parameter | Required | Description |
table_name | Yes | The name of the table to view. |
pt_spec | No | The partition to view in a partitioned table. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). |
Examples
View information about the test1 table.
DESC test1;
Click to expand and view execution 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 | | |
+------------------------------------------------------------------------------------+
View information about the sale_detail table.
DESC sale_detail;
Click to expand and view execution 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 | |
+--------------------------------------------------------------------+
View detailed information about the sale_detail_ctas1 table.
DESC extended sale_detail_ctas1;
Click to expand and view execution 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 |
+------------------------------------------------------------------------------------+
sale_date and region are regular columns, not partition columns.
View information about the sale_detail_ctas2 table.
DESC sale_detail_ctas2;
Click to expand and view execution 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 | | |
+--------------------------------------------------------------------+
View detailed information about the sale_detail_like table.
DESC extended sale_detail_like;
Click to expand and view execution 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 |
+------------------------------------------------------------------------------------+
sale_detail_like has the same columns and partitions as sale_detail but a different lifecycle.
Note The Size value from DESC <table_name>; includes recycle bin data. To exclude it, first run PURGE TABLE <table_name>;, then run DESC <table_name>; again. Run SHOW recyclebin; to view recycle bin details.
View information about the test_newtype table.
DESC test_newtype;
Click to expand and view execution 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) | | |
+------------------------------------------------------------------------------------+
OK
View information about the hash-clustered, non-partitioned table t1. The cluster properties appear in the Extended Info section.
DESC extended t1;
Click to expand and view execution 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] |
+------------------------------------------------------------------------------------+
OK
View information about the hash-clustered, partitioned table t2. The cluster properties appear in the Extended Info section.
DESC EXTENDED t2;
Click to expand and view execution 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] |
+------------------------------------------------------------------------------------+
OK
View information about the range-clustered, non-partitioned table t3. The cluster properties appear in the Extended Info section.
DESC extended t3;
Click to expand and view execution 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] |
+------------------------------------------------------------------------------------+
OK
View information about the range-clustered, partitioned table t4. The cluster properties appear in the Extended Info section.
DESC extended t4;
Click to expand and view execution 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] |
+------------------------------------------------------------------------------------+
OK
Determine if the non-partitioned table t5 is a transactional table.
Note Use the MaxCompute client (version 0.35.4 or later) to check if a table is transactional. Other tools or older client versions may not show this information.
DESC extended t5;
Click to expand and view execution 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 |
+------------------------------------------------------------------------------------+
Determine if the partitioned table t6 is a transactional table.
Note Use the MaxCompute client (version 0.35.4 or later) to check if a table is transactional. Other tools or older client versions may not show this information.
DESC extended t6;
Click to expand and view execution 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 |
+------------------------------------------------------------------------------------+
Create table statement
Returns the DDL statement used to create a table.
Syntax
SHOW CREATE TABLE <table_name>;
Parameters
table_name: Required. The name of the target table.
Examples
-- View the CREATE TABLE statement for the sale_detail table.
SHOW CREATE TABLE sale_detail;
Sample output:
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 project tables and views
Lists all tables, external tables, views, and materialized views in a project, or filters by name pattern.
Command format
-- List all tables and views in the project.
SHOW TABLES;
-- List tables and views whose names match the specified pattern.
SHOW TABLES LIKE '<pattern>';
Examples
-- List tables whose names match the 'sale*' pattern. The asterisk (*) is a wildcard for any sequence of characters.
SHOW TABLES LIKE 'sale*';
Sample output:
ALIYUN$account_name:sale_detail
......
-- The system prompt is ALIYUN for an Alibaba Cloud account and RAM for a RAM user.
List external tables
Lists all external tables in a project, or filters by name pattern.
Note SHOW EXTERNAL TABLES requires MaxCompute client (odpscmd) version 0.43.0 or later.
Syntax
-- List all external tables in the project.
SHOW EXTERNAL TABLES;
-- List external tables that match a specified pattern.
SHOW EXTERNAL TABLES LIKE '<pattern>';
Examples
-- List external tables that match the 'a*' pattern. The asterisk (*) is a wildcard for any sequence of characters.
SHOW EXTERNAL TABLES LIKE 'a*';
Sample output:
ALIYUN$account_name:a_et
......
-- The system prompt is ALIYUN for an Alibaba Cloud account and RAM for a RAM user.