查看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;
sale_date和region两个字段仅会作为普通列存在，而不是表的分区。+------------------------------------------------------------------------------------+ | 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 | +------------------------------------------------------------------------------------+ - 示例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;返回结果如下。
除生命周期属性外，sale_detail_like的其它属性（字段类型、分区类型等）均与sale_detail完全一致。+------------------------------------------------------------------------------------+ | 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 | +------------------------------------------------------------------------------------+说明 通过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
相关命令
- CREATE TABLE：创建非分区表、分区表、外部表或聚簇表。
- CREATE VIEW：基于查询语句创建视图或更新已存在的视图。
- CREATE MATERIALIZED VIEW：基于满足物化视图场景的数据创建物化视图，支持分区和聚簇场景。
- ALTER MATERIALIZED VIEW：更新物化视图、修改物化视图的生命周期、开启或禁用物化视图的生命周期和删除物化视图分区。
- SELECT MATERIALIZED VIEW：查询物化视图状态。
- DROP MATERIALIZED VIEW：删除已创建的物化视图。