Getting Started

Build your first dashboard and get familiar with Shaper’s SQL approach.

Shaper is built on top of DuckDB and uses SQL to not only to query and transform data, but also to build interactive dashboards on top of it.

Quick Start

1. Make sure Shaper is installed and running and open http://localhost:5454.
2. Click on “New Dashboard”.
3. Then copy & paste the following SQL code into the editor:

   SELECT
     min(created_at)::DATE::DATEPICKER_FROM AS start_date,
     max(created_at)::DATE::DATEPICKER_TO AS end_date,
   FROM 'https://taleshape.com/sample-data/sessions.csv';
   
   SELECT 'Category'::LABEL;
   SELECT category::DROPDOWN_MULTI AS category
   FROM 'https://taleshape.com/sample-data/sessions.csv'
   GROUP BY category
   ORDER BY category;
   
   SELECT ('sessions-' || today())::DOWNLOAD_CSV AS "CSV";
   SELECT * FROM 'https://taleshape.com/sample-data/sessions.csv'
   WHERE category IN getvariable('category')
     AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date');
   
   SELECT count(*) AS "Total Sessions"
   FROM 'https://taleshape.com/sample-data/sessions.csv'
   WHERE category IN getvariable('category')
     AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date');
   
   SELECT 'Summary'::LABEL;
   SELECT
     category AS "Category",
     count(*) AS "Sessions",
     to_seconds(round(avg(duration))) AS "Avg Duration",
   FROM 'https://taleshape.com/sample-data/sessions.csv'
   WHERE category IN getvariable('category')
     AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
   GROUP BY "Category"
   ORDER BY "Sessions" DESC;
   
   SELECT 'Sessions By Time of Day'::LABEL;
   SELECT
     date_trunc('hour', created_at)::TIME::XAXIS AS "Time of Day",
     count()::BARCHART AS "Total Sessions",
   FROM 'https://taleshape.com/sample-data/sessions.csv'
   WHERE category IN getvariable('category')
     AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
   GROUP BY ALL
   ORDER BY ALL;
   
   SELECT ''::SECTION;
   
   SELECT 'Sessions per Week'::LABEL;
   SELECT
     date_trunc('week', created_at)::XAXIS,
     category::CATEGORY,
     count()::BARCHART_STACKED,
   FROM 'https://taleshape.com/sample-data/sessions.csv'
   WHERE category IN getvariable('category')
     AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
   GROUP BY ALL
   ORDER BY ALL;
   
   SELECT 'Average Session Duration per Week'::LABEL;
   SELECT
     date_trunc('week', created_at)::XAXIS,
     category::CATEGORY,
     to_seconds(round(avg(duration)))::LINECHART,
   FROM 'https://taleshape.com/sample-data/sessions.csv'
   WHERE category IN getvariable('category')
     AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
   GROUP BY ALL
   ORDER BY ALL;

4. Now click “Create” (top-right) and give the dashboard a name.
5. You should see a dashboard similar to this:

Step by Step

All the SQL queries above are simply DuckDB SQL queries.

If you are new to DuckDB, you can learn more about all its SQL features, data types, functions and more in its comprehensive documentation:

DuckDB SQL Documentation

Loading Data

In the example above, we load data from a CSV file via HTTP:

FROM 'https://taleshape.com/sample-data/sessions.csv'

DuckDB supports different formats and data sources out of the box.

You can also ingest and store data in Shaper directly.

To learn more about loading data see:

Loading Data

Building Dashboards using SQL

Shaper renders each SQL query as UI component. We define custom data types such as BARCHART or DROPDOWN to specify how data should be rendered.

You can see the full list of available data types here:

Dashboard SQL Reference

Defining Variables

Let’s take a look at the first SQL query. The first SELECT renders a date range picker:

SELECT
  min(created_at)::DATE::DATEPICKER_FROM AS start_date,
  max(created_at)::DATE::DATEPICKER_TO AS end_date,
FROM 'https://taleshape.com/sample-data/sessions.csv';

We use DATEPICKER_FROM and DATEPICKER_TO to define the variables start_date and end_date, and set the default values to the min and max date in the dataset.

The variables can be used in later queries using DuckDB’s getvariable() function. Note that variables are always strings or arrays of strings.

Right after we define another variable as a multi-select dropdown menu that we use to filter other data later on:

SELECT 'Category'::LABEL;
SELECT category::DROPDOWN_MULTI AS category
FROM 'https://taleshape.com/sample-data/sessions.csv'
GROUP BY category
ORDER BY category;

We use an additional, optional SELECT statement to define a label for the select button. The same pattern can be used to set labels for any UI component.

Downloading Data

One of the most crucial feature of any data dashboard is the ability to download data and do further analysis in your favorite tool. We can render download buttons using ::DOWNLOAD_CSV or ::DOWNLOAD_XLSX:

SELECT ('sessions-' || today())::DOWNLOAD_CSV AS "CSV";
SELECT * FROM 'https://taleshape.com/sample-data/sessions.csv'
WHERE category IN getvariable('category')
  AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date');

The value 'sessions-' || today() is used as file name and the column name AS "CSV" is used as button label. The second SELECT statement is not rendered as a UI component, but is used to fetch the data for the download. Here we can see how variables can be used to dynamically filter data.

Displaying Data

The next two queries render data as is. We have a specific display for rendering queries that only return a single value:

SELECT count(*) AS "Total Sessions"
FROM 'https://taleshape.com/sample-data/sessions.csv'
WHERE category IN getvariable('category')
  AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date');

And if queries return multiple values, they are rendered as tables if no further formatting is specified:

SELECT 'Summary'::LABEL;
SELECT
  category AS "Category",
  count(*) AS "Sessions",
  to_seconds(round(avg(duration))) AS "Avg Duration",
FROM 'https://taleshape.com/sample-data/sessions.csv'
WHERE category IN getvariable('category')
  AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
GROUP BY "Category"
ORDER BY "Sessions" DESC;

Bar and Line Charts

To render charts, we define one column as chart axis using XAXIS or YAXIS, and then we define another column as chart value using ::BARCHART, ::BARCHART_STACKED, ::LINECHART, etc.

SELECT 'Sessions By Time of Day'::LABEL;
SELECT
  date_trunc('hour', created_at)::TIME::XAXIS AS "Time of Day",
  count()::BARCHART AS "Total Sessions",
FROM 'https://taleshape.com/sample-data/sessions.csv'
WHERE category IN getvariable('category')
  AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
GROUP BY ALL
ORDER BY ALL;

The above query uses trailing commas on all selected columns, it skips the * in count() and it uses GROUP BY ALL and ORDER BY ALL. These are all DuckDB-specific SQL features that make working with SQL more convenient. You can read more about them in the DuckDB’s Friendly SQL documentation.

The column names defined via AS ... are used as labels in the chart. Omit them to render a chart without labels.

SELECT 'Sessions per Week'::LABEL;
SELECT
  date_trunc('week', created_at)::XAXIS,
  category::CATEGORY,
  count()::BARCHART_STACKED,
FROM 'https://taleshape.com/sample-data/sessions.csv'
WHERE category IN getvariable('category')
  AND created_at BETWEEN getvariable('start_date') AND getvariable('end_date')
GROUP BY ALL
ORDER BY ALL;

Charts can define an additional CATEGORY column to render multiple series in the same chart.

Dashboard Layout

We try to automatically layout the dashboard based on the screen size and the number of queries, but there are additional options to control the layout.

In the example dashboard we are using SECTION to start a new section in the dashboard:

SELECT ''::SECTION;

You can think of this as a line-break. To give the section a title add the title instead of the empty string.