Common SQL statements for table management - MaxCompute

Tables are the fundamental data storage units in MaxCompute. All development, analysis, and O&M tasks in a data warehouse revolve around table data. This document describes common table operations, such as creating, dropping, and querying tables.

Execution tools

Run the commands in this topic using the following tools:

Common operations

Table Operations

Type	Function	Role
Create Table	Creates a Non-partitioned Table, Partitioned Table, External Table, or Clustered Table.	Users with the `CreateTable` permission on a Project.
Change Table Owner	Changes the Owner of a Table.	Project Owner
Drop Table	Drops a Non-partitioned Table or Partitioned Table.	Users with the `DROP` permission on a Table.
View Table or View Information	Views information about a MaxCompute Managed Table, View, or External Table.	Users with the `Describe` permission on table metadata.
View Partition Information	Views partition information for a specific Partitioned Table.	Users with the `Describe` permission on table metadata.
List Tables and Views in a Project	Lists all tables and views in a Project, or those that match a specific rule (regular expressions are supported).	Users with the `List` permission on a Project.
List All Partitions	Lists all partitions in a Table.	Users with the `List` permission on a Project.

Partition Operations
Column Operations
Table Lifecycle Operations

Create a table

Creates a table in the current project.

Limits

Maximum partition levels: 6 (e.g., year/month/week/day/hour/minute).
Maximum partitions per table: 60,000 (default).

For a full list of limits, see MaxCompute SQL limits.

Syntax

Standard table (partitioned or non-partitioned)

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type>, ... )
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type> [COMMENT <col_comment>], ...)]
[AUTO PARTITIONED 
   BY (<auto_partition_expression> [AS <auto_partition_column_name>])
  [TBLPROPERTIES('ingestion_time_partition'='true')]
];

Clustered table

CREATE TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type>, ... )
[CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...]) 
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] 
INTO <number_of_buckets> BUCKETS];

External table (Example: OSS)

See Create an OSS external table.

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( 
  <col_name> <data_type>, ... ) 
STORED AS '<file_format>' 
[WITH SERDEPROPERTIES (options)]  
LOCATION '<oss_location>';

Transactional and Delta tables

Transactional table: Supports UPDATE/DELETE.

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type> [NOT NULL] [DEFAULT <default_value>] [COMMENT <col_comment>], ...   
[COMMENT <table_comment>]
[TBLPROPERTIES ("transactional"="true")];

Delta table: Supports upserts, incremental queries, and time travel.

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
  <col_name> <data_type> [NOT NULL] [DEFAULT <default_value>] [COMMENT <col_comment>], ...   
  [PRIMARY KEY (<pk_col_name>[, <pk_col_name2>, ...] )]) 
