Tutorial: Unity Catalog metastore admin tasks for Databricks SQL

This tutorial guides Databricks metastore admins through all the steps necessary to configure catalogs, databases, and tables with Unity Catalog. You use the Catalog Explorer UI to complete all steps.

As a Unity Catalog metastore admin, you can configure access to all securable objects associated with a metastore in Unity Catalog. Databricks account administrators can assign the metastore admin role in the account console. While many metastore admins may also be Databricks account administrators or Databricks workspace administrators, the default permissions for these roles vary.

This article demonstrates the following:

  1. Using the Catalog Explorer to discover data objects

  2. Creating a catalog in Unity Catalog

  3. Creating a database in Unity Catalog

  4. Uploading a CSV to create a table in Unity Catalog

  5. Managing permissions for catalogs, databases, and tables

Requirements

In order to successfully complete this demo, you will need:

  • A Databricks workspace with Unity Catalog enabled

  • Admin privileges on a metastore defined in the Databricks account console

  • Access to Databricks SQL in a workspace associated with the metastore

  • One or more users or groups defined in Unity Catalog

Metastore admin privileges enable you to work with all data objects in the metastore. By working through this tutorial with another Databricks SQL user without metastore admin privileges, you can view how changing grants immediately updates access to various actions for different assets.

Step 1: Navigate to the Catalog Explorer UI and review data objects

You can use the Catalog Explorer to view catalogs, databases, tables, and views. Unity Catalog has two levels of nesting: catalogs contain databases, and databases contain tables and views. You can use the Catalog Explorer to click on the names of these data objects to see objects contained in them, get a preview of data, and explore metadata and access grants.

  1. Click Catalog icon Catalog in the sidebar.

  2. Review the currently defined catalogs in the workspace. Note that hive_metastore and samples are special catalogs.

  3. Click the name of a catalog to view all databases in the catalog.

  4. Click the Permissions tab to see the current owner and grants for the catalog.

  5. Click the name of a database to view all tables in the database.

  6. Click the name of a table to view the fields in the table.

  7. Click the Sample Data tab to see a preview of the data in the table.

Step 2: Create a new catalog in the metastore

You can use the Catalog Explorer UI to add catalogs. To use SQL, see CREATE CATALOG.

  1. The right side of the Catalog Explorer contains links to all assets in the hierarchy of your currently viewed data object in the pattern: Catalogs > catalog_name > database_name >. Click Catalogs to return to the top level of the metastore hierarchy.

  2. Click Create catalog in the top right of the screen.

  3. Enter a unique catalog name.

  4. Enter an optional comment.

  5. Click Create.

Newly created catalogs contain two schemas (databases): default and information_schema. For more information on this system table, see Information schema.

Step 3: Create a new schema in the catalog

You can use the Catalog Explorer UI to add databases. To use SQL, see CREATE SCHEMA.

  1. Click the name of the catalog from the list on the left side of the screen.

  2. Click Create database in the top right of the screen.

  3. Enter a unique database name.

  4. Enter an optional comment.

  5. Click Create.

Step 4: Create a table from a CSV

Databricks provides a UI that allows you to upload a CSV file into a Unity Catalog table. You can use any CSV under 100 megabytes. Your SQL warehouse must be active in order to use the UI. For more details, see Load data using the add data UI.

If you prefer to create a table using SQL, see CREATE TABLE and skip this step.

  1. Click Add > Add data, and then click Create or modify a table.

  2. Add a CSV file from your computer by browsing or dropping the file in the interactive box.

    • It may take a few seconds for the file to upload.

  3. Use the first dropdown menu to select the catalog you created in step 2.

  4. Use the second dropdown menu to select the database you created in step 3.

  5. You can provide a custom name for the table.

  6. If the table has a header row, make sure the First row contains the header box is checked. Otherwise, uncheck this box.

  7. Optionally, provide custom names for each column.

  8. If the data from your file is not previewing correctly, click Advanced attributes and adjust the available settings.

  9. Click Create.

Step 5: Grant permissions for catalog, database, and table

The minimum set of permissions required for Databricks SQL end users to complete their tasks varies based on the desired outcome. You can assign permissions to either individual users or groups. By working through this tutorial with another Databricks SQL user, you can view how changing grants immediately updates access to various actions for different assets.

Step 5a: Grant USE CATALOG permissions for catalog

Users who want to perform actions in a catalog must have USE CATALOG permissions.

  1. Click Catalog icon Catalog in the sidebar to return to the Catalog Explorer.

  2. Click the name of the catalog you created in step 2.

  3. Click the Permissions tab to see the grants for the catalog.

  4. Click Grant.

  5. Use the dropdown to select the user or group you wish to add.

  6. Click the box next to the USE CATALOG privilege.

  7. Click Grant.

Step 5b: Grant USE SCHEMA permissions for database

USE SCHEMA is the minimum privilege that users need in order to view a database. This allows users to interact with tables or views contained within the database. Users must have CREATE TABLE privileges to define new tables or views from queries or UIs.

  1. Click the name of the database you created in step 3.

  2. Click the Permissions tab to see the grants for the database.

  3. Click Grant.

  4. Use the dropdown to select the user or group you wish to add.

  5. Click the box next to the USE SCHEMA privilege.

  6. Click Grant.

Step 5c: Grant SELECT permissions for table

Users can only see tables and views they have permissions on. SELECT is the minimum privilege required to see and query a table or view. Users with MODIFY privileges can add, delete, and modify data, and can also drop tables entirely.

  1. Click the name of the table you created in step 4.

  2. Click the Permissions tab to see the grants for the table.

  3. Click Grant.

  4. Use the dropdown to select the user or group you wish to add.

  5. Click the box next to the SELECT privilege.

  6. Click Grant.

Next steps

Secure access to production data should follow a principle of least privilege. In this tutorial, you configured a catalog, database, and table for an end user to openly query production data, but denied them the ability to accidentally corrupt or delete that data. Most end users should have this configuration for most data they have access to, as most queries are read-only queries.

Users may also need access to save derivative datasets to a database. Databricks recommends creating a separate database for derivative datasets to isolate these tables from production tables. To test this, create a new database and grant the CREATE TABLE and CREATE VIEW privileges to the same user. The user can now save the results of a query on the table you created in step 4 to a newly created table or view.

If groups of users need to work on tables together, grant MODIFY permissions on specific tables.

Databricks recommends managing permission grants to groups rather than individual users.