MaxCompute:Column operations

The column operation commands in MaxCompute SQL are as follows:

Limits

Add columns or comments

Add columns or comments to an existing non-partitioned table 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 in the table. By default, the new column is added as the last column.
  col_name	Yes	The name of the column that you want to add to the table.
  type	Yes	The data type of the column that you want to add to the table.
  col_comment	No	The comment of the column that you want to add to the table.

• 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 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 columns to a Delta table.

    ALTER TABLE mf_tt ADD columns (val2 bigint);

  • Example 5: Add the ID column to the sale_detail table. If the ID column already exists in the sale_detail table, the SQL statement is executed successfully but the ID column is not repeatedly added to the table.

    --If the ID column already exists in the sale_detail table, the ID column is not repeatedly added to the table.
    ALTER TABLE sale_detail ADD columns if NOT EXISTS(id bigint);

Drop columns

Drop one or more specified columns from an existing partitioned or non-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 drop columns.
  col_name	Yes	The name of the column you want to drop.

• Example: Drop columns from a table

  --Drop the customer_id column from the sale_detail table. After you input 'yes' for confirmation, the column will be deleted.
  ALTER TABLE sale_detail DROP columns customer_id;
  
  --Drop the shop_name and customer_id columns from the sale_detail table. After you input 'yes' for comfirmation, the column will be deleted.
  ALTER TABLE sale_detail DROP columns shop_name, customer_id;

Modify column data types

Modify 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 in which you want to modify the data type of a column.
  old_column_name	Yes	The name of the column whose data type you want to modify.
  new_column_name	Yes	The new name of the column whose data type you want to modify. Note old_column_name can be the same as new_column_name, which indicates that the column name is not modified. However, new_column_name cannot match any column name other than old_column_name.
  new_data_type	Yes	The new data type of the column whose data type you want to modify.

• Examples.

  --Modify the id field of the mf_evol_t3 table from int to bigint. 
  ALTER TABLE mf_evol_t3 change id id bigint;
  --Modify the id field of the mf_evol_t3 table from bigint to string. 
  ALTER TABLE mf_evol_t3 change COLUMN id id string;

• Data type conversion.

  Note

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

  

Modify column order

Modify the order of a column in an existing partitioned or non-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 whose order you want to modify. Note new_col_name can be the same as old_column_name, which indicates that the column name is not modified. However, new_col_name cannot match any column name other than old_column_name.
  column_type	Yes	The original data type of the column whose order you want to modify. You cannot modify the parameter.
  column_name	Yes	Place the column whose order you want to modify after column_name.

• Examples

  --Modify the table sale_detail to change the column name customer_id to customer and place it after the total_price column.
  ALTER TABLE sale_detail change customer_id customer string after total_price;
  --Modify the table sale_detail to move the column customer_id after the total_price column without changing its name.
  ALTER TABLE sale_detail change customer_id customer_id string after total_price;

Modify the name of a column

Modify the name of a column in an existing partitioned or non-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 in which you want to modify the name of a column.
  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 duplicated.

• Examples

  --Modify the table sale_detail to change the column_name from customer_name to customer.
  ALTER TABLE sale_detail change COLUMN customer_name rename TO customer;

Modify column comments

Modify the comment of a column in an existing partitioned or non-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 in which you want to modify the column comment.
  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 of the column. The comment must be a valid string that does not exceed 1,024 bytes in length. If the value of this parameter does not meet the requirements, an error is returned.

• Examples

  --Modify the comment for the customer column in sale_detail table. 
  ALTER TABLE sale_detail change COLUMN customer comment 'customer';

Modify the names and comments of columns

Modify the non-nullable properties of non-partition columns

Modify the Nullable property of a non-partition column in a table. If the values of a non-partition column in a table cannot be null, you can execute the statement to allow the column values to be null.

You can run the desc extended table_name; command to view the value of the Nullable property. If Nullable is true, the column values can be null. If Nullable is false, the column values cannot be null.

• Limits

  After you modify the value of the Nullable property to true, you cannot revert the property setting.

• Syntax

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

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the table in which you want to modify the value of the Nullable property.
  old_col_name	Yes	The name of the non-partition column whose Nullable property you want to modify. Note The column must be an existing non-partition column.

• Examples

  --Create a partition table and the column id cannot be null.
  CREATE TABLE null_test(id int NOT NULL, name string) partitioned BY (ds string);
  --Modify the id column in the table to allow null values.
  ALTER TABLE null_test change COLUMN id NULL;

References

For more information about table operation commands, see the following:

• Create and drop tables

• Modify and view tables

• Partition operations

• View tables and views

• CLONE TABLE