Table-level operations

Create a table

• Syntax

  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]
   [AS select_statement]
   CREATE TABLE [IF NOT EXISTS] table_name
   LIKE existing_table_name

• Features
  • Tables can be divided into partitioned tables and non-partitioned tables.
  • If you execute a CREATE TABLE statement without the IF NOT EXISTS clause to create a table that already exists, the system returns an error. If you execute a CREATE TABLE statement with the IF NOT EXISTS clause to create a table, the system returns a success message regardless of whether a table with the same name exists. If a table to be created shares the same name with an existing table, the table is not created and the metadata of the existing table is not changed.
• Parameters
  • table_name: the name of the table. The table name is not case-sensitive and cannot contain special characters. It can contain only letters, digits, and underscores (_). The name must start with a letter and can be up to 128 bytes in length. Otherwise, an error is returned.
  • col_name: the name of the column. The column name is not case-sensitive and cannot contain special characters. It can contain only letters, digits, and underscores (_). The name must start with a letter and can be up to 128 bytes in length. Otherwise, an error is returned.
  • col_comment: the comment about the column. The comment must be a valid string and can be up to 1,024 bytes in length. Otherwise, an error is returned.
  • table_comment: the comment about the table. The comment must be a valid string and can be up to 1,024 bytes in length. Otherwise, an error is returned.
  • data_type: the data type of the column. The following data types are supported: BIGINT, DOUBLE, BOOLEAN, DATETIME, DECIMAL, and STRING. For more information, see Date types.
  • PARTITIONED BY: the partition key of the table. In MaxCompute V1.0, partition key columns must be of the STRING type. MaxCompute V2.0 supports partition key columns of the TINYINT, SMALLINT, INT, BIGINT, VARCHAR, and STRING types. If you use a partition key to partition a table, a full table scan is not required when you add partitions, update partition data, or read partition data. This improves processing efficiency.
    Note

    • A partition key value cannot contain double-byte characters. It must start with a letter and can contain letters, digits, and some supported special characters. It can be up to 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 /.
    • A table can contain a maximum of six partition levels. By default, a table can contain a maximum of 60,000 partitions. You can adjust the maximum number of partitions for a table based the specific project.
  • LIFECYCLE: the lifecycle of the table. Only positive integers are supported. Unit: days.
    • Non-partitioned tables: If the 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 its LastDataModifiedTime value. 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.
    Note If you execute a CREATE TABLE LIKE statement to create a table based on the definition of another table, the lifecycle settings of the source table are not duplicated.
• Examples

  -- Create a partitioned table named sale_detail if no table with this name exists.
  CREATE TABLE IF NOT EXISTS sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  PARTITIONED BY (sale_date STRING,region STRING); 
  -- Create a non-partitioned table named test1. 
  CREATE TABLE test1 (key STRING);
  -- Create a partitioned table named test2.
  CREATE TABLE test2 (key BIGINT) PARTITIONED BY (pt STRING, ds STRING);
  -- Create a table named test3 with a lifecycle.
  CREATE TABLE test3 (key BOOLEAN) PARTITIONED BY (pt STRING, ds STRING) LIFECYCLE 100;
  -- Create a table named test4 based on the definition of the test3 table, without replicating the lifecycle settings. 
  CREATE TABLE test4 like test3;
  -- Create a table named test5 based on the data of the test2 table, without replicating the partition and lifecycle settings. This operation copies only the data of the test2 table to the test5 table. If the test2 table has no data, the test5 table is an empty table. Data import will be described later in this document.
  CREATE TABLE test5 as select * from test2;

  Note

  • For more information about how to add a partition, see Partition and column operations.
  • For more information about how to modify the lifecycle, see Lifecycle management operations.
  • For more information about table-level operations, see Table operations.

Change the owner of a table

• Syntax

  ALTER TABLE table_name CHANGEOWNER to new_owner;

• Features

  This statement is used to change the owner of a table.

• Examples

  -- Change the owner of the test1 table to ALIYUN$xxx@aliyun.com.
  ALTER TABLE test1 CHANGEOWNER to 'ALIYUN$xxx@aliyun.com';

