Victor Wiebe

Adding to Grafana

In a recent post (https://victorwiebe.com/2021/02/09/node-red-and-grafana/) I had written about putting together a small Grafana dashboard, powered by a Node Red and MySQL combination (actually, MariaDB, but the difference is academic). The dashboard has one simple purpose: display a chart of the USD price of a few cryptocurrencies that I like to follow (primarily altcoins). The dashboard does precisely what I want it to do, but I found that I also wanted a table that shows quickly what the most recent price for all the coins was at a quick glance. Like this:

Grafana definitely has a table panel type, so building this out was not going to require much in the way of customisation.

What I needed to do this was a way to pull out the most recent value for each of the coins. In pseudo-query language, the request would read something like this:

GET the most recent value for EACH coin, regardless of WHEN that value was put into the database.

The rationale behind the “regardless” clause is that each entry might be a second apart: 12:01.01, 12:01.01, and 12:01.02, for instance.

My database schema currently consists of only two tables: coin_names, and coin_values:

Because I cannot guarantee that the most recent timestamp for each of the coin_id,value combinations – for each coin – is universal, I had to essentially build a single table join on itself. It’s somewhat self-referential, but the jist of it is to think of the table as two different tables with identical data, and then join that. If I had multiple tables – one for each coin – my approach would be different, but I don’t.

In short, my approach is to build a join across three tables: coin_names, coin_values(one) and coin_values(two).

The query ended up being this (and yes, I know I’m using an archaic method of joining the tables; it need not be pointed out).

SELECT t3.coin_name,t1.value FROM coin_values t1,coin_names t3 WHERE t1.coin_id = t3.coin_id AND t1.timestamp = (SELECT max(t2.timestamp) FROM coin_values t2 WHERE t1.coin_id = t2.coin_id)

Believe me, I only want to type that once, and that’s a simple query for what I’ve done in the past. The Thing To Do was to make an SQL View out of it, so I only need to query the view:

CREATE VIEW "last_value" AS (SELECT t3.coin_name,t1.value FROM coin_values t1,coin_names t3 WHERE t1.coin_id = t3.coin_id AND t1.timestamp = (SELECT max(t2.timestamp) FROM coin_values t2 WHERE t1.coin_id = t2.coin_id))

Now, when I want to query for the most recent values, I have a view that I can query that contains only the information I want.

MariaDB [coins]> SELECT * FROM last_value;
+-----------------------+------------+
| coin_name             | value      |
+-----------------------+------------+
| Hive                  |   0.210398 |
| Basic Attention Token |   0.445418 |
| Bitcoin Cash          |      502.4 |
| Doge Coin             |   0.072883 |
| Steem                 |   0.326043 |
| Appics                |  0.0275567 |
| Presearch             |   0.073745 |
| Blurt                 | 0.00559335 |
+-----------------------+------------+

And this is the query that I dumped into the new Grafana Table panel. Now, when I choose a coin to view, two panels update, and the table stays static to show all the coins.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll Up