[COMMENT <table_comment>]
[TBLPROPERTIES ("transactional"="true" 
[, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [LIFECYCLE <days>];

CTAS and LIKE clauses

CTAS: Creates a table and copies data. Partition properties are NOT copied.

CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] AS <select_statement>;

Create Like: Copies the schema but not the data.

CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] LIKE <existing_table_name>;

Parameters

Common parameters

Parameter	Required	Description	Remarks
OR REPLACE	No	Replaces the table if it already exists.	Equivalent to dropping the table (if it exists) and then creating it. Note Cannot be used with `IF NOT EXISTS`, `AS SELECT`, or `LIKE`.
EXTERNAL	No	Creates an external table.	N/A
IF NOT EXISTS	No	Creates the table only if it does not already exist.	If the table exists, the operation is ignored.
table_name	Yes	The name of the table.	Length limit: 128 bytes. Case-insensitive. Allowed characters: letters, digits, and underscores (_).
PRIMARY KEY(pk)	No	Specifies primary key columns.	Supported only for Delta tables. Must be NOT NULL and unique.
col_name	Yes	The name of the column.	Length limit: 128 bytes. Case-insensitive.
COMMENT	No	Comment for the table or column.	Max length: 1,024 bytes.
data_type	Yes	The data type of the column.	See Data types.
NOT NULL	No	Prohibits NULL values in the column.	See Modify table properties.
DEFAULT	No	The default value used when none is specified.	Dynamic functions like `NOW()` are not supported.
LIFECYCLE	No	The data retention period in days.	A positive integer. Non-partitioned tables: Dropped if no data is modified for N days. Partitioned tables: Partitions are dropped if no data is modified for N days. The table definition remains.

Parameters for partitioned tables

Standard Partitioned Tables

Parameter	Required	Description	Remarks
PARTITIONED BY	Yes	Specifies partition columns.	Mutually exclusive with `AUTO PARTITIONED BY`.
col_name	Yes	The name of the partition column.	Length limit: 128 bytes. Case-insensitive.
data_type	Yes	The data type of the partition column.	MaxCompute V1.0: STRING only. MaxCompute V2.0: TINYINT, SMALLINT, INT, BIGINT, VARCHAR, and STRING.

AUTO PARTITION Tables

Parameter	Required	Description	Remarks
AUTO PARTITIONED BY	Yes	Specifies auto-partitioning logic.	Mutually exclusive with `PARTITIONED BY`.
Expression	Yes	Expression for partition key generation.	Currently supports `TRUNC_TIME`.

Parameters for clustered tables

Hash Clustered Tables

Parameter	Required	Description	Remarks
CLUSTERED BY	Yes	Specifies the columns used for hash clustering.	Best practice: Use high-cardinality columns or join keys.
SORTED BY	Yes	Specifies the sort order within buckets.	Should match `CLUSTERED BY` columns for optimal performance.
INTO N BUCKETS	Yes	Specifies the number of buckets.	Recommended: Powers of 2 (e.g., 512, 1024). Maximum: 4096.

Examples

Create a non-partitioned table

CREATE TABLE test1 (key STRING);

Create a partitioned table

CREATE TABLE IF NOT EXISTS sale_detail(
 shop_name     STRING,
 customer_id   STRING,
 total_price   DOUBLE)
PARTITIONED BY (sale_date STRING, region STRING);

Create a table by copying (CTAS)

-- Create table and copy data (partition properties are lost)
CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

Change table owner

Limitations

Only users with the Project Owner or Super_Administrator Role can run this command.

Syntax

ALTER TABLE <table_name> CHANGEOWNER TO <new_owner>;

Parameters

table_name: Required. The name of the table whose owner is to be changed.
new_owner: Required. The account of the new owner.
If the new owner is a Resource Access Management (RAM) user:
- The RAM user must be a member of the Project containing the table.
- Format: RAM$<UID>:<ram_name>, where UID is the Account ID of your Alibaba Cloud account and ram_name is the Display Name of the RAM user.

Examples

Change the owner of the table test1 to ALIYUN$xxx@aliyun.com.
```
ALTER TABLE test1 CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';
```
Change the owner of the table test1 to the RAM user named ram_test.
```
ALTER TABLE test1 CHANGEOWNER TO 'RAM$13xxxxxxxxxxx:ram_test';
```

Drop a table

Drops a table and deletes its data. This operation is irreversible.

Precautions

Warning: Dropped tables cannot be recovered unless the backup and restore feature is enabled.

Syntax

DROP TABLE [IF EXISTS] <table_name>;

Examples

DROP TABLE IF EXISTS sale_detail;

Tables or views

Displays information about Managed Tables, Views, Materialized Views, External Tables, Clustered Tables, or Transactional Tables in MaxCompute.

Syntax

-- View information about an internal table or a view.
DESC <table_name|view_name> [PARTITION (<pt_spec>)]; 
-- View information about a materialized view, foreign table, clustered table, or transactional table. You can also use this command to view extended information about an internal table.
DESC extended <table_name|mv_name>;

Parameters

Parameter Name	Description
table_name	Required. The name of the table that you want to view.
view_name	Required. The name of the view that you want to view.
mv_name	The name of the materialized view that you want to query.
pt_spec	Optional. The partition that you want to view in a partitioned table. The format is `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`.
extended	Include this parameter to display extended information for a materialized view, foreign table, clustered table, or transactional table. You can also use this parameter to view extended information for an internal table, such as the non-null property of a column.

Return values

Parameter name	Description
Owner	The account of the table or view owner.
Project	The project to which the table or view belongs.
TableComment	The comment of the table or view.
CreateTime	The time when the table or view was created.
LastDDLTime	The time when the Data Definition Language (DDL) of the table or view was last modified.
LastModifiedTime	The time when the data in the table or view was last modified.
LastAccessTime	The time when the data in the table or view was last accessed. This time is for reference only. A difference of up to 24 hours can exist between this value and the actual last access time. If data is frequently accessed, the LastAccessTime value is not updated if it has been updated in the last 24 hours. This reduces the performance overhead caused by frequent updates.
Lifecycle	The lifecycle in days.
InternalTable	Indicates whether the object is an internal table. This parameter is returned only for table objects.
VirtualView	Indicates whether the object is a view. This parameter is returned only for view objects.
Size	The table size in bytes.
NativeColumns	The column information of the table or view.
PartitionColumns	The partition key column information. This parameter is returned only for partitioned tables.

Examples

Note

The size returned by DESC table_name includes data in the recycle bin.
To exclude data in the recycle bin from the size calculation, first run PURGE TABLE table_name. Then, run DESC table_name.
Run SHOW recyclebin to view details of data in the recycle bin for the current project.

Example 1: View a non-partitioned table

Create a test table.

CREATE TABLE test_table (
    key STRING
);

View information about the test_table table.
```
DESC test_table;
```

Returned result.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 15:04:49                                      |
| LastDDLTime:              2025-12-15 15:04:50                                      |
| LastModifiedTime:         2025-12-15 15:04:49                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| key             | string     |       |                                             |
+------------------------------------------------------------------------------------+

Example 2: View a partitioned table

Create a test table.

CREATE TABLE test_table_partition (
    shop_name       STRING,
    customer_id     STRING,
    total_price     DOUBLE
)
PARTITIONED BY (
    sale_date       STRING,
    region          STRING
);

View information about the created table.
```
DESC test_table_partition;
```

Returned result.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 15:08:27                                      |
| LastDDLTime:              2025-12-15 15:08:27                                      |
| LastModifiedTime:         2025-12-15 15:08:27                                      |
+------------------------------------------------------------------------------------+
| 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     |                                                     |
+------------------------------------------------------------------------------------+

Example 3: View detailed information of a partitioned table

Create a test table.

CREATE TABLE IF NOT EXISTS test_table_partition (
    shop_name       STRING,
    customer_id     STRING,
    total_price     DOUBLE
)
PARTITIONED BY (
    sale_date       STRING,
    region          STRING
);

View information about the created table.
```
DESC EXTENDED test_table_partition;
```

Returned result.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 15:08:27                                      |
| LastDDLTime:              2025-12-15 15:08:27                                      |
| LastModifiedTime:         2025-12-15 15:08:27                                      |
+------------------------------------------------------------------------------------+
| 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:                  8c4d6ed34c964326b45d0435a3babe45                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                        |
| encryption_enable:        false                                                    |
+------------------------------------------------------------------------------------+

Example 4: View a table with a lifecycle

Create a test table.

CREATE TABLE sale_detail_ctas(
    shop_name       STRING,
    customer_id     STRING,
    total_price     DOUBLE,
    sale_date       STRING,
    region          STRING
)
LIFECYCLE 10;

View detailed information about the created table.
```
DESC EXTENDED sale_detail_ctas;
```

Returned result.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 15:13:22                                      |
| LastDDLTime:              2025-12-15 15:13:22                                      |
| LastModifiedTime:         2025-12-15 15:13:22                                      |
| 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:                  8271334ac9724d09a4973b5b3d536f4c                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                        |
| ColdStorageStatus:        N/A                                                      |
| encryption_enable:        false                                                    |
| StorageTier:              Standard                                                 |
| StorageTierLastModifiedTime:  2025-12-15 15:13:22                                  |
+------------------------------------------------------------------------------------+

Example 5: View a table with various data types

Create a test table.

CREATE TABLE  test_newtype(
    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)
);

