Grafana is great at creating custom dashboard with vibrant custom charts. One lesser known feature is the ability to create dynamic dashboards which adjust to your dataset on-the-fly. This is particularly useful in financial use cases where you ingest data on a multitude of distinct symbols and add new symbols over time. QuestDB - known for its peak ingestion performance and SQL analytics - is a strong match for Grafana for market data use-cases.
Our tutorial will use the ECB historical FX rates dataset as an example. This dataset is well representative of financial market data. Note that it is a simplified daily dataset that only covers a few currencies exclusively against the EUR.
In real cases, the list of distinct symbols could be much larger and the frequency much higher. The bottom line is that you will be able to do something like this in no time, regardless of the size of the list and the underlying sampling frequency:
Let's say you start a new dashboard to view FX rates in realtime. The simple approach is to create individual charts for each currency.
For example something like this:
The last part
AND currency = 'USD' means that if you want to add another
currency, you need to manually create a new chart, copy paste the query and
adjust the hardcoded currency. This quickly becomes tedious and will not scale
well, particularly if you have a future plan to change the original query. If
you do, you'll need to adjust all of the individual charts.
A strong first step is to create a custom variable with a custom list. You can
do so using the
variables section of your
Dashboard settings in Grafana:
This allows you to create
query based variables:
customallows you to create a hard coded list. You can then
SELECTfilter your data and repeat charts for a specific subset of data.
queryallows you to create a dynamic list which will consist of the results of a query to your underlying QuestDB database.
We can start by defining a custom list as follows:
There are two notable options..
Multi-valueallows you to select multiple values at a time. This is useful when the values are not mutually exclusive or when you want to display them side by side.
Include all optionadds an
ALLoption to your list of possible values which means you do not have to select them all individually.
Once you are done configuring, a list preview appears at the bottom. Click
Update to save the variable. It will now be available as a dropdown in your
dashboard variables at the top of the cart:
The dashboard does not react to this variable yet. For it to do so, we need to adjust our settings.
We make three adjustments…
- Adjust the chart so that it repeats based on individual selections of the variable. For example, one chart for EUR, one chart for USD and so on
- Adjust the query so that the variable settings are reflected in the data
- Adjust the chart settings so that the titles reflect appropriately
To adjust the chart, edit the chart and alter the
The parameters are:
Repeat by variable: Choose the variable you want to use. In this case, we choose our newly created
Repeat direction: When
horizontal, then the chart panel will be duplicated for each individual variable to the right up to the
max per rowvalue. The latter will define how many charts to display per row in your window before moving to the next row. The
verticaloption is useful when you want to display various charts side-by-side. The charts will repeat vertically and you can set another adjacent column with another chart repeating vertically to see, for example, another data metric or a derived metric.
Grafana uses a variable inside of a query to make it dynamic. This is done by
replacing the hardcoded values by the variable name preceded by a
$ sign. For
example, your query could be changed from this:
In the background, Grafana will replicate and convert the query and dispatch it
to the database. So, for example, if you have repeat options to display a chart
for USD and another for JPY, Grafana will issue two queries. It will replace
='USD' in the first and with
='JPY' in the second.
One nice thing about these Grafana variables is that you can use them in your chart settings as well as in your queries. So for example, you can use them in your chart title, description and so on:
In the above example, we still hardcoded the list of possible currencies. This
is not strictly best practice. You'd still need to edit the list whenever you
want to add a new currency. To make this dynamic, we can replace the
parameter in the variable definition with
Doing so allows us to use the results of a QuestDB query to generate a list in real time. The main benefit is that if a new symbol value is added, then the dashboard will adjust automatically with no manual intervention whatsoever.
In this particular instance, we import the FX rates from the ECB into a QuestDB instance. Since the original file displays the different currencies as distinct columns, we can also change the format to something more usable.
The original ECB file:
And the updated DDL statement to create the new schema:
After, we import the ECB CSV file into the QuestDB web console and run a script to copy each column into the database. This uses the to_timestamp function to convert string to QuestDB timestamps:
The resulting dataset looks as follows:
Time for the cool part. We can get a list of unique symbols with a DISTINCT query. We use ORDER BY clause to ensure the symbols are in alphabetical order which makes it easier later to pick them from a list:
Now we can modify the original Grafana variable to be a
query and use the
above syntax as the definition:
Our list of currencies is now dynamic!
And our array of charts is dynamically linked to this list and it automatically adjusts with our selections!
In this tutorial, we demonstrated how to manage large lists of symbols using Grafana variables and QuestDB. We used the ECB historical FX rates dataset to create dynamic dashboards that adjust to your dataset in real-time, a feature particularly useful in financial use cases where data is ingested on a multitude of distinct symbols.
Onwards, we used Grafana's custom variables to create repeating charts, adjust queries and chart settings. We also showed how to use queries to generate dynamic lists, making our dashboard adjust automatically with no manual intervention when a new symbol value is added. The result is a more efficient and scalable way to monitor and analyze market data.
Checkout our real time (crypto dashboard)[/dashboards/crypto] for further inspiration.