All Products
Search
Document Center

MaxCompute:Modify and view tables

Last Updated:Jan 02, 2025

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

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

Modify the comment of a table.

Users with the ALTER permission on tables

Modify the last update time of a table

Change the LastModifiedTime of a table to the current time.

Modify the clustering attributes of a table

Add or remove clustering attributes to or from a table.

Rename a table

Change the name of a table.

Clear data from a non-partitioned table

Clear data from a specified non-partitioned table.

Clear column data

Clear columns of a standard table.

View Table

View table information

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 CREATE TABLE statement

View the DDL statement of a table.

List tables and views in a project

List all internal and foreign tables in a project, or tables that meet specific criteria.

Users with the LIST permission on project objects

List external tables in a project

List all foreign tables in a project, or ones that meet specific criteria.

Change the owner of a table

Important

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 RAM$<UID>:<ram_name>, where UID is the Alibaba Cloud account ID, and ram_name is the RAM user's display name.

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;
    Note
    • The ALTER TABLE statement is only valid for partitioned tables. Once clustering attributes are set for non-partitioned tables, they cannot be changed. The ALTER TABLE statement applies to existing tables. After adding new clustering attributes, new partitions will be stored based on these attributes.

    • The ALTER TABLE statement affects only new partitions of a partitioned table, including those generated by INSERT 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 TABLE statement 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 (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...).

Usage examples

Note

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_date and region fields 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 of sale_detail precisely.

    Note

    The size indicated by DESC <table_name>; includes the data within the recycle bin. To empty the recycle bin, first execute PURGE TABLE <table_name>;. Afterwards, run DESC <table_name>; again to check the data size without the recycle bin contents. To see the contents of the recycle bin for the project, use SHOW 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) |       |                                            |
    +------------------------------------------------------------------------------------
    
    OK
  • Retrieve 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]                                                  |
    +------------------------------------------------------------------------------------
    
    OK
  • Retrieve 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]                                                               |
    +------------------------------------------------------------------------------------
    
    OK
  • Retrieve 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]                                                  |
    +------------------------------------------------------------------------------------
    
    OK
  • Retrieve 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]                                                  |
    +------------------------------------------------------------------------------------
    
    OK
  • Verify if the non-partitioned table t5 is transactional.

    Note

    We 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.

    Note

    We 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.

Note

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 (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...).

without touch

No

Specifies that LastDataModifiedTime will not be updated. If omitted, LastDataModifiedTime will automatically be refreshed.

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