ALTER CATALOG

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

Transfers the ownership of a catalog to a new principal, changes the managed storage location of a catalog, applies tags to a catalog, enables or disables predictive optimization for a catalog, or sets the recovery period for dropped managed tables.

Syntax

ALTER CATALOG [ catalog_name ]
 { DEFAULT COLLATION default_collation_name |
   [ SET ] OWNER TO principal
   SET TAGS ( { tag_name = tag_value } [, ...] ) |
   UNSET TAGS ( tag_name [, ...] ) |
   { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION |
   OPTIONS (option value [, ...] ) |
   SET MANAGED LOCATION location |
   [ SET ] RETAIN DROPPED TO number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } }

Parameters

  • catalog_name

    The name of the catalog to be altered. If you provide no name the default is hive_metastore.

  • DEFAULT COLLATION default_collation_name

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

    Changes the default collation for new schemas defined within the catalog. The default collation of existing schemas and objects within the catalog is not changed.

  • [ SET ] OWNER TO principal

    Transfers ownership of the catalog 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 catalog. You need to have USE CATALOG permission to apply a tag to a catalog. See USE CATALOG.

    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 catalog. You need to have USE CATALOG permission to apply a tag to a catalog.

    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 catalog.

  • 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

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

    If the catalog is altered, the behavior will cascade to all schemas and their objects, which inherit predictive optimization. Objects in schemas that do not inherit predictive optimization or objects that explicitly ENABLE or DISABLE predictive optimization are not affected by the catalog setting.

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

    Only non-OpenSharing, managed catalogs in Unity Catalog are eligible for predictive optimization.

  • OPTIONS

    Sets catalog-specific parameters. Replaces the existing list of options with a new list of options.

    Use OPTIONS to set authorized paths for foreign catalogs created using Hive metastore federation.

    • option

      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 and are case-sensitive.

    • value

      The value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL constant expression.

      For example a the value for password may be using the constant expression secret('secrets.r.us', 'postgresPassword') as opposed to entering the literal password.

  • SET MANAGED LOCATION location

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

    Changes the managed storage location for the catalog. New managed tables and managed volumes created in the catalog use the updated location. Databricks does not move existing objects.

    • location

      The cloud storage path for the new managed storage location. It must be contained within an external location.

  • [ SET ] RETAIN DROPPED TO number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

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

    Important

    This feature is in Public Preview.

    Sets the recovery period for dropped managed tables in the catalog — the period during which dropped tables can be recovered using the UNDROP TABLE command. The default is 7 days. The value must be 0 hours (to disable recovery) or between 7–30 days, inclusive.

    Setting the recovery period to 0 means dropped tables are not recoverable using UNDROP and data files are deleted from cloud storage within 48 hours.

    If a recovery period is also set on a child schema, the schema-level setting takes precedence for tables in that schema. This setting only applies to tables dropped after the recovery period is configured. After the recovery period ends, data files for managed tables are deleted from your cloud storage within 48 hours.

    SET is allowed as an optional keyword. Requires MANAGE privilege or ownership on the catalog. See also Drop a managed table.

Examples

-- Creates a catalog named `some_cat`.
> CREATE CATALOG some_cat;

-- Transfer ownership of the catalog to another user
> ALTER CATALOG some_cat OWNER TO `alf@melmak.et`;

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

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

-- Change the default collation of a catalog to case sensitive unicode
> ALTER CATALOG test DEFAULT COLLATION UNICODE_CS;

-- Adds an authorized path to a foreign catalog created using :re[HMS] federation.
> ALTER CATALOG my_federated_catalog OPTIONS (authorized_paths 'path/to/dir1, path/to/dir2');

–– Enables predictive optimization for catalog main
> ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION;
> DESCRIBE CATALOG EXTENDED main;
  Key                        value
  ------------------------    ---------
  < other rows of describe extended >
  Predictive Optimization    ENABLE

-- Sets catalog main to inherit setting from parent object (metastore)
> ALTER CATALOG main INHERIT PREDICTIVE OPTIMIZATION;
> DESCRIBE CATALOG EXTENDED main;
  Key                         value
  ------------------------    ----------------------------------------
  < other rows of describe extended >
  Predictive Optimization     ENABLE (inherited from METASTORE metastore_name)

-- Change the managed storage location of a catalog
> ALTER CATALOG my_catalog SET MANAGED LOCATION 's3://my-bucket/managed/';

-- Set a 30-day recovery period for dropped managed tables in a catalog
> ALTER CATALOG my_catalog RETAIN DROPPED TO 30 DAYS;

-- Set the recovery period to 0 (tables are not recoverable after drop)
> ALTER CATALOG my_catalog SET RETAIN DROPPED TO 0 DAYS;
  • Last updated on