FAQ: DDL operations-MaxCompute(MaxCompute)-阿里云帮助中心

Frequently asked questions about DDL operations on tables, partitions, and columns in MaxCompute.

Category	FAQ
Table operations	Does MaxCompute support virtual tables, such as the DUAL table in MySQL? Do MaxCompute tables have an index? How do I modify the Hash Clustering attribute of a table? How do I convert a non-partitioned table to a partitioned table? Can I restore a table that was accidentally deleted? How do I query tables created by a specific user? How do I check if a specific table exists? How do I get the names of all tables in a project? How do I quickly find all partitioned tables in a project? How do I check the last access time of a MaxCompute table? How do I check the data volume of a table? How do I check the number of rows in a table? Why can't I see a table I created in DataWorks?
Partition operations	What is the difference between a partition and a partition key column? Is it better for a table to have more partitions? If a source table has no partition key fields, can I add or change partitions? How do I check if a specific partition exists? How do I check the number of partitions?
Column operations	Can I add or delete columns? How do I add a column? How do I set up an auto-increment column? What is the maximum number of columns in a single MaxCompute table? What should I do if a column name is the same as a keyword? How do I modify the data type of a column?
Lifecycle operations	How is old data in a partitioned table cleaned up when a 3-day lifecycle is set?

Virtual table support

MaxCompute does not support virtual tables. You can create a DUAL table manually.

Index support

No. MaxCompute tables do not have indexes. However, the Hash Clustering feature works similarly to a cluster index in a database. For more information, see Table operations.

Modifying the Hash Clustering attribute

To add the Hash Clustering attribute to a table, run the following command: 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];.
To remove the Hash Clustering attribute from a table, run the following command: alter table table_name not clustered;.

Converting non-partitioned to partitioned tables

You cannot convert a non-partitioned table to a partitioned table or add partition key columns to an existing table. You must create a new partitioned table. For more information, see Table operations.

Restoring a deleted table

MaxCompute provides backup and recovery with a default free retention period of 24 hours, which you can customize. If you accidentally delete a table, you can restore its data within this period. For more information about backup and recovery, see backup and recovery.

Querying tables by owner

Use the TABLES metadata view and filter by the owner_name field to find tables created by a specific user. For more information about TABLES, see TABLES.

Checking if a table exists

You can use the TABLE_EXISTS function to check if a specific table exists. For more information, see TABLE_EXISTS.

Getting all table names in a project

You can run the show tables; command in the MaxCompute client or use the metadata service of MaxCompute. For more information, see Table operations or Information Schema.

Finding all partitioned tables in a project

You can run the following command in the MaxCompute client to find all partitioned tables in your project.

select table_name from information_schema.columns where is_partition_key = true group by table_name;

Checking the last access time of a table

In DataWorks, go to the data map page, search for your table, and open its details page. In the Technical Information panel, you can find the following fields: Technical type, Last DDL change time, Last data modification time, Last access time, and Compute engine information.

Checking the data volume of a table

A table's data volume refers to its row count and the physical space it occupies:

To check the physical space of an entire table, run the desc command. To check the number of rows, run the SQL statement select count() as cnt from table_name;.
To check the physical space of a specific partition, run the desc command with a WHERE clause. To check the number of rows in the partition, run the SQL statement select count() as cnt from table_name where ...;.

Checking the row count of a table

You can run the select count(*) from table_name; command in the MaxCompute client to check the number of rows in a partitioned or non-partitioned table.

Table not visible in DataWorks

If you create a table by using an SQL statement instead of creating it in the DataWorks UI, the table may not appear for two to three hours.

Partitions and partition key columns

Tables in MaxCompute can be partitioned. A partitioned table has partition key columns, which you use to create partitions.

For example, in a partition specified as ds=20150101, ds is a partition key column, and ds=20150101 is a partition.

Optimal number of partitions

A MaxCompute table can have a maximum of 60,000 partitions with unlimited capacity per partition. However, too many partitions can complicate data statistics and analysis.

MaxCompute limits the maximum number of instances per job. The number of instances a job uses depends on the input data volume and the number of partitions. Therefore, you must choose a partitioning strategy that fits your business needs.

Adding partitions to a table without partition keys

MaxCompute does not support adding or modifying partition key fields in an existing table. You can create a new partitioned table and use a dynamic partitioning SQL statement to import data from the source table to the new table. For more information, see Insert or overwrite data into dynamic partitions (DYNAMIC PARTITION).

Checking if a partition exists

You can use the PARTITION_EXISTS function to check if a specific partition exists. For more information, see PARTITION_EXISTS.

Checking the number of partitions

You can use the PARTITIONS view in Information Schema to get the partition names and then count them.

Adding or deleting columns

MaxCompute supports adding and deleting columns. Adding columns of a complex data type and deleting columns is available for invitational preview. To apply for a trial, see Apply for a trial of new features.

Adding a column

Run the following command to add columns. If the table already contains data, the new columns are NULL for all existing rows.

alter table table_name add columns (col_name1 type1, col_name2 type2…);

For more information about the syntax, see Add a column.

Setting up an auto-increment column

MaxCompute does not support auto-increment columns. For a small amount of data, you can use the ROW_NUMBER function as a workaround.

Maximum number of columns per table

A single MaxCompute table can have a maximum of 1,200 columns. If your table exceeds this limit, consider the following solutions:

Reduce the number of dimensions in your data to 1,200 columns or fewer.
Change the data storage method, for example, by using complex data types like arrays or maps to consolidate related data.

Using keywords as column names

If you use a keyword to name a table, column, or partition, you must enclose the keyword in backticks (``). Otherwise, an error occurs.

Modifying a column's data type

You cannot change the data type of an existing column. Instead, create a new table with the desired data type and migrate the data. For more information about data types, see Data type editions.

Data cleanup for partitioned tables with a lifecycle

When a partition in a table with a configured lifecycle is not modified within the specified period, MaxCompute automatically reclaims it.

Run the desc table_name partition(pt_spec); command to check if an old partition was modified within the lifecycle period. Run the desc tablename; command to view the lifecycle setting. MaxCompute reclaims expired partitions daily at 17:00. The status update in the DataWorks console may be delayed, typically by up to one day.