Create and manage schemas (databases)

Preview

Unity Catalog is in Public Preview. To participate in the preview, contact your Databricks representative.

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

Requirements

  • You must be a Databricks account admin, metastore admin, or a user with USAGE and CREATE data permissions on the parent catalog.

  • 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, Databricks SQL editor, or Data Explorer workflow to create the schema must be compliant with Unity Catalog security requirements.

  • You must have the USAGE and CREATE data permissions on the schema‚Äôs parent catalog.

Create a schema

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

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

  2. From the persona switcher, select SQL.

  3. Click Data Icon Data.

  4. In the Data pane on the left, click the catalog you want to create the schema in.

  5. In the detail pane, click Create database.

  6. Give the schema a name and add any comment that would help users understand the purpose of the schema, then click Create.

  7. Assign permissions for your catalog. See Manage privileges.

  8. 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 Manage privileges.

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

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

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

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 the 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. From the persona switcher, select SQL.

  3. Click Data Icon Data.

  4. In the Data pane, on the left, click the schema (database) that you want to delete.

  5. In the detail pane, click the three-dot menu in the upper right corner and select Delete.

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