View detailed information about the created table.
```
DESC test_newtype;
```

Returned result.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:09:18                                      |
| LastDDLTime:              2025-12-15 16:09:18                                      |
| LastModifiedTime:         2025-12-15 16:09:18                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| c1              | tinyint    |       |                                             |
| c2              | smallint   |       |                                             |
| c3              | int        |       |                                             |
| c4              | bigint     |       |                                             |
| c5              | float      |       |                                             |
| c6              | double     |       |                                             |
| c7              | decimal(38,18) |   |                                             |
| c8              | binary     |       |                                             |
| c9              | timestamp  |       |                                             |
| c10             | array<map<bigint,bigint>> |       |                              |
| c11             | map<string,array<bigint>> |       |                              |
| c12             | struct<s1:string,s2:bigint> |       |                            |
| c13             | varchar(20) |       |                                            |
+------------------------------------------------------------------------------------+

Example 6: View a non-partitioned hash-clustered table

Create a test table.

CREATE TABLE hash_clustered_nonpar (
    a               STRING,
    b               STRING,
    c               BIGINT
)
CLUSTERED BY (c)
SORTED BY (c ASC)
INTO 1024 BUCKETS;

View detailed information about the created table.
```
DESC EXTENDED hash_clustered_nonpar;
```

Returned result.

