Tables are the units that are used to store data in MaxCompute. Table data must be processed when you develop, analyze, and maintain a data warehouse. This topic describes common table operations, such as create, delete, and view tables.
The following table describes common statements that are used for table operations. For more information about table operations, see Table operations.
Operation | Description | Role | Operation platform |
---|---|---|---|
Create a table | Creates a non-partitioned table or partitioned table. | Users who have the CREATE TABLE permission in a project | You can execute the statements described in this topic on the following platforms: |
Change the owner of a table | Changes the owner of a table. | The project owner | |
Delete a table | Deletes a non-partitioned table or partitioned table. | Users who have the DROP permission on tables | |
View table or view information | Views the information of MaxCompute views, internal tables, or external tables. | Users who have the DESCRIBE permission to read the metadata of a table | |
View partition information | Views the partition information of a partitioned table. | Users who have the DESCRIBE permission to read the metadata of a table | |
List tables and views in a project | Lists all tables and views or the tables and views that meet specific rules (including regular expressions) in a project. | Users who have the LIST permission on the objects in a project | |
List partitions | Lists all the partitions of a table. | Users who have the LIST permission on the objects in a project |
For more information about the operations on partitions and columns, see Partition and column operations.
For more information about the operations on the lifecycle of tables, see Lifecycle management operations.
Create a table
Creates a non-partitioned table or partitioned table.
- Limits
- A partitioned table can have a maximum of six levels of partitions.
- By default, a table can have a maximum of 60,000 partitions. You can adjust the maximum number of partitions in a table based on your project configurations.
- Syntax
-- Create a table. create table [if not exists] <table_name> [(<col_name> <data_type> [comment <col_comment>], ...)] [comment <table_comment>] [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)] [lifecycle <days>]; -- Create a table based on an existing table. create table [if not exists] <table_name> [as <select_statement> | like <existing_table_name>];
- Parameters
- if not exists: optional. If a table that has the same name as the table you want to create exists and you do not specify the if not exists option, an error is returned. If you specify the if not exists option when you create a table, a success message is returned regardless of whether a table with the same name exists, even if the schema of this table is different from that of the table you want to create. If the table you want to create has the same name as an existing table, the table is not created and the metadata of the existing table is not changed.
- table_name: required. The name of a table. The name must be 1 to 128 bytes in length, and can contain letters, digits, and underscores (_). 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 that is a maximum of 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.
- table_comment: optional. The comment of a table. The comment must be a valid string that is a maximum of 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 Date types.
- partitioned by (<col_name> <data_type> [comment <col_comment>], ...: optional. The partition field 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 (_). 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 Date 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 that is a maximum of 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 some supported special characters. It can be a maximum of 128 bytes in length. Supported special characters are 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/
. - lifecycle: optional. The lifecycle of a 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. The reason is that partition key columns in the source table are considered common columns in the created table.
- If you execute the CREATE TABLE…LIKE existing_table_name statement to create a table, the destination table may have the same schema as the source table. However, the destination table does not replicate data or lifecycle settings from the source table.
- Examples
- Example 1: Create a non-partitioned table named test1.
create table test1 (key STRING);
- Example 2: Create a partitioned table named sale_detail.
create table if not exists sale_detail( shop_name STRING, customer_id STRING, total_price DOUBLE) partitioned by (sale_date STRING, region STRING);
- Example 3: Create a table named test3 with a lifecycle.
create table test3 (key BOOLEAN) partitioned by (pt STRING, ds STRING) lifecycle 100;
- Example 4: Create a table named test4 based on the test3 table. Make sure that all
properties (such as 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 the test5 table 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;
- Example 1: Create a non-partitioned table named test1.
Change the owner of a table
Changes the owner of a table.
- Syntax
alter table <table_name> changeowner to <new_owner>;
- Parameters
- table_name: required. The name of the table whose owner you want to change.
- new_owner: required. The new owner of the table.
- Example
-- Change the owner of the test1 table to ALIYUN$xxx@aliyun.com. alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';
Delete a table
Deletes a non-partitioned table or partitioned table.
- Syntax
drop table [if exists] <table_name>;
- Parameters
- if exists: optional. If you do not specify the if exists option and the specified table does not exist, an error is returned. If you specify the if exists option, a success message is returned regardless of whether the specified table exists.
- table_name: required. The name of the table that you want to delete.
- Example
-- Delete the sale_detail table. A success message is returned regardless of whether the sale_detail table exists. drop table if exists sale_detail;
View table or view information
Views the information of MaxCompute views, internal tables, or external tables.
- Syntax
-- View the information of a table or view. desc <table_name|view_name>; -- View the information of an external table. desc extended <table_name>;
- Parameters
- table_name: required. The name of the table that you want to view.
- view_name: required. The name of the view that you want to view.
- extended: the external table. This parameter is required if the table you want to view is an external table.
- Returned results
The following information is returned:
- Owner: the owner of the table.
- Project: the project to which the table belongs.
- CreateTime: the time at which the table was created.
- LastDDLTime: the time at which a DDL statement was last executed.
- LastModifiedTime: the time at which the table was last modified.
- InternalTable: a property specific to a partitioned table or non-partitioned table. This parameter indicates whether the object is a table. The value of this parameter is always YES for tables.
- VirtualView: a property specific to a view. This parameter indicates whether the object is a view. The value of this parameter is always YES for views.
- Size: the storage space occupied by compressed table data, which is generally one fifth
the size of the original data. Unit: bytes.
Note The size of table data in the execution results of
DESC table_name
includes the size of the data in the recycle bin. To view the size of table data excluding the data in the recycle bin, execute thePURGE TABLE table_name;
statement before you execute theDESC table_name
statement. To view the data in the recycle bin, execute theSHOW RECYCLEBIN;
statement. - ViewText: the view definition. For more information, see View-related operations.
- Native Columns: the information of non-partition key columns, including the column name, type, and comment.
- Partition Columns: a property specific to a partitioned table. This parameter indicates the information of partition key columns, including the column name, type, and comment.
- Extended Info: a property specific to an external table. This parameter indicates the information of an external table, such as StorageHandler and Location.
- Examples
- Example 1: Query the information of the partitioned table sale_detail.
Returned results:desc sale_detail;
+------------------------------------------------------------------------------------+ | 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 of the view sale_detail_view.
Returned results:desc sale_detail_view;
+------------------------------------------------------------------------------------+ | 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
- Example 1: Query the information of the partitioned table sale_detail.
View partition information
Views the partition information of a partitioned table.
- Syntax
desc <table_name> partition (<pt_spec>);
- Parameters
- table_name: required. The name of the partitioned table whose partition information you want to query.
- pt_spec: required. The information of the partition that you want to query. The value of
this parameter is in the
partition_col1=col1_value1, partition_col2=col2_value1...
format. For a table with multi-level partitions, you must specify all values of the partition key columns.
- Example
Returned results:-- Query information of the partitioned table sale_detail. 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 tables and views in a project
Lists all tables and views or the tables and views that meet specific rules (including regular expressions) in a project.
- Syntax
-- List all tables and views in a project. show tables; -- List the tables or views whose names contain the
chart
keyword in a project. show tables like '<chart>'; - Example
Returned results:-- List the tables whose names contain the
sale*
keyword in a project. The asterisk (*) indicates any fields. show tables like 'sale*';ALIYUN$account_name:sale_detail ...... -- ALIYUN is a system prompt. It indicates that the table was created by an Alibaba Cloud account. If you are a Resource Access Management (RAM) user of an Alibaba Cloud account, the system prompt is RAM.
List partitions
Lists all partitions of a table. An error is returned if the table does not exist or the table is a non-partitioned table.
- Syntax
show partitions <table_name>;
- Parameters
table_name: required. The name of the partitioned table whose partition information you want to query.
- Example
Returned results:-- 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