Graphing Non Time Series SQL Data in Grafana

Video Lecture

Graphing Non Time Series SQL Data in Grafana Graphing Non Time Series SQL Data in Grafana Graphing Non Time Series SQL Data in Grafana

Description

Grafana graphs Time Series data from many types of data sources extremely well. But sometimes you just want to graph, simple non time series data. i.e, Data without timestamps, flat tables that show simple statistics,values and snapshots.

Example non time series data as a flat table.

id name value
1 Cat 56
2 Dog 5
3 Lizard 4
4 Crocodile 2
5 Koala 2
6 Cassowary 2
7 Peacock 1
8 Emu 1
9 Kangaroo 1

And you want to see this simple data, as graphs similar to below. img/non-time-series-graphs.jpg

In an earlier video, we created the 'exampledb' schema on our mysql server that we installed locally on our Grafana server and setup as a datasource inside Grafana. Create MySQL Data Source

Lets now create a simple table with some data that we can query.

CREATE TABLE `exampledb`.`flat_table_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `total` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

Insert Statement

INSERT INTO `exampledb`.`flat_table_example`
(`username`,
`total`)
VALUES
('Cat',56),
('Dog',5),
('Lizard',4),
('Crocodile',2),
('Koala',2),
('Cassowary',2),
('Peacock',1),
('Emu',1),
('Kangaroo',1);

Check data exists

SELECT *
FROM `exampledb`.`flat_table_example`;

Explore the Data in Grafana

Open the Explore tab, select MySQL as the datasource and show SQL editor.

Replace the default sql with this below

SELECT
  username AS metric,
  total as value
FROM flat_table_example
ORDER BY id

Now convert it to a time series by adding NOW() as "time"

SELECT
  NOW() AS "time",
  username AS metric,
  total as value
FROM flat_table_example
ORDER BY id

You can test updating the data in the table, and seeing it in Grafana by opening your mysql prompt and executing,

UPDATE `exampledb`.`flat_table_example` SET `total` = 50 WHERE `username` = 'Koala';