Skip to content


 Zabbix
 Grafana
 Prometheus
 Threejs and TypeScript
 SocketIO and TypeScript
 Blender Topological Earth
 Sweet Home 3D
 Design Patterns Python
 Design Patterns TypeScript
   
 Course Coupon Codes
Three.js and TypeScript
Kindle Edition
$6.99 $9.99 Paperback 
$22.99 $29.99




Design Patterns in TypeScript
Kindle Edition
$6.99 $9.99 Paperback
$11.99 $19.99




Design Patterns in Python
Kindle Edition
$6.99 $9.99 Paperback
$11.99 $19.99




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 can graph 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, basic tables that show simple statistics.

Example non time series data as a flat table.

id name value
1 Cat 56
2 Dog 35
3 Lizard 41
4 Crocodile 22
5 Koala 26
6 Cassowary 29
7 Peacock 19
8 Emu 10
9 Kangaroo 13

We would like to see this simple example data, as a graph similar to below. img/non-time-series-graphs.jpg

For this exercise, we can quickly create a new database named exampledb and allow our grafana database user select permissions on it.

mysql
> CREATE DATABASE exampledb;

> show databases;

You should see a new database named exampledb.

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

> CREATE TABLE `exampledb`.`simple_table` (
    `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`.`simple_table`
  (`username`,
  `total`)
  VALUES
  ('Cat',56),
  ('Dog',35),
  ('Lizard',41),
  ('Crocodile',22),
  ('Koala',26),
  ('Cassowary',29),
  ('Peacock',19),
  ('Emu',10),
  ('Kangaroo',13);

Check data exists

> SELECT * FROM `exampledb`.`simple_table`;

Now allow our grafana database user select permissions on the table.

> GRANT SELECT ON exampledb.simple_table TO 'grafana'@'###.###.###.###';

> FLUSH PRIVILEGES;

> quit;

Explore the Data in Grafana

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

Replace the default sql with this below

SELECT
  username AS metric,
  total as value
FROM exampledb.simple_table
ORDER BY id

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

SELECT
  NOW() as time_sec,
  username AS metric,
  total as value
FROM exampledb.simple_table
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`.`simple_table` SET `total` = 50 WHERE `username` = 'Koala';

Grafana 9 and Ubuntu 22.04 Notes

There are no considerable differences to be aware of as shown and discussed in the video.