MaxCompute:ALTER TABLE

Limitations

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 account of the new owner.

• Examples

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

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.

• Examples

  ALTER TABLE sale_detail SET COMMENT 'new comments for table sale_detail';

  You can run the MaxCompute command DESC <table_name> to view the modified COMMENT of the table.

Change the last modified time of a table

MaxCompute SQL provides the touch operation to change the LastModifiedTime of a table to the current time. This operation causes MaxCompute to treat the table data as changed and recalculate its lifecycle.

• Syntax

  ALTER TABLE <table_name> touch;

• Parameters

  table_name: Required. The name of the table whose last modified time you want to change.

• Examples

  ALTER TABLE sale_detail touch;

Modify the clustering properties of a table

For a partitioned table, you can run the ALTER TABLE statement to add or remove clustering properties.

• Syntax

  • Add hash clustering properties to 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];

  • Remove the hash clustering properties from a table:

    ALTER TABLE <table_name> NOT clustered;

  • Add range clustering properties to a table. The number of buckets is not required. If you do not specify the number of buckets, the system automatically determines the optimal number based on the data volume.

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

  • Remove the range clustering properties from a table or partition:

    ALTER TABLE <table_name> NOT clustered;
    ALTER TABLE <table_name> PARTITION [<pt_spec>] NOT clustered;

    Note

    • The ALTER TABLE statement to change clustering properties is effective only for partitioned tables. The clustering properties of a non-partitioned table cannot be changed after the table is created. The ALTER TABLE statement applies to existing tables. After you add new clustering properties, new partitions are stored based on the specified clustering properties.

    • The ALTER TABLE statement affects only new partitions of a partitioned table, including partitions generated by INSERT OVERWRITE. New partitions are stored based on the new clustering properties, while the properties and storage of existing partitions remain unchanged. This lets you disable existing clustering properties and then add new ones. You can specify different clustering columns, sorting columns, and bucket numbers for the new partitions.

    • Because the ALTER TABLE statement affects only new partitions, you cannot specify a partition in the statement.

• Parameters

  The parameters are the same as those in CREATE TABLE.

• Examples

  -- Create a partitioned table.
  CREATE TABLE IF NOT EXISTS sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  partitioned BY (sale_date STRING, region STRING);
  
  -- Modify the clustering properties of the table.
  ALTER TABLE sale_detail clustered BY (customer_id) sorted BY (customer_id) INTO 10 buckets;

  For more information about clustering properties, see Hash Clustering and Range Clustering.

Rename a table

Renames a table. This operation changes only the table name and does not change the data in the table.

• Syntax

  ALTER TABLE <table_name> RENAME TO <new_table_name>;

• Parameters

  • table_name: Required. The table that you want to rename.

  • new_table_name: Required. The new name of the table. If a table with the same name as new_table_name already exists, an error is returned.

• Examples

  ALTER TABLE sale_detail RENAME TO sale_detail_rename;

Modify the lifecycle of a table

Modifies the lifecycle of an existing partitioned or non-partitioned table.

• Syntax

  ALTER TABLE <table_name> SET LIFECYCLE <days>;

• Parameters

  • table_name: Required. The name of the table whose lifecycle you want to modify.

  • days: Required. The new lifecycle period. The value must be a positive integer. Unit: days.

• Examples

  -- Modify the lifecycle of the test_lifecycle table to 50 days.
  ALTER TABLE test_lifecycle SET LIFECYCLE 50;

Disable or restore a lifecycle

Disables or restores the lifecycle of a specified table or partition.

• Syntax

  ALTER TABLE <table_name> PARTITION [<pt_spec>] {enable|disable} LIFECYCLE;

