Create and manage schemas (databases)

  • Article

This article shows how to create and manage schemas (databases) in Unity Catalog. A schema contains tables, views, volumes, models, and functions. You create schemas inside catalogs.

Requirements

  • You must have the USE CATALOG and CREATE SCHEMA data permissions on the schema’s parent catalog. Either a metastore admin or the owner of the catalog can grant you these privileges. If you are a metastore admin, you can grant these privileges to yourself.

    All users have the USE CATALOG privilege on the main catalog by default.

    Important

    The above statement does not apply if your workspace was enabled for Unity Catalog automatically. Instead of a main catalog, your workspace has an auto-provisioned workspace catalog, and all users have the USE CATALOG privilege on that catalog. See Automatic enablement of Unity Catalog.

  • You must have a Unity Catalog metastore linked to the workspace where you perform the schema creation.

  • The cluster that you use to run a notebook to create a schema must use a Unity Catalog-compliant access mode. See Access modes.

    SQL warehouses always support Unity Catalog.

Create a schema

To create a schema (database), you can use Catalog Explorer or SQL commands.

Catalog explorer

  1. Log in to a workspace that is linked to the metastore.

  2. Click Catalog icon Catalog.

  3. In the Catalog pane on the left, click the catalog you want to create the schema in.

  4. In the detail pane, click Create database.

  5. Give the schema a name and add any comment that would help users understand the purpose of the schema.

  6. (Optional) Specify the location where data for managed tables in the schema will be stored.

    Note

    This option appears only if you are using a SQL warehouse or a cluster running Databricks Runtime 11.3 or above.

    Specify a location here only if you want managed tables and volumes in this schema to be stored in a location that is isolated from the catalog storage location or metastore storage location. See Managed storage.

    The path that you specify must be defined in an external location configuration, and you must have the CREATE MANAGED STORAGE privilege on that external location. You can also use a subpath of that path. See Manage external locations and storage credentials.

  7. Click Create.

  8. Assign permissions for your catalog. See Unity Catalog privileges and securable objects.

  9. Click Save.

Sql

  1. Run the following SQL commands in a notebook or Databricks SQL editor. Items in brackets are optional. You can use either SCHEMA or DATABASE. Replace the placeholder values:

    • <catalog-name>: The name of the parent catalog for the schema.
    • <schema-name>: A name for the schema.
    • <location-path>: Optional. Available only if you are using Databricks SQL or a cluster running Databricks Runtime 11.3 or above. Provide a storage location path only if you want managed tables and volumes in this schema to be stored in a location that is isolated from the catalog storage location or metastore 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://my-container-name@storage-account-name.dfs.core.windows.net/finance' or 'abfss://my-container-name@storage-account-name.dfs.core.windows.net/finance/product').
    • <comment>: Optional description or other comment.
    • <property-key> = <property-value> [ , ... ]: Optional. Spark SQL properties and values to set for the schema.

    For parameter descriptions, see CREATE SCHEMA.

    USE CATALOG <catalog>;
CREATE { DATABASE | SCHEMA } [ IF NOT EXISTS ] <schema-name>
    [ MANAGED LOCATION '<location-path>' ]
    [ COMMENT <comment> ]
    [ WITH DBPROPERTIES ( <property-key = property_value [ , ... ]> ) ];

    You can optionally omit the USE CATALOG statement and replace <schema-name> with <catalog-name>.<schema-name>.

  2. Assign privileges to the schema. See Unity Catalog privileges and securable objects.

Python

  1. Run the following SQL commands in a notebook. Items in brackets are optional. You can use either SCHEMA or DATABASE. Replace the placeholder values:

    • <catalog-name>: The name of the parent catalog for the schema.
    • <schema-name>: A name for the schema.
    • <location-path>: Optional. Available only if you are using Databricks SQL or a cluster running Databricks Runtime 11.3 or above. Provide a storage location path only if you want managed tables and volumes in this schema to be stored in a location that is isolated from the catalog storage location or metastore 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://my-container-name@storage-account-name.dfs.core.windows.net/finance' or 'abfss://my-container-name@storage-account-name.dfs.core.windows.net/finance/product').
    • <comment>: An optional comment.
    • <property-key> = <property-value> [ , ... ]: The Spark SQL properties and values to set for the schema.

    For parameter descriptions and more options, see CREATE SCHEMA.

    spark.sql("USE CATALOG <catalog>")

spark.sql("CREATE { DATABASE | SCHEMA } [ IF NOT EXISTS ] <schema-name> " \
  "[ MANAGED LOCATION '<location-path>' ] " \
  "[ COMMENT <comment> ] " \
  "[ WITH DBPROPERTIES ( <property-key = property_value [ , ... ]> ) ]")

    You can optionally omit the USE CATALOG statement and replace <schema-name> with <catalog-name>.<schema-name>.

  2. Assign privileges to the schema. See Unity Catalog privileges and securable objects.