Cluster properties appear in the Extended Info section.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:18:07                                      |
| LastDDLTime:              2025-12-15 16:18:07                                      |
| LastModifiedTime:         2025-12-15 16:18:07                                      |
+------------------------------------------------------------------------------------+
| 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:                  904e6a0d76624346903d59a2b536d0a3                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                 |
| ColdStorageStatus:        N/A                                                      |
| encryption_enable:        false                                                    |
| ClusterType:              hash                                                     |
| BucketNum:                1024                                                     |
| ClusterColumns:           [c]                                                      |
| SortColumns:              [c ASC]                                                  |
| StorageTier:              Standard                                                 |
| StorageTierLastModifiedTime:  2025-12-15 16:18:07                                  |
+------------------------------------------------------------------------------------+

Example 7: View a partitioned hash-clustered table

Create a test table.

CREATE TABLE hash_clustered_par (
    a               STRING,
    b               STRING,
    c               BIGINT
)
PARTITIONED BY (
    dt              STRING
)
CLUSTERED BY (c)
SORTED BY (c ASC)
INTO 1024 BUCKETS
LIFECYCLE 2;

View detailed information about the created table.
```
DESC EXTENDED hash_clustered_par;
```

Returned result.

Cluster properties appear in the Extended Info section.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:26:24                                      |
| LastDDLTime:              2025-12-15 16:26:24                                      |
| LastModifiedTime:         2025-12-15 16:26:24                                      |
| Lifecycle:                2                                                        |
+------------------------------------------------------------------------------------+
| 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:                  5680f0711add43928389db3655d9183e                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                 |
| encryption_enable:        false                                                    |
| ClusterType:              hash                                                     |
| BucketNum:                1024                                                     |
| ClusterColumns:           [c]                                                      |
| SortColumns:              [c ASC]                                                  |
+------------------------------------------------------------------------------------+

Example 8: View a non-partitioned range-clustered table

Create a test table.

CREATE TABLE range_clustered_nonpar (
    a               STRING,
    b               STRING,
    c               BIGINT
)
RANGE CLUSTERED BY (c);

View detailed information about the created table.
```
DESC EXTENDED range_clustered_nonpar;
```

Returned result.

Cluster properties appear in the Extended Info section.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:30:45                                      |
| LastDDLTime:              2025-12-15 16:30:45                                      |
| LastModifiedTime:         2025-12-15 16:30:45                                      |
+------------------------------------------------------------------------------------+
| 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:                  bf01d946c4b24c0e9c54ccfe8750b7c2                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                 |
| ColdStorageStatus:        N/A                                                      |
| encryption_enable:        false                                                    |
| ClusterType:              range                                                    |
| BucketNum:                0                                                        |
| ClusterColumns:           [c]                                                      |
| StorageTier:              Standard                                                 |
| StorageTierLastModifiedTime:  2025-12-15 16:30:45                                  |
+------------------------------------------------------------------------------------+

Example 9: View a partitioned range-clustered table

Create a test table.

CREATE TABLE range_clustered_par (
    a               STRING,
    b               STRING,
    c               BIGINT
)
PARTITIONED BY (
    dt              STRING
)
RANGE CLUSTERED BY (c);

View detailed information about the created table.
```
DESC EXTENDED range_clustered_par;
```

Returned result.