• Parameters

  • table_name: Required. The name of the table whose lifecycle you want to disable or restore.

  • pt_spec: Optional. The partition of the table whose lifecycle you want to disable or restore. The format is partition_col1=col1_value1, partition_col2=col2_value1.... For a table with multi-level partitions, you must specify all partition values.

  • enable: Restores the lifecycle feature for the table or a specified partition.

    • The table and its partitions become subject to lifecycle-based reclamation again. By default, the current lifecycle configuration of the table and its partitions is used.

    • Before you enable the lifecycle feature for a table, you can modify the lifecycle configuration of the table and its partitions. This prevents data from being reclaimed by mistake based on previous configurations after you enable the lifecycle feature.

  • disable: Disables the lifecycle feature for the table or a specified partition.

    • Prevents the table itself and all its partitions from being reclaimed by the lifecycle feature. This has a higher priority than restoring the lifecycle of a table partition. If you use table disable LIFECYCLE, the pt_spec enable LIFECYCLE setting is invalid.

    • After you disable the lifecycle feature for a table, the lifecycle configuration of the table and the enable and disable flags of its partitions are retained.

    • After you disable the lifecycle feature for a table, you can still modify the lifecycle configuration of the table and its partitions.

• Examples

  • Example 1: Disable the lifecycle feature for the trans table.

    ALTER TABLE trans disable LIFECYCLE;

  • Example 2: Disable the lifecycle feature for the partition `ds='20141111'` in the `trans` table.

    ALTER TABLE trans PARTITION (dt='20141111') disable LIFECYCLE;

Add partitions

Adds partitions to an existing partitioned table.

• Limits

  • A single MaxCompute table supports a maximum of 60,000 partitions.

  • For a table with multi-level partitions, you must specify all partition values to add a new partition.

  • You can only add partition values. You cannot add partition fields.

• Syntax

  ALTER TABLE <table_name> ADD [IF NOT EXISTS] PARTITION <pt_spec> [PARTITION <pt_spec> PARTITION <pt_spec>...];

• Parameters

  • table_name: Required. The name of the partitioned table to which you want to add partitions.

  • IF NOT EXISTS: Optional. If you do not specify IF NOT EXISTS and a partition with the same name already exists, the operation fails and an error is returned.

  • pt_spec: Required. The new partition. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition field, and partition_col_value is a partition value. Partition fields are not case-sensitive, but partition values are case-sensitive.

• Examples

  • Example 1: Add a partition to the `sale_detail` table to store sales records from the Hangzhou region in December 2013.

    ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='201312', region='hangzhou');

  • Example 2: Add two partitions to the `sale_detail` table at the same time to store sales records from the Beijing and Shanghai regions in December 2013.

    ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='201312', region='beijing') PARTITION (sale_date='201312', region='shanghai');

  • Example 3: Add a partition to the `sale_detail` table and specify only one partition field, `sale_date`. An error is returned because you must specify both partition fields: `sale_date` and `region`.

    ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION (sale_date='20111011');

Delete partitions

Deletes partitions from an existing partitioned table.

MaxCompute lets you delete partitions based on filter conditions. If you want to delete multiple partitions that meet a specific rule, you can use an expression to specify a filter condition. This condition is used to match and delete the partitions in a batch.

• Limits

  • Each partition filter clause can access only one partition column.

  • The function used in the expression must be a built-in scalar function.

• Notes

  • After you delete a partition, the storage usage of your MaxCompute project decreases.

  • You can use the lifecycle feature of MaxCompute to automatically reclaim old partitions. For more information about lifecycles, see Lifecycle.

• Syntax

  • Without a filter condition

    -- Delete one partition at a time.
    ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>;
    
    -- Delete multiple partitions at a time.
    ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <pt_spec>, PARTITION <pt_spec>[, PARTITION <pt_spec>....];

  • Filter criteria

    ALTER TABLE <table_name> DROP [IF EXISTS] PARTITION <partition_filtercondition>;

