Create and manage schemas (databases)

This article shows how to create and manage schemas (databases) in Unity Catalog. A schema contains tables, views, 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 permission on the main catalog by default.
Your Databricks account must be on the Premium plan or above.
You must have a Unity Catalog metastore linked to the workspace where you perform the schema creation.
The compute resource that you use to run the notebook or Databricks SQL to create the catalog must be using a Unity Catalog compliant access mode.

Create a schema

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

Log in to a workspace that is linked to the metastore.
Click Catalog.
In the Catalog pane on the left, click the catalog you want to create the schema in.
In the detail pane, click Create database.
Give the schema a name and add any comment that would help users understand the purpose of the schema.
(Optional) Specify the location where data for managed tables in the schema will be stored.

Note

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

Specify a location here only if you do not want managed tables in this schema to be stored in the default root storage location that was configured for the metastore or the managed storage location specified for the catalog (if any). See Create a Unity Catalog metastore.

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.
Click Create.
Assign permissions for your catalog. See Unity Catalog privileges and securable objects.
Click Save.

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 if you want managed tables in this schema to be stored in a location that is different than the catalog’s or 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, 's3://depts/finance' or 's3://depts/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>.
Assign privileges to the schema. See Unity Catalog privileges and securable objects.

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 if you want managed tables in this schema to be stored in a location that is different than the catalog’s or 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, 's3://depts/finance' or 's3://depts/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>.
Assign privileges to the schema. See Unity Catalog privileges and securable objects.

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 if you want managed tables in this schema to be stored in a location that is different than the catalog’s or 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, 's3://depts/finance' or 's3://depts/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>.
Assign privileges to the schema. See Unity Catalog privileges and securable objects.

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 if you want managed tables in this schema to be stored in a managed location that is different than the catalog’s or 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, 's3://depts/finance' or 's3://depts/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>.
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.

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

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

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

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")

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")

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")