Column operations

Commands

MaxCompute SQL provides the following column operation commands:

Usage notes

Schema evolution operations (adding complex-type columns, deleting columns, reordering columns, or changing data types) alter table read/write behavior. If you reorder, add-and-reorder, or delete a column, the following limits apply:

• If the job type is MapReduce 1.0, Graph tasks cannot read from or write to the modified table.

• For CUPID jobs, only the following Spark versions can read from the table. They cannot write to the table:

  • Spark-2.3.0-odps0.34.0

  • Spark-3.1.1-odps0.34.0

• PAI jobs can read from the table, but cannot write to it.

• For Hologres jobs, if you use a Hologres version earlier than 1.3, you cannot read from or write to the modified table when it is referenced as a foreign table.

• If schema evolution occurs, CLONE TABLE is not supported.

• If schema evolution occurs, an error is reported when you use Streaming Tunnel.

Add columns or comments

Add columns or comments to a non-partitioned or partitioned table. Review the scope of schema evolution. Complex types such as STRUCT<x: STRING, y: BIGINT> and MAP<STRING, STRUCT<x: DOUBLE, y: DOUBLE>> are supported.

• Command format

  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 target table. New columns are appended to the end of the table; you cannot specify their order.
  col_name	Yes	The name of the new column.
  type	Yes	The data type of the new column.
  col_comment	No	The comment for 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 with 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_info struct<province:string, area:string>);

  • Example 4: If the column already exists, IF NOT EXISTS ensures the statement succeeds without duplicating the column.

    -- The statement is successful, but the ID column is not repeatedly added.
    ALTER TABLE sale_detail ADD COLUMNS IF NOT EXISTS(id bigint);

  • Example 5: Add a column to a Delta table.

    CREATE TABLE delta_table_test (pk BIGINT NOT NULL PRIMARY KEY, val BIGINT) 
      TBLPROPERTIES ("transactional"="true");
    ALTER TABLE delta_table_test ADD COLUMNS (val2 bigint);

Delete columns

Delete one or more columns from a non-partitioned or partitioned table. Review the scope of schema evolution.

• Command format

  -- Delete one or more columns.
  ALTER TABLE <table_name> DROP COLUMNS <col_name1>[, <col_name2>...];

• Parameters

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

  • col_name: Required. The name of the column to delete.

• Example

  -- Example 1: Delete the customer_id column from the sale_detail table.
  ALTER TABLE sale_detail DROP COLUMNS customer_id;
  -- Example 2: Delete the shop_name and total_price columns from the sale_detail table.
  ALTER TABLE sale_detail DROP COLUMNS shop_name, total_price;

Change column data types

Change a column's data type. Review the scope of schema evolution.

• Command format

  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 target table.
  old_column_name	Yes	The column to change.
  new_column_name	Yes	The column name after the type change. old_column_name and new_column_name can be the same (no rename). However, new_column_name must not match any other existing column name except old_column_name.
  new_data_type	Yes	The new data type of the column.

• Example

  -- Change the data type of the id field in the sale_detail table from BIGINT to STRING.
  ALTER TABLE sale_detail 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 condition in the parentheses is met.

  The data type conversion rules cover the following data types: BOOLEAN, BYTE, SHORT, INT, LONG, FLOAT, DOUBLE, DECIMAL(p1,s1), DECIMAL V1, STRING, CHAR(n), VARCHAR(n), BINARY, DATETIME, TIMESTAMP, DATE, INTERVEL_YEAR_MONTH, and INTERVEL_DAY_TIME. Implicit conversions between numeric types (BYTE→SHORT→INT→LONG→FLOAT→DOUBLE) are supported from lower precision to higher precision. Converting FLOAT or DOUBLE to DECIMAL requires the value to be truncated, similar to a truncate to s2 operation. Converting DECIMAL(p1,s1) to DECIMAL(p2,s2) is supported only if s1<=s2 && (p1-s1)<=(p2-s2). Converting CHAR(n) to CHAR(m) or VARCHAR(m) requires that n<=m. STRING can be converted to most types. BOOLEAN can be converted only to STRING. BINARY cannot be converted to or from other types. Some conversions are supported between time types (DATETIME, TIMESTAMP, DATE). Conversions between INTERVEL_YEAR_MONTH and INTERVEL_DAY_TIME are not supported.

Change the column order

Change the column order in a non-partitioned or partitioned table. Review the scope of schema evolution.