• Parameters

  • table_name: Required. The name of the partitioned table from which you want to delete partitions.

  • IF EXISTS: Optional. If you do not specify IF EXISTS and the partition does not exist, an error is returned.

  • pt_spec: Required. The partition to delete. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition field, and partition_col_value is a partition value. Partition fields are not case-sensitive, but partition values are case-sensitive.

  • partition_filtercondition: Required when specifying a filter condition. The partition filter condition is not case-sensitive. The format is:

    partition_filtercondition
        : PARTITION (<partition_col> <relational_operators> <partition_col_value>)
        | PARTITION (scalar(<partition_col>) <relational_operators> <partition_col_value>)
        | PARTITION (<partition_filtercondition1> AND|OR <partition_filtercondition2>)
        | PARTITION (NOT <partition_filtercondition>)
        | PARTITION (<partition_filtercondition1>)[,PARTITION (<partition_filtercondition2>), ...]

    • partition_col: The partition name.

    • relational_operators: A relational operator. For more information, see Operators.

    • partition_col_value: The comparison value or regular expression for the partition column. It must be consistent with the data type of the partition column.

    • scalar(): A scalar function. A scalar function processes the value of the partition column (partition_col) and returns a scalar value. This value is then compared with partition_col_value using the specified relational_operators.

    • The partition filter condition supports the logical operators NOT, AND, and OR. You can use a NOT filter clause to select the complement of a filter rule. You can combine multiple filter clauses with AND or OR to create the overall partition matching rule.

    • Multiple partition filter clauses are supported. When multiple filter clauses are separated by commas (,), the filter clauses are combined with an OR relationship to form the overall partition matching rule.

• Examples

  • Without a filter condition

    -- Delete a partition from sale_detail that contains sales records from the Hangzhou partition in December 2013.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='201312',region='hangzhou');
     
    -- Delete two partitions from the sale_detail table at the same time, which contain sales records from the Hangzhou and Shanghai partitions in December 2013.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date='201312',region='hangzhou'), PARTITION(sale_date='201312',region='shanghai');

  • You can specify the filter criteria.

    -- Create a partitioned table.
    CREATE TABLE IF NOT EXISTS sale_detail(
    shop_name     STRING,
    customer_id   STRING,
    total_price   DOUBLE)
    partitioned BY (sale_date STRING);
    
    -- Add partitions.
    ALTER TABLE sale_detail ADD IF NOT EXISTS
    PARTITION (sale_date= '201910')
    PARTITION (sale_date= '201911')
    PARTITION (sale_date= '201912')
    PARTITION (sale_date= '202001')
    PARTITION (sale_date= '202002')
    PARTITION (sale_date= '202003')
    PARTITION (sale_date= '202004')
    PARTITION (sale_date= '202005')
    PARTITION (sale_date= '202006')
    PARTITION (sale_date= '202007');
    
    -- Batch delete partitions.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date < '201911');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date >= '202007');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date LIKE '20191%');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date IN ('202002','202004','202006'));
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date BETWEEN '202001' AND '202007');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(substr(sale_date, 1, 4) = '2020');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date < '201912' OR sale_date >= '202006');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date > '201912' AND sale_date <= '202004');
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(NOT sale_date > '202004');
    
    -- Multiple partition filter expressions are supported. The expressions have an OR relationship.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'), PARTITION(sale_date >= '202007');
    
    -- Add partitions in other formats.
    ALTER TABLE sale_detail ADD IF NOT EXISTS
    PARTITION (sale_date= '2019-10-05') 
    PARTITION (sale_date= '2019-10-06') 
    PARTITION (sale_date= '2019-10-07');
    
    -- Batch delete partitions using a regular expression to match partitions.
    ALTER TABLE sale_detail DROP IF EXISTS PARTITION(sale_date RLIKE '2019-\\d+-\\d+');
    
    -- Create a multi-level partitioned table.
    CREATE TABLE IF NOT EXISTS region_sale_detail(
    shop_name     STRING,
    customer_id   STRING,
    total_price   DOUBLE)
    partitioned BY (sale_date STRING , region STRING );
    
    -- Add partitions.
    ALTER TABLE region_sale_detail ADD IF NOT EXISTS
    PARTITION (sale_date= '201910',region = 'shanghai')
    PARTITION (sale_date= '201911',region = 'shanghai')
    PARTITION (sale_date= '201912',region = 'shanghai')
    PARTITION (sale_date= '202001',region = 'shanghai')
    PARTITION (sale_date= '202002',region = 'shanghai')
    PARTITION (sale_date= '201910',region = 'beijing')
    PARTITION (sale_date= '201911',region = 'beijing')
    PARTITION (sale_date= '201912',region = 'beijing')
    PARTITION (sale_date= '202001',region = 'beijing')
    PARTITION (sale_date= '202002',region = 'beijing');
    
    -- Run the following statement to batch delete multi-level partitions. The two matching conditions have an OR relationship. All partitions where sale_date is less than 201911 or region is beijing will be deleted.
    ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911'),PARTITION(region = 'beijing');
    
    -- To delete partitions where sale_date is less than 201911 and region is beijing, use the following method.
    ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911', region = 'beijing');

    When you batch delete multi-level partitions, you cannot write a combined condition to match partitions based on multiple partition columns in a single partition filter clause. The following statement reports an error: FAILED: ODPS-0130071:[1,82] Semantic analysis exception - invalid column reference region, partition expression must have one and only one column reference.

    -- A partition filter clause can access only one partition column. The following statement reports an error.
    ALTER TABLE region_sale_detail DROP IF EXISTS PARTITION(sale_date < '201911' AND region = 'beijing');

