Table is the unit for storing data in MaxCompute. When you develop, analyze, and maintain a data warehouse, you must process table data. This topic describes the operations that you can perform on tables.

The following table describes the regular operations that you can perform on tables.

Operation Description Role Operation platform
Create a table Create a non-partitioned table, a partitioned table, an external table, or a clustered table. Users who have the CREATE TABLE permission in a project. You can execute the statements that are described in this topic on the following platforms:
Change the owner of a table Change the owner of a table. The project owner.
Modify the comment of a table Modify the comment of a table. Users who have the ALTER permission on tables.
Change LastDataModifiedTime Change LastDataModifiedTime of a table to the current time.
Modify the clustering attribute of a table Add or remove the clustering attribute to or from a table.
Rename a table Rename a table.
Clear a non-partitioned table Clear a specified non-partitioned table.
Delete a table Delete a partitioned table or a non-partitioned table. Users who have the DROP permission on tables.
View the information about tables or views View the information about MaxCompute internal tables, views, external tables, clustered tables, or transactional tables. Users who have the DESCRIBE permission to read the metadata of a table.
View the CREATE TABLE statement View the CREATE TABLE statement of a table.
List tables and views in a project List all tables and views or the tables and views that meet specific rules including regular expressions in a project. Users who have the LIST permission on objects in a project.
List all partitions List all partitions of a table. An error is returned if the table does not exist or the table is a non-partitioned table.

Create a table

