Build a Monitoring Dashboard With QuestDB and Grafana
Use QuestDB as a data source for your Grafana dashboards and create visualizations using aggregate functions and sampling.
Join the DZone community and get the full member experience.
Join For FreePhoto by Luke Chesser on Unsplash
In this tutorial, we will cover how to load demo data from.CSV
files into QuestDB and use this as a data source for a Grafana dashboard. The dashboard will have line charts as data visualizations that make use of aggregate SQL functions and Grafana global variables for sampling data based on dashboard settings.
What Is Grafana?
Grafana is an open-source visualization tool. It consists of a server that connects to one or more data-sources to retrieve data, which is then visualized by the user in a browser.
The following three Grafana features will be used in this tutorial:
- Data source - this is how you tell Grafana where your data is stored and how you want to access it. For this tutorial, we will have a QuestDB server running, which we will access via Postgres Wire using the PostgreSQL data source plugin.
- Dashboard - A group of widgets that are displayed together on the same screen.
- Panel - A single visualization which can be a graph or table.
Setup
Start Grafana
xxxxxxxxxx
docker run -p 3000:3000 grafana/grafana
Once the Grafana server has started, you can access it via port 3000 (http://locahost:3000). The default login credentials are as follows:
user:admin
password:admin
Start QuestDB
The Docker version for QuestDB can be run exposing the port 8812
for the PostgreSQL connection and port 9000
for the web and REST interface:
xxxxxxxxxx
docker run -p 8812:8812 -p 9000:9000 questdb/questdb
Loading the Dataset
On our live demo, we use 10+ years of taxi data. For this tutorial, we have a subset of that data, the data for the whole of February 2018. You can download the compressed dataset from Amazon S3:
xxxxxxxxxx
curl https://s3-eu-west-1.amazonaws.com/questdb.io/datasets/grafana_tutorial_dataset.tar.gz > grafana_data.tar.gztar -xvf grafana_data.tar.gz
There should be two datasets available as .CSV
files:
weather.csv
taxi_trips_feb_2018.csv
These can be imported via curl using the /imp
REST entry point:
xxxxxxxxxx
curl -F data=@taxi_trips_feb_2018.csv http://localhost:9000/imp
curl -F data=@weather.csv http://localhost:9000/imp
Creating Your First Visualization
Create a Data Source
In Grafana, select to the cog icon to expand the Configuration menu, select Data Sources and click the Add data source button. Choose the PostgreSQL plugin and configure it with the following settings:
xxxxxxxxxx
host:localhost:8812
database:qdb
user:admin
password:quest
SSL mode:disable
If localhost
cannot be resolved by the Grafana Docker image, the local IP address of your machine should be used for the host field, e.g. 192.168.0.3:8812
.
Note that Grafana does not validate that queries are read-only. This means it's possible to run queries such as drop table x
in Grafana which would be destructive to a dataset. To protect against this, set a dedicated QuestDB instance to read-only mode by setting the property http.security.readonly=true
in your server.conf
. Details of setting this configuration can be found on Grafana's configuration page.
Create a New Dashboard and Add a Panel
Now that we have a data source and a dashboard, we can add a panel. Navigate to + Create and select Dashboard:
Toggle the query editor to text edit mode by clicking the pencil icon or by clicking the Edit SQL button. The query editor will now accept SQL statements that we can input directly:
x
SELECT pickupDatetime AS time,
avg(tripDistance) AS distance
FROM ('taxi_trips_feb_2018.csv' timestamp(pickupDatetime))
WHERE $__timeFilter(pickupDatetime)
SAMPLE BY $__interval
Click the time range selector in above the chart and set the following date range:
- Set the From value to
2018-02-07 00:00:00
- Set the To value to
2018-02-14 23:59:59
- Click Apply time range
We have built our first panel with aggregations:
Query Details
To graph the average trip distance above, we use the avg()
function on the tripDistance
column. This function aggregates data over the specified sampling interval. If the sampling interval is 1-hour, we are calculating the average distance traveled during each 1-hour interval. You can find more information on QuestDB aggregate functions in our documentation.
There are also two key Grafana-specific expressions used which can be identified by the $__
prefix:
$__interval
This is a dynamic interval based on the time range applied to the dashboard. By using this function, the sampling interval changes automatically as the user zooms in and out of the panel.$__timeFilter(pickupDatetime)
tells Grafana to send the start-time and end-time defined in the dashboard to the QuestDB server. Given the settings we have configured so far with our date range, Grafana translates this to the following:pickupDatetime BETWEEN '2018-02-01T00:00:00Z' AND '2018-02-28T23:59:59Z'
Adding Multiple Queries
You can add multiple queries to the same panel which will display multiple lines on a graph. To demonstrate this, separate the taxi data into two series, one for cash payments and one for card payments. The first query will have a default name of A
x
--Cash
SELECT pickupDatetime AS time,
avg(tripDistance) AS cash
FROM ('taxi_trips_feb_2018.csv' timestamp(pickupDatetime))
WHERE $__timeFilter(pickupDatetime)
AND paymentType IN ('Cash')
SAMPLE BY $__interval
Click + Query to add a second query (automatically labeled B
) and paste the following in text mode:
xxxxxxxxxx
--Card
SELECT pickupDatetime AS time,
avg(tripDistance) AS card
FROM ('taxi_trips_feb_2018.csv' timestamp(pickupDatetime))
WHERE $__timeFilter(pickupDatetime)
AND paymentType IN ('Card')
SAMPLE BY $__interval

We can see in this graph that the distance traveled by those paying with cards is longer than for those paying with cash. This could be due to the fact that users usually carry less cash than the balance in their card.
Let’s add another panel:
x
SELECT
pickupDatetime AS "time",
count()
FROM ('taxi_trips_feb_2018.csv' timestamp(pickupDatetime))
WHERE $__timeFilter(pickupDatetime)
SAMPLE BY $__interval;

$__interval
property:
The daily cycle of activity is visible, with rides peaking in the early evening and reaching a low in the middle of the night.
Conclusion
We have learned how to import time series data into QuestDB and build a dashboard with multiple queries in Grafana. If you like this content and want to see more tutorials about third-party integrations, let us know in our Slack Community or drop us a star on GitHub.
Published at DZone with permission of Joan Augsburger. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments