![[Tutorial] Pushing IoT Sensor Data to Google Data Studio to Create a Time Series Graph](https://cdn.prod.website-files.com/5ba3150a54f21cfe3f02b031/61fa4e7a44ccdd62a4f50a59_google_data_studio_mysql_iot_tutorial.jpg)
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.
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).
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.
Google Data Studio is free of charge with a Google account.
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.
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:
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.
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.
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!
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!
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.