Create a non-partitioned table, a partitioned table, an external table, or a clustered table.

  • Limits

    For more information about the limits on tables, see MaxCompute SQL limits.

  • Syntax
    -- Create a table.
     create [external] table [if not exists] <table_name>
     [(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
     [comment <table_comment>]
     [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
     -- Configure the shuffle and sort properties of the clustered table that you want to create.
     [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] 
     -- Used only for external tables.
     [stored by StorageHandler] 
     -- Used only for external tables.
     [with serdeproperties (options)] 
     -- Used only for external tables.
     [location <osslocation>] 
     -- Specify the table as a transactional table. You can later modify or delete the data of the transactional table. Transactional tables have specific limits. Create a transactional table base on your business requirements.
     [tblproperties("transactional"="true")]   
     [lifecycle <days>];
    
    -- Create a table based on an existing table.
    create table [if not exists] <table_name> [as <select_statement> | like <existing_table_name>];
  • Parameters
    • external: optional. Specifies that the table that you want to create is an external table.
    • if not exists: optional. If you create a table by using the name of an existing table but do not specify the if not exists parameter, an error is returned. If you specify the if not exists parameter, a success message is returned when you create a table by using the name of an existing table. The success message is returned even if the schema of the existing table is different from that of the table you want to create. If you create a table by using the name of an existing table, the table is not created and the metadata of the existing table is not changed.
    • table_name: required. The name of the table. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). It must start with a letter and cannot contain special characters. It is not case-sensitive. If the value of this parameter does not meet the requirements, an error is returned.
    • col_name: optional. The name of a table column. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). It must start with a letter and cannot contain special characters. It is not case-sensitive. If the value of this parameter does not meet the requirements, an error is returned.
    • col_comment: optional. The comment of a column. The comment must be a valid string and can be up to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
    • data_type: optional. The data type of a column. The following data types are supported: BIGINT, DOUBLE, BOOLEAN, DATETIME, DECIMAL, and STRING. For more information, see Data types.
    • not null: optional. If you specify this parameter for a column, the values of the column cannot be NULL. For more information about how to modify the NOT NULL attribute, see Partition and column operations.
    • default_value: optional. The default value of the specified column. If a column is not specified in an INSERT operation, the default value is used for the column.
    • table_comment: optional. The comment of the table. The comment must be a valid string and can be up to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
    • partitioned by (<col_name> <data_type> [comment <col_comment>], ...: optional. The partition fields of the specified partitioned table.
      • col_name: the name of a partition key column. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). It must start with a letter and cannot contain special characters. It is not case-sensitive. If the value of this parameter does not meet the requirements, an error is returned.
      • data_type: the data type of a partition key column. The following data types are supported: BIGINT, DOUBLE, BOOLEAN, DATETIME, DECIMAL, and STRING. In MaxCompute V1.0, partition key columns must be of the STRING type. In MaxCompute V2.0, partition key columns can be of the TINYINT, SMALLINT, INT, BIGINT, VARCHAR, or STRING types. For more information, see Data types. If you use a partition field to partition a table, a full table scan is not required when you add partitions, update partition data, or read partition data. This improves the efficiency of data processing.
      • col_comment: the comment of a partition key column. The comment must be a valid string and can be up to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
      Note The value of a partition key column cannot contain double-byte characters. It must start with a letter and can contain letters, digits, and specific supported special characters. It can be up to 128 bytes in length. The following special characters are supported: spaces, colons (:), underscores (_), dollar signs ($), number signs (#), periods (.), exclamation points (!), and at signs (@). The behavior of other characters, such as escaped characters \t, \n, and /, is not defined.
    • clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets: optional. The shuffle and sort properties of the clustered table that you want to create.
      Clustered tables are classified into hash-clustered tables and range-clustered tables.
      • Hash-clustered tables
        • CLUSTERED BY: the hash key. MaxCompute performs a hash operation on specified columns and distributes data to each bucket based on the hash values. To prevent data skew and hot spots and to improve the efficiency of concurrent executions, we recommend that you specify columns with a large value range and a small number of duplicate key values in CLUSTERED BY. To optimize the performance of JOIN operations, we recommend that you select commonly used join or aggregate keys. Join and aggregate keys are similar to primary keys in conventional databases.
        • SORTED BY: specifies how to sort fields in a bucket. To improve performance, we recommend that you keep the value of the SORTED BY clause consistent with that of the CLUSTERED BY clause. After you specify fields in the SORTED BY clause, MaxCompute automatically generates indexes, which can be used to accelerate data queries.
        • number_of_buckets: the number of hash buckets. This parameter is required and the value of this parameter varies based on the data volume. By default, MaxCompute supports a maximum of 1,111 reducers. This means that MaxCompute supports a maximum of 1,111 hash buckets. You can run the set odps.sql.reducer.instances=xxx; command to increase the maximum number of hash buckets. However, the maximum number of hash buckets cannot exceed 4,000. Otherwise, performance may be affected.
          To maintain optimal performance, we recommend that you take note of the following rules when you specify the number of hash buckets:
          • Keep the size of each hash bucket around 500 MB. For example, if you want to add 1,000 hash buckets to a partition whose size is 500 GB, the size of each hash bucket is 500 MB on average. If a table contains large amounts of data, you can increase the size of each hash bucket from 500 MB to a size in the range of 2 GB to 3 GB. You can also run the set odps.sql.reducer.instances=xxx; command to set the maximum number of hash buckets to a value greater than 1,111.
          • To optimize the performance of JOIN operations, we recommend that you do not configure the shuffle and sort properties for hash-clustered tables. The two tables must have a multiple relationship in terms of the number of hash buckets. For example, one table has 256 hash buckets and the other table has 512 hash buckets. We recommend that you set the number of hash buckets to 2n, such as 512, 1024, 2048, and 4096. This way, MaxCompute can automatically split and merge hash buckets. To improve execution efficiency, you can also skip the step of configuring the shuffle and sort properties for hash-clustered tables.
      • Range-clustered tables
        • RANGE CLUSTERED BY: the range-clustered columns. MaxCompute performs the bucket operation on the specified columns and distributes data to each bucket based on the bucket ID.
        • SORTED BY: the sequence of fields in a bucket. You can use this parameter in the same way as you use it for a hash-clustered table.
        • number_of_buckets: the number of hash buckets. Compared with hash-clustered tables, range-clustered tables have no limits on the number of buckets when data is evenly distributed. If you do not specify the number of buckets in a range-clustered table, MaxCompute automatically determines the optimal number based on the data volume.
        • If JOIN and AGGREGATE operations are performed on range-clustered tables and the join key or group key is the range-clustered key or the prefix of the range-clustered key, you can control flags to disable shuffling. This improves the execution efficiency. To control shuffling, you can set odps.optimizer.enable.range.partial.repartitioning to true or false. By default, this parameter is set to false, which indicates that shuffling is disabled.
          Note
          • Clustered tables help optimize the following aspects:
            • Bucket pruning
            • Aggregation
            • Storage
          • Limits on clustered tables:
            • The INSERT INTO statement is not supported. You can add data only by executing the INSERT OVERWRITE statement.
            • Data that is imported by using Tunnel commands is not arranged in order. Therefore, you cannot import data into a range-clustered table by using Tunnel commands.
    • stored by StorageHandler: optional. Specify StorageHandler based on the data format of the external table.
    • with serdeproperties (options): optional. The parameters related to the authorization, compression, and character parsing of the external table.
    • osslocation: optional. The Object Storage Service (OSS) bucket where the data of the external table is stored. For more information, see Access OSS data by using the built-in extractor and Process OSS data stored in open source formats.
    • tblproperties("transactional"="true"): optional. -- Specify the table as a transactional table. You can later perform the UPDATE or DELETE operation on the transactional table to update or delete data by rows. For more information, see UPDATE and DELETE.
      A transactional table has the following limits:
      • MaxCompute only allows you to specify a table as a transactional table when you create the table. If you execute the ALTER TABLE statement to change an existing table to a transactional table, an error is returned.
        alter table not_txn_tbl set tblproperties("transactional"="true");
        -- The following error is returned:
        FAILED: Catalog Service Failed, ErrorCode: 151, Error Message: Set transactional is not supported
      • When you create a clustered table or an external table, you cannot specify it as a transactional table.
      • You cannot change transactional tables to MaxCompute internal tables, external tables, or clustered tables, and vice versa.
      • Jobs from other systems, such as MaxCompute Spark, Machine Learning Platform for AI, and Graph, cannot access transactional tables.
      • CLONE TABLE and MERGE PARTITION operations are not supported.
      • Before you perform the UPDATE, DELETE, or INSERT OVERWRITE operation on transactional tables, you must perform SELECT and INSERT operations to back up data in transactional tables to other tables.
    • lifecycle: optional. The lifecycle of the table. The value must be a positive integer. Unit: days.
      • Non-partitioned tables: If data in a non-partitioned table remains unchanged for the number of days specified by days after the last data modification, MaxCompute executes a statement such as DROP TABLE to reclaim the table.
      • Partitioned tables: MaxCompute determines whether to reclaim a partition based on the value of LastDataModifiedTime. Unlike non-partitioned tables, a partitioned table is not deleted even if all of its partitions are reclaimed. You can configure lifecycles for tables, but not for partitions.
    • create table [if not exists] <table_name> [as <select_statement> | like <existing_table_name>]:
      • If you execute the CREATE TABLE…AS select_statement… statement to create a table with data replicated from another table, the replicated information does not include partition attributes. This is because partition key columns in the source table are considered regular columns in the created table.
      • If you execute the CREATE TABLE…LIKE existing_table_name statement to create a table, the destination table may have the same schema as the source table. However, the destination table does not replicate data or lifecycle settings from the source table.
  • Examples
    • Example 1: Create a non-partitioned table that is named test1.
      create table test1 (key STRING);
    • Example 2: Create a partitioned table that is named sale_detail.
      create table if not exists sale_detail(
       shop_name     STRING,
       customer_id   STRING,
       total_price   DOUBLE)
      partitioned by (sale_date STRING, region STRING); 
    • Example 3: Create the sale_detail_ctas1 table and replicate data from the sale_detail table to the sale_detail_ctas1 table.
      create table sale_detail_ctas1 as select * from sale_detail;
      Note The sale_detail table is a partitioned table, whereas the sale_detail_ctas1 table that is created by using the CREATE TABLE...AS select_statement... statement is a non-partitioned table that has five columns. This is because the replicated information does not include partition attributes and partition key columns in the sale_detail table. They are considered regular columns in the sale_detail_ctas1 table.
    • Example 4: Create the sale_detail_ctas2 table and use constants as column values in the SELECT clause.
      -- Column names are specified.
      create table sale_detail_ctas2
      as
      select shop_name, customer_id, total_price, '2013' as sale_date, 'China' as region
      from sale_detail;
      -- Column names are not specified.
      create table sale_detail_ctas3
      as
      select shop_name, customer_id, total_price, '2013', 'China' 
      from sale_detail;
      Note If you use constants as column values in the SELECT clause, we recommend that you specify column names. In this example, the names of the fourth and fifth columns in the sale_detail_ctas3 table contain suffixes that are similar to _c4 and _c5.
    • Example 5: Create the sale_detail_like table that uses the same schema as the sale_detail table.
      create table sale_detail_like like sale_detail;

      The schema of the sale_detail_like table is the same as that of the sale_detail table. The two tables have the same attributes, such as column names, column comments, and table comments, except for the lifecycle. However, data in the sale_detail table is not replicated to the sale_detail_like table.

    • Example 6: Create the test_newtype table that uses new data types.
      set odps.sql.type.system.odps2=true;
      CREATE TABLE test_newtype (
          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))
      LIFECYCLE 1
      ;
    • Example 7: Create the t1 hash-clustered table. This table is a non-partitioned table.
      create table t1 (a STRING, b STRING, c BIGINT) clustered by (c) sorted by (c) into 1024 buckets; 
    • Example 8: Create the t2 hash-clustered table. This table is a partitioned table.
      create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by (c) sorted by (c) into 1024 buckets; 
    • Example 9: Create the t3 range-clustered table. This table is a non-partitioned table.
      create table t3 (a STRING, b STRING, c BIGINT) range clustered by (c) sorted by (c) into 1024 buckets;
    • Example 10: Create the t4 range-clustered table. This table is a partitioned table.
      create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by (c) sorted by (c); 
    • Example 11: Create the t5 transactional table. This table is a non-partitioned table.
      create table t5(id bigint) tblproperties("transactional"="true");
    • Example 12: Create the t6 transactional table. This table is a partitioned table.
      create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");

Change the owner of a table

Change the owner of a table.

  • Syntax
    alter table <table_name> changeowner to <new_owner>;
  • Parameters
    • table_name: required. The name of the table whose owner you want to change.
    • new_owner: required. The new owner of the table.
  • Example
    -- Change the owner of the test1 table to ALIYUN$xxx@aliyun.com.
    alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';

Modify the comment of a table

Modify the comment of a table.

  • Syntax
    alter table <table_name> set comment '<new_comment>';
  • Parameters
    • table_name: required. The name of the table whose comment you want to modify.
    • new_comment: required. The new comment of the table.
  • Example
    alter table sale_detail set comment 'new coments for table sale_detail';
    You can view the modification results of the comment in the table by executing the DESC table_name statement of MaxCompute.

Change LastDataModifiedTime

MaxCompute SQL allows you to execute the TOUCH statement to change the value of LastDataModifiedTime. You can change the value to the current time. After you execute this statement to change the value of LastDataModifiedTime, MaxCompute determines that the table data has changed, and restarts the lifecycle of the table from the time that is specified by LastDataModifiedTime.

  • Syntax
    alter table <table_name> touch;
  • Parameters

    table_name: required. The name of the table whose LastDataModifiedTime you want to modify.

  • Example
    alter table sale_detail touch;

Modify the clustering attribute of a table

MaxCompute allows you to add or remove the clustering attribute to or from a table by executing the ALTER TABLE statement.

  • Syntax
    • Add the hash clustering attribute to a table.
      alter table <table_name> [clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets];
    • Remove the hash clustering attribute from a table.
      alter table <table_name> not clustered;
    • Add the range clustering attribute to a table without specifing the number of buckets. MaxCompute automatically determines the optimal number based on the data volume. Syntax:
      alter table <table_name> [range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets];
    • Remove the range clustering attribute from a table or partition.
      alter table <table_name> not clustered;
      alter table <table_name> <pt_spec> not clustered;
      Note
      • The ALTER TABLE statement can modify the clustering attribute only for a partitioned table. The clustering attribute of a non-partitioned table cannot be modified after the table is created. The ALTER TABLE statement applies to existing tables. After you specify the clustering attribute, new partitions are stored based on the clustering attribute you specified.
      • The ALTER TABLE statement takes effect only on the new partitions in a partitioned table. The new partitions include those generated by the INSERT OVERWRITE statement and are stored based on the new clustering attribute. The clustering attribute and storage method remain unchanged for the original partitions. After you remove the clustering attribute from a table and then add the clustering attribute to the table again, the clustered columns, ordered columns, and the number of buckets you specified for new partitions can be different from those you specified for the original partitions.
      • The ALTER TABLE statement takes effect only on the new partitions. Therefore, this statement cannot be used to specify partitions.
  • Parameters

    For more information, see Create a table.

Rename a table

Rename a table. Only the name of a table is changed. Data in the table is not changed.

  • Syntax
    alter table <table_name> rename to <new_table_name>;
  • Parameters
    • table_name: required. The name of the table you want to rename.
    • new_table_name: required. The new name of the table. If the name specified by the new_table_name parameter already exists, an error is returned.
  • Example
    alter table sale_detail rename to sale_detail_rename;

Clear a non-partitioned table

Clear a specified non-partitioned table. You cannot execute this statement for a partitioned table. You can execute the ALTER TABLE table_name DROP PARTITION statement to clear the partitions of a partitioned table.

  • Syntax
    truncate table <table_name>;
  • Parameters

    table_name: required. The name of the non-partitioned table that you want to clear.

Delete a table

Delete a non-partitioned table or partitioned table.

  • Syntax
    drop table [if exists] <table_name>; 
  • Parameters
    • if exists: optional. If you do not specify the if exists parameter and the specified table does not exist, an error is returned. If you specify the if exists parameter, a success message is returned regardless of whether the specified table exists.
    • table_name: required. The name of the table that you want to delete.
  • Example
    -- Delete the sale_detail table. A success message is returned regardless of whether the sale_detail table exists.
    drop table if exists sale_detail; 

View the information about tables or views

View 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 the table or view.
    desc <table_name|view_name> [partition (<pt_spec>)]; 
    -- View the information about the external table, clustered table, or transactional table. You can also execute this statement to view extended information about an internal table.
    desc extended <table_name>; 
  • Parameters
    • pt_spec: optional. The partition in the partitioned table that you want to view. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format.
    • extended: This parameter is required if the table is an external table, a clustered table, or a transactional table. This parameter is used to query extended information about a table. You can also use this parameter to view extended information about an internal table, such as whether a column of the internal table can contain NULL values.
  • Examples
    • Example 1: View the information about the test1 table.
      desc test1;
      The following information 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     |       |                                             |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 2: View the information about the sale_detail table.
      desc sale_detail;
      The following information 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     |                                     |
      +--------------------------------------------------------------------+
      
      OK
    • Example 3: View the information about the sale_detail_ctas1 table.
      desc sale_detail_ctas1;
      The following information is returned:
      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name                      |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 17:27:33                                      |
      | LastDDLTime:              2020-11-16 17:27:33                                      |
      | LastModifiedTime:         2020-11-16 17:27:33                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | shop_name       | string     |       |                                             |
      | customer_id     | string     |       |                                             |
      | total_price     | double     |       |                                             |
      | sale_date       | string     |       |                                             |
      | region          | string     |       |                                             |
      +------------------------------------------------------------------------------------+
      
      OK
      The sale_date and region columns are considered regular columns instead of partition key columns.
    • Example 4: View the information about the sale_detail_ctas2 table.
      desc sale_detail_ctas2;
      The following information 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     |       |                             |
      +--------------------------------------------------------------------+
      OK
    • Example 5: View the information about the sale_detail_like table.
      desc sale_detail_like;
      The following information 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     |       |                             |
      +--------------------------------------------------------------------+
      | Partition Columns:                                                 |
      +--------------------------------------------------------------------+
      | sale_date       | string     |                                     |
      | region          | string     |                                     |
      +--------------------------------------------------------------------+
      
      OK
      Except for the lifecycle, the attributes, such as field types and partition types, of the sale_detail_like table are the same as those of the sale_detail table.
      Note The size of table data in the execution results of the DESC table_name statement includes the size of the data in the recycle bin. If you want to clear the recycle bin, execute the PURGE TABLE table_name statement. Then, execute the DESC table_name statement to view the size of data that excludes the size of data in the recycle bin. You can also execute the SHOW RECYCLEBIN statement to view the details about data in the recycle bin for the current project.
    • Example 6: View the information about the test_newtype table.
      desc test_newtype;
      The following information 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) |       |                                            |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 7: View the information about the t1 hash-clustered table. This table is a non-partitioned table. The clustering attribute is displayed in Extended Info.
      desc extended t1;
      The following information 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]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 8: View the information about the t2 hash-clustered table. This table is a partitioned table. The clustering attribute is displayed in Extended Info.
      desc extended t2;
      The following information 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]                                                               |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 9: View the information about the t3 range-clustered table. This table is a non-partitioned table. The clustering attribute is displayed in Extended Info.
      desc extended t3;
      The following information 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]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 10: View the information about the t4 range-clustered table. This table is a partitioned table. The clustering attribute is displayed in Extended Info.
      desc extended t4;
      The following information 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]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 11: Check whether the t5 non-partitioned table is a transactional table.
      Note We 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 to display transactional information.
      desc extended t5;
      The following information 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                                                     |
      +------------------------------------------------------------------------------------+
    • Example 12: Check whether the t6 non-partitioned table is a transactional table.
      Note We 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 to display transactional information.
      desc extended t6;
      The following information 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

