Build a Monitoring Dashboard With QuestDB and Grafana

In this tutorial you will learn how to use QuestDB as a data source for your Grafana dashboards and create visualizations using aggregate functions and sampling

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.

Grafana's Concepts

Before going ahead, let's review 3 essential Grafana concepts that we will use in this tutorial:

1. Data source - this is how you tell Grafana where your data is stored and how you want to access it. For the purposes of our tutorial, we will have a QuestDB server running and we will access it via Postgres Wire using the PostgreSQL data source plugin.
2. Dashboard - A group of widgets that are displayed together on the same screen.
3. Panel - A single visualization. Think of it as a graph/table.

Setup

Running Grafana

Once the Grafana server has started, you can access it via port 3000 (http://locahost:3000).

Running QuestDB

Downloading the Dataset

On our live demo, you can find 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.

Importing the Dataset

Now that we have the dataset, you can import the data by following out the documentation.

Creating Your First Visualization

Create a Data Source

Choose the PostgreSQL plugin and configure it with the following settings:

xxxxxxxxxx

host:localhost:8812

database:qdb

user:admin

password:quest

SSL mode:disable

Note: Grafana does not validate that queries are read-only, therefore a Grafana user could run:

To protect against this, you might want to run a dedicated QuestDB instance set to read-only mode. You can do this by http.security.readonly=true in your server.conf. Please check the configuration page.

Create a New Dashboard and Add a Panel

Now that we have a data source and a dashboard, we can go ahead and add our first panel.

The new panel will look like this:

Toggle the query edit box to "text edit mode" by clicking on the pencil icon. Your edit box should look like this:

For our first panel, we will show the fare of each taxi ride in the time filter applied.

xxxxxxxxxx

SELECT pickup_datetime AS time,

       avg(trip_distance) AS cash

FROM taxi_trips

WHERE

$__timeFilter(pickup_datetime)

SAMPLE BY $__interval

And here we have our first panel!

You can add multiple queries to the same panel and show multiple lines in the same panel.

Let's separate the taxi trips into 2 series — one for cash payments and one for card payments:

xxxxxxxxxx

--Cash

SELECT pickup_datetime AS time,

       avg(trip_distance) AS cash

FROM taxi_trips

WHERE

$__timeFilter(pickup_datetime)

AND

payment_type IN ('Cash')

SAMPLE BY $__interval

xxxxxxxxxx

--Card

SELECT pickup_datetime AS time,

       avg(trip_distance) AS card

FROM taxi_trips

WHERE

$__timeFilter(pickup_datetime)

AND

payment_type IN ('Card')

SAMPLE BY $__interval

And this is what the panel now looks like:

Aggregations With Sampling

You might have noticed that we are using the avg() function to calculate the average distance. The avg() function is an aggregate function, in this case, aggregating data over the specified sampling interval. This means that if the sampling interval is 1-hour, then we are in effect calculating the average distance traveled during each 1-hour interval.

You can find more information on aggregate functions on our documentation.

There are also 2 key Grafana functions used in the query above:

1. $__timeFilter(pickup_datetime): This handy function tells Grafana to send the from and to time defined in the Dashboard to the QuestDB server. Grafana translates this to: pickup_datetime BETWEEN '2018-02-01T00:00:00Z' AND '2018-02-28T23:59:59Z'.
2. $__interval: This function calculates 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 panel.

Let's add a new panel using these functions:

xxxxxxxxxx

SELECT

pickup_datetime AS "time",

count()

FROM taxi_trips

WHERE $__timeFilter(pickup_datetime)

SAMPLE BY $__interval;

This is what it looks like when viewing it over a time range of 28 days:

If we zoom in and choose a single day as the time range, it looks like this:

ASOF JOIN

ASOF joins allow us to join 2 tables based on timestamp where timestamps do not exactly match.

Here we are joining the taxi trips data with weather data:

xxxxxxxxxx

SELECT

    pickup_datetime as "time",

    sum(fare_amount) as sumOfFares,

    avg(rain1H) as rain1H

FROM (taxi_trips WHERE $__timeFilter(pickup_datetime))

ASOF JOIN weather

SAMPLE BY $__interval;

This is what it looks like for the whole month of February 2018:

If we zoom in on a rainy day:

Note that the graphs above have 2 Y-axis. To enable the right Y-axis, do this, click on the yellow line next to the rainH label:

In the pop-up, click on the Y-axis tab and enable use of the right axis for this series.

Conclusion

In summary, we have showed you how to visualize your data by harnessing the power of Grafana and QuestDB. For more information on QuestDB, please go to our documentation page.