Run and visualize a query in Databricks SQL, PowerBI, and Tableau
This quickstart shows you how to query a table of people to find the number of women named Mary
grouped by birth year, and visualize the result.
The columns of the table are id
, firstName
, middleName
, lastName
, gender
, birthDate
, ssn
, and salary
.
This article shows how to use the Databricks SQL UI and the BI tools Power BI and Tableau Online to do the same query.
Before you begin
Your Databricks SQL admin must complete the Databricks SQL Set up a user to query a table before you complete the steps in the following sections.
Databricks does not automatically create a serverless starter warehouse. Create one now and call it Serverless Starter Warehouse. See Configure SQL warehouses.
Log in to Databricks SQL
You can access all of your Databricks assets using the sidebar. The sidebar’s contents depend on the selected persona: Data Science & Engineering, Machine Learning, or 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 below the Databricks logo
, and select a persona.
To pin a persona so that it appears the next time you log in, click
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.
If the Databricks Data Science & Engineering or Databricks Machine Learning environment displays, use the sidebar to switch to Databricks SQL. If you do not have access to Databricks SQL, request access from an admin.
When you log in to Databricks SQL your landing page looks like this:
Query the people table in Databricks SQL
In Set up a user to query a table, an admin created a table named people10
. In this section, you run a query on this table for number of people named Mary for each birth year.
Click
New in the sidebar and select Query.
The SQL editor displays.
Select a warehouse.
If your workspace satisfies serverless compute requirements and your account does not require accepting terms of use, choose a serverless SQL warehouse, which is called Serverless Starter Warehouse if you created one based on the instructions above. For more information about account terms of use and instance profile configuration, see Enable serverless SQL warehouses.
Otherwise, click Starter Warehouse.
The first time you create a query the list of available SQL warehouses displays in alphabetical order. The next time you create a query, the last used warehouse is selected.
Paste in this
SELECT
statement that queries the number of women namedMary
that are born each year:SELECT year(birthDate) as birthYear, count(*) AS total FROM default.people10m WHERE firstName = 'Mary' AND gender = 'F' GROUP BY birthYear ORDER BY birthYear
Press Ctrl/Cmd + Enter or click Run (1000).
Limit 1000 is selected by default for all queries to ensure that the query returns at most 1000 rows. If you want to return all rows for this query, you can unselect LIMIT 1000 by clicking the Run (1000) drop-down. If you want to specify a different limit on the number of rows, you can add a
LIMIT
clause in your query with a value of your choice.The query result displays in the Table tab.
Click Save to save this query.
In the Save query text box, enter Marys and click Save.
Create a visualization in Databricks SQL
In the results table, click + and then click Visualization.
The visualization editor displays.
Change the visualization name from Scatter 1 to
Marys by Birth Year
.In the Visualization Type drop-down, select
Bar
.In the X Column drop-down, verify
birthYear
is selected.In the Y Column drop-down, verify
total
is selected.Click the X Axis tab.
In the Name field, enter
Birth Year
.Click the Y Axis tab.
In the Name field, enter
Number of Marys by Birth Year
.Click Save.
The saved chart displays in the SQL editor.
Create a dashboard in Databricks SQL
Click
New in the sidebar and select Dashboard.
Enter
People
as the dashboard name.Click Save.
In the warehouse drop-down list, select Serverless Starter Warehouse (if serverless is enabled for your workspace, or Starter Warehouse is serverless is not enabled).
In the Add drop-down list, click Visualization.
In the query list, select
Marys
.In the Select existing visualization list, select
Marys by Birth Year
.In the Title text box, enter
Marys by Birth Year
.Click Add to Dashboard.
Click Done Editing.
Get SQL warehouse connection details
Click
SQL Warehouses in the sidebar.
In the Warehouses list, type
Serverless Starter
in the filter box (orStarter
if serverless is not enabled).Click the Serverless Starter Warehouse link (or the warehouse of your choice).
Click the Connection Details tab.
Click
to copy the Server Hostname and HTTP Path.
Get a personal access token
A personal access token authenticates you to Databricks SQL.
Click your username on the menu bar in the upper right of the workspace and select User Settings from the drop down.
Click the Personal Access Tokens tab.
Click + Generate New Token.
Optionally enter a comment and modify the token lifetime.
Click Generate.
Click
to copy the token and click OK.
Query Databricks SQL using Power BI
Configure the connection from Power BI to Databricks SQL
Select Get Data > More… > Azure and select the Azure Databricks connector.
Note
Although the label says Azure Databricks, the connector also works with Databricks on AWS.
Click Connect.
Enter the Server Hostname and HTTP Path you copied in Get SQL warehouse connection details.
Click OK.
At the authentication prompt, select the Personal Access Token tab. Enter the personal access token you copied in Get a personal access token.
Click Connect.
If the SQL warehouse is not running, it will start but may take a few minutes to become available.
In the Power BI Navigator, select the
default.people10m
table.Click Load.
Shape the data
Return the records of women with the first name Mary and compute the number of Marys in each year.
Remove all columns except
firstName
,gender
, andbirthDate
.Filter the data with
firstName = Mary
andgender = F
.Change the type of
birthDate
toDate
and remove all date components except year.Group by year to count the number of Marys per year.

Query Databricks SQL using Tableau Online
Configure the connection from Tableau Online to Databricks SQL
In Tableau Online, open a workbook or create a new one.
Select Data > New Data Source.
In the Connect to Data dialog, select Connectors > Databricks.
In the Databricks connection dialog, enter the Server Hostname and HTTP Path you copied in Get SQL warehouse connection details.
Authenticate using
token
as the username and the token from Get a personal access token as the password.Click Sign in.
If the SQL warehouse is not running, it will start but may take a few minutes to become available.
In the Schema field, enter
default
. Press Enter.In the Select schema drop-down, select
default
.In the Table field, enter
people10m
. Press Enter.Drag the
people10
table to the canvas.
Shape the data
Return the records of women with the first name Mary and compute the number of Marys in each year.
Remove all columns except
firstName
,gender
, andbirthDate
.Filter the data with
firstName = Mary
andgender = F
.Change the type of
birthDate
toDate
and remove all date components except year.Group by year to count the number of Marys per year.
