All Products
Search
Document Center

MaxCompute:Table operations

Last Updated:Dec 22, 2023

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 the operations that you can perform on tables.

The following table describes the supported operations on tables.

Operation

Description

Authorized user

Platform

Create a table

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

Users who have the CreateTable permission on a project

You can perform the operations by using the following platforms:

Change the owner of a table

Changes the owner of a table.

Project owner

Modify the comment of a table

Modifies the comment of a table.

Users who have the Alter permission on tables

Change the value of LastModifiedTime

Changes the value of LastModifiedTime for a table to the current time.

Modify the clustering attribute of a table

Adds or removes the clustering attribute to or from a table.

Rename a table

Renames a table.

Clear data from a non-partitioned table

Clears data from a specified non-partitioned table.

Drop a table

Drops a partitioned table or a non-partitioned table.

Users who have the Drop permission on tables

View the information about tables or views

Views 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 partition information

Views the details of partitions in a table.

View the CREATE TABLE statement

Views the SQL DDL statement that is used to create a specified table.

Display tables and views in a project

Displays all tables and views in a project 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. If the table does not exist or the table is a non-partitioned table, an error is returned.

Create a table

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

  • Limits

    • A partitioned table can have a maximum of six levels of partitions. For example, if a table uses date columns as partition key columns, the six levels of the partitions are year/month/week/day/hour/minute.

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

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

  • Syntax

    -- Create a table. 
     create [external] table [if not exists] <table_name>
     [primary key (<pk_col_name>, <pk_col_name2>),(<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 a 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>] 
    
    -- Set the table to a Transaction Table 1.0 table. You can later modify or delete the data of the Transaction Table 1.0 table. Transaction Table 1.0 tables have specific limits. Create a Transaction Table 1.0 table base on your business requirements. 
     [tblproperties("transactional"="true")]
    
    -- Set the table to a Transaction Table 2.0 table. You can perform the UPSERT operation to write data to the table, and perform incremental queries and time travel queries on the table.
     [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle <days>]
    ;
    
    -- Create a table based on an existing table and replicate data from the existing table to the new table. Partition properties are not replicated. You can execute the CREATE TABLE statement to create a table based on an external table or an existing table of an external project that is used to implement the data lakehouse solution. 
    create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;
    
    -- Create a table based on an existing table and replicate the schema of the existing table. You can execute the CREATE TABLE statement to create a table based on an external table or an existing table of an external project that is used to implement the data lakehouse solution. 
    create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];
  • Parameters

    • external: optional. This parameter specifies that the table 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 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 the table that you want to create. 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 the 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.

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

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

      Note

      The default value of the specified column cannot be a function, such as getdate() or now().

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

    • 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 LastModifiedTime. Unlike non-partitioned tables, a partitioned table is not deleted even if all of its partitions have been reclaimed. You can configure lifecycles for tables, but not for partitions.

  • Parameters for partitioned tables

    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 the MaxCompute V1.0 data type edition, partition key columns must be of the STRING type. In the MaxCompute V2.0 data type edition, partition key columns can be of the TINYINT, SMALLINT, INT, BIGINT, VARCHAR, or STRING type. For more information about data types, 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. The value of a partition key column must start with a letter and can contain letters, digits, and supported special characters. It must be 1 to 255 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 escape characters \t, \n, and /.

  • Parameters for clustered tables

    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 better execute concurrent statements, we recommend that you specify columns that have a large value range and a small number of duplicate key values in CLUSTERED BY. In addition, to optimize the JOIN operation, we recommend that you select commonly used join or aggregation keys. Join and aggregation 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 configuration 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 amount of data. 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.stage.reducer.num =<Number of concurrent reducers>; command to increase the maximum number of hash buckets. The maximum number of hash buckets cannot exceed 4,000. If the number of hash buckets exceeds 4,000, computing 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 the partition size is 500 GB, we recommend that you specify 1,000 hash buckets. This way, the size of each hash bucket is 500 MB on average. If a table contains a large amount 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.stage.reducer.num =<Number of concurrent reducers>; command to set the maximum number of hash buckets to a value that is larger than 1111.

      • To optimize the performance of the JOIN operation, we recommend that you do not configure the shuffle and sort properties for hash-clustered tables. The number of hash buckets of a table must be a multiple of the number of hash buckets of the other table. 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, or 4096. This way, MaxCompute can automatically split and merge hash buckets. To ensure that the execution is more efficient, we recommend that you do not configure the shuffle and sort properties.

    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 amount of data.

    • 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 manage flags to disable shuffling. This improves execution efficiency. You can set odps.optimizer.enable.range.partial.repartitioning to true or false to control shuffling. By default, this parameter is set to false. The default value 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 execute only the INSERT OVERWRITE statement to add data to a clustered table.

        • The 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.

        • The data backup and restoration feature is not supported.

  • Parameters for external tables

    • stored by StorageHandler: optional. The StorageHandler specified 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 Create an OSS external table.

  • Parameters for Transaction Table 1.0 tables and Transaction Table 2.0 tables

    Note
    • If you specify only tblproperties("transactional"="true") for a table, the table is a Transaction Table 1.0 table. If you also specify a primary key or other transaction property parameters for a table, the table is a Transaction Table 2.0 table.

    • The Transaction Table 2.0 feature is in invitational preview. By default, this feature cannot be used. If the Transaction Table 2.0 feature is used in the following examples, you can submit a ticket on the trail application page of MaxCompute. Then, you can use the Transaction Table 2.0 feature to run commands. For more information about how Transaction Table 2.0, see Overview of Transaction Table 2.0.

    Parameters for Transaction Table 1.0 tables

    tblproperties("transactional"="true"): optional. Set the table to a Transaction Table 1.0 table. You can perform the UPDATE or DELETE operation on the Transaction Table 1.0 table to update or delete data by rows. For more information, see UPDATE and DELETE.

    A Transaction Table 1.0 table has the following limits:

    • MaxCompute allows you to set a table to a Transaction Table 1.0 table only when you create the table. If you execute the ALTER TABLE statement to change an existing table to a Transaction Table 1.0 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 set it to a Transaction Table 1.0 table.

    • You cannot convert between Transaction Table 1.0 tables and MaxCompute internal tables, external tables, or clustered tables.

    • Transaction Table 1.0 table files cannot be automatically merged. You must manually merge Transaction Table 1.0 table files. For more information, see the ALTER TABLE COMPACT section in UPDATE and DELETE.

    • The MERGE PARTITION operation is not supported.

    • Specific limits are imposed on access to Transaction Table 1.0 tables from jobs of other systems. For example, if your job is a Graph job, you cannot use the job to read data from or write data to a Transaction Table 1.0 table. If your job is a Spark job or a Machine Learning Platform for AI (PAI) job, you can use the job to only read data from a Transaction Table 1.0 table and you cannot use the job to write data to a Transaction Table 1.0 table.

    • Before you execute the UPDATE, DELETE, or INSERT OVERWRITE statement on important data in Transaction Table 1.0 tables, you must execute the SELECT and INSERT statements to back up the data to other tables.

    Parameters for Transaction Table 2.0 tables

    • primary key:

      This parameter is required when you create a Transaction Table 2.0 table. You can specify multiple columns as the primary key. You must comply with the standard SQL syntax for primary keys. The not null property must be configured for the primary key and cannot be modified. After you specify a primary key for a Transaction Table 2.0 table, duplicate data is removed from the table based on the primary key. The uniqueness constraint for the primary key column is valid in a single partition or in a non-partitioned table.

    • tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])]

      • transactional: This parameter is required when you create a Transaction Table 2.0 table. You must set this parameter to true. The true value indicates that the table complies with the transaction characteristics of MaxCompute atomicity, consistency, isolation, durability (ACID) tables and the Multi Version Concurrency Control (MVCC) model is used to support snapshot isolation.

      • write.bucket.num: optional. Default value: 16. Valid values: (0,4096]. This parameter indicates the number of buckets in a partition of a partitioned table or in a non-partition table. This parameter also specifies the number of concurrent nodes that are used to write data. You can change the value of this parameter for a partitioned table. If new partitions are added to a partitioned table, the configuration of this parameter takes effect on the new partitions by default. You cannot change the value of this parameter for a non-partitioned table. Take note of the following points:

        • If data is written by using a MaxCompute tunnel, the value of this parameter specifies the number of concurrent nodes that are used to write data. The setting of the parameter affects the import traffic and is also subject to the maximum number of concurrent nodes in the tunnel.

        • If data is written by using an SQL statement, the value of this parameter specifies the concurrency of the reducers that are used to write data. The setting is subject to the maximum number of concurrent reducer nodes.

        • We recommend that you write approximately 500 MB of data to each bucket. For example, if the partition size is about 500 GB, we recommend that you specify 1,000 buckets. This way, the size of each bucket is 500 MB on average. If a table contains a large amount of data, you can increase the size of each bucket from 500 MB to a size in the range of 2 GB to 3 GB.

      • acid.data.retain.hours: optional. Default value: 72. Valid values: [0,168]. The time range during which the historical data status can be queried by using the time travel feature. Unit: hours.

        • If you set this parameter to 0, the historical data status is not retained, and time travel is not supported.

        • If the historical data status is retained for a period of time that is not in the range that is specified by this parameter, the data can be deleted. You can use the compact method to reclaim the space that is occupied by the data.

        • If you perform a time travel query on data that is generated earlier than the time range specified by this parameter, an error is returned. For example, if the value of this parameter is 72 hours, and the time travel query is performed to query the historical data status 72 hours ago, an error is returned.

      • acid.incremental.query.out.of.time.range.enabled: optional. Default value: false. If you set this parameter to true, the value of the endTimestamp property specified by an incremental query can be a point in time that is later than the maximum commit time of data in a table. If the value of the endTimestamp property is greater than the current time, new data may be inserted into a Transaction Table 2.0 table, and you may obtain different results for multiple queries. You can change the value of this parameter for a table.

    • Parameter settings for other common parameters of Transaction Table 2.0 tables

      • lifecycle: the lifecycle of the table. Unit: days. The lifecycle of the table must be greater than or equal to the value of the following formula: acid.data.retain.hours/24. The value of this formula indicates the number of days during which the historical data status can be queried by using the time travel feature. When you create a table, MaxCompute checks the lifecycle of the table that you specified. If the specified lifecycle does not meet the requirements, an error is returned.

      • cluster by: This parameter is not supported for Transaction Table 2.0 tables. A Transaction Table 2.0 table uses a storage structure that is similar to a hash clustered table by default. If you configure this parameter for the Transaction Table 2.0 table, a conflict may occur.

      • Other unsupported features: Transaction Table 2.0 tables do not support the RANGE CLUSTER BY and CREATE TABLE AS statements and cannot be used as external tables.

    • Other limits:

      • Only MaxCompute SQL can use Transaction Table 2.0 tables. Jobs of other engines, such as Spark and Hologres, cannot use Transaction Table 2.0 tables.

      • You cannot change existing common tables to Transaction Table 2.0 tables.

  • Create a table based on existing data or tables

    • You can execute the create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>; statement to create a table and replicate data to the table.

      • However, partition properties and the lifecycle property of the source table are not replicated to the created table. The partition key columns of the source table are considered common columns in the created table.

      • You can configure the lifecycle parameter to reclaim the table. You can also execute this statement to create an internal table and replicate data of an external table to the internal table.

    • You can execute the create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>]; statement to create a table that has the same schema as the source table.

      • However, tables created by using this statement do not replicate table data or the lifecycle property of the source table.

      • You can configure the lifecycle parameter to reclaim the table. You can also execute this statement to create an internal table that has the same schema as an existing external table.

  • 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 sale_detail_ctas1, replicate data from the sale_detail table to the sale_detail_ctas1 table, and then configure the lifecycle for the sale_detail_ctas1 table.

      SET odps.sql.allow.fullscan=true;
      create table sale_detail_ctas1 lifecycle 10 as select * from sale_detail;

      You can run the desc extended sale_detail_ctas1; command to view table details, such as the schema and lifecycle of a table.

      The sale_detail table is a partitioned table, but the sale_detail_ctas1 table that is created by using create table ... as select_statement ... does not replicate partition properties. The partition key columns of the source table are considered common columns in the table that is created. The sale_detail_ctas1 table is a non-partitioned table that contains five columns.

    • Example 4: Create the sale_detail_ctas2 table and use constants as column values in the SELECT clause.

      SET odps.sql.allow.fullscan=true;
      -- 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 a table named sale_detail_like that uses the same schema as the sale_detail table and configure the lifecycle for the sale_detail_like table.

      create table sale_detail_like like sale_detail lifecycle 10;

      You can run the desc extended sale_detail_like; command to view table details, such as the schema and lifecycle of a table.

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

    • Example 6: Create a table named mc_oss_extable_orc_like that uses the same schema as the external table mc_oss_extable_orc.

      create table mc_oss_extable_orc_like like mc_oss_extable_orc;

      You can run the desc mc_oss_extable_orc_like; command to view table details, such as the schema of a table.

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$****@***.aliyunid.com | Project: max_compute_7u************yoq              |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2022-08-11 11:10:47                                      |
      | LastDDLTime:              2022-08-11 11:10:47                                      |
      | LastModifiedTime:         2022-08-11 11:10:47                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | id              | string     |       |                                             |
      | name            | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • Example 7: Create a table named test_newtype 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 8: Create a hash-clustered table named t1. 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 9: Create a hash-clustered table named t2. 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 10: Create a range-clustered table named t3. 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 11: Create a range-clustered table named t4. 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 12: Create a Transaction Table 1.0 table named t5. This table is a non-partitioned table.

      create table t5(id bigint) tblproperties("transactional"="true");
    • Example 13: Create a Transaction Table 1.0 table named t6. This table is a partitioned table.

      create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");
    • Example 14: Create a non-partitioned table named test_default and configure default values for the parameters.

      create table test_default
      (
      tinyint_name tinyint not NULL default 1Y,
      smallint_name SMALLINT not NULL DEFAULT 1S,
      int_name INT not NULL DEFAULT 1,
      bigint_name BIGINT not NULL DEFAULT 1,
      binary_name BINARY ,
      float_name FLOAT ,
      double_name DOUBLE not NULL DEFAULT 0.1,
      decimal_name DECIMAL(2, 1) not NULL DEFAULT 0.0BD,
      varchar_name VARCHAR(10) ,
      char_name CHAR(2) ,
      string_name STRING not NULL DEFAULT 'N',
      boolean_name BOOLEAN not NULL DEFAULT TRUE
      );
    • Example 15: Execute the CREATE TABLE [if not exists] <table_name> [lifecycle <days>] as <select_statement>; statement to create an internal table and replicate data of a partitioned external table to the internal table. Partition properties are not replicated to the internal table.

      -- Query the external table of an external project that is used to implement the data lakehouse solution in MaxCompute.
      select * from hive_external2_1.myhive_0110;
      -- The following result is returned:
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      -- Execute the CREATE TABLE AS statement to create an internal table.
      create table from_exetbl_as_par as select * from hive_external2_1.myhive_0110_par;
      
      -- Query the internal table.
      select * from from_exetbl_as_par;
      -- All data in the internal table is returned.
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      
      -- Query the schema of the internal table.
      desc from_exetbl_as_par;
      -- The following result is returned:
      +------------------------------------------------------------------------------------+
      | Owner:                    ALIYUN$***********                                       |
      | Project:                  ***_*****_***                                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2023-01-10 15:16:33                                      |
      | LastDDLTime:              2023-01-10 15:16:33                                      |
      | LastModifiedTime:         2023-01-10 15:16:33                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 919                                                |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | a               | string     |       |                                             |
      | b               | string     |       |                                             |
      | c               | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • Example 16: Execute the CREATE TABLE [if not exists] <table_name> like <existing_table_name> [lifecycle <days>]; statement to create an internal table and replicate the schema of a partitioned external table to the internal table. Partition properties are replicated to the internal table.

      -- Query the external table of an external project that is used to implement the data lakehouse solution in MaxCompute.
      select * from hive_external2_1.myhive_0110_par;
      -- The following result is returned:
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      -- Execute the CREATE TABLE LIKE statement to create an internal table.
      create table from_exetbl_like like hive_external2_1.myhive_0110_par;
      
      -- Query the internal table.
      select * from from_exetbl_like;
      -- Only the schema of the internal table is returned.
      a    b    c
      
      -- Query the schema of the internal table.
      desc from_exetbl_like;
      -- The following result is returned:
      +------------------------------------------------------------------------------------+
      | Owner:                    ALIYUN$************                                      |
      | Project:                  ***_*****_***                                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2023-01-10 15:09:47                                      |
      | LastDDLTime:              2023-01-10 15:09:47                                      |
      | LastModifiedTime:         2023-01-10 15:09:47                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | a               | string     |       |                                             |
      | b               | string     |       |                                             |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | c               | string     |                                                     |
      +------------------------------------------------------------------------------------+
    • Example 17: Create a Transaction Table 2.0 table.

      create table mf_tt (pk bigint not null primary key, 
                          val bigint) 
                          tblproperties ("transactional"="true");
    • Example 18. Create a Transaction Table 2.0 table and configure the main table properties.

      create table mf_tt2 (pk bigint not null, 
                        pk2 bigint not null, 
                        val bigint, 
                        val2 bigint, primary key (pk, pk2)
                       ) 
                   tblproperties ("transactional"="true", 
                                  "write.bucket.num" = "64", 
                                  "acid.data.retain.hours"="120") 
                   lifecycle 7;

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. If you want to change the owner of a table to a RAM user, you must set this parameter to a value in the RAM$<UID>:<ram_name> format. UID indicates the ID of the Alibaba Cloud account and ram_name indicates the display name of the RAM user.

      Note

      If you want to change the owner of a table to a RAM user, make sure that the RAM user is added to the project to which the table belongs.

  • Examples

    • Change the owner of the test1 table to ALIYUN$xxx@aliyun.com.

      alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';
    • Change the owner of the test1 table to a RAM user named ram_test.

      alter table test1 changeowner to 'RAM$13xxxxxxxxxxx:ram_test';

