Gravio Blog
February 2, 2022

[Tutorial] Pushing IoT Sensor Data to Google Data Studio to Create a Time Series Graph

In this tutorial we learn how to send data from a Gravio Edge environment to a MySQL database using HTTP, and then setup Google Data Studio to read that data from a MySQL database to visualise the data.
[Tutorial] Pushing IoT Sensor Data to Google Data Studio to Create a Time Series Graph

Introduction

This article is part of a series that shows how to integrate Gravio with different Dashboard or Data visualization platforms, using different techniques to show your data in new and interesting ways, while also enabling you to use your existing dashboards or introduce new ones.

Approach

tldr; we are using a Gravio CO2 sensor and HTTPS API requests sent to a web server with a database, and then reading that data from Google Data Studio to plot it on a time series graph.

Note: if you have a Gravio Basic subscription, you could use the Temperature Sensor instead, as that will also provide Time Series data, though the data is sent less frequently.

As Gravio is an Edge computing service and does not typically provide an external API for accessing its data, we can use an intermediate MySQL database to store that data, which is accessible by Google Data Studio. Google Data Studio does not hold any data on its own, instead it reads data from various databases and data warehouses. You could also push the data to a Google-based Cloud Database or other data storage system that Google Data Studio can integrate with (see here).

About Google Data Studio

Website: https://datastudio.google.com/ 

Google Data Studio is an online tool for combining and converting data to enable users to build their own customizable reports and dashboards, it was introduced by Google in early 2016 as part of the enterprise Google Analytics 360 suite. Later in 2016 Google announced a free version of Data Studio for individuals and small teams. For this tutorial you can use the free version of Google Data Studio.

Pricing & Account information

Google Data Studio is free of charge with a Google account.

Tutorial

Requirements

  • Gravio HubKit setup - the Gravio Edge server
  • Gravio Studio installed - used to configure your Gravio HubKit
  • Gravio Standard account - see pricing page
  • Gravio CO2 sensor (or a Temperature sensor available with Gravio Basic)
  • A free Google Account with Google Data Studio
  • A server that can host a PHP script and a MySQL database

Steps

Part 1 - Setting up the Database and PHP Server

You can use many different data sources that Google Data Studio can hook into, but in this case, we are using a MySQL server over the internet. You can add any MySQL server in Data Studio:



On your server, install a MySQL Database and ensure it is able to run PHP scripts via a webserver. In this tutorial we use a Ubuntu server running Apache.

Once you have your database and PHP up and running, create a new database and a new user, that can only access this database.

You will also have to enable MySQL to accept connections from outside. For this, open the MySQL configuration file (in our case /etc/mysql/my.cnf) and set the 

bind-address = 0.0.0.0

And restart MySQL

/etc/init.d/mysql restart

We also highly recommend to install SSL certificates to encrypt the connection. For this, follow the instructions provided by MySQL for your respective version (Version 5 manual, version 8 manual). 

Furthermore, we highly recommend you also whitelist IP addresses that should be able to access your MySQL port using a firewall. For this, you can use the manual on https://www.netfilter.org/documentation/ whitelisting the IP addresses from Google’s Data Studio servers.

Part 2 - Setting up the Database

We are using the script and instructions from https://github.com/cburgdorfer/http-post-to-mysql-insert. According to the instruction, run the following SQL query to create your database table:


