Share via

CREATE CATALOG

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

Creates a catalog with the specified name. If a catalog with the same name already exists, an exception is thrown.

When you create a FOREIGN catalog it will be populated with all the schemas and their tables visible to the authenticating user.

Syntax

CREATE CATALOG [ IF NOT EXISTS ] catalog_name
    [ USING SHARE provider_name . share_name |
      MANAGED LOCATION location_path |
      COMMENT comment |
      OPTIONS ( { option_name = option_value } [ , ... ] ) ] [...]

CREATE FOREIGN CATALOG [ IF NOT EXISTS ] catalog_name
    USING CONNECTION connection_name
    [ COMMENT comment ]
    OPTIONS ( { option_name = option_value } [ , ... ] )

Parameters

  • FOREIGN

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

    Specifies that the catalog is imported from a CONNECTION.

  • IF NOT EXISTS

    Creates a catalog with the given name if it does not exist. If a catalog with the same name already exists, nothing will happen.

  • catalog_name

    The name of the catalog to be created.

  • USING SHARE provider_name . share_name

    Optionally specifies that the catalog is based on a Delta Sharing share.

  • MANAGED LOCATION location_path

    Optionally specifies the path to a managed storage ___location for the catalog that is different than the metastore's root storage ___location. This path must be defined in an external ___location configuration, and you must have the CREATE MANAGED STORAGE privilege on the external ___location configuration. You can use the path that is defined in the external ___location configuration or a subpath (in other words, 'abfss://container@storageaccount.dfs.core.windows.net/finance' or 'abfss://container@storageaccount.dfs.core.windows.net/finance/product'). Supported in Databricks SQL or on clusters running Databricks Runtime 11.3 LTS and above.

    See also Work with managed tables and Create a Unity Catalog metastore.

  • USING CONNECTION connection_name

    Specifies the connection where the source catalog resides.

  • comment

    An optional STRING literal. The description for the catalog.

  • OPTIONS

    Sets connection-type specific parameters needed to identify the catalog at the connection.

    • option_name

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

      Option keys must be unique and are case-sensitive.

    • option_value

      The value for the option. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL constant expression. The value may also be a call to the SECRET SQL function. For example, the value for password may comprise secret('secrets.r.us', 'postgresPassword') instead of entering the literal password.

Examples

-- Create catalog `customer_cat`. This throws exception if catalog with name customer_cat
-- already exists.
> CREATE CATALOG customer_cat;

-- Create catalog `customer_cat` only if catalog with same name doesn't exist.
> CREATE CATALOG IF NOT EXISTS customer_cat;

-- Create catalog `customer_cat` only if catalog with same name doesn't exist, with a comment.
> CREATE CATALOG IF NOT EXISTS customer_cat COMMENT 'This is customer catalog';

-- Create a catalog from a Delta Sharing share.
> CREATE CATALOG customer_cat USING SHARE cdc.vaccinedata;

-- Create a catalog with a different managed storage ___location than the metastore's.
> CREATE CATALOG customer_cat MANAGED LOCATION 'abfss://container@storageaccount.dfs.core.windows.net/finance';

-- Create a foreign catalog linked to postgresdb at postgresql_connection
> CREATE FOREIGN CATALOG postgresql_catalog
     USING CONNECTION postgresql_connection
     OPTIONS (database 'postgresdb');