R

  1. Run the following SQL commands in a notebook. Items in brackets are optional. You can use either SCHEMA or DATABASE. Replace the placeholder values:

    • <catalog-name>: The name of the parent catalog for the schema.
    • <schema-name>: A name for the schema.
    • <location-path>: Optional. Available only if you are using Databricks SQL or a cluster running Databricks Runtime 11.3 or above. Provide a storage location path only if you want managed tables and volumes in this schema to be stored in a location that is isolated from the catalog storage location or metastore 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://my-container-name@storage-account-name.dfs.core.windows.net/finance' or 'abfss://my-container-name@storage-account-name.dfs.core.windows.net/finance/product').
    • <comment>: An optional comment.
    • <property-key> = <property-value> [ , ... ]: The Spark SQL properties and values to set for the schema.

    For parameter descriptions, see CREATE SCHEMA.

    library(SparkR)

sql("USE CATALOG <catalog>")

sql(paste("CREATE { DATABASE | SCHEMA } [ IF NOT EXISTS ] <schema-name> ",
  "[ MANAGED LOCATION '<location-path>' ] " ,
  "[ COMMENT <comment> ] ",
  "[ WITH DBPROPERTIES ( <property-key> = <property-value> [ , ... ] ) ]",
  sep = ""))

    You can optionally omit the USE CATALOG statement and replace <schema-name> with <catalog-name>.<schema-name>.

  2. Assign privileges to the schema. See Unity Catalog privileges and securable objects.

Scala

  1. Run the following SQL commands in a notebook. Items in brackets are optional. You can use either SCHEMA or DATABASE. Replace the placeholder values:

    • <catalog-name>: The name of the parent catalog for the schema.
    • <schema-name>: A name for the schema.
    • <location-path>: Optional. Provide a storage location path only if you want managed tables and volumes in this schema to be stored in a location that is isolated from the catalog storage location or metastore 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://my-container-name@storage-account-name.dfs.core.windows.net/finance' or 'abfss://my-container-name@storage-account-name.dfs.core.windows.net/finance/product'). Requires Databricks Runtime 11.3 and above.
    • <comment>: An optional comment.
    • <property-key> = <property-value> [ , ... ]: The Spark SQL properties and values to set for the schema.

    For parameter descriptions, see CREATE SCHEMA.

    spark.sql("USE CATALOG <catalog>")

spark.sql("CREATE { DATABASE | SCHEMA } [ IF NOT EXISTS ] <schema-name> " +
  "[ MANAGED LOCATION '<location-path>' ] " +
  "[ COMMENT <comment> ] " +
  "[ WITH DBPROPERTIES ( <property-key> = <property-value> [ , ... ] ) ]")

    You can optionally omit the USE CATALOG statement and replace <schema-name> with <catalog-name>.<schema-name>.

  2. Assign privileges to the schema. See Unity Catalog privileges and securable objects.

You can also create a schema by using the Databricks Terraform provider and databricks_schema. You can retrieve a list of schema IDs by using databricks_schemas.

Next steps

Now you can add tables to your schema.

Delete a schema

To delete (or drop) a schema (database), you can use Catalog Explorer or a SQL command. To drop a schema you must be its owner.

Catalog explorer

You must delete all tables in the schema before you can delete it.

  1. Log in to a workspace that is linked to the metastore.
  2. Click Catalog icon Catalog.
  3. In the Catalog pane, on the left, click the schema (database) that you want to delete.
  4. In the detail pane, click the three-dot menu in the upper right corner and select Delete.
  5. On the Delete Database dialog, click Delete.

Sql

Run the following SQL command in a notebook or Databricks SQL editor. Items in brackets are optional. Replace the placeholder <schema-name>.

For parameter descriptions, see DROP SCHEMA.

If you use DROP SCHEMA without the CASCADE option, you must delete all tables in the schema before you can delete it.

DROP SCHEMA [ IF EXISTS ] <schema-name> [ RESTRICT | CASCADE ]

For example, to delete a schema named inventory_schema and its tables:

DROP SCHEMA inventory_schema CASCADE

Python

Run the following SQL command in a notebook. Items in brackets are optional. Replace the placeholder <schema-name>.

For parameter descriptions, see DROP SCHEMA.

If you use DROP SCHEMA without the CASCADE option, you must delete all tables in the schema before you can delete it.

spark.sql("DROP SCHEMA [ IF EXISTS ] <schema-name> [ RESTRICT | CASCADE ]")

For example, to delete a schema named inventory_schema and its tables:

spark.sql("DROP SCHEMA inventory_schema CASCADE")

R

Run the following SQL command in a notebook. Items in brackets are optional. Replace the placeholder <schema-name>.

For parameter descriptions, see DROP SCHEMA.

If you use DROP SCHEMA without the CASCADE option, you must delete all tables in the schema before you can delete it.

library(SparkR)

sql("DROP SCHEMA [ IF EXISTS ] <schema-name> [ RESTRICT | CASCADE ]")

For example, to delete a schema named inventory_schema and its tables:

library(SparkR)

sql("DROP SCHEMA inventory_schema CASCADE")

Scala

Run the following SQL command in a notebook. Items in brackets are optional. Replace the placeholder <schema-name>.

For parameter descriptions, see DROP SCHEMA.

If you use DROP SCHEMA without the CASCADE option, you must delete all tables in the schema before you can delete it.

spark.sql("(DROP SCHEMA [ IF EXISTS ] <schema-name> [ RESTRICT | CASCADE ]")

For example, to delete a schema named inventory_schema and its tables:

spark.sql("DROP SCHEMA inventory_schema CASCADE")