#Data visualization for time-series data
One of the most common uses of a time-series database is visualizing data on a dashboard to inform business decisions. This tutorial will show you how to connect QuestDB and Apache Superset. QuestDB is an open-source time-series database and Apache Superset is an open-source data exploration and visualization platform. By combining these, you can create a dashboard displaying data from multiple tables.
#Installing and starting QuestDB
If you are not yet using QuestDB, or if you are using a version lower than 7.1.2, please refer to our documentation. In my case, I start a QuestDB instance (with ephemeral storage) using Docker:
We will be working with two datasets that we need to download and import into QuestDB. The first one represents taxi rides in the city of New York. The original dataset is very large. Therefore, we are going to work with a subset that represents a few minutes of taxi rides on January 1st, 2018. You can download and import the dataset from the command line using curl:
The second dataset represents energy consumption and next-day forecast at 15-minute intervals for a few European countries. We have curated a subset of the original dataset containing data for 2018. Download the data and import it to QuestDB with:
If you visit your QuestDB web interface at
http://localhost:9000, you should
see the two new tables. You should have 999 rows in the
trips_2018 table and
205189 rows in the
#Installing and starting Apache Superset
You will also need a working installation of Apache Superset with the QuestDB-Connect module installed. See Superset integration for more details. I choose Docker to run Superset, and these are the steps to install it:
By default, your Apache Superset runs at
Apache Superset must be initialized when started for the first time. This includes setting up default databases and sample datasets. This process may take some time before it is fully functional.
#Connecting QuestDB and Superset
In Superset you create charts that you add to dashboards. Charts get their data from different databases, so the first step is to configure the database connections you will use. In our case, we need to configure a single connection to QuestDB.
Superset supports a wide range of databases by default. QuestDB is not one of them, but since we installed the questdb-connect module, we can add the connection by following these steps:
- From the
Superset web interface, select
+ DATABASEto add the following parameters:
Other / DISPLAY NAME:
Note that I use
host.docker.internal instead of
localhost because I started
Superset from Docker. If you are running Superset outside of Docker, you would
need to use
#Creating a dataset
Before you create a chart, Superset requires you to create a dataset. In its simplest form, a dataset represents a table in your database. Still, you can also add calculated columns, or create a dataset from a complex SQL statement that references one or more tables.
For the first dataset, let's use the
trips_2018 table we created earlier.
- From the
Superset web interface, select
- Select QuestDB as the
trips_2018as the table. A list of columns is displayed.
CREATE DATASET AND CREATE CHART
There is also the optional step to edit the dataset by selecting
can remove columns and add calculated columns or metadata.
#Creating your first chart
At this point, you should see a dialog to select a visualization type.
If not, select the
Charts link at the top of the Superset web interface and
click the name of the dataset we created in the previous step.
We're now ready to create a chart.
Superset has many types of charts. One of the simplest charts is the
chart. If you cannot find the thumbnail, you can always type
Table in the
search box or navigate through the different types of charts using the
categories on the left.
When you select the
Table type and
CREATE NEW CHART, you should see the
Chart dialog. This is virtually the same for all the charts, with different
options depending on the chart type.
At the top of this dialog, there are two tabs: one for defining the data to display, and one for customizing the chart appearance.
For this first chart, we want to display the total number of entries, the average trip distance, and the average total amount paid, grouped by vendor, cab type, and rate code:
rate_code_idas dimensions by dragging columns or by clicking on the
PERCENTAGE METRICSbox, so we can see how much of the total each row in the table contributes
- Uncheck the
Your screen should look very similar to the screenshot below:
To save the chart, select
SAVE in the top right. Choose a name (e.g., "Trips
Stats"), and the name of a new dashboard (e.g., "QuestDB Demo"). Click
SAVE AND GO TO DASHBOARD.
Congrats. You have just created your first chart and dashboard.
If you want to spice up the dashboard by adding headings, links, images, tabs,
dividers, etc., click
EDIT DASHBOARD and play around with the layout. Check
for more information.
#Creating a dataset from a SQL statement
We will now add a new dataset, but in this case, we want to use QuestDB-specific
SQL, such as
SAMPLE BY. We can do this by
creating a dataset from a SQL statement. Select
SQL Lab and type the
following query in the SQL box:
RUN to see the results. The
energy_2018 table contains data in
15-minute intervals, and we use
SAMPLE BY to get the total usage and forecast
per day and country.
Click the arrow by the
SAVE button, select
SAVE DATASET, type the name
energy_sampled_1d, and then click
SAVE AND EXPLORE.
You should now see the
Chart dialog. Ignore any error messages you see on the
next screen, as they will disappear as we add some fields and update the chart.
view all charts link and look for the
Mixed Chart type, which
allows us to represent line, bar, and area charts for different series on a
single chart. We will use it to plot the actual versus projected energy
consumption by country.
This time, the chart dialog has more options than before.
Let's select the
timestamp column for the
Shared query fields section,
SUM(daily_actual) as the
Query A metric,
country_code as the
SUM(daily_forecast) as the
Query B metric, and
Query B dimension. Uncheck
SORT DESCENDING for
B queries, and
If all goes well, you will see a line chart representing the energy data. Click
SAVE, name the chart (e.g., "Energy Forecast"), and add it to your existing
Apache Superset is a complete tool for creating data visualizations. In this post, we have connected QuestDB to Apache Superset and added some basic charts. There are many more charts and options you can use.
The next step is to explore the Superset documentation. You can learn how to create business dashboards for both batch and real-time scenarios.
We encourage you to share some of your dashboards with our Slack Community. You can also play with our live demo to execute queries on demo datasets. Of course, contributions to our open-source projects on GitHub are more than welcome.