CREATE TABLE IF NOT EXISTS `gravio_data` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `AreaName` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `LayerName` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `DataKind` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `PhysicalDeviceName` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `PhysicalDeviceId` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `DataId` varchar(128) COLLATE latin1_general_ci NOT NULL,

  `Timestamp` datetime NOT NULL COMMENT 'Original Sensor Timestamp',

  `Data` varchar(256) COLLATE latin1_general_ci NOT NULL,

  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'MySQL Database Timestamp',

  PRIMARY KEY (`id`),

  KEY `id` (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8 ;

Once that’s created, move to your webserver’s folder that is accessible from the internet and add the contents from the above github repository to that folder. Rename the credentials.php.sample file to credentials.php and enter your database credentials, adding some random string as salt. Remember this random string as you will need it in Gravio to sign the post requests.

If you now call this URL in your browser, you should see something like this:



Part 3 - Sending Data to the API

The PHP script takes JSON structured data from the HTTP Post body and turns it into the values inserted into the database. The format of the JSON is as follows:


"Root": {

"AreaName": "< Room or space that data is coming from >",

"Checksum": "e381fed54d1974ae0008d8fcb93331a6",

"Data": 665,

"DataId": "1125dbce42cb40feb7f70073867ef9be",

"KindName": "Gravio-CO2",

"LayerName": "CO2",

"PhysicalDeviceId": "D1-8E-06-FE-FF-57-B4-14",

"PhysicalDeviceName": "GRAVIO CO2-sensor7",

"Timestamp": 1643441743

}


Note the “Checksum” value, which is constructed from all the other values plus the secret “salt” string you have entered in the database configuration file. This Checksum is to confirm that the data is actually coming from the intended originator, so do not share it with anyone.

Both Gravio and the PHP script are compiling the checksum from the other data points plus the “salt”, and the checksums need to match on both sides in order for the data to be inserted into the database.

Now in order to construct the Checksum, this is how the Gravio pre-mapping needs to look:



For your convenience, this is the string:


{

  "AreaName": tv.AreaName,

  "LayerName": tv.LayerName,

  "KindName": tv.KindName,

  "PhysicalDeviceName": tv.PhysicalDeviceName,

  "PhysicalDeviceId": tv.PhysicalDeviceId,

  "DataId": tv.DataId,

  "Timestamp": ToInt(tv.Timestamp),

  "Data": tv.Data,

  "Checksum": MD5(tv.AreaName+tv.Data+tv.KindName+tv.PhysicalDeviceName+tv.PhysicalDeviceId+tv.DataId+ToInt(tv.Timestamp)+tv.LayerName+"yoursecretsalt")

}


Also, note that we are sending the time as a UNIX timestamp to avoid any time zone related issues. On the MySQL side the timestamp is translated back.

Note: You may want to use a tool like MySQLWorkBench to verify that the data is being inserted properly into your database. You may also need to allow your IP address to be whitelisted as well to enable remote access. Alternatively, you could install PhpMyAdmin on the server to access the data more directly.


Part 4 - Connecting your Google Data Studio to your MySQL Database

First, create free Google Data Studio account on https://datastudio.google.com/. Once you have that, create a new report by clicking on the button on the top left and give it a sensible name. Upon creation of the new report you’ll be asked to add a data source. Type “mysql” and pick the MySQL Connector:


Next, you enter the connection details of your MySQL server and if you have set up the SSL certificates, add those too. After clicking on the “Authenticate” button, you will see the database table, select it and click “Add”. Confirm the message popup that confirms you’re adding the data and Data Editors will have access to it, and you should a screen similar to this:


We don’t need that table, so you can delete it.


Part 5 - How to Create the Minute Time Series Graph inside Google Data Studio

First we need to tell Google Data Studio that the data should be read not aggregated by day, but by minute. You can achieve this by selecting “Resource” from the menu and then “Manage added data sources”





Click on the “Edit” button next to your MySQL Data and you’ll see the available fields:


In the “Data” row click on “Text” in the “Type” column and pick “Number” to cast the value as a numeric value:



In the “created” row click on “Date and Time” in the “Type” column and pick “Date Hour Minute” under “Date & Time”: 



If you want, you can change the Data Freshness at the top, but you’re essentially done and can click the “Done” button and close the Data Source editor.

Next we add a Timeseries chart. For this demo we pick the first one:



It is important to take a time series in order for the X axis to be linear and consistent reflecting time.

Drag the graph across the Canvas:


If you already have data in the database it may look something like the above. Obviously we want to have the data down to the minute, not the whole day, therefore click on the little calendar next to the “created” field under “Dimension” and pick “Date Hour Minute”:



This shows currently how many records there are in the database per minute. But we actually want the measured average in any given minute. Therefore we click on the “Record Count” button and select “Data”:


This however is now the sum for any given minute, so we want to change it to the average. Click on the little “SUM” button next to “Data”, and pick “Average”:

Now we need to deal with the minutes that don't have any value. By default it sets them to zero. But they effectively were not zero, they were just not measured, so we can “break” the line there. Pick your graph and choose the “Style” tab on the top right:


Scroll down to “General” and “Missing Data” and pick “Line Breaks”:


There you go! You now have Gravio IoT sensor data in Google Data Studio! 

Bonus: 

You might want to adjust for timezones. You can achieve this by adding a new field under from the button at the bottom right, then use the built-in data functions:


This field is now available under “created UTC+8” in the Dimensions, provided that the database’s timezone is in UTC. Don’t forget to switch it to “Date Hour Minute”, too.



Happy graphing!


Summary

Google Data Studio does not hold any data, and you can’t “inject” data into Google Data Studio directly. Therefore you need to create an intermediate database where you inject the Gravio data into, then pull the data again from Google Data Studio. Overall, this is a fairly straight forward process. You can, of course, use a different database or even Google’s own data warehouse. 

Follow us on Twitter or Linkedin to stay informed when we will publish a tutorial on how to achieve that or if you have any questions, also feel free to join our Slack

References


Latest Posts
[Tutorial] How to Set Up a Mosquitto MQTT Broker Securely — Using Client Certificates
Step by step guide how to secure MQTT Mosquitto Broker
Tuesday, August 20, 2024
Read More
Coming Thursday, 29th August Learning Session: Learn About How LoRa Works
Join our free event to Learn More on How LoRa Works
Tuesday, August 20, 2024
Read More