Cluster properties appear in the Extended Info section.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:33:09                                      |
| LastDDLTime:              2025-12-15 16:33:09                                      |
| LastModifiedTime:         2025-12-15 16:33:09                                      |
+------------------------------------------------------------------------------------+
| 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:                  bdc4f6897691479ea9c315664f26fe39                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                 |
| encryption_enable:        false                                                    |
| ClusterType:              range                                                    |
| BucketNum:                0                                                        |
| ClusterColumns:           [c]                                                      |
+------------------------------------------------------------------------------------+

Example 10: Check if a non-partitioned table is transactional

Note

Use the MaxCompute client version 0.35.4 or later to check whether a table is a transactional table.

Other tools may not support this feature if they are not upgraded to the required version, and the query result may omit transactional information.

Create a test table.

CREATE TABLE tran_nonpar (
    id              BIGINT
)
TBLPROPERTIES ('transactional'='true');

View detailed information about the created table.
```
DESC EXTENDED tran_nonpar;
```

Returned result.

The Transactional property appears in the Extended Info section.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:37:27                                      |
| LastDDLTime:              2025-12-15 16:37:27                                      |
| LastModifiedTime:         2025-12-15 16:37:27                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| id       | bigint |       |               | true     | NULL         |              |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  43e9710c2b4c404780a7be9998afb23e                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| Transactional:            true                                                     |
| IsolationMin:             NONSTRICT_SNAPSHOT_ISOLATION                             |
| odps.timemachine.retention.days: 1                                                 |
| ColdStorageStatus:        N/A                                                      |
| encryption_enable:        false                                                    |
| StorageTier:              Standard                                                 |
| StorageTierLastModifiedTime:  2025-12-15 16:37:27                                  |
+------------------------------------------------------------------------------------+

Example 11: Check if a partitioned table is transactional

Note

Use the MaxCompute client version 0.35.4 or later to check whether a table is a transactional table.

Other tools may not support this feature if they are not upgraded to the required version, and the query result may omit transactional information.

Create a test table.

CREATE TABLE tran_par (
    id              BIGINT
)
PARTITIONED BY (
    ds              STRING
)
TBLPROPERTIES ('transactional'='true');

View detailed information about the created table.
```
DESC EXTENDED tran_par;
```

Returned result.

The Transactional property appears in the Extended Info section.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:42:26                                      |
| LastDDLTime:              2025-12-15 16:42:26                                      |
| LastModifiedTime:         2025-12-15 16:42:26                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| id       | bigint |       |               | true     | NULL         |              |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| ds              | string     |                                                     |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  d4dd59b15f7940bcad4cb5efdb42f242                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| Transactional:            true                                                     |
| IsolationMin:             NONSTRICT_SNAPSHOT_ISOLATION                             |
| odps.timemachine.retention.days: 1                                                 |
| encryption_enable:        false                                                    |
+------------------------------------------------------------------------------------+

Example 12: View the materialized view mv

Create a test table.

-- Create a test base table.
CREATE TABLE page_view_logs (
    page_id STRING,
    user_id STRING,
    view_timestamp BIGINT
);

-- Create a materialized view to calculate the page views (PV) of each page.
CREATE MATERIALIZED VIEW mv AS
SELECT
    page_id,
    COUNT(1) AS pv_count
FROM
    page_view_logs
GROUP BY
    page_id;

View detailed information about the materialized view.
```
DESC EXTENDED mv;
```

Returned result.

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***_com                                           |
| Project:                  testproject                                              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:47:51                                      |
| LastDDLTime:              2025-12-15 16:47:51                                      |
| LastModifiedTime:         2025-12-15 16:47:51                                      |
+------------------------------------------------------------------------------------+
| MaterializedView: YES                                                              |
| ViewText: SELECT
    page_id,
    COUNT(1) AS pv_count
FROM
    page_view_logs
GROUP BY
    page_id |
| Rewrite Enabled: true                                                              |
| AutoRefresh Enabled: false                                                         |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| page_id  | string |       |               | true     | NULL         |              |
| pv_count | bigint |       |               | true     | NULL         |              |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| IsOutdated:               false                                                    |
| TableID:                  a8742f3751904ec3ade23a7ecc2a2b0b                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 CFile                                                    |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                        |
| ColdStorageStatus:        N/A                                                      |
| encryption_enable:        false                                                    |
| StorageTier:              Standard                                                 |
| StorageTierLastModifiedTime:  2025-12-15 16:47:51                                  |
+------------------------------------------------------------------------------------+