• Command format

  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 target table.
  old_column_name	Yes	The column to reorder.
  new_col_name	Yes	The new column name. new_col_name and old_column_name can be the same (no rename). However, new_col_name must not match any other existing column name except old_column_name.
  column_type	Yes	The column's current data type. Cannot be changed by this command.
  column_name	Yes	Places the column immediately after column_name.

• Example

  -- Change the name of the customer column in the sale_detail table to customer_id and move it after the total_price column.
  ALTER TABLE sale_detail CHANGE customer customer_id STRING AFTER total_price;
  -- Move the customer_id column in the sale_detail table after the total_price column without changing the column name.
  ALTER TABLE sale_detail CHANGE customer_id customer_id STRING AFTER total_price;

Change a column name

Rename a column in a non-partitioned or partitioned table.

• Command format

  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 target table.
  old_col_name	Yes	The current column name. Must exist in the table.
  new_col_name	Yes	The new column name. Must be unique within the table.

• Example

  -- Rename the customer_name column in the sale_detail table to customer.
  ALTER TABLE sale_detail CHANGE COLUMN customer_name RENAME TO customer;

Change a column comment

Change the comment of a column in a 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 target table.
  col_name	Yes	The column whose comment you want to change. Must exist in the table.
  col_comment	Yes	The new comment. Maximum 1024 bytes.

• Example

  -- Change the comment of the customer column in the sale_detail0113 table.
  ALTER TABLE sale_detail0113 CHANGE COLUMN customer COMMENT 'customer';

Change a column name and comment

Change the name and comment of a column in a non-partitioned or partitioned table.

• Command format

  ALTER TABLE <table_name> CHANGE COLUMN <old_col_name> <new_col_name> <column_type> COMMENT '<col_comment>';

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the target table.
  old_col_name	Yes	The column to modify. Must exist in the table.
  new_col_name	Yes	The new column name. Must be unique within the table.
  column_type	Yes	The data type of the column.
  col_comment	Optional	The new comment. Maximum 1024 bytes.

• Example

  -- Change the name of the customer column in the sale_detail table to customer_newname and change its comment to 'customer'.
  ALTER TABLE sale_detail CHANGE COLUMN customer customer_newname STRING COMMENT 'customer';

Change the NOT NULL property of a column

Change the NOT NULL constraint of a non-partition key column to allow NULL values.

This change is irreversible. Once a column allows NULL values, you cannot revert it to NOT NULL.

• You can run the DESC EXTENDED table_name; command to view the value of the Nullable property and determine whether the column allows NULL values:

  • If Nullable is true, NULL values are allowed.

  • If Nullable is false, NULL values are not allowed.

• Command format

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

• Parameters

  Parameter	Required	Description
  table_name	Yes	The name of the target table.
  old_col_name	Yes	The non-partition key column to modify. Must be an existing non-partition key column.

• Example

  -- Create a partitioned table where the id column does not allow NULL values.
  CREATE TABLE null_test(id INT NOT NULL, name STRING) PARTITIONED BY (ds string);
  -- View the table properties.
  DESC EXTENDED null_test;
  -- The following result is returned:
  +------------------------------------------------------------------------------------+
  | Native Columns:                                                                    |
  +------------------------------------------------------------------------------------+
  | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
  +------------------------------------------------------------------------------------+
  | id       | int    |       |               | false    | NULL         |              |
  | name     | string |       |               | true     | NULL         |              |
  +------------------------------------------------------------------------------------+
  | Partition Columns:                                                                 |
  +------------------------------------------------------------------------------------+
  | ds              | string     |                                                     |
  +------------------------------------------------------------------------------------+
  -- Change the id column to allow NULL values.
  ALTER TABLE null_test CHANGE COLUMN id NULL;
  -- View the table properties.
  DESC EXTENDED null_test;
  -- The following result is returned:
  +------------------------------------------------------------------------------------+
  | Native Columns:                                                                    |
  +------------------------------------------------------------------------------------+
  | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
  +------------------------------------------------------------------------------------+
  | id       | int    |       |               | true     | NULL         |              |
  | name     | string |       |               | true     | NULL         |              |
  +------------------------------------------------------------------------------------+
  | Partition Columns:                                                                 |
  +------------------------------------------------------------------------------------+
  | ds              | string     |                                                     |
  +------------------------------------------------------------------------------------+

References

Related table operation commands:

• Create and delete tables

• Modify and view tables

• Partition operations

• View tables and views

• CLONE TABLE