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 which means that users must always be granted access to data.

Requirements

Use the sidebar

You can access all of your Databricks assets using the left sidebar. The sidebar’s contents depend on the selected persona: Data Science & Engineering, Machine Learning, or Databricks SQL.

  • By default, the sidebar appears in a collapsed state and only the icons are visible. Move your cursor over the sidebar to expand to the full view.

  • To change the persona, click the icon immediately below the Databricks logo, and select a persona from the menu.

    change persona
  • To pin a persona so that it appears next time you log in, click pin persona next to the persona. Click it again to remove the pin.

  • Use Menu options at the bottom of the sidebar to set the sidebar mode to Auto (default behavior), Expand, or Collapse.

Step 1: Add a user

  1. Ensure that you are in the Databricks SQL environment. Use the sidebar persona-switcher if necessary.

  2. Go to the admin console.

  3. On the Users tab, click Add User.

  4. Enter the user email ID. The examples in this article assume the user is user@example.com.

    Add user
  5. 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
  6. Click the Databricks SQL access checkbox.

  7. Click Confirm.

Step 2: Create and start a SQL endpoint

  1. Ensure that you are in the Databricks SQL environment. Use the sidebar persona-switcher if necessary.

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

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

  3. Click + New SQL Endpoint.

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

    Add quickstart 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.

    Quickstart endpoint permissions
  8. Click Save.

  9. Click Endpoints Icon SQL Endpoints 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 Create Icon Create 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.

You can configure access using data explorer or query editor.

Data explorer

  1. Click the Data Icon Data in the sidebar.

  2. Select QS Endpoint in the drop-down list at the top right. The default database is selected. The database comment and owner display.

  3. Type peo in the text box following the default database. The data explorer displays the people10m table.

    Select QS endpoint
  4. Click the Permissions tab.

  5. Click the Grant button.

    Default db grant
  6. Enter user@example.com.

  7. Select the USAGE checkbox.

  8. Click OK.

  9. Click the people10m table.

  10. Click the Permissions tab.

  11. Click the Grant button.

    People table grant
  12. Enter user@example.com.

  13. Click the SELECT and READ_METADATA checkboxes.

  14. Click OK.

Query editor

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

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

  3. Enter the following queries one by one:

    GRANT USAGE ON DATABASE default TO `user@example.com`;
    
    GRANT SELECT, 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 History Icon Query History in the sidebar.

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

    Query history details

Next steps

To learn how to enable access to your own data and tables, see Data access overview.