Modify the comment of a table

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

  • Examples

    alter table sale_detail set comment 'new coments for table sale_detail';

    You can execute the DESC table_name statement of MaxCompute to view the change result of the comment in the table.

Change the value of LastModifiedTime

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

  • Syntax

    alter table <table_name> touch;
  • Parameters

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

  • Examples

    alter table sale_detail touch;

Modify the clustering attribute of a table

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

  • Syntax

    • Syntax of the statement that is used to add the hash clustering attribute for 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];
    • Syntax of the statement that is used to remove the hash clustering attribute from a table:

      alter table <table_name> not clustered;
    • If you do not specify the number of buckets in a range-clustered table, MaxCompute automatically determines the optimal number based on the amount of data. 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];
    • Syntax of the statement that is used to 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 is suitable for existing tables. After you specify the clustering attribute, new partitions are stored based on the clustering attribute that you specified.

      • ALTER TABLE takes effect only on the new partitions in a partitioned table. The new partitions include those generated by using INSERT OVERWRITE and are stored based on the new clustering attribute. The clustering attribute and storage method remain unchanged for the original partitions. After you specify the clustering attribute for a table, you can remove the clustering attribute and add a clustering attribute for the table again. You can specify different clustering columns, sort columns, and numbers of buckets for new partitions.

      • ALTER TABLE 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

