ALTER TABLE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Alters the schema or properties of a table.

For type changes or renaming columns in Delta Lake see rewrite the data.

To change the comment on a table, you can also use COMMENT ON.

To alter a STREAMING TABLE, use ALTER STREAMING TABLE.

If the table is cached, the command clears cached data of the table and all its dependents that refer to it. The cache will be lazily filled when the table or the dependents are accessed the next time.

Note

When you add a column to an existing Delta table, you cannot define a DEFAULT value. All columns added to Delta tables are treated as NULL for existing rows. After adding a column, you can optionally define a default value for the column, but this is only applied for new rows inserted into the table. Use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_expression

On foreign tables, you can perform only ALTER TABLE SET OWNER and ALTER TABLE RENAME TO.

Required permissions

If you use Unity Catalog you must have MODIFY permission to:

  • ALTER COLUMN

  • ADD COLUMN

  • DROP COLUMN

  • SET TBLPROPERTIES

  • UNSET TBLPROPERTIES

  • modify PREDICTIVE OPTIMIZATION

All other operations require ownership of the table.

Syntax

ALTER TABLE table_name
   { RENAME TO clause |
     ADD COLUMN clause |
     ALTER COLUMN clause |
     DROP COLUMN clause |
     RENAME COLUMN clause |
     ADD CONSTRAINT clause |
     DROP CONSTRAINT clause |
     DROP FEATURE clause |
     ADD PARTITION clause |
     DROP PARTITION clause |
     PARTITION SET LOCATION clause |
     RENAME PARTITION clause |
     RECOVER PARTITIONS clause |
     SET { ROW FILTER clause } |
     DROP ROW FILTER |
     SET TBLPROPERTIES clause |
     UNSET TBLPROPERTIES clause |
     SET SERDE clause |
     SET LOCATION clause |
     SET OWNER TO clause |
     SET SERDE clause |
     SET TAGS clause |
     UNSET TAGS clause |
     CLUSTER BY clause }
     PREDICTIVE OPTIMIZATION clause}

Parameters

  • table_name

    Identifies the table being altered. The name must not include a temporal specification. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

  • RENAME TO to_table_name

    Renames the table within the same schema.

    Note

    If you use AWS Glue Data Catalog as the metastore, RENAME is not supported.

  • ADD COLUMN

    Adds one or more columns to the table.

  • ALTER COLUMN

    Changes a property or the location of a column.

  • DROP COLUMN

    Drop one or more columns or fields in a Delta Lake table.

  • RENAME COLUMN

    Renames a column or field in a Delta Lake table.

  • ADD CONSTRAINT

    Adds a check constraint, informational foreign key constraint, or informational primary key constraint to the table.

    Foreign keys and primary keys are supported only for tables in Unity Catalog, not the hive_metastore catalog.

  • DROP CONSTRAINT

    Drops a primary key, foreign key, or check constraint from the table.

  • DROP FEATURE feature_name [ TRUNCATE HISTORY ]

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 14.1 and above

    Removes a feature from a Delta Lake table.

    Removal of features which affect both readers and writers require a two stage process:

    See table features for details.

    • feature_name

      The name of a feature in form of a STRING literal or identifier, that must be understood by Databricks and be supported on the table.

      Supported feature_names are:

      • ‘deletionVectors’ or deletionvectors

        • ‘v2Checkpoint’ or v2checkpoint

      If the feature is not present in the table Databricks raises DELTA_FEATURE_DROP_FEATURE_NOT_PRESENT.

    • TRUNCATE HISTORY

      Optionally allows you to initiate the second phase of dropping a reader-plus-writer feature after 24 hours by truncating the table history to when the invocation command was executed.

      Truncating the table history limits your ability to perform DESCRIBE HISTORY and execute time travel queries.

  • ADD PARTITION

    Adds one or more partitions to the table.

  • DROP PARTITION

    Drops one or more partitions from the table.

  • PARTITION … SET LOCATION

    Sets the location of a partition.

  • RENAME PARTITION

    Replaces the keys of a partition.

  • RECOVER PARTITIONS

    Instructs Databricks to scan the table’s location and add any files to the table which have been added directly to the filesystem.

  • SET ROW FILTER clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and above check marked yes Unity Catalog

    Preview

    This feature is in Public Preview.

    Adds a row filter function to the table. All future queries from that table will receive subset of its rows for which the function evaluates to boolean TRUE. This can be useful for fine-grained access control purposes wherein the function can inspect the identity and/or group memberships of the invoking user in order to decide whether to filter certain rows.

  • DROP ROW FILTER

    Applies to: check marked yes Unity Catalog

    Preview

    This feature is in Public Preview.

    Drops the row filter from the table, if any. Future queries will return all rows from the table without any automatic filtering.

  • SET TBLPROPERTIES

    Sets or resets one or more user defined properties.

  • UNSET TBLPROPERTIES

    Removes one or more user defined properties.

  • SET LOCATION

    Moves the location of a table.

    SET LOCATION path
    
    • LOCATION path

      path must be a STRING literal. Specifies the new location for the table.

      Files in the original location will not be moved to the new location.

  • [ SET ] OWNER TO principal

    Transfers ownership of the table to principal.

    Applies to: check marked yes Databricks SQL SQL warehouse version 2022.35 or higher check marked yes Databricks Runtime 11.2 and above

    SET is allowed as an optional keyword.

  • SET TAGS ( { tag_name = tag_value } [, ...] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Apply tags to the table. You need to have apply_tag permission to add tags to the table.

    • tag_name

      A literal STRING. The tag_name must be unique within the table or column.

    • tag_value

      A literal STRING.

  • UNSET TAGS ( tag_name [, ...] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Remove tags from the table. You need to have apply_tag permission to remove tags from the table.

    • tag_name

      A literal STRING. The tag_name must be unique within the table or column.

  • CLUSTER BY clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

    Adds, changes, or drops the clustering strategy for a Delta Lake table.

  • { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and above check marked yes Unity Catalog only

    Preview

    This feature is in Public Preview.

    Alters the managed Delta Lake table to the desired predictive optimization setting.

    By default, when tables are created, the behavior is to INHERIT from the schema.

    When predictive optimization is explicitly enabled or inherited as enabled OPTIMIZE and VACUUM will be automatically invoked on the table as deemed appropriate by Databricks. For more details see: Predictive optimization for Delta Lake.

Examples

For Delta Lake add constraints and alter column examples, see

-- RENAME table
> DESCRIBE student;
                col_name data_type comment
 ----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL
 # Partition Information
              # col_name data_type comment
                     age       int    NULL

> ALTER TABLE Student RENAME TO StudentInfo;

-- After Renaming the table
> DESCRIBE StudentInfo;
                col_name data_type comment
 ----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL
 # Partition Information
              # col_name data_type comment
                     age       int    NULL


-- RENAME partition
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=10
    age=11
    age=12

> ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');

-- After renaming Partition
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15

-- Add new columns to a table
> DESCRIBE StudentInfo;
                col_name data_type comment
 ----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL
 # Partition Information
              # col_name data_type comment
                     age       int    NULL


> ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);

-- After Adding New columns to the table
> DESCRIBE StudentInfo;
                col_name data_type comment
 ----------------------- --------- -------
                    name    string    NULL
                  rollno       int    NULL
                LastName    string    NULL
                     DOB timestamp    NULL
                     age       int    NULL
 # Partition Information
              # col_name data_type comment
                     age       int    NULL

-- Add a new partition to a table
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15

> ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);

-- After adding a new partition to the table
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15
    age=18


-- Drop a partition from the table
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15
    age=18

> ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);

