Graphing Non Time Series SQL Data in Grafana
Video Lecture
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.
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';