Share via

CREATE CATALOG

  • Article

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 ]

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

      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.

    • 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');