In MaxCompute, tables are used to store data. When you develop, analyze, and maintain a data warehouse, you must process table data. This topic describes common table operations, such as create, drop, and view tables.

The following table describes the common statements that are used for table operations. For more information about table operations, see Table operations.

Operation Description Role Platform
Create a table Creates a non-partitioned table or partitioned table. Users who have the CREATE TABLE permission on a project You can execute the statements that are described in this topic on the following platforms:
Change the owner of a table Changes the owner of a table. Project owner
Drop a table Drops a non-partitioned table or partitioned table. Users who have the DROP permission on tables
View the information about tables or views Views the information about MaxCompute views, internal tables, or external tables. Users who have the DESCRIBE permission on the metadata of a table
View partition information Views the partition information about a partitioned table. Users who have the DESCRIBE permission to read the metadata of a table
Display tables and views in a project Displays all the tables and views or the tables and views that meet specific rules, such as regular expressions, in a project. Users who have the LIST permission on objects in a project
Display partitions Displays all the partitions of a table. Users who have the LIST permission on objects in a project

For more information about the operations on partitions and columns, see Partition and column operations.

For more information about the operations on the lifecycle of tables, see Lifecycle management operations.

Create a table

Creates a non-partitioned table or partitioned table.

  • Limits
    • A partitioned table can have a maximum of six levels of partitions.
    • By default, a table can have a maximum of 60,000 partitions. You can adjust the maximum number of partitions in a table based on your business requirements.
  • Syntax
    -- Create a table. 
    create table [if not exists] <table_name>
     [(<col_name> <data_type> [not null] [comment <col_comment>], ...)]
     [comment <table_comment>]
     [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
     [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
    • 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 create a table by using the name of an existing table and specify the if not exists parameter, a success message is returned even if the schema of the existing table is different from the schema of the table that 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 remains unchanged.
    • table_name: required. The name of a table. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). The name must start with a letter and cannot contain special characters. The name 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 (_). The name must start with a letter and cannot contain special characters. The name 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 that is 1 to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
    • table_comment: optional. The comment of a table. The comment must be a valid string that is 1 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 about data types, see Data type editions.
    • 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 parameter, see Change the non-nullable property of a non-partition key column in a table.
    • partitioned by (<col_name> <data_type> [comment <col_comment>], ...: optional. The partition fields of a 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 (_). The name must start with a letter and cannot contain special characters. The name 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. In MaxCompute V1.0 data type edition, partition key columns must be of the STRING type. In MaxCompute V2.0 data type edition, partition key columns can be of the TINYINT, SMALLINT, INT, BIGINT, VARCHAR, or STRING type. For more information, see Data type editions. 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 that is 1 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, such as Chinese characters. It must start with a letter and can contain letters, digits, and supported special characters. It must be 1 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 is not defined, such as escaped characters \t, \n, and /.
    • 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 update, 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 dropped even if all the partitions in the table are reclaimed. You can configure lifecycles for tables, but not for partitions.
  • Examples
    • Example 1: Create a non-partitioned table named test1.
      create table test1 (key STRING);
    • Example 2: Create a partitioned table 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 a table named test3 with a lifecycle.
      create table test3 (key BOOLEAN) partitioned by (pt STRING, ds STRING) lifecycle 100;
    • Example 4: Create a table named test4 based on the test3 table. Make sure that all the properties, such as field type and partition type, of the test4 table except the lifecycle are the same as the properties of the test3 table.
      create table test4 like test3;
    • Example 5: Create a table named test5 based on the test3 table and copy the data of the test3 table, except the lifecycle, to the test5 table.
      create table test5 AS select * from test3;

Change the owner of a table

Changes 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';

Drop a table

Drops a non-partitioned table or a partitioned table.

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

Views the information about MaxCompute views, internal tables, or external tables.

  • Syntax
    -- View the information about a table or view. 
    desc <table_name|view_name>; 
    -- View the information about an external table. 
    desc extended <table_name>; 
  • Parameters
    • table_name: required. The name of the table that you want to view.
    • view_name: required. The name of the view that you want to view.
    • extended: the external table that you want to view. This parameter is required if the table that you want to view is an external table.
  • Returned results
    The following information is returned:
    • Owner: the owner of the table.
    • Project: the project to which the table belongs.
    • CreateTime: the time when the table was created.
    • LastDDLTime: the time when a data definition language (DDL) statement was last executed.
    • LastModifiedTime: the time when the table was last modified.
    • InternalTable: a property that is specific to a partitioned table or non-partitioned table. This parameter specifies whether the object is a table. The value of this parameter is always YES for tables.
    • VirtualView: a property that is specific to a view. This parameter indicates whether the object is a view. The value of this parameter is always YES for views.
    • Size: the storage space occupied by compressed table data. In most cases, the size of compressed table data is equivalent to 20% of the original data. Unit: bytes.
      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. 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 details about the data in the recycle bin for the current project.
    • ViewText: the definition of the view. For more information, see View-related operations.
    • Native Columns: the information about non-partition key columns, including the column name, type, and comment.
    • Partition Columns: a property that is specific to a partitioned table. This parameter indicates the information about partition key columns, including the column name, type, and comment.
    • Extended Info: a property that is specific to an external table. This parameter indicates the information about an external table, such as StorageHandler and Location.
  • Examples
    • Example 1: Query the information about the partitioned table sale_detail.
      desc sale_detail;               
      The following result is returned:
      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$****@aliyun.com | Project: test_project                              |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2014-01-01 17:32:13                                      |
      | LastDDLTime:              2014-01-01 17:57:38                                      |
      | LastModifiedTime:         1970-01-01 08:00:00                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Comment                                             |
      +------------------------------------------------------------------------------------+
      | shop_name       | string     |                                                     |
      | customer_id     | string     |                                                     |
      | total_price     | double     |                                                     |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | sale_date       | string     |                                                     |
      | region          | string     |                                                     |
      +------------------------------------------------------------------------------------+
    • Example 2: Query the information about the view sale_detail_view.
      desc sale_detail_view;
      The following result is returned:
      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$****@aliyun.com | Project: test_project                              |
      | TableComment: a view for table sale_detail                                         |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-19 16:18:35                                      |
      | LastDDLTime:              2020-11-19 17:27:22                                      |
      | LastModifiedTime:         2020-11-19 16:18:35                                      |
      +------------------------------------------------------------------------------------+
      | VirtualView  : YES  | ViewText: select shop_name, customer_id, total_price from sale_detail |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | store_name      | string     |       |                                             |
      | customer_id     | string     |       |                                             |
      | price           | double     |       |                                             |
      +------------------------------------------------------------------------------------+
      
      OK

View partition information

Views 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 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.
  • Example
    -- Query information about the partitioned table sale_detail. 
    desc sale_detail partition (sale_date='201310',region='beijing');
    The following result 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

Display tables and views in a project

Displays all the tables and views or the tables and views that meet specific rules in a project.

  • Syntax
    -- Display all the tables and views in a project. 
    show tables;
    -- Display the tables or views whose names contain the chart keyword in a project. 
    show tables like '<chart>';
  • Example
    -- Display the tables whose names contain the sale* keyword in a project. The asterisk (*) indicates any character. 
    show tables like 'sale*';              
    The following result is 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 was created by a RAM user, the system prompt is RAM. 

Display partitions

Displays 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>; 
  • Parameter

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

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