Tutorial: Create your first table and grant privileges in Unity Catalog

This article provides a quick walkthrough of creating a table and granting privileges in Unity Catalog. It is intended for users but may also be of interest to admins who are newly responsible for Unity Catalog management.

To get started immediately, you can go straight to the first section of this article. If you want to familiarize yourself with basic concepts first, see What is Unity Catalog?.

If you are an admin who is responsible for setting up Unity Catalog and managing it for your organization, see Set up and manage Unity Catalog.

Before you begin

In order to perform the tasks described in this article, you must have:

Confirm access to a catalog

To run the tutorials in this article, you must have the USE CATALOG privilege on a catalog in Unity Catalog and both the USE SCHEMA and CREATE TABLE privileges on the default schema in that catalog. Catalogs are the top of the three-level namespace in Unity Catalog: catalog.schema.table.

To determine whether you have access to a catalog:

  1. Log in to your workspace.

  2. Click Catalog icon Catalog.

  3. Under Catalog in the left pane, view any catalogs that you have permission to list.

    If you see a main catalog, go to Create your first table and manage permissions (for workspaces that include a main catalog).

    To determine whether you have adequate permission on any other catalogs, click the catalog name in the left pane to open the details pane and open the Permissions tab to view your permissions. If you have USE CATALOG on the catalog and both USE SCHEMA and CREATE TABLE on the catalog’s default schema, you can use the tutorial Create your first table and manage permissions (for workspaces that include a main catalog).

If you do not have USE CATALOG on at least one catalog, ask an account admin to assign it for you. Also ask for USE SCHEMA and CREATE TABLE on the catalog’s default schema.

Create your first table and manage permissions (for workspaces that include a main catalog)

Use this tutorial if you have access to the main catalog. See Confirm access to a catalog.

Note

Alternatively, you can use this tutorial with any catalog for which you have the following permissions: USE CATALOG on the catalog and both USE SCHEMA and CREATE TABLE on the default schema. Substitute your catalog name for main in the examples that follow.

Unity Catalog enables you to define access to tables declaratively using SQL or the Databricks Explorer UI.

In this example, you’ll run a notebook that creates a table named department in the main catalog and default schema (database). This catalog and schema are created automatically for all metastores.

You can also try running an example notebook that performs the same tasks.

Permissions required

This tutorial requires the USE CATALOG privilege on the main catalog and both USE SCHEMA and CREATE TABLE on the main.default schema.

By default, all account users have USE CATALOG on main and USE SCHEMA on default. You might need to ask the catalog owner to grant you CREATE TABLE on the default schema. If you are a metastore admin or the catalog owner, you can grant yourself or any user or group the CREATE TABLE privilege using the following command in a notebook that supports Unity Catalog:

GRANT CREATE TABLE ON SCHEMA main.default TO `<user or group>`;

Tutorial instructions

  1. Create a notebook and attach it to a cluster that supports Unity Catalog. See Before you begin.

    Select SQL as your notebook language.

  2. Add the following commands to the notebook and run them:

    CREATE TABLE IF NOT EXISTS main.default.department
    (
      deptcode   INT,
      deptname  STRING,
      location  STRING
    );
    
    INSERT INTO main.default.department VALUES
      (10, 'FINANCE', 'EDINBURGH'),
      (20, 'SOFTWARE', 'PADDINGTON');
    

    You now have a table in Unity Catalog.

  3. Find the new table in Catalog Explorer.

    In the sidebar, click Catalog icon Catalog, then use the browser (or search) to find the main catalog and the default schema, where you’ll find the department table.

    Use Catalog Explorer to find a table

    Notice that you don’t need a running cluster or SQL warehouse to browse data in Catalog Explorer.

  4. Grant permissions on the table.

    As the original table creator, you’re the table owner, and you can grant other users permission to read or write to the table. You can even transfer ownership, but we won’t do that here.

    On the table page in Catalog Explorer, go to the Permissions tab and click Grant.

    On the Grant on dialog:

    1. Select the users and groups you want to give permission to. In this example, we use a group called data-consumers.

    2. Select the privileges you want to grant. For this example, assign the SELECT privilege and click Grant.

    For more information about the Unity Catalog privileges and permissions model, see Manage privileges in Unity Catalog.

    You can also grant those permissions using the following SQL statement in a Databricks notebook or the Databricks SQL query editor:

    GRANT SELECT ON main.default.department TO `data-consumers`;
    
  5. Run one of the example notebooks that follow for a more detailed walkthrough that includes catalog and schema creation, a summary of available privileges, a sample query, and more.

Example notebooks: Create your first catalog, schema, and table

You can use the following example notebooks to create a catalog, schema, and table, as well as manage permissions on each.

Create and manage a Unity Catalog table using Python notebook

Open notebook in new tab

Create and manage a Unity Catalog table using SQL notebook

Open notebook in new tab