Databricks Unity Catalog: Catalogs and Schemas

Summary

This video tutorial guides viewers on managing catalogs and schemas in Unity Catalog using both the Catalog Explorer UI and SQL syntax. It explains the hierarchical structure of Unity Catalog, detailing the creation and deletion processes for catalogs and schemas, including the necessary permissions and options for storage locations. The video also covers the SQL commands for creating and dropping catalogs and schemas, emphasizing the use of 'CASCADE' to remove non-empty structures.

Takeaways

• 📚 Catalogs and schemas are foundational layers in the Unity Catalog object model for organizing data assets.
• 🛠️ To create a catalog, users must have administrative privileges or specific permissions on the metastore and a linked Unity Catalog metastore.
• 🔍 The Catalog Explorer UI provides an interface to manage catalogs and schemas visually.
• 🆕 Creating a catalog involves selecting 'Standard' for management by Unity Catalog or 'Foreign' for external data system connections.
• 📂 Storage location for a catalog determines where data and metadata files are stored, which can be the default or an external registered location.
• 🗑️ Deleting a catalog requires selecting the catalog, using the 'delete' option, and managing permissions for different users and groups.
• 🏭 Creating a schema requires 'use catalog' and 'create schema' permissions and can be done similarly to creating a catalog through the UI.
• 📝 SQL syntax is also provided for creating and deleting catalogs and schemas, offering an alternative to the UI.
• 🔑 Permissions and access can be assigned to different workspaces when creating or modifying catalogs and schemas.
• 🔄 The 'Cascade' option in SQL is crucial for deleting non-empty catalogs or schemas, ensuring all associated objects are removed.
• 🔗 Links for more information and references are provided in the video description for further learning.

Q & A

What is the purpose of catalogs and schemas in Unity Catalog?

Catalogs and schemas in Unity Catalog serve as layers of the object model used to organize data assets. Catalogs are the first layer, while schemas, also known as databases, are the second layer and contain objects like tables and views.

Who can create a catalog in Unity Catalog?

To create a catalog, a user must be an Azure Meta Store admin or have the 'create catalog' privilege on the Meta Store. Additionally, they must have a Unity Catalog Meta Store linked to the workspace where the catalog creation is performed.

What are the two types of catalogs in Unity Catalog?

There are two types of catalogs: standard and foreign. A standard catalog is managed by Unity Catalog, while a foreign catalog mirrors a database in an external data system using Lakehouse Federation, essentially acting as a connection to an external data source.

What is the significance of the storage location in catalog creation?

The storage location is where the data for managed tables, volumes, and metadata files will be stored. It can be the same as the Unity Catalog Meta Store or a different external location, provided the user has the necessary privileges.

How can you create a catalog using the Catalog Explorer UI?

To create a catalog using the Catalog Explorer UI, go to Catalog Explorer, click on 'Create Catalog', give the catalog a name, select the type (standard or foreign), and specify the storage location. If the storage location is left blank, it will default to the Unity Catalog Meta Store.

What does the term 'demo catalog' refer to in the script?

In the script, 'demo catalog' refers to a sample catalog created by the presenter for demonstration purposes, named 'demo catalog' for simplicity.

How can permissions be assigned to different users or groups for accessing a catalog?

Permissions can be assigned by selecting 'Grant' in the catalog details, choosing the different privileges, and then specifying which workspaces should have access to the catalog.

What is the process of creating a schema in a catalog?

To create a schema, a user must have the 'use catalog' and 'create schema' data permissions on the schema's parent catalog. They can then go to the catalog, select 'Create Schema', provide a schema name, and optionally specify a storage location.

What are the default and information schemas in Unity Catalog?

The default and information schemas are automatically created with the creation of a catalog. They provide information about the schema and are part of the catalog's structure.

How can you delete a catalog or schema using SQL syntax?

To delete a catalog or schema using SQL syntax, use the 'DROP CATALOG' or 'DROP SCHEMA' command followed by the name of the catalog or schema. If the catalog or schema is not empty, use the 'CASCADE' option to recursively delete all associated schemas and objects.

What does the 'CASCADE' option do when deleting a catalog or schema?

The 'CASCADE' option, when specified during deletion, allows for the recursive deletion of all associated schemas and objects within the catalog or schema being dropped.