Modify the update time of a partition

MaxCompute SQL provides the touch operation to modify the LastModifiedTime of a partition in a partitioned table. This operation changes the LastModifiedTime to the current time. At this point, MaxCompute treats the data as changed and recalculates its lifecycle.

• Limits

  For a table with multi-level partitions, you must specify all partition values.

• Syntax

  ALTER TABLE <table_name> touch PARTITION (<pt_spec>);

• Parameters

  • table_name: Required. The name of the partitioned table whose partition update time you want to modify. If the table does not exist, an error is returned.

  • pt_spec: Required. The partition whose update time you want to modify. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition field, and partition_col_value is a partition value. If the specified partition field or partition value does not exist, an error is returned.

• Examples

  -- Modify the LastModifiedTime of the partition sale_date='201312', region='shanghai' in the sale_detail table.
  ALTER TABLE sale_detail touch PARTITION (sale_date='201312', region='shanghai');

Modify a partition value

MaxCompute SQL supports changing the partition value of a partitioned table using the rename operation.

• Limits

  • You cannot modify the name of a partition column. You can only modify the value that corresponds to the partition column.

  • For a table with multi-level partitions, you must specify all partition values.

• Syntax

  ALTER TABLE <table_name> PARTITION (<pt_spec>) rename to PARTITION (<new_pt_spec>);

• Parameters

  • table_name: Required. The name of the table whose partition value you want to modify.

  • pt_spec: Required. The partition whose value you want to modify. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). partition_col is a partition field, and partition_col_value is a partition value. If the specified partition field or partition value does not exist, an error is returned.

  • new_pt_spec: Required. The modified partition information. The format is (partition_col1 = new_partition_col_value1, partition_col2 = new_partition_col_value2, ...). partition_col is a partition field, and new_partition_col_value is the new partition value.

• Examples

  -- Modify the partition value of the sale_detail table.
  ALTER TABLE sale_detail PARTITION (sale_date = '201312', region = 'hangzhou') rename to PARTITION (sale_date = '201310', region = 'beijing');

Merge partitions

MaxCompute SQL provides the merge partition operation to merge partitions of a partitioned table. This operation merges multiple partitions of a partitioned table into a single partition, deletes the dimension information of the merged partitions, and moves the data to the specified partition.

• Limits

  • Foreign tables are not supported. The clustering properties of a clustered table are eliminated after its partitions are merged.

  • A maximum of 4,000 partitions can be merged at a time.

• Syntax

  ALTER TABLE <table_name> MERGE [IF EXISTS] PARTITION (<predicate>) [, PARTITION(<predicate2>) ...] overwrite PARTITION (<fullpartitionSpec>) [purge];

