Jaa

Manage schemas

  • Artikkeli

This article shows how to view, update, and delete schemas in Unity Catalog.

See also What are schemas in Azure Databricks? and Create schemas.

Note

In Hive metastore, you must use a SQL commands to manage schemas. Behavior in legacy Hive metastore differs from Unity Catalog. Permissions required depend on whether or not you are using table access control. See Schemas in Hive metastore and Hive metastore privileges and securable objects (legacy).

Before you begin

To work with schemas in 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 manage a schema must use a Unity Catalog-compliant access mode. See Access modes. SQL warehouses always support Unity Catalog.
  • Permissions required to alter, view, and drop schemas in Unity Catalog are listed in the sections that follow.

Find and view schemas

In Unity Catalog, users can see all schemas on which they have been assigned the USE SCHEMA permission, along with the USE CATALOG permission on the schema’s parent catalog. To access or list a table or view in a schema, users must also have SELECT permission on the table or view.

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 that contains the schema (hive_metastore if it’s in the Hive metastore).

Alternatively, you can search for the schema using the Type to filter field in the Catalog pane.

SQL

Run SHOW SCHEMAS in a notebook or the SQL query editor to list all schemas in a catalog or those that match a regular expression. Run DESCRIBE SCHEMA to get details about a schema. See SHOW SCHEMAS and DESCRIBE SCHEMA.

Update a schema

To update (or alter) a schema in a Unity Catalog catalog, you can use Catalog Explorer or a SQL command.

Permissions required: Permissions required to update a schema in Unity Catalog depend on the update:

  • To change the owner, you must be the owner or have MANAGE and USE SCHEMA on the schema and USE CATALOG on the parent catalog.
  • To rename the schema, you must be the owner or have MANAGE and USE SCHEMA on the schema and USE CATALOG on the parent catalog.
  • To add or update a comment, you must be the owner or have USE SCHEMA and MANAGE or MODIFY on the schema and USE CATALOG on the parent catalog.
  • To add or update a tags, you must be the owner or have USE SCHEMA and MODIFY on the schema and USE CATALOG on the parent catalog.
  • To add a table, you must be the schema owner or have USE SCHEMA and CREATE TABLE on the schema and USE CATALOG on the parent catalog.
  • To add a volume, you must be the schema owner or have USE SCHEMA and CREATE VOLUME on the schema and USE CATALOG on the parent catalog.
  • To grant and revoke permissions on the schema, you must be the schema owner, the owner of the parent catalog, a metastore admin, or have MANAGE and USE SCHEMA on the schema and USE CATALOG on the parent catalog.

Catalog Explorer

  1. Log into 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 that contains the schema and then click the schema to open the schema details page.

    Alternatively, you can search for the schema using the Type to filter field in the Catalog pane.

  4. Use the following page elements on the schema details page to update the schema:

SQL

  • To change the owner, add or update tags, set schema properties, and manage predictive optimization on the tables in a schema, run the ALTER SCHEMA command in a notebook or the SQL query editor. See ALTER SCHEMA.
  • To grant or revoke privileges, use the GRANT or REVOKE command. See GRANT and REVOKE.
  • To add tables, volumes, and other data and AI assets to a schema, see the documentation for creating the asset (for example What are tables and views? or Manage model lifecycle in Unity Catalog).
  • To rename a schema using SQL, you must create a new schema and move all assets into the new schema.

Delete a schema

To delete (or drop) a schema in a Unity Catalog catalog, you can use Catalog Explorer or a SQL command.

Permissions required: To drop a schema in Unity Catalog, 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 that you want to delete.
  4. In the detail pane, click the kebab menu Kebab menu in the upper right corner and select Delete.
  5. On the Delete schema dialog, click Delete.

SQL

Run the following SQL command in a notebook or the SQL query 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

Next steps

To learn how to add tables and volumes to your schema, see What are tables and views? and What are Unity Catalog volumes?.