Create schemas

  • Article

This article shows how to create schemas in Unity Catalog and the legacy Hive metastore.

To learn about schemas in Azure Databricks, including a comparison of schema behavior in Unity Catalog and Hive metastore, see What are schemas in Azure Databricks?.

Before you begin

To create a schema in Unity Catalog:

  • You must have a Unity Catalog metastore linked to the workspace where you perform the schema creation.
  • 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.
  • To specify an optional managed storage location for the tables and volumes in the schema, an external location must be defined in Unity Catalog, and you must have the CREATE MANAGED STORAGE privilege on the external location. See Specify a managed storage location in Unity Catalog.
  • 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.

To create a schema in Hive metastore:

Create a schema

To create a schema in Unity Catalog, you can use Catalog Explorer or SQL commands. To create a schema in Hive metastore, you must use SQL commands.

Catalog Explorer

  1. Log in to a workspace that is linked to the Unity Catalog 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 schema.
  5. Give the schema a name and add any comment that would help users understand the purpose of the schema.
  6. (Optional) Specify a managed storage location. Requires the CREATE MANAGED STORAGE privilege on the target external location. See Specify a managed storage location in Unity Catalog and Managed locations for schemas.
  7. Click Create.
  8. Grant privileges on the schema. See Manage privileges in Unity Catalog.
  9. Click Save.

SQL

  1. Run the following SQL commands in a notebook or the SQL query 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. If you are creating a schema in Hive metastore and you are in a Unity Catalog-enabled workspace, use hive_metastore as the catalog name. If your workspace is not enabled for Unity Catalog, don't specify a catalog at all.
    • <schema-name>: A name for the schema.
    • <location-path>: Optional path to a managed storage location. Use with MANAGED LOCATION for Unity Catalog and with LOCATION for Hive metastore. In Unity Catalog, you must have the CREATE MANAGED STORAGE privilege on the external location for the path that you specify. See Specify a managed storage location in Unity Catalog and Managed locations for schemas.
    • <comment>: Optional description or other comment.
    • <property-key> = <property-value> [ , ... ]: Optional. Spark SQL properties and values to set for the schema.

    For more detailed parameter descriptions, see CREATE SCHEMA.

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

  2. Grant privileges on the schema. For Unity Catalog privileges, see Manage privileges in Unity Catalog.

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