• Parameters

  • table_name: Required. The name of the partitioned table whose partitions you want to merge.

  • IF EXISTS: Optional. If you do not specify IF EXISTS and the partition does not exist, the operation fails and an error is returned. If you specify IF EXISTS but no partitions meet the merge condition, no new partition is generated. If the source data is concurrently modified during the operation (including insert, rename, or drop), an error is reported even if you specify IF EXISTS.

  • predicate: Required. The condition that the partitions to be merged must meet.

  • fullpartitionSpec: Required. The information of the target partition.

  • purge: Optional keyword. If you specify this keyword, the session directory is cleared. By default, logs within 3 days are cleared. For more information, see Purge.

• Examples

  • Example 1: Merge partitions that meet a specified condition into a target partition.

    -- View the partitions of the partitioned table.
    SHOW partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00
    ds=20181101/hh=00/mm=10
    ds=20181101/hh=10/mm=00
    ds=20181101/hh=10/mm=10
    
    -- Merge all partitions that meet the condition hh='00' into the partition where hh='00' and mm='00'.
    ALTER TABLE intpstringstringstring MERGE PARTITION(hh='00') overwrite PARTITION(ds='20181101', hh='00', mm='00');
    
    -- View the partitions after merging.
    SHOW partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00
    ds=20181101/hh=10/mm=00
    ds=20181101/hh=10/mm=10                    

  • Example 2: Merge multiple specified partitions into a target partition.

    -- Merge multiple specified partitions.
    ALTER TABLE intpstringstringstring MERGE IF EXISTS 
      PARTITION(ds='20181101', hh='00', mm='00'), 
      PARTITION(ds='20181101', hh='10', mm='00'),  
      PARTITION(ds='20181101', hh='10', mm='10') overwrite PARTITION(ds='20181101', hh='00', mm='00') purge;
    
    -- View the partitions of the partitioned table.
    SHOW partitions intpstringstringstring;
    
    ds=20181101/hh=00/mm=00

Add columns or comments

Add columns or comments to an existing non-partitioned or partitioned table.

• Syntax

  ALTER TABLE <table_name> 
        ADD columns [if NOT EXISTS]
            (<col_name1> <type1> comment ['<col_comment>']
             [, <col_name2> <type2> comment '<col_comment>'...]
            );

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table to which you want to add columns. You cannot specify the position of a new column. By default, a new column is added as the last column.
  col_name	Yes	The name of the new column.
  type	Yes	The data type of the new column.
  col_comment	No	The comment of the new column.

• Examples

  • Example 1: Add two columns to the sale_detail table.

    ALTER TABLE sale_detail ADD columns if NOT EXISTS(customer_name STRING, education BIGINT);

  • Example 2: Add two columns and their comments to the sale_detail table.

    ALTER TABLE sale_detail ADD columns (customer_name STRING comment 'customer', education BIGINT comment 'education' );

  • Example 3: Add a column of a complex data type to the sale_detail table.

    ALTER TABLE sale_detail ADD columns (region struct<province:string, area:string>);

  • Example 4: Add a column to a Delta Lake table.

    ALTER TABLE mf_tt ADD columns (val2 bigint);

  • Example 5: Add the ID column to the sale_detail table. The command returns a success message, but the column is not added again if it already exists.

    -- The command returns a success message, but the ID column is not added again.
    ALTER TABLE sale_detail ADD columns if NOT EXISTS(id bigint);

Delete columns

Delete one or more specified columns from an existing non-partitioned or partitioned table.

setproject odps.schema.evolution.enable=true;

• Syntax

  ALTER TABLE <table_name> DROP columns <col_name1>[, <col_name2>...];

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table from which you want to delete columns.
  col_name	Yes	The name of the column that you want to delete.

