Quickstart: Run and visualize a query

This quickstart shows how to create a table of 10 million people from a Databricks dataset, query the table to find the number of women named Mary grouped by birth year, and visualize the result.

Requirements

Your SQL Analytics admin must complete the SQL Analytics admin quickstart.

Step 1: Create a table of 10 million people

  1. Log in to SQL Analytics.

    If you have access to both Databricks Workspace and SQL Analytics, you might need to switch from Workspace to SQL Analytics. At the bottom of the sidebar, click the AppSwitcher Icon app switcher icon and select AppSQLAnalytics

  2. Click the Queries Icon icon in the sidebar.

  3. Click + New Query.

    The query editor displays.

  4. In the box below New Query, click the Down Arrow  Icon icon and select QS Endpoint.

    Query editor

    The first time you create a query the list of available SQL endpoints displays in alphabetical order. The next time you create a query, the last used endpoint is selected.

  5. In the box below the endpoint, click the Down Arrow  Icon icon and if not selected, select the default database.

    Default database
  6. 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 a Delta Lake file stored in Databricks datasets.

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

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

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

    Schema browser

Step 2: Query the table

  1. Paste in a SELECT statement that queries the number of women named Mary:

     SELECT year(birthDate) as birthYear, count(*) AS total
     FROM default.people10m
     WHERE firstName = 'Mary' AND gender = 'F'
     GROUP BY birthYear
     ORDER BY birthYear
    
  2. Press Ctrl/Cmd + Enter or click the Execute button.

    Execute query

    The Limit 1000 checkbox is selected by default to ensure that the query returns at most 1000 rows. If you want more rows, you can unselect the checkbox and specify a LIMIT clause in your query. The query result displays in the Table tab.

    Query result

Step 3: Create a visualization

  1. Click the + Add Visualization tab.

    The visualization editor displays.

    Visualization editor
  2. In the X Column drop-down, select birthYear.

  3. In the Y Column drop-down, select total.

  4. Click the X Axis tab.

  5. In the Name field, enter Birth Year.

  6. Click the Y Axis tab.

  7. In the Name field, enter Number of Marys.

  8. Click Save.

    The saved chart displays in the query editor.

    Marys chart