查看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。
- NativeColumns：表或視圖的列資訊。
- PartitionColumns：分區列資訊，分區表才有此傳回值。
使用樣本
- 樣本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：刪除已建立的物化視圖。