Dashboard SQL Reference
Dashboards are expressed as SQL queries. Each query defines an UI element. Queries are separated by ;
.
All SQL is valid DuckDB SQL. For more look at the DuckDB SQL docs.
Shaper creates custom SQL types. You can cast your data to these types to render the data differently. The simplest way to cast a column’s type is by appending the type with ::
, for example SELECT 'hello'::LABEL
.
This can be used to control the visualization of the data, add filter options and control the dashboard layout.
Tables
Section titled “Tables”Data is rendered as table by default. Column names are used as table headers.
PERCENT
Section titled “PERCENT”SELECT mynumber::PERCENT AS percent;
Renders a DOUBLE
as percentage value. Your numbers should probably be between 0 and 1.
Show a trend indicator as a table column:
SELECT 10 AS "this week", 5 AS "last week", (10 / 5)::TREND AS "change";
The change
column will show “100%↑”.
Single Value
Section titled “Single Value”SELECT 'hello world';
If a query returns a single row and a single column, it is rendered as big single card.
PERCENT
Section titled “PERCENT”SELECT 0.33::PERCENT AS percent;
Renders a DOUBLE
as percentage value. Your numbers should probably be between 0 and 1.
COMPARE
Section titled “COMPARE”Show a comparison value below the value and a trend indicator:
SELECT 10 AS "this week", 5::COMPARE AS "last week";
This will show last week: 5 [100%↑]
below the value.
Bar Charts
Section titled “Bar Charts”To render a bar chart you need specify along which axis to render the chart and how to plot the values. Additionally you can group the data by a category.
Any additional columns will only be visible in the tooltip on hover.
Example:
SELECT time::XAXIS, value::BARCHART, category::CATEGORY FROM my_table
XAXIS
, YAXIS
Section titled “XAXIS, YAXIS”You can render a bar chart along the x-axis or y-axis.
Often you will be using a timestamp as the axis column.
The axis column must be a VARCHAR
, TIMESTAMP
, TIME
or numer type.
BARCHART
Section titled “BARCHART”Mark the column as BARCHART
that you like to display as value of the bars in the chart.
This must be a numeric value or INTERVAL.
CATEGORY
Section titled “CATEGORY”Mark a VARCHAR column as category to render multiple bars for each point on the axis.
This will also display a legend for the categories in the chart.
When CATEGORY
is set to NULL or an empty VARCHAR then the category will not show up in the legend.
This is useful to to only change bar colors or to only label specific bars in a chart.
For example:
SELECT 'Would you recommend the product to a friend?'::LABEL;SELECT col0::XAXIS, col1::BARCHART_STACKED_PERCENT, col2::CATEGORYFROM ( VALUES (5, 0.7, 'Fully recommend'), (4, 0.025, NULL), (3, 0.1, NULL), (2, 0.025, NULL), (1, 0.15, 'Would not recommend'),);
BARCHART_STACKED
Section titled “BARCHART_STACKED”When using CATEGORY
you can change BARCHART
to BARCHART_STACKED
to render categories as a single, stacked bar instead of rendering multiple bars.
_PERCENT
Section titled “_PERCENT”Use BARCHART_PERCENT
or BARCHART_STACKED_PERCENT
to show the value of the chart as percentage.
This sets the max value of the chart to 100%.
The values should most likely be between 0 and 1.
Line Charts
Section titled “Line Charts”Line charts work almost identical to bar charts, but they don’t have the YAXIS
and _STACKED
options.
Specify the column to use as x-axis. Most likely this will be a time dimension.
LINECHART
Section titled “LINECHART”Specify which column to show as value (numeric or INTERVAL)
CATEGORY
Section titled “CATEGORY”Optionally mark add a column category to render multiple lines in the line chart
LINECHART_PERCENT
Section titled “LINECHART_PERCENT”Render the value of the chart as percentage. This sets the max value of the chart to 100%. The values should most likely be between 0 and 1.
Gauges
Section titled “Gauges”Use a gauge to visualize a single value as progress towards a goal or to indicate a status across categories.
A range query must return a single with a column marked as GAUGE
.
Use RANGE
to specify the range of the gauge:
SELECT 4::GAUGE, [0, 10]::RANGE;
GAUGE_PERCENT
Section titled “GAUGE_PERCENT”To render a gauge that shows a percentage, use GAUGE_PERCENT
.
If the value falls within 0 and 1 (0% to 100%), you can skip specifying the RANGE
.
SELECT 'CPU Usage'::LABEL;SELECT 0.22::GAUGE_PERCENT;
RANGE
, LABELS
, COLORS
Section titled “RANGE, LABELS, COLORS”To show a status across multiple categories, you can specify more than two range values.
Once you do that you most likely also want to set specific colors for each category using COLORS
.
And you can display a label for each category using LABELS
.
Note that the LABELS
and COLORS
lists contain one less element than the RANGE
list.
SELECT 'Score'::LABEL;SELECT 75::GAUGE, [0, 33, 66, 100]::RANGE, ['#f55d5d', '#fbd953', '#48af45']::COLORS, ['Bad', 'Okay', 'Good']::LABELS;
Filtering
Section titled “Filtering”Make dashboards interactive by adding filter options to them.
The resulting filter value will be available in all following queries on the dashboard via DuckDB’s built-in getvariable('varname')
function.
varname
is the column name you specified via AS varname
in the filter query.
DATEPICKER
Section titled “DATEPICKER”Create a date picker:
SELECT today()::DATEPICKER AS date;
The value (above today()
) is the date that is selected by default.
You can use the resulting variable in the following queries with getvariable('date')
.
The variable is of type DATE
.
DATEPICKER_FROM
, DATEPICKER_TO
Section titled “DATEPICKER_FROM, DATEPICKER_TO”Create a date range picker:
SELECT (today() - INTERVAL '7 days')::DATEPICKER_FROM AS "from", (today())::DATEPICKER_TO AS "to";
The query specifies the default date range and generates two variables from
and to
;
Use the variables in following queries:
SELECT *FROM mytableWHERE created_at BETWEEN getvariable('from') AND getvariable('TO');
DROPDOWN
Section titled “DROPDOWN”Define a drop-down select menu:
SELECT type::DROPDOWN AS typeFROM my_tableGROUP BY typeORDER BY type;
The first value is selected by default.
You can use the resulting variable in the following queries with getvariable('type')
.
The variable is of type VARCHAR
.
To add a custom default value, return it as first result of the query.
Use UNION ALL
for this:
SELECT 'default'::DROPDOWN AS typeUNION ALL( SELECT type FROM my_table GROUP BY type ORDER BY type);
Specify separate label and value for a dropdown:
SELECT EXTRACT(MONTH FROM range)::TEXT::DROPDOWN AS month, strftime(range, '%B')::LABELFROM range(DATE '2024-01-01', DATE '2025-01-01', INTERVAL 1 MONTH)ORDER BY range;
This uses a number as value for the dropdown and shows the month name as label.
DROPDOWN_MULTI
Section titled “DROPDOWN_MULTI”Same as DROPDOWN
, but allows selecting multiple values:
SELECT type::DROPDOWN_MULTI AS typeFROM my_tableGROUP BY typeORDER BY type;
By default all values are selected.
The resulting variable is a list of VARCHAR
values.
You can use it with an IN
clause:
... WHERE type IN getvariable('type')
Labels for multi-select dropdowns work the same as for single-select dropdowns. See the example above.
For multi-select dropdowns you can add a hint in addition to the value and the label:
SELECT type::DROPDOWN_MULTI AS type, count(*)::HINTFROM my_tableGROUP BY typeORDER BY type;
A common use case is to show the count of items for each category in a dropdown.
Download Buttons
Section titled “Download Buttons”DOWNLOAD_CSV
Section titled “DOWNLOAD_CSV”Download the result of a query as CSV file:
SELECT concat('mydownload-', today())::DOWNLOAD_CSV AS Download;SELECT *FROM my_tableWHERE created_at BETWEEN getvariable('from') AND getvariable('to')ORDER BY created_at;
To render a download button return a single row with a single column with the DOWNLOAD_CSV
type.
The value (here concat('mydownload-', today())
) is the name of the file that will be downloaded.
And the column name (here Download
) is the label of the button.
The query that follows the download button query is the data that will be downloaded.
You can use variables from previous queries to customize the download and create flexible download interfaces with this.
DOWNLOAD_XLSX
Section titled “DOWNLOAD_XLSX”Download the result of a query as Excel file:
SELECT concat('mydownload-', today())::DOWNLOAD_XLSX AS Download;SELECT *FROM my_tableWHERE created_at BETWEEN getvariable('from') AND getvariable('to')ORDER BY created_at;
Excel files (.xlsx
) mostly work the same as CSV files (above).
Use Excel files if you like the data to be formatted nicely for Excel. But note that Excel files cannot be streamed to the client. They must fit fully into the server’s memory. For large datasets use CSV downloads instead.
Layout
Section titled “Layout”Shaper tries to automatically layout the dashboard for you and use all available screen space.
Filters and download buttons are at the top in the header of a section. Other queries are rendered as content below.
Depending on the screen size, up to 5 content queries are rendered next to each other.
SECTION
Section titled “SECTION”Use SECTION
to create a new section in the dashboard:
SELECT 'My Section'::SECTION;
This will create a new section with the title “My Section”.
Create a section without title to only affect the layout:
SELECT ''::SECTION;
If a section header query returns no rows, the whole section is hidden:
SELECT ''::SECTION WHERE FALSE;SELECT 'this query result is never displayed';
This is useful to conditionally hide a section depending on other query results or filters.
Adding filters or download buttons creates a new section automatically.
Add labels to any element on the page with a LABEL
query right before it.
Return a single row with a single column:
SELECT 'My Label'::LABEL;SELECT 10 AS "My Value";
For content queries the label is shown as title at the top of the card.
For filters and download buttons the label is shown before the button.
DROPDOWN_MULTI
is an exception and shows the label on the button itself.
PLACEHOLDER
Section titled “PLACEHOLDER”Leave an empty space in the content grid:
SELECT ''::PLACEHOLDER;
Placeholders are useful to align content vertically if you have no other content to fill the space:
SELECT 200 AS "Total Overall";SELECT 150 AS "Total This Week";SELECT ''::SECTION;SELECT ''::PLACEHOLDER;SELECT 50 AS "Total Last Week";
This shows four cards on the screen. “Total This Week” and “Total Last Week” are aligned vertically. And the space below “Total Overall” is empty.
Auto-Reload
Section titled “Auto-Reload”Configure dashboards to automatically reload after a certain time interval.
RELOAD
Section titled “RELOAD”Specify one RELOAD
query per dashboard to configure the dashboard to reload automatically:
SELECT (INTERVAL '1 minute')::RELOAD;
The query must return a single row with a single RELOAD column.
Add this query to the bottom or the top of the dashboard.
The value can be either a TIMESTAMP
or an INTERVAL
.
If the value is in the past, the dashboard won’t reload.
Non-SELECT Statements
Section titled “Non-SELECT Statements”While most queries in a dashboard will be SELECT
statements, there are other DuckDB SQL statements that are useful for building dashboards.
Data Exploration
Section titled “Data Exploration”The following statements return data the same way SELECT
statements do.
They are particularly useful to explore the data in the system before actually building a dashboard.
You can also use them as sub-queries. For example, you use SUMMARIZE
and then use SELECT
to only show some of the info:
SELECT column_name, avg, count FROM (SUMMARIZE sessions);
DESCRIBE
Section titled “DESCRIBE”DESCRIBE
or short DESC
returns the schema of a table:
DESC sessions;
Result:
column_name | column_type | null | key | default | extra |
---|---|---|---|---|---|
session_id | VARCHAR | YES | |||
created_at | TIMESTAMP | YES | |||
user_id | VARCHAR | YES | |||
project_id | VARCHAR | YES | |||
duration | BIGINT | YES | |||
category | VARCHAR | YES |
SUMMARIZE
Section titled “SUMMARIZE”SUMMARIZE;
returns varies statistics for the data in each column of a table.
SHOW TABLES
Section titled “SHOW TABLES”SHOW TABLES;
returns a list of tables in the current database schema.
Combine it with USE
to switch to a specific database+schema first.
Or use SHOW ALL TABLES
and then filter the results to the database and schema you are interested in.
SHOW ALL TABLES
Section titled “SHOW ALL TABLES”SHOW ALL TABLES;
returns a list of all tables in databases and all schemas.
It also includes column_names
and column_types
which means the output can be a lot of data. You might want to skip the column info and maybe filter the result to a specific database or schema:
SELECT database, schema, name FROM (SHOW ALL TABLES);
Code Reuse
Section titled “Code Reuse”The following statements don’t display any data themselves, but they can make the dashboard SQL code easier to read and maintain.
CREATE TEMPORARY TABLE
Section titled “CREATE TEMPORARY TABLE”Use CREATE TEMPORARY TABLE
or short CREATE TEMP TABLE
to create a temporary table that is only available in the current dashboard.
This is useful to pre-compute a dataset that is used across multiple queries. It can speed up loading times, reduce the amount of code needed and avoid errors when adjusting dashboards later on.
For example, use this to filter data based on embedding and user-selected variables:
CREATE TEMP TABLE dataset AS ( SELECT * FROM sessions WHERE user_id = getvariable('user_id') AND category IN getvariable('category') AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date'));
Keep in mind that the whole table is computed and stored in memory.
CREATE TEMPORARY VIEW
Section titled “CREATE TEMPORARY VIEW”CREATE TEMPORARY VIEW
or short CREATE TEMP VIEW
creates a temporary view that is only available in the current dashboard.
Use temporary views instead of tables for in-between states that are too big to store in memory and you plan to filter or aggregate further in later queries.
Choose between temporary tables and views depending on the performance characteristics of your queries and the size of the data.
CREATE TEMP VIEW user_sessions AS ( SELECT * FROM sessions WHERE user_id = getvariable('user_id'));
SET VARIABLE
Section titled “SET VARIABLE”SET VARIABLE
allows you to set a variable that can be used in queries via getvariable('varname')
.
Use variables instead of temporary tables and views if you only need to store a single value instead of tabular data.
For example, format the company name:
SET VARIABLE company_name = ( SELECT name.upper().trim().replace(' ', '_').regexp_replace('[^\w]', '', 'g') FROM companies WHERE id = getvariable('company_id'));
Note that the example uses the DuckDB-specific function chaining syntax to keep the code easier to read.
Shaper also uses this mechanism internally to provide variables when embedding dashboards or defining filters such as date pickers or dropdowns. Beware that SET VARIABLE
overwrites any existing variable with the same name.
The DuckDB metadata function duckdb_variables()
lists all variables that are currently set, which is useful for debugging. Try this after defining some variables or filters:
FROM duckdb_variables();