View the partition information about a partitioned table.

  • Syntax
    desc <table_name> partition (<pt_spec>);
  • Parameters
    • table_name: required. The name of the partitioned table whose partition information you want to query.
    • pt_spec: required. The information about the partition that you want to query. 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 partition key columns.
  • Example
    -- Query information about the sale_detail partitioned table.
    desc sale_detail partition (sale_date='201310',region='beijing');
    The following information is returned:
    +------------------------------------------------------------------------------------+
    | PartitionSize: 2109112                                                             |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2015-10-10 08:48:48                                      |
    | LastDDLTime:              2015-10-10 08:48:48                                      |
    | LastModifiedTime:         2015-10-11 01:33:35                                      |
    +------------------------------------------------------------------------------------+
    OK

View the CREATE TABLE statement

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

  • Example
    -- View the CREATE TABLE statement that is used to create the sale_detail table.
    show create table sale_detail;
    The following information is returned:
    CREATE TABLE IF NOT EXISTS doc_test_dev.sale_detail(shop_name STRING,customer_id STRING,total_price DOUBLE) PARTITIONED BY (sale_date STRING,region STRING) STORED AS ALIORC;

List tables and views in a project

List all tables and views or the tables and views that meet specific rules including regular expressions 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>';
  • Example
    -- List the tables whose names contain the sale* keyword in a project. The asterisk (*) indicates any characters.
    show tables like 'sale*';              
    The following information is returned:
    ALIYUN$account_name:sale_detail
    ......
    -- ALIYUN is a system prompt. It indicates that the table was created by using an Alibaba Cloud account. If the table was created by a RAM user, the system prompt is RAM.

List partitions

List all partitions of a table. An error is returned if the table does not exist or the table is a non-partitioned table.

  • Syntax
    show partitions <table_name>; 
  • Parameters

    table_name: required. The name of the partitioned table whose partition information you want to query.

  • Example
    -- List all partitions of the sale_detail table.
    show partitions sale_detail;
    The following information is returned:
    sale_date=201310/region=beijing
    sale_date=201312/region=shenzhen
    sale_date=201312/region=xian
    sale_date=2014/region=shenzhen
    
    OK