Renames a table. After you rename a table, only the name of the 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 that you want to rename.

    • new_table_name: required. The new name of the table. If the name specified by the new_table_name parameter exists, an error is returned.

  • Examples

    alter table sale_detail rename to sale_detail_rename;

Clear data from a non-partitioned table

Clears data from a specified non-partitioned table. For more information about how to clear data from one or more partitions in a partitioned table, see Clear data from a partition.

  • Syntax

    truncate table <table_name>;
  • Parameters

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

Drop a table

Drops a non-partitioned table or partitioned table.

  • Precautions

    • Before you drop a table, confirm that the table can be dropped. Proceed with caution. If you accidentally drop a table, you can restore the table if the backup and restoration feature is enabled for the project and the table is dropped within the backup data retention period specified for the project. For more information about the backup and restoration feature, see Backup and restoration.

    • After you drop a table, the volume of stored data in a MaxCompute project decreases.

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

  • Examples

    -- 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 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. You can also execute this statement to view extended information about an internal 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 whose information you want to view.

    • 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 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     |       |                                             |
      +------------------------------------------------------------------------------------+
    • Example 2: 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     |                                     |
      +--------------------------------------------------------------------+
    • Example 3: 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_date and region columns are considered as common columns. They are not partition key columns.

    • Example 4: 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     |       |                             |
      +--------------------------------------------------------------------+
    • Example 5: View the details 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                                                   |
      +------------------------------------------------------------------------------------+

      Aside from the lifecycle configuration, the properties, 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 data size in the output of the DESC table_name statement includes the data size of 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 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) |       |                                            |
      +------------------------------------------------------------------------------------+
      
      OK
    • Example 7: View the information of 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 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]                                                  |
      +------------------------------------------------------------------------------------+
      
      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 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]                                                               |
      +------------------------------------------------------------------------------------+
      
      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 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]                                                  |
      +------------------------------------------------------------------------------------+
      
      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 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]                                                  |
      +------------------------------------------------------------------------------------+
      
      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 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                                                     |
      +------------------------------------------------------------------------------------+
    • Example 12: Check whether the partitioned table t6 is a transactional table. Sample statement:

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

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.

  • Examples

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

