Table operations

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

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

• if not exists: optional. If you create a table by using the name of an existing table but do not specify the if not exists parameter, an error is returned. If you specify the if not exists parameter, a success message is returned when you create a table by using the name of an existing table. The success message is returned even if the schema of the existing table is different from that of the table you want to create. If you create a table by using the name of an existing table, the table is not created and the metadata of the existing table is not changed.
• table_name: required. The name of the table. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). It must start with a letter and cannot contain special characters. It is not case-sensitive. If the value of this parameter does not meet the requirements, an error is returned.
• col_name: optional. The name of a table column. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). It must start with a letter and cannot contain special characters. It is not case-sensitive. If the value of this parameter does not meet the requirements, an error is returned.
• col_comment: optional. The comment of a column. The comment must be a valid string and can be up to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
• table_comment: optional. The comment of the table. The comment must be a valid string and can be up to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
• data_type: optional. The data type of a column. The following data types are supported: BIGINT, DOUBLE, BOOLEAN, DATETIME, DECIMAL, and STRING. For more information, see Data types.
• not null: optional. If you specify this parameter for a column, the values of the column cannot be NULL. For more information about how to modify the NOT NULL attribute, see Partition and column operations.
• partitioned by (<col_name> <data_type> [comment <col_comment>], ...: optional. The partition fields of the specified partitioned table.
  • col_name: the name of a partition key column. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). It must start with a letter and cannot contain special characters. It is not case-sensitive. If the value of this parameter does not meet the requirements, an error is returned.
  • data_type: the data type of a partition key column. The following data types are supported: BIGINT, DOUBLE, BOOLEAN, DATETIME, DECIMAL, and STRING. In MaxCompute V1.0, partition key columns must be of the STRING type. In MaxCompute V2.0, partition key columns can be of the TINYINT, SMALLINT, INT, BIGINT, VARCHAR, or STRING types. For more information, see Data types. If you use a partition field to partition a table, a full table scan is not required when you add partitions, update partition data, or read partition data. This improves the efficiency of data processing.
  • col_comment: the comment of a partition key column. The comment must be a valid string and can be up to 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
  Note The value of a partition key column cannot contain double-byte characters. It must start with a letter and can contain letters, digits, and specific supported special characters. It can be up to 128 bytes in length. The following special characters are supported: spaces, colons (:), underscores (_), dollar signs ($), number signs (#), periods (.), exclamation points (!), and at signs (@). The behavior of other characters, such as escaped characters \t, \n, and /, is not defined.
• lifecycle: optional. The lifecycle of the table. The value must be a positive integer. Unit: days.
  • Non-partitioned tables: If data in a non-partitioned table remains unchanged for the number of days specified by days after the last data modification, MaxCompute executes a statement such as DROP TABLE to reclaim the table.
  • Partitioned tables: MaxCompute determines whether to reclaim a partition based on the value of LastDataModifiedTime. Unlike non-partitioned tables, a partitioned table is not deleted even if all of its partitions are reclaimed. You can configure lifecycles for tables, but not for partitions.
• create table [if not exists] <table_name> [as <select_statement> | like <existing_table_name>]:
  • If you execute the CREATE TABLE…AS select_statement… statement to create a table with data replicated from another table, the replicated information does not include partition attributes. This is because partition key columns in the source table are considered regular columns in the created table.
  • If you execute the CREATE TABLE…LIKE existing_table_name statement to create a table, the destination table may have the same schema as the source table. However, the destination table does not replicate data or lifecycle settings from the source table.

• Example 1: Create a non-partitioned table that is named test1.

  create table test1 (key STRING);

• Example 2: Create a partitioned table that is named sale_detail.

  create table if not exists sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  partitioned by (sale_date STRING, region STRING); 

• Example 3: Create a table that is named test3 and set a lifecycle for the table.

  create table test3 (key BOOLEAN) partitioned by (pt STRING, ds STRING) lifecycle 100;

• Example 4: Create a table that is named test4 based on the test3 table. All properties such as the field type and partition type of the test4 table except its lifecycle are the same as those of the test3 table.

  create table test4 like test3;

• Example 5: Create a table that is named test5 based on the test3 table and copy the data of the test3 table, except its lifecycle, to the test5 table.

  create table test5 AS select * from test3;

alter table <table_name> changeowner to <new_owner>;

• table_name: required. The name of the table whose owner you want to change.
• new_owner: required. The new owner of the table.

-- Change the owner of the test1 table to ALIYUN$xxx@aliyun.com.
alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';

drop table [if exists] <table_name>; 

• if exists: optional. If you do not specify the if exists parameter and the specified table does not exist, an error is returned. If you specify the if exists parameter, a success message is returned regardless of whether the specified table exists.
• table_name: required. The name of the table that you want to delete.

-- Delete the sale_detail table. A success message is returned regardless of whether the sale_detail table exists.
drop table if exists sale_detail; 

-- View the information about the table or view.
desc <table_name|view_name>; 
-- View the information about the external table.
desc extended <table_name>; 

• 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: specifies that you want to view the information about an external table. This parameter is required if the table you want to view is an external table.

• Example 1: Query the information about the sale_detail partitioned table.

  desc sale_detail;               

  The following information 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 sale_detail_view view.

  desc sale_detail_view;

  The following information 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

desc <table_name> partition (<pt_spec>);

• table_name: required. The name of the partitioned table whose partition information you want to query.
• pt_spec: required. The information about the partition that you want to query. The value of this parameter is in the partition_col1=col1_value1, partition_col2=col2_value1... format. If a table has multi-level partitions, you must specify the values of all partition key columns.

-- Query information about the sale_detail partitioned table.
desc sale_detail partition (sale_date='201310',region='beijing');

+------------------------------------------------------------------------------------+
| PartitionSize: 2109112                                                             |
+------------------------------------------------------------------------------------+
| CreateTime:               2015-10-10 08:48:48                                      |
| LastDDLTime:              2015-10-10 08:48:48                                      |
| LastModifiedTime:         2015-10-11 01:33:35                                      |
+------------------------------------------------------------------------------------+
OK

-- List all tables and views in a project.
show tables;
-- List the tables or views whose names contain the chart keyword in a project.
show tables like '<chart>';

-- List the tables whose names contain the sale* keyword in a project. The asterisk (*) indicates any characters.
show tables like 'sale*';              

ALIYUN$account_name:sale_detail
......
-- ALIYUN is a system prompt. It indicates that the table was created by using an Alibaba Cloud account. If the table was created by a RAM user, the system prompt is RAM.

show partitions <table_name>; 

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

-- List all partitions of the sale_detail table.
show partitions sale_detail;

sale_date=201310/region=beijing
sale_date=201312/region=shenzhen
sale_date=201312/region=xian
sale_date=2014/region=shenzhen

OK