查看MaxCompute内部表、视图、物化视图、外部表、聚簇表或Transactional表的信息。

命令格式

--查看内部表或视图信息。
desc <table_name|view_name> [partition (<pt_spec>)]; 
--查看物化视图、外部表、聚簇表或Transactional表信息。也可以查看内部表的扩展信息。
desc extended <table_name|mv_name>; 

参数说明

  • table_name:必填。必填。待查看表的名称。
  • view_name:必填。待查看视图的名称。
  • mv_name:待查询物化视图的名称。
  • pt_spec:可选。待查看分区表的指定分区。格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)
  • extended:如果为物化视图、外部表、聚簇表或Transactional表,需要包含此参数。显示表的扩展信息。也可以查看内部表的扩展信息,例如列的非空属性。

返回值说明

  • Owner:表或视图的所有者账号。
  • Project:表或视图的所属项目。
  • TableComment:表或视图的注释信息。
  • CreateTime:表或视图的创建时间。
  • LastDDLTime:表或视图的DDL最后变更时间。
  • LastModifiedTime:表或视图的数据最后变更时间。
  • Lifecycle:生命周期(天)。
  • InternalTable:是否为内部表,Table对象才有此返回值。
  • VirtualView:是否为视图,View对象才有此返回值。
  • Size:表大小,单位为Byte。
  • Native Columns:表或视图的列信息。
  • Partition Columns:分区列信息,分区表才有此返回值。

