Quickstart: Set up a user to query a table

Preview

This feature is in Public Preview. Contact your Databricks representative to request access.

This quickstart shows how to add a user, create a SQL endpoint, create a table containing 10 million people records from a Databricks dataset, configure access to the table for the user, run a query, and view the query runtime details.

Important

Data access control is always enabled in Databricks SQL Analytics which means that users must always be granted access to data. For an example, see Step 4: Configure access to the default.people10m table.

Requirements

Step 1: Add a user

  1. Click the App Switcher Icon icon at the bottom of the sidebar.

  2. Select App Switcher - Workspace

  3. Go to the Admin Console.

  4. On the Users tab, click Add User.

  5. Enter the user email ID.

    Add user
  6. Click Send invite.

    Databricks sends a confirmation email with a temporary password. If the user does not receive the confirmation email within 5 minutes, ask the user to check their spam folder.

    The user is added to the workspace.

    Added user
  7. Click the SQL Analytics access checkbox.

  8. Click Confirm.

Step 2: Create and start a SQL endpoint

  1. At the bottom of the sidebar, select App Switcher Icon > App Switcher - SQL Analytics

    Your landing page looks like this when you first log in to Databricks SQL Analytics:

    Admin landing page
  2. Click the Endpoints Icon icon in the sidebar.

  3. Click + New SQL Endpoint.

    Create endpoint
  4. In the Name field, enter QS Endpoint.

  5. Click Create.

  6. In the SQL Endpoint Permissions dialog, click the Down Arrow Icon icon. Select the All Users principal and the Can Use permission.

  7. Click Add.

    Add quickstart endpoint
  8. Click Save.

  9. Click the Endpoints Icon icon in the sidebar.

  10. In the Endpoints list, type QS in the filter box.

    View QS endpoint

    The QS Endpoint should display with State Starting Starting. Wait until the State is Running Running.

Step 3: Create a table of 10 million people

  1. Click the Create Icon icon in the sidebar and select Query.

  2. In the drop-box at the left, select the QS Endpoint endpoint.

  3. Paste the following in the query editor:

     CREATE TABLE default.people10m OPTIONS (PATH 'dbfs:/databricks-datasets/learning-spark-v2/people/people-10m.delta')
    

    This statement creates a Delta table using Delta Lake files stored in Databricks datasets.

  4. Press Ctrl/Cmd + Enter or click the Execute button. The query will return No data was returned.

  5. To refresh the schema, click the Refresh Schema button at the bottom of the schema browser.

  6. Type peo in the text box to the right of the database. The schema browser displays the new table.

    Schema browser

Step 4: Configure access to the default.people10m table

Enable the user you created in Step 1 to access the default.people10m table you created in Step 3. Enter the following queries one by one:

GRANT USAGE ON DATABASE default TO `user@example.com`;

GRANT SELECT ON TABLE default.people10m TO `user@example.com`;

GRANT READ_METADATA on TABLE default.people10m TO `user@example.com`;

SHOW GRANT `user@example.com` ON TABLE default.people10m;

After each query, press Ctrl/Cmd + Enter or click the Execute button. After the last query, it should display:

+------------------+---------------+------------+-----------------------+
| Principal        | ActionType    | ObjectType | ObjectKey             |
+------------------+---------------+------------+-----------------------+
| user@example.com | READ_METADATA | TABLE      | `default`.`people10m` |
+------------------+---------------+------------+-----------------------+
| user@example.com | SELECT        | TABLE      | `default`.`people10m` |
+------------------+---------------+------------+-----------------------+
| user@example.com | USAGE         | DATABASE   | default               |
+------------------+---------------+------------+-----------------------+

Step 5: Generate a query

Complete the user quickstart.

Step 6: View query history

  1. Click the History Icon icon in the sidebar.

  2. Click a string in the Query column to display query details:

    Query history details