ALTER SCHEMA

  • Article

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

Changes the owner of a schema, sets predictive optimization behavior, or alters metadata associated with a schema by setting DBPROPERTIES. The specified property values override any existing value with the same property name.

While usage of SCHEMA and DATABASE is interchangeable, SCHEMA is preferred.

Syntax

ALTER SCHEMA schema_name
   { SET DBPROPERTIES ( { key = val } [, ...] ) |
    [ SET ] OWNER TO principal
    SET TAGS ( { tag_name = tag_value } [, ...] ) |
    UNSET TAGS ( tag_name [, ...] ) |
    { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION } }

Parameters

  • schema_name

    The name of the schema to be altered. If the schema cannot be found, Azure Databricks raises a SCHEMA_NOT_FOUND error.

  • DBPROPERTIES ( key = val [, …] )

    The schema properties to be set or unset.

  • [ SET ] OWNER TO principal

    Transfers ownership of the schema to principal.

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

    SET is allowed as an optional keyword.

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

    Apply tags to the schema. You need to have use_schema to apply a tag to the schema.

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

  • UNSET TAGS ( tag_name [, …] )

    Remove tags from the schema. You need to have use_schema to remove a tag from the schema.

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

  • tag_name

    A literal STRING. The tag_name must be unique within the schema.

  • tag_value

    A literal STRING.

  • { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION

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

    Alters the schema to the desired predictive optimization setting. By default, when schemas are created, the behavior is to INHERIT from the catalog. By default, new objects created in the schema will inherit the setting from the schema.

    If the schema is altered, the behavior will cascade to all objects that inherit predictive optimization. Objects which explicitly ENABLE or DISABLE predictive optimization are not affected by the schema setting.

    To set predictive optimization for a schema, the user must have CREATE permission on the schema.

    Only schemas in non-Delta Sharing, managed catalogs in Unity Catalog are eligible for predictive optimization.

Examples

-- Creates a schema named `inventory`.
> CREATE SCHEMA inventory;

-- Alters the schema to set properties `Edited-by` and `Edit-date`.
> ALTER SCHEMA inventory SET DBPROPERTIES ('Edited-by' = 'John', 'Edit-date' = '01/01/2001');

-- Verify that properties are set.
> DESCRIBE SCHEMA EXTENDED inventory;
 database_description_item                 database_description_value
 ------------------------- ------------------------------------------
             Database Name                                  inventory
               Description
                  Location    file:/temp/spark-warehouse/inventory.db
                Properties ((Edit-date,01/01/2001), (Edited-by,John))

-- Transfer ownership of the schema to another user
> ALTER SCHEMA inventory OWNER TO `alf@melmak.et`

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

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

-- Enables predictive optimization for objects in schema my_schema
> ALTER SCHEMA my_schema ENABLE PREDICTIVE OPTIMIZATION;