• Example: Delete columns from a table

  -- Delete the customer_id column from the sale_detail table. Enter yes to confirm the deletion.
  ALTER TABLE sale_detail DROP columns customer_id;
  
  -- Delete the shop_name and customer_id columns from the sale_detail table. Enter yes to confirm the deletion.
  ALTER TABLE sale_detail DROP columns shop_name, customer_id;

Change column data types

Change the data type of an existing column.

setproject odps.schema.evolution.enable=true;

• Syntax

  ALTER TABLE <table_name> change [COLUMN] <old_column_name> <new_column_name> <new_data_type>;

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table that contains the column whose data type you want to change.
  old_column_name	Yes	The name of the column whose data type you want to change.
  new_column_name	Yes	The new name of the column. Note old_column_name can be the same as new_column_name. This indicates that the column name is not changed. However, new_column_name cannot be the same as any existing column name except old_column_name.
  new_data_type	Yes	The new data type of the column.

• Examples

  -- Change the data type of the id field in the mf_evol_t3 table from INT to BIGINT.
  ALTER TABLE mf_evol_t3 change id id bigint;
  -- Change the data type of the id field in the mf_evol_t3 table from BIGINT to STRING.
  ALTER TABLE mf_evol_t3 change COLUMN id id string;

• Data type conversion table

  Note

  Y indicates that the conversion is supported. N indicates that the conversion is not supported. - indicates that the conversion is not applicable. Y() indicates that the conversion is supported if the conditions in the parentheses are met.

  

Modify column order

Modify the column order in an existing non-partitioned or partitioned table.

setproject odps.schema.evolution.enable=true;

• Syntax

  ALTER TABLE <table_name> change <old_column_name> <new_column_name> <column_type> after <column_name>;

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table in which you want to modify the column order.
  old_column_name	Yes	The original name of the column whose order you want to modify.
  new_col_name	Yes	The new name of the column. Note new_col_name can be the same as old_column_name. This indicates that the column name is not changed. However, new_col_name cannot be the same as any existing column name except old_column_name.
  column_type	Yes	The original data type of the column. You cannot change the data type.
  column_name	Yes	The column after which you want to place the column that you are moving. The column is specified by column_name.

• Example

  --Change the name of the customer_id column in the sale_detail table to customer and move the column to be after the total_price column.
  ALTER TABLE sale_detail change customer_id customer string after total_price;
  --Move the customer_id column in the sale_detail table to be after the total_price column without changing the column name.
  ALTER TABLE sale_detail change customer_id customer_id string after total_price;

Modify a column name

Modify the name of a column in an existing non-partitioned or partitioned table.

• Syntax

  ALTER TABLE <table_name> change COLUMN <old_col_name> rename TO <new_col_name>;

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table that contains the column whose name you want to modify.
  old_col_name	Yes	The name of the column that you want to modify. Note The column must exist.
  new_col_name	Yes	The new name of the column. The column name cannot be the same as an existing column name.

• Example

  --Change the name of the customer_name column in the sale_detail table to customer.
  ALTER TABLE sale_detail change COLUMN customer_name rename TO customer;

Modify a column comment

Modify the comment of a column in an existing non-partitioned or partitioned table.

• Syntax

  ALTER TABLE <table_name> change COLUMN <col_name> comment '<col_comment>';

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table that contains the column whose comment you want to modify.
  col_name	Yes	The name of the column whose comment you want to modify. Note The column must exist.
  col_comment	Yes	The new comment. The comment must be a valid string of up to 1024 bytes in length. Otherwise, an error is reported.

• Example

  --Change the comment of the customer column in the sale_detail table.
  ALTER TABLE sale_detail change COLUMN customer comment 'customer';

Modify a column name and comment

Modify the non-nullable property of a column

You can change the non-nullable property of a non-partition key column. If a column is set to NOT NULL, you can run this command to allow the column to contain NULL values.

Run the desc extended table_name; command to view the value of the Nullable property and check whether a column can contain NULL values. If Nullable is true, the column can contain NULL values. If Nullable is false, the column cannot contain NULL values.