-- After dropping the partition of the table
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15

-- Adding multiple partitions to the table
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15


> ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);

-- After adding multiple partitions to the table
> SHOW PARTITIONS StudentInfo;
 partition
 ---------
    age=11
    age=12
    age=15
    age=18
    age=20


-- ALTER or CHANGE COLUMNS
> DESCRIBE StudentInfo;
                col_name data_type comment
+-----------------------+---------+-------
                    name    string    NULL
                  rollno       int    NULL
                LastName    string    NULL
                     DOB timestamp    NULL
                     age       int    NULL
 # Partition Information
              # col_name data_type comment
                     age       int    NULL

ALTER TABLE StudentInfo ALTER COLUMN name COMMENT "new comment";

--After ALTER or CHANGE COLUMNS
> DESCRIBE StudentInfo;
                col_name data_type     comment
 ----------------------- --------- -----------
                    name    string new comment
                  rollno       int        NULL
                LastName    string        NULL
                     DOB timestamp        NULL
                     age       int        NULL
 # Partition Information
              # col_name data_type     comment
                     age       int        NULL

-- RENAME COLUMN
> ALTER TABLE StudentInfo RENAME COLUMN name TO FirstName;

--After RENAME COLUMN
> DESCRIBE StudentInfo;
                col_name data_type     comment
 ----------------------- --------- -----------
               FirstName    string new comment
                  rollno       int        NULL
                LastName    string        NULL
                     DOB timestamp        NULL
                     age       int        NULL
 # Partition Information
              # col_name data_type     comment
                     age       int        NULL

-- Change the file Location
> ALTER TABLE dbx.tab1 PARTITION (a='1', b='2') SET LOCATION '/path/to/part/ways';

-- SET SERDE/ SERDE Properties (DBR only)
> ALTER TABLE test_tab SET SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';

> ALTER TABLE dbx.tab1 SET SERDE 'org.apache.hadoop' WITH SERDEPROPERTIES ('k' = 'v', 'kay' = 'vee');

-- SET TABLE PROPERTIES
> ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser');

-- DROP TABLE PROPERTIES
> ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner');

-- Drop the "deletion vectors" from a Delta table
> ALTER TABLE my_table DROP FEATURE deletionVectors;

-- 24 hours later
> ALTER TABLE my_table DROP FEATURE deletionVectors TRUNCATE HISTORY;

-- Applies three tags to the table named `test`.
> ALTER TABLE test SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');

-- Removes three tags from the table named `test`.
> ALTER TABLE test UNSET TAGS ('tag1', 'tag2', 'tag3');

-- Applies three tags to table `main.schema1.test` column `col1`.
> ALTER TABLE main.schema1.test ALTER COLUMN col1 SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');

-- Removes three tags from table `main.schema1.test` column `col1`.
> ALTER TABLE main.schema1.test ALTER COLUMN col1 UNSET TAGS ('tag1', 'tag2', 'tag3');
-- Enables predictive optimization for my_table
> ALTER TABLE my_table ENABLE PREDICTIVE OPTIMIZATION;