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.

  • Your Databricks account must be on the Premium plan and 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 Data Explorer or SQL commands.

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

  2. Click Data Icon Data.

  3. In the Data 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.

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

  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.

    • <comment>: An optional comment.

    • <property_name> = <property_value> [ , ... ]: The 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>
        [ COMMENT <comment> ]
        [ WITH DBPROPERTIES ( <property_name = 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.

  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.

    • <comment>: An optional comment.

    • <property_name> = <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> " \
      "[ COMMENT <comment> ] " \
      "[ WITH DBPROPERTIES ( <property_name = 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.

  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.

    • <comment>: An optional comment.

    • <property_name> = <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> ",
      "[ COMMENT <comment> ] ",
      "[ WITH DBPROPERTIES ( <property_name = 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.

  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.

    • <comment>: An optional comment.

    • <property_name> = <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> " +
      "[ COMMENT <comment> ] " +
      "[ WITH DBPROPERTIES ( <property_name = 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 Data Explorer or a SQL command.

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 Data Icon Data.

  3. In the Data 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.

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