This topic describes how to use SQL statements to view tables and views.
Operation | Description | Authorized user | Operation platform |
Displays the information about MaxCompute internal tables, views, external tables, clustered tables, or transactional tables. | Users who have the Describe permission on the metadata of a table. | You can perform the operations by using the following platforms: | |
Displays the details of partitions in a table. | |||
Displays the SQL DDL statement that is used to create a specified table. | |||
Lists all tables, external tables, views, and materialized views in a project, or those that meet specific rules in a project. | Users who have the List permission on objects in a project. | ||
List all external tables in a project, or those that meet specific rules in a project. | |||
List all views in a project, or those that meet specific rules in a project. | |||
List all materialized views in a project, or those that meet specific rules in a project. | |||
Lists all the partitions of a table. If the table does not exist or the table is a non-partitioned table, an error is returned. |
View the information about tables or views
Displays the information about MaxCompute internal tables, views, external tables, clustered tables, or transactional tables. For more information about how to view detailed table information, see SELECT syntax.
Syntax
View the information about a table or view.
DESC <table_name|view_name> [partition (<pt_spec>)];View the information about an external table, a clustered table, or a transactional table, or the extended information about an internal table, such as the non-null attribute of columns.
-- View the information about an external table, a clustered table, or a transactional table. You can also execute this statement to view the extended information about an internal table. DESC EXTENDED <table_name>;
Parameters
table_name: required. The name of the table whose information you want to view.
view_name: required. The name of the view whose information you want to view.
pt_spec: optional. The partition whose information you want to view. The value of this parameter is in the
(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)format.
Examples
For more information about the table data used in this section, see Create and drop tables.
View the information about the test1 table.
DESC test1;The following result is returned.
+------------------------------------------------------------------------------------+ | 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 | | | +------------------------------------------------------------------------------------+View the information about the sale_detail table.
DESC sale_detail;The following result is returned.
+--------------------------------------------------------------------+ | 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 | | +--------------------------------------------------------------------+View the detailed information about the sale_detail_ctas1 table.
DESC extended sale_detail_ctas1;The following result is returned.
+------------------------------------------------------------------------------------+ | 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_dateandregioncolumns are common columns rather than partition key columns.View the information about the sale_detail_ctas2 table.
DESC sale_detail_ctas2;The following result is returned.
+--------------------------------------------------------------------+ | 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 | | | +--------------------------------------------------------------------+View the detailed information about the sale_detail_like table.
DESC extended sale_detail_like;The following result is returned.
+------------------------------------------------------------------------------------+ | 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 | +------------------------------------------------------------------------------------+Except for the lifecycle settings, the other attributes, such as field types and partition types, of the
sale_detail_liketable are the same as the attributes of thesale_detailtable.NoteThe data size in the output of the DESC table_name command includes the size of data in the recycle bin. If you want to permanently remove the data from the recycle bin, execute the
PURGE TABLE table_namestatement. Then, execute theDESC table_namestatement to view the size of data that excludes data in the recycle bin. You can also executeSHOW recyclebinto view the details about data in the recycle bin for the current project.View the information about the test_newtype table.
DESC test_newtype;The following result is returned.
| 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) | | | +------------------------------------------------------------------------------------+ OKView the information about the hash-clustered table t1. This table is a non-partitioned table. The clustering attributes are displayed in Extended Info.
DESC extended t1;The following result is returned.
+------------------------------------------------------------------------------------+ | 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] | +------------------------------------------------------------------------------------+ OKView the information about the hash-clustered table t2. This table is a partitioned table. The clustering attributes are displayed in Extended Info.
DESC EXTENDED t2;The following result is returned.
+------------------------------------------------------------------------------------+ | 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] | +------------------------------------------------------------------------------------+ OKView the information about the range-clustered table t3. This table is a non-partitioned table. The clustering attributes are displayed in Extended Info.
DESC extended t3;The following result is returned.
+------------------------------------------------------------------------------------+ | 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] | +------------------------------------------------------------------------------------+ OKView the information about the range-clustered table t4. This table is a partitioned table. The clustering attributes are displayed in Extended Info.
DESC extended t4;The following result is returned.
+------------------------------------------------------------------------------------+ | 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] | +------------------------------------------------------------------------------------+ OKCheck whether the non-partitioned table t5 is a transactional table.
NoteWe recommend that you use the MaxCompute client to check whether a table is a transactional table. The version of the MaxCompute client must be V0.35.4 or later. For more information about how to download and use the MaxCompute client, see MaxCompute client. Other tools may not be updated and do not display transactional information.
DESC extended t5;The following result is returned.
+------------------------------------------------------------------------------------+ | 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 | +------------------------------------------------------------------------------------+Check whether the partitioned table t6 is a transactional table.
NoteWe recommend that you use the MaxCompute client to check whether a table is a transactional table. The version of the MaxCompute client must be V0.35.4 or later. For more information about how to download and use the MaxCompute client, see MaxCompute client. Other tools may not be updated and do not display transactional information.
DESC extended t6;The following result is returned.
+------------------------------------------------------------------------------------+ | 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 partition information
Displays the partition information about a partitioned table.
Sample data
The sale_detail table is used in this section. For information about the CREATE TABLE statement of this table, see Create a table named sale_detail.
Create partitions in the sale_detail table and insert data into the partitions.
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);Syntax
DESC <table_name> PARTITION (<pt_spec>);Parameters
table_name: required. The name of the partitioned table whose partition information you want to view.
pt_spec: required. The information about the partition that you want to view. The value of this parameter is in the
partition_col1=col1_value1, partition_col2=col2_value1...format. If a table has multi-level partitions, you must specify the values of all the partition key columns.
Examples
-- View the partition information about the partitioned table sale_detail.
DESC sale_detail PARTITION (sale_date='2023',region='china');The following result is returned.
+------------------------------------------------------------------------------------+
| PartitionSize: 1234 |
+------------------------------------------------------------------------------------+
| CreateTime: 2024-11-14 16:43:22 |
| LastDDLTime: 2024-11-14 16:45:37 |
| LastModifiedTime: 2024-11-14 16:45:37 |
+------------------------------------------------------------------------------------+
OKView the CREATE TABLE statement
Displays the CREATE TABLE statement that is used to create a table. This helps you recreate a schema of the table by using SQL statements.
Syntax
SHOW CREATE TABLE <table_name>;Parameters
table_name: required. The name of the table for which you want to view the CREATE TABLE statement.
Examples
-- View the CREATE TABLE statement that is used to create the sale_detail table.
SHOW CREATE TABLE sale_detail;The following result is returned.
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
Lists all tables, external tables, views, and materialized views in a project, or the tables and views that meet specific rules in a project.
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>';Examples
-- List the tables whose names start with sale* in a project. The asterisk (*) indicates any character.
SHOW TABLES LIKE 'sale*'; The following results are returned:
ALIYUN$account_name:sale_detail
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If the table is created by a RAM user, the system prompt is RAM.List external tables in a project
List all external tables in a project, or those that meet specific rules in a project.
The SHOW EXTERNAL TABLES command must be run in version 0.43.0 or later of the MaxCompute client (odpscmd).
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>';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*'; The following results are returned:
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 an Alibaba Cloud RAM user, the system prompt is RAM.List views in a project
List all views in a project, or those that meet specific rules in a project.
The SHOW VIEWS command must be run in version 0.43.0 or later of the MaxCompute client (odpscmd).
Syntax
-- List all views in a project.
SHOW VIEWS;
-- View the views whose names match the condition specified by view in a project.
SHOW VIEWS LIKE '<view>';Examples
-- View the views whose names match the condition specified by mf* in a project. The asterisk (*) indicates any character.
SHOW VIEWS LIKE 'mf*'; The following results are returned:
ALIYUN$account_name:mf_v
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If you are an Alibaba Cloud RAM user, the system prompt is RAM.List materialized views in a project
List all materialized views in a project, or those that meet specific rules in a project.
The SHOW MATERIALIZED VIEWS command must be run in version 0.43.0 or later of the MaxCompute client (odpscmd).
Syntax
-- List all materialized views in a project.
SHOW MATERIALIZED VIEWS;
-- View the materialized views whose names match the condition specified by materialized_view in a project.
SHOW MATERIALIZED VIEWS LIKE '<materialized_view>';Examples
-- View the materialized views whose names match the condition specified by test* in a project. The asterisk (*) indicates any character.
SHOW MATERIALIZED VIEWS LIKE 'test*'; The command returns the following information.
ALIYUN$account_name:test_two_mv
ALIYUN$account_name:test_create_one_mv
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If you are an Alibaba Cloud RAM user, the system prompt is RAM.List all partitions
Lists all the partitions of a table. If the table does not exist or the table is a non-partitioned table, an error is returned.
Syntax
SHOW PARTITIONS <table_name>; Parameters
table_name: required. The name of the partitioned table whose partition information you want to view.
Examples
-- List all the partitions of the sale_detail table.
SHOW PARTITIONS sale_detail;The command returns the following information:
sale_date=2023/region=china
sale_date=2024/region=shanghai
OK