使用示例

  • 示例1:查看创建的test1表的信息。
    desc test1;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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     |       |                                             |
    +------------------------------------------------------------------------------------+
  • 示例2:查看创建的sale_detail表的信息。
    desc sale_detail;
    返回结果如下。
    +--------------------------------------------------------------------+
    | 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     |                                     |
    +--------------------------------------------------------------------+
  • 示例3:查看创建的sale_detail_ctas1表的详细信息。
    desc extended sale_detail_ctas1;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name                      |
    | TableComment:                                                                      |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2021-07-07 15:29:53                                      |
    | LastDDLTime:              2021-07-07 15:29:53                                      |
    | LastModifiedTime:         2021-07-07 15:29:53                                      |
    | Lifecycle:                10                                                       |
    +------------------------------------------------------------------------------------+
    | InternalTable: YES      | Size: 0                                                  |
    +------------------------------------------------------------------------------------+
    | Native Columns:                                                                    |
    +------------------------------------------------------------------------------------+
    | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
    +------------------------------------------------------------------------------------+
    | shop_name | string |       |               | true     | NULL         |              |
    | customer_id | string |       |               | true     | NULL         |              |
    | total_price | double |       |               | true     | NULL         |              |
    | sale_date | string |       |               | true     | NULL         |              |
    | region   | string |       |               | true     | NULL         |              |
    +------------------------------------------------------------------------------------+
    | Extended Info:                                                                     |
    +------------------------------------------------------------------------------------+
    | TableID:                  98cb8a38733c49eabed4735173818147                         |
    | IsArchived:               false                                                    |
    | PhysicalSize:             0                                                        |
    | FileNum:                  0                                                        |
    | StoredAs:                 AliOrc                                                   |
    | CompressionStrategy:      normal                                                   |
    +------------------------------------------------------------------------------------+
    sale_date和region两个字段仅会作为普通列存在,而不是表的分区。
  • 示例4:查看创建的sale_detail_ctas2表的信息。
    desc sale_detail_ctas2;
    返回结果如下。
    +--------------------------------------------------------------------+
    | 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     |       |                             |
    +--------------------------------------------------------------------+
  • 示例5:查看创建的sale_detail_like表的详细信息。
    desc extended sale_detail_like;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
    | TableComment:                                                                      |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2021-07-07 15:40:38                                      |
    | LastDDLTime:              2021-07-07 15:40:38                                      |
    | LastModifiedTime:         2021-07-07 15:40:38                                      |
    | Lifecycle:                10                                                       |
    +------------------------------------------------------------------------------------+
    | InternalTable: YES      | Size: 0                                                  |
    +------------------------------------------------------------------------------------+
    | Native Columns:                                                                    |
    +------------------------------------------------------------------------------------+
    | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
    +------------------------------------------------------------------------------------+
    | shop_name | string |       |               | true     | NULL         |              |
    | customer_id | string |       |               | true     | NULL         |              |
    | total_price | double |       |               | true     | NULL         |              |
    +------------------------------------------------------------------------------------+
    | Partition Columns:                                                                 |
    +------------------------------------------------------------------------------------+
    | sale_date       | string     |                                                     |
    | region          | string     |                                                     |
    +------------------------------------------------------------------------------------+
    | Extended Info:                                                                     |
    +------------------------------------------------------------------------------------+
    | TableID:                  61782ff7713f426e9d6f91d5deeac99a                         |
    | IsArchived:               false                                                    |
    | PhysicalSize:             0                                                        |
    | FileNum:                  0                                                        |
    | StoredAs:                 AliOrc                                                   |
    | CompressionStrategy:      normal                                                   |
    +------------------------------------------------------------------------------------+
    除生命周期属性外,sale_detail_like的其它属性(字段类型、分区类型等)均与sale_detail完全一致。
    说明 通过desc table_name查看到的Size包含了在回收站的数据Size。如果您需要清空回收站,可以先执行purge table table_name,然后再执行desc table_name查看除回收站以外的数据大小。您也可以执行show recyclebin查看本项目中回收站内的数据明细。
  • 示例6:查看创建的test_newtype表的信息。
    desc test_newtype;
    返回结果如下。
    | 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
  • 示例7:查看创建的Hash聚簇非分区表t1的信息。聚簇属性将显示在Extended Info中。
    desc extended t1;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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
  • 示例8:查看创建的Hash聚簇分区表t2的信息。聚簇属性将显示在Extended Info中。
    desc extended t2;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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
  • 示例9:查看Range聚簇非分区表t3的信息。聚簇属性将显示在Extended Info中。
    desc extended t3;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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
  • 示例10:查看Range聚簇分区表t4的信息。聚簇属性将显示在Extended Info中。
    desc extended t4;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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
  • 示例11:查看非分区表t5是否为Transactional表。
    说明 推荐您使用MaxCompute客户端查看表是否为Transactional表,需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题,导致查询结果不显示Transactional信息。
    desc extended t5;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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                                                     |
    +------------------------------------------------------------------------------------+
  • 示例12:查看分区表t6是否为Transactional表。
    说明 推荐您使用MaxCompute客户端查看表是否为Transactional表,需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题,导致查询结果不显示Transactional信息。
    desc extended t6;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | 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                                                     |
    +------------------------------------------------------------------------------------+
  • 示例13:查询物化视图mv的信息。
    desc extended mv;
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | Owner: ALIYUN$****@aliyunid.com | Project: ****                                    |
    | TableComment:                                                                      |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2021-08-01 17:50:15                                      |
    | LastDDLTime:              2021-08-01 17:50:15                                      |
    | LastModifiedTime:         2021-08-01 17:50:15                                      |
    +------------------------------------------------------------------------------------+
    | InternalTable: YES      | Size: 0                                                  |
    +------------------------------------------------------------------------------------+
    | Native Columns:                                                                    |
    +------------------------------------------------------------------------------------+
    | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
    +------------------------------------------------------------------------------------+
    | page_id  | string |       |               | true     | NULL         |              |
    | _c1      | bigint |       |               | true     | NULL         |              |
    +------------------------------------------------------------------------------------+
    | Extended Info:                                                                     |
    +------------------------------------------------------------------------------------+
    | TableID:                  e4a7f1169588400ab39bc3076426****                         |
    | IsArchived:               false                                                    |
    | PhysicalSize:             0                                                        |
    | FileNum:                  0                                                        |
    +------------------------------------------------------------------------------------+
  • 示例14:查询分区表sale_detail的分区信息。
    desc sale_detail partition (sale_date='201310',region='beijing');
    返回结果如下。
    +------------------------------------------------------------------------------------+
    | PartitionSize: 2109112                                                             |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2015-10-10 08:48:48                                      |
    | LastDDLTime:              2015-10-10 08:48:48                                      |
    | LastModifiedTime:         2015-10-11 01:33:35                                      |
    +------------------------------------------------------------------------------------+
    OK

相关命令