Delete a table

• Syntax

  DROP TABLE [IF EXISTS] table_name; 

• Features
  • This statement is used to delete a table.
  • If you execute a DROP TABLE statement without the IF EXISTS clause to delete a table that does not exist, the system returns an error. If you execute a DROP TABLE statement with the IF EXISTS clause to delete a table, the system returns a success message regardless of whether the table exists.
• Examples

  -- Delete the sale_detail table. If the table exists, the system returns a success message.
  DROP TABLE sale_detail; 
  -- Delete the sale_detail table. The system returns a success message regardless of whether the sale_detail table exists.
  DROP TABLE IF EXISTS sale_detail; 

View table information

• Syntax
  • View information about a table or view.

    DESC <table_name>; 

    table_name: the name of the table or view.
  • View information about a foreign table.

    DESC extended <table_name>; 

    table_name: the name of the foreign table.
• Features
  This statement returns the following information about a table:

  • Owner: the owner of the table.
  • Project: the project to which the table belongs.
  • CreateTime: the time when the table was created.
  • LastDDLTime: the last time when a DDL operation was performed.
  • LastModifiedTime: the last time when the table was modified.
  • InternalTable: indicates that the described object is a table. The value is always YES.
  • Size: the storage capacity 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 except for the data in the recycle bin, execute the PURGE TABLE table_name; statement before you execute the DESC table_name statement. To view the details of the data in the recycle bin, execute the SHOW RECYCLEBIN; statement.
  • Native Columns: the information about non-partition key columns, including the column name, type, and description.
  • Partition Columns: the information about partition key columns, including the column name, type, and description.
  • Extended Info: the information about foreign tables, including StorageHandler and Location.
  Note

  • For a non-partitioned table, the Partition Columns section is not displayed.
  • For a view, the InternalTable parameter is replaced by the VirtualView parameter, whose value is always YES. The Size parameter is replaced by the ViewText parameter, which defines the view, such as SELECT * FROM src. For more information about views, see View-related operations.
• Examples

  -- Query information about the partitioned table sale_detail.
  odps@ project_name>DESC sale_detail; 
  +------------------------------------------------------------------------------------+
  | Owner: ALIYUN$odpsuser@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     |                                                     |
  +------------------------------------------------------------------------------------+

View partition information

• Syntax

  DESC table_name PARTITION(pt_spec);

• Features

  This statement is used to query the partition information about a partitioned table.

• Examples

  odps@ project_name>DESC meta.m_security_users PARTITION(ds='20151010');
  +------------------------------------------------------------------------------------+
  | 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 in a project

• Syntax

  SHOW TABLES;
  SHOW TABLES LIKE 'chart';

• Features
  • SHOW TABLES: shows all tables in the current project.
  • SHOW TABLES LIKE 'chart': shows the tables whose names match chart in the current project. You can use regular expressions in the statement to filter tables.
• Examples

  odps@ project_name>SHOW TABLES;
  odps@ project_name>SHOW TABLES LIKE 'ods_brand*';
  ALIYUN$odps_user@aliyun.com:table_name
  ......

  • ALIYUN: indicates that the table was created by an Alibaba Cloud user.
  • odps_user@aliyun.com: the name of the user who created the table.
  • table_name: the name of the table.

List all partitions

• Syntax

  SHOW PARTITIONS table_name; 

• Features

  This statement is used to list all partitions of a table. An error is returned if the table does not exist or the table is not a partitioned table.

• Examples

  odps@ project_name>SHOW PARTITIONS table_name;
  partition_col1=col1_value1/partition_col2=col2_value1
  partition_col1=col1_value2/partition_col2=col2_value2
  ......

  • partition_col1 and partition_col2 indicate the partition key columns of the table.
  • col1_value1, col2_value1, col1_value2, and col2_value2 indicate the values in the matching columns.

Add a column

ALTER TABLE table_name ADD COLUMNS (col_name1 type1,col_name2 type2...) ;

ALTER TABLE table_name ADD COLUMNS (col_name1 type1 comment 'XXX',col_name2 type2 comment 'XXX');