In this tutorial, we will cover how to load demo data from
.CSV files into
QuestDB and to 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
Grafana is an open-source visualization tool consisting 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.
Once the Grafana server has started, you can access it via port 3000 (http://locahost:3000). The default login credentials are as follows:
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:
There should be two datasets available as
These can be imported via curl using the
/imp REST entrypoint:
In Grafana, select to the cog icon to expand the Configuration menu, select Data Sources and click the Add data source button. Choose PostgreSQL plugin and configure it with the following settings:
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.
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 read-only mode by
setting the property
http.security.readonly=true in your
Details of setting this configuration can be found on Grafana's
Now that we have a data source and a dashboard, we can add a panel. Navigate to + Create and select Dashboard:
The new panel has a graphing area on the top half of the window and a query builder in the bottom half:
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:
Paste the following query into the editor:
Click the time range selector above the chart and set the following date range:
- Set the From value to
- Set the To value to
2018-02-14 23:59:59and click Apply time range
We have built our first panel with aggregations:
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
aggregate functions on our documentation.
There are also 2 key Grafana-specific expressions used which can be identified
$__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
These are global variables which can be used in queries and elsewhere in panels and dashboards. To learn more about the use of these variables, refer to the Grafana reference documentation on Global variables.
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
Click + Query to add a second query (automatically labeled
B) and paste
the following in text mode:
Both queries are now layered on the same panel with a green line for cash and a yellow line for card payments:
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:
This is what our query looks like when viewing a time range of 28 days:
Zooming in to a single day shows more detailed data points as we are sampling by
The daily cycle of activity is visible, with rides peaking in the early evening and reaching a low in the middle of the night.
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.