• Limits

  This change is irreversible. After you change the property to allow NULL values for a column, you cannot change it back to NOT NULL. Proceed with caution.

• Syntax

  ALTER TABLE <table_name> change COLUMN <old_col_name> NULL;

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table that contains the column whose non-nullable property you want to modify.
  old_col_name	Yes	The name of the non-partition key column that you want to modify. Note The column must be an existing non-partition key column.

• Example

  --Create a partitioned table in which the id column is set to NOT NULL.
  CREATE TABLE null_test(id int NOT NULL, name string) partitioned BY (ds string);
  --Change the id column to allow NULL values.
  ALTER TABLE null_test change COLUMN id NULL;

Merge files of a transactional table

The underlying physical storage of a transactional table consists of Base files and Delta files, which cannot be read directly. When you run an update or delete operation on a transactional table, only Delta files are appended. The Base files are not modified. As you perform more update or delete operations, the table occupies more storage, and query costs increase.

Running multiple update or delete operations on the same table or partition generates many Delta files. When the system reads data, it needs to load these Delta files to determine which rows were updated or deleted. Many Delta files can degrade data read efficiency. In this case, you can merge the Base files and Delta files to reduce storage and improve data read efficiency.

• Syntax

  ALTER TABLE <table_name> [PARTITION (<partition_key> = '<partition_value>' [, ...])] compact {minor|major};

• Parameters

  • table_name: Required. The name of the transactional table whose files you want to merge.

  • partition_key: Optional. When the transactional table is a partitioned table, specify the partition key column name.

  • partition_value: Optional. When the transactional table is a partitioned table, specify the value that corresponds to the partition key column name.

  • major|minor: You must specify one of the following keywords:

    • minor: Merges only the Base file and all its underlying Delta files, which eliminates the Delta files.

    • major: Merges not only the Base file and all its underlying Delta files but also the small files in the corresponding Base file of the table. This eliminates the Delta files. When the Base file is small (less than 32 MB) or there are Delta files, this is equivalent to running an insert overwrite operation on the table again. However, if the Base file is large enough (greater than or equal to 32 MB) and there are no Delta files, the Base file is not rewritten.

• Examples

  • Example 1: Merge the files of the transactional table `acid_delete`. The sample command is as follows:

    ALTER TABLE acid_delete compact minor;

    The following result is returned:

    Summary:
    Nothing found to merge, set odps.merge.cross.paths=true if cross path merge is permitted.
    OK

  • Example 2: Merge the files of the transactional table `acid_update_pt`. The sample command is as follows:

    ALTER TABLE acid_update_pt PARTITION (ds = '2019') compact major;

    The following result is returned:

    Summary:
    table name: acid_update_pt /ds=2019  instance count: 2  run time: 6
      before merge, file count:        8  file size: 2613  file physical size: 7839
       after merge, file count:        2  file size: 679  file physical size: 2037
    
    OK

Merge small files

A distributed file system stores data in blocks. A file that is smaller than the block size (64 MB) is called a small file. Distributed systems inevitably produce small files. For example, SQL or other distributed engine computation results and tunnel data ingestion all produce small files. Merging small files can improve computing performance.

ALTER TABLE <tablename> [PARTITION(<partition_key>=<partition_value>)] MERGE SMALLFILES;

The merge small files feature consumes compute resources. If you use a pay-as-you-go instance, you will incur fees. The billing rules are consistent with the pay-as-you-go billing rules for SQL jobs. For more information, see Compute costs (pay-as-you-go).

For more information, see Merge small files.

Related commands

• CREATE TABLE: Creates a non-partitioned table, partitioned table, foreign table, or clustered table.

• TRUNCATE: Clears the data in a specified table.

• DROP TABLE: Deletes a partitioned or non-partitioned table.

• DESC TABLE/VIEW: Views information about MaxCompute internal tables, views, materialized views, foreign tables, clustered tables, or transactional tables.

• SHOW: Views the SQL DDL statement of a table, lists all tables and views in a project, or lists all partitions in a table.