ALTER TABLE (Databricks SQL)

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 use COMMNENT ON.

Syntax

ALTER TABLE table_name
   { RENAME clause |
     ADD COLUMN clause |
     ALTER COLUMN clause |
     ADD CONSTRAINT clause |
     DROP CONSTRAINT clause |
     SET LOCATION clause |
     ADD PARTITION clause |
     DROP PARTITION clause |
     RENAME PARTITION clause |
     SET TBLPROPERTIES clause |
     UNSET TBLPROPERTIES clause |
     SET LOCATION clause }

Parameters

• table_name

  Identifies the table being altered. The name must not include a temporal specification.

• RENAME TO to_table_name

  Renames the table to a new table in the same database.

  • to_table_name

    Identifies the new table name. The name must not include a temporal specification.

• ADD COLUMN

  This clause is not supported for JDBC data sources.

  Adds one or more columns to the table, or fields to existing columns in a Delta Lake table.

  { ADD [COLUMNS | COLUMNS ]
    ( { {column_identifier | field_name} data_type [COMMENT comment] [FIRST | AFTER identifier] } [, ...] ) }
  

  • column_identifier

    The name of the column to be added. The name must be unique within the table.

    Unless FIRST or AFTER name are specified the column or field will be appended at the end.

  • field_name

    The fully qualified name of the field to be added to an existing column. All components of the path to the nested field must exist and the field name itself must be unique.

  • COMMENT comment

    An optional STRING literal describing the added column or field.

  • FIRST

    If specified the column will be added as the first column of the table, or the field will be added as the first field of in the containing struct.

  • AFTER identifier

    If specified the column or field will be added immediately after the field or column identifier.

• ALTER COLUMN

  Changes a property or the location of a column.

  { { ALTER | CHANGE } [COLUMN] { column_identifier | field_name }
    { COMMENT comment |
      { FIRST | AFTER column_identifier } |
      { SET | DROP } NOT NULL } } }
  

  • column_identifier

    The name of the column to be altered.

  • field_name

    The fully qualified name of the field to be altered. All components of the path to the nested field must exist.

  • COMMENT comment

    Changes the description of the column_name column. comment must be a STRING literal.

  • FIRST or AFTER identifier

    Moves the column from its current position to the front (FIRST) or immediately AFTER the identifier. This clause is only supported if table_name is a Delta table.

  • SET NOT NULL or DROP NOT NULL

    Changes the domain of valid column values to exclude nulls SET NOT NULL, or include nulls DROP NOT NULL. This option is only supported for Delta Lake tables. Delta Lake will ensure the constraint is valid for all existing and new data.

• ADD CONSTRAINT constraint_name CHECK ( condition )

  Adds a check constraint to the Delta Lake table.

  • constraint_name

    An identifier that is unique within the table.

  • CHECK ( condition )

    An deterministic expression returning a BOOLEAN.

    condition may be composed of literals, column identifiers within the table, and deterministic, built-in SQL functions or operators except:

    • Aggregate functions
    • Analytic window functions
    • Ranking window functions
    • Table valued generator functions

    Also condition must not contain any subquery.

    For a CHECK constraint to be satisfied in Databricks it must evaluate to true.

    Delta Lake verified the validity of the check constraint against both new and existing data. If any existing row violates the constraint an error will be raised.

• DROP CONSTRAINT constraint_name

  Removes a check constraint from the Delta Lake table.

  • constraint_name

    The name of the constraint to be dropped.

• ADD PARTITION

  If specified adds one or more partitions to the table. Adding partitions is not supported for Delta Lake tables.

  ADD [IF NOT EXISTS] { partition_spec [ LOCATION path ] } [...]
  

  • IF NOT EXISTS

    An optional clause directing Databricks SQL to ignore the statement if the partition already exists.

  • partition_spec

    A partitions to be added. The partition keys must match the partitioning of the table. If the partition already exists an error is raised unless IF NOT EXISTS has been specified.

  • LOCATION path

    path must be a STRING literal representing an optional location pointing to the partition.

    If no location is specified the location will be derived from the location of the table and the partition keys.

    If there are files present at the location they populate the partition and must be compatible with the data_source of the table and its options.

• DROP PARTITION

  If specified this clause drops one or more partitions from the table, optionally deleting any files at the partitions’ locations.

  Delta Lake tables do not support dropping of partitions.

  DROP [ IF EXISTS ] partition_spec [, ...] [PURGE]
  

  • IF EXISTS

    When you specify IF EXISTS Databricks will ignore an attempt to drop partitions that do not exists. Otherwise, non existing partitions will cause an error.

  • partition_spec

    Specifies a partition to be dropped.

  • PURGE

    If set, the table catalog must remove partition data by skipping the Trash folder even when the catalog has configured one. The option is applicable only for managed tables. It is effective only when:

    The file system supports a Trash folder. The catalog has been configured for moving the dropped partition to the Trash folder. There is no Trash folder in AWS S3, so it is not effective.

    There is no need to manually delete files after dropping partitions.

• RENAME PARTITION

  Replaces the keys of a partition.

  Delta Lake tables do not support renaming partitions.

  from_partition_spec RENAME TO partition_spec
  

  • from_partition_spec

    The definition of the partition to be renamed.

  • partition_spec

    The new definition for this partition. A partition with the same keys must not already exist.

• SET TBLPROPERTIES

  Sets one or more user defined properties of the table. Properties are key value pairs. If the properties’ keys exist, the values are replaced with the new values. If the properties’ keys do not exist, the key-value pairs are added to the properties.

  SET TBLPROPERTIES ( { property_key = property_val } [, ...] )
  

  • property_key

    The property key. The key can consist of one or more identifiers separated by a dot, or a string literal.

    Property keys must be unique.

  • property_val

    The new value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

• UNSET TBLPROPERTIES

  Removes one or more user defined properties of table_name.

  UNSET TBLPROPERTIES [ IF EXISTS ] ( property_key [, ...] )
  

  • IF EXISTS

    Unless IF EXISTS is specified an error is raided if the property has not been set.

  • property_key

    The key can consist of one or more identifiers separated by a dot, or a string literal.

    Property keys must be unique.

• SET LOCATION

  Moves the location of a partition or table and all files within.

  Delta Lake does not support moving individual partitions of a Delta Lake table.

  [ partition_spec ] SET LOCATION path
  

  • partition_spec

    Optionally identifies the partition for which the location will to be changed. If you omit naming a partition Databricks moves the location of the table.

  • LOCATION path

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

    All files in the original location will be moved to the new location.

• partition_spec

  Specifies a single partition by assigning a value to each partition column.

  PARTITION ( { partition_column  = partition_val } [ , ... ] )
  

  • partition_column

    An column named as a partition column of the table. If you specify more than one column there must be no duplicates.

  • partition_value

    A literal of a data type matching the type of the partition column.

Examples

For Delta Lake add, and alter column examples, see

• Explicitly update schema
• Add columns
• Change column comment or ordering
• Constraints

-- RENAME table
> DESC 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
> DESC 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
> DESC 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
> DESC 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
> DESC 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
> DESC 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

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

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

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

Related articles

• ALTER VIEW
• COMMENT ON
• CREATE TABLE
• DROP TABLE