Creating Time Series Plots in Grafana

Grafana is a multi-platform open source analytics and interactive visualization web application. If you are performing Data Analytics, Grafana is an invaluable tool that allows you to build dashboards using a combination of various data sources, as well as different build-in visualization types.

You can download and install Grafana from https://grafana.com/grafana/download. Platforms supported include Mac, Windows, Linux, Docker and ARM.

For this article, I will show you how you can build a time series plot showing the temperature and humidity of a particular location.

For this article, I am going to assume that you are already familiar with the basics of Grafana and MySQL. If you are new to Grafana, check out my article (https://www.codemag.com/Article/2207061/Developing-Dashboards-Using-Grafana) from Code Magazine.

Developing Dashboards Using Grafana

Sample App

For this article, there will be three components that I will be working on:

• A Python script to continuous write values into a MySQL server. In real-life, the Python script may be reading values from sensors. For this article, I will simulate some random temperature and humidity values. These values will be sent to a MySQL server every five seconds.
• A MySQL server containing a database to store all the values sent by the Python script.
• Grafana dashboard to pull the data from the MySQL server.

The MySQL server will have a database named WeatherReadings, with one single table named Readings. The schema of the table looks like this:

All images in this article created by author.

Creating the Database and Table

The following SQL statement creates the database and the table in MySQL:

CREATE DATABASE `WeatherReadings`; 
USE WeatherReadings;
CREATE TABLE `Readings` (
  `datetime` datetime NOT NULL,
  `temperature` float DEFAULT NULL,
  `humidity` float DEFAULT NULL,
  PRIMARY KEY (`datetime`)
) 

Creating the Python Script

The following Python script connects to the MySQL server using the mysqlclient package and writes values to the database table:

import datetime
import random
import threading
import MySQLdb                                 # conda install mysqlclient

db = MySQLdb.connect (user='user1',            # database user account
                      passwd='password',       # password for user1
                      host='127.0.0.1',        # IP address of MySQL
                      db='WeatherReadings')    # database name

cur = db.cursor()

def insert_record(datetime, temperature, humidity) :    
    try:
        cur.execute("""
            INSERT INTO Readings (datetime, temperature, humidity) VALUES
            (%s, %s, %s)
        """, (datetime, temperature, humidity))
        db.commit()
    except MySQLdb.Error as e:
        print ("Error %d: %s" % (e.args[0], e.args[1]))
        db.rollback()

def update():
    threading.Timer(5.0, update).start()      # call update() every 5s
    insert_record(datetime.datetime.utcnow(), # datetime in UTC
                  random.uniform(20, 39),     # temperature
                  random.uniform(0.7, 0.9))   # humidity

update()

The above script does the following:

• Connects to the MySQL server's WeatherReadings database using the user1 account with "password" as password.
• Defines the insert_record() function to insert a new temperature and humidity value into the Readings table.
• The update() function calls the insert_record() function every five seconds, simulating temperature and humidity values.

Note that when saving the date and time, I used the utcnow() function instead of the now() function. The utcnow() function returns the current date and time in UTC (or Coordinated Universal Time). For example, Singapore's time zone is UTC +8, and so if the current date and time in Singapore is 2023–02–08 10:07:38, then UTC time is 2023–02–08 02:07:38 (minus 8 hours). The reason for storing the time in UTC is because Grafana will automatically convert the UTC time to local time based on the browser time zone.

Configuring Grafana

With the Python script out of the way, it is now time to configure Grafana.

Adding a MySQL Data Source

In Grafana, add a new MySQL Data Source and configure it as follows:

Adding a Panel to the Dashboard

Create a new Dashboard in Grafana and click on the Add a new panel button:

Use the default Time Series panel and configure the panel with the following values:

Note the SQL statement used:

Select
    datetime as time,
    temperature,
    humidity * 100
FROM Readings

You need to set the datetime field as time in order for the Time Series panel to recognise the data as a time series.

Configure the Title property for the panel as follows:

Configure the various properties for the Graph styles property for the panel as follows:

Click the Apply button to exit the panel and return to the dashboard. You should now see the panel displaying the time series for the temperature and humidity readings:

Updating the Time Series Dynamically

Click on the arrow displayed next to the refresh button and select 5s:

This will cause the panel to fetch data from the MySQL data source every five seconds. The panel should now refresh every five seconds:

If you like reading my articles and that it helped your career/study, please consider signing up as a Medium member. It is $5 a month, and it gives you unlimited access to all the articles (including mine) on Medium. If you sign up using the following link, I will earn a small commission (at no additional cost to you). Your support means that I will be able to devote more time on writing articles like this.

Join Medium with my referral link – Wei-Meng Lee

Summary

In this article, you learned how to plot a time series using Grafana and MySQL server. In the real-world, your data would be continually sent to the database server and Grafana would be configured to refresh its data every few seconds (up to you to configure).

Tags: Data Analytics Grafana MySQL Python Time Series

Comment