Example 13: View partition information

Create a test table.

-- Create a test table.
CREATE TABLE IF NOT EXISTS test_table_partition (
    shop_name       STRING,
    customer_id     STRING,
    total_price     DOUBLE
)
PARTITIONED BY (
    sale_date       STRING,
    region          STRING
);

-- Create a partition.
ALTER TABLE test_table_partition ADD IF NOT EXISTS 
  PARTITION (sale_date='201310', region='beijing');
  
-- Use INSERT INTO to append data to the specified partition.
INSERT INTO TABLE test_table_partition PARTITION (sale_date='201310', region='beijing')
VALUES
    ('Apple Store', 'user001', 8888.0),
    ('Nike Store', 'user002', 1200.5),
    ('Starbucks', 'user001', 45.0);

Query partition information for the partitioned table.

DESC test_table_partition PARTITION (sale_date='201310', region='beijing');

Returned result.

+------------------------------------------------------------------------------------+
| PartitionSize: 1163                                                                |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-12-15 16:54:16                                      |
| LastDDLTime:              2025-12-15 16:54:16                                      |
| LastModifiedTime:         2025-12-15 16:54:23                                      |
+------------------------------------------------------------------------------------+

View partition information

Views the partition information about a partitioned table.

Syntax

desc <table_name> partition (<pt_spec>);

Parameters
- table_name: required. The name of the partitioned table whose partition information you want to view.
- pt_spec: required. The information about the partition that you want to view. The value of this parameter is in the partition_col1=col1_value1, partition_col2=col2_value1... format. If a table has multi-level partitions, you must specify the values of all the partition key columns.

Examples

-- Query information about the partitioned table sale_detail. 
desc sale_detail partition (sale_date='201310',region='beijing');

The following result is returned:

+------------------------------------------------------------------------------------+
| PartitionSize: 2109112                                                             |
+------------------------------------------------------------------------------------+
| CreateTime:               2015-10-10 08:48:48                                      |
| LastDDLTime:              2015-10-10 08:48:48                                      |
| LastModifiedTime:         2015-10-11 01:33:35                                      |
+------------------------------------------------------------------------------------+
OK

Display tables and views in a project

Displays all the tables and views or the tables and views that meet specific rules in a project.

Syntax

-- Display all tables and views in a project. 
show tables;
-- Display the tables or views whose names contain the chart keyword in a project. 
show tables like '<chart>';

Examples

-- Display the tables whose names contain the sale* keyword in a project. The asterisk (*) indicates any character. 
show tables like 'sale*';

The following result is returned:

ALIYUN$account_name:sale_detail
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If the table was created by a RAM user, the system prompt is RAM.

Display partitions

Displays all the partitions of a table. If the table does not exist or the table is a non-partitioned table, an error is returned.

Syntax
```
show partitions <table_name>; 
```
Parameters
table_name: required. The name of the partitioned table whose partition information you want to view.

Examples

-- Display all the partitions of the sale_detail table. 
show partitions sale_detail;

The following result is returned:

sale_date=201310/region=beijing
sale_date=201312/region=shenzhen
sale_date=201312/region=xian
sale_date=2014/region=shenzhen

OK

Execution tools

Common operations

Create a table

Limits

Syntax

Parameters

Common parameters

Parameters for partitioned tables

Standard Partitioned Tables

AUTO PARTITION Tables

Parameters for clustered tables

Hash Clustered Tables

Examples

Create a non-partitioned table

Create a partitioned table

Create a table by copying (CTAS)

Change table owner

Limitations

Syntax

Parameters

Examples

Drop a table

Precautions

Syntax

Examples

Tables or views

Syntax

Parameters

Return values

Examples

Example 1: View a non-partitioned table

Example 2: View a partitioned table

Example 3: View detailed information of a partitioned table

Example 4: View a table with a lifecycle

Example 5: View a table with various data types

Example 6: View a non-partitioned hash-clustered table

Example 7: View a partitioned hash-clustered table

Example 8: View a non-partitioned range-clustered table

Example 9: View a partitioned range-clustered table

Example 10: Check if a non-partitioned table is transactional

Example 11: Check if a partitioned table is transactional

Example 12: View the materialized view mv

Example 13: View partition information

View partition information

Display tables and views in a project

Display partitions

Related documentation