View the CREATE TABLE statement

Views 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 doc_test_dev.sale_detail(shop_name STRING,customer_id STRING,total_price DOUBLE) PARTITIONED BY (sale_date STRING,region STRING) STORED AS ALIORC;

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 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>';
  • Examples

    -- 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>; 
  • Parameters

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

  • Examples

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

Delete data from columns

Deletes data from columns. You can execute the ALTER TABLE CLEAR COLUMN statement to delete data from columns that are no longer required in a common table from a disk and set the values in the columns to null. This can reduce storage costs.

  • Syntax

    ALTER TABLE <table_name> 
               [partition ( <pt_spec>[, <pt_spec>....] )] 
    					 CLEAR COLUMN column1[, column2, column3, ...]
    			                         [without touch];
  • Parameters

    • table_name: the name of the table on which you want to execute the ALTER TABLE CLEAR COLUMN statement.

    • column1 , column2...: the names of columns from which data needs to be deleted.

    • partition: the partition on which you want to execute the ALTER TABLE CLEAR COLUMN statement. If no partition is specified, the statement is executed on all partitions in the table.

    • pt_spec: the description of the partition. Configure this parameter in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format.

    • without touch: If you configure this parameter, the time when the data was last modified is not updated. If you do not configure this parameter, the time is updated.

      Note

      By default, the without touch parameter is configured. You can also delete data from columns in the future even if the without touch parameter is not configured. If you do not configure the without touch parameter, the time when the data was last modified is updated.

For more information about the limits and examples about deleting data from columns, see ALTER TABLE CLEAR COLUMN.

References

  • For more information about how to perform operations on columns in a MaxCompute table, such as adding columns, dropping columns, or changing the data type of a column, see Partition and column operations.

  • For more information about how to clone data from one table to another, see CLONE TABLE.

  • For more information about how to delete or update data of specific rows in transactional tables, see UPDATE and DELETE.