MySQL Flexible Schema for IoT

1 .

2 .● ○ ○ 

3 .● Part 1: IoT demo ● Part 2: MySQL, JSON and Flexible storage 

4 .

5 .

6 .

7 .

8 . … $ node particle_mysql_all.js Starting... INSERT INTO cloud_data_json (name, data) values ('particle', '{\"data\":\"null\",\"ttl\":60,\"published_at\":\"2017-09-28T19:40:49.869Z\",\"coreid\":\"1f0039000947343337373738 \",\"name\":\"Server Error\"}') ... 

9 .console.log("Starting..."); var Particle = require('particle-api-js'); var particle = new Particle(); fs = require('fs') var token = fs.readFileSync('.token', 'ascii').replace(/\n$/, ''); //Get your devices events // MySQL Connection var mysql = require('mysql'); var connection = mysql.createConnection({ host : 'localhost', user : 'photon', password : 'photon', database : 'particle' }); 

10 .... particle.getEventStream({deviceId: 'mine', auth: token}).then(function(stream) { stream.on('event', function(data) { var query = connection.query(' INSERT INTO cloud_data (name, data) values (?, ?)', [data.name, data.data], function(err, result) { if (err) { console.log('Error in ' + query.sql + err); } }); console.log(query.sql); }); }); 

11 .… 

12 .

13 .Storing data in MySQL … $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.3-rc MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 

14 .CREATE TABLE `sensor_wide` ( `id` int(11) NOT NULL AUTO_INCREMENT, `light` int(11) DEFAULT NULL, `temp` double DEFAULT NULL, `humidity` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB alter table sensor_wide add water_level double ...; 

15 .CREATE TABLE `cloud_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `data` text DEFAULT NULL, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (`id`) ) ENGINE=InnoDB + More Flexible - High overhead on storage 

16 .Everyone knows what JSON is, right? 

17 .CREATE TABLE `cloud_data_json` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `data` JSON, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (`id`) ) ENGINE=InnoDB; + Most Flexible + Indexes - Overhead on storage 

18 .... stream.on('event', function(data) { var query = connection.query( 'INSERT INTO cloud_data_json (client_name, data) VALUES (?, ?)', ['particle', JSON.stringify(data)] ) ... (demo) 

19 .

20 . { "ttl": 60, "data": "FvGav,tagkey=beer-store spFridge=7.00,pvFridge=7.44", "name": "LOG_DATA_DEBUG", const mysqlx = require('@mysql/xdevapi'); "coreid": "3600....", // MySQL Connection "published_at": var mySession = "2017-09-28T18:21:16.517Z" mysqlx.getSession({ } host: 'localhost', port: 33060, dbUser: 'photon' }); ... session.getSchema("particle").getCollection("cloud_data_docstore") .add( data ) .execute(function (row) { }).catch(err => { console.log(err); }) .then( function (notices) { console.log("Wrote to MySQL") }); … https://dev.mysql.com/doc/dev/connector-nodejs/ 

21 .select data->>'$.name' as data_name, data->>'$.data' as data, data->>'$.published_at' as published from cloud_data_json order by data->'$.published_at' desc limit 10; 

22 .EXPLAIN select data->>'$.name' as data_name … order by data->>'$.published_at' desc limit 10 select_type: SIMPLE table: cloud_data_json partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 101589 filtered: 100.00 Extra: Using filesort 

23 .mysql> ALTER TABLE cloud_data_json -> ADD published_at DATETIME(6) -> GENERATED ALWAYS AS (STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ")) VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE cloud_data_json -> ADD data_name VARCHAR(255) -> GENERATED ALWAYS AS (data->>'$.name') VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 

24 .mysql> alter table cloud_data_json add key (published_at); Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G table: cloud_data_json partitions: NULL type: index possible_keys: NULL key: published_at key_len: 9 ref: NULL rows: 10 filtered: 100.00 Extra: Backward index scan 

25 .mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc, data_name asc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 101589 filtered: 100.00 Extra: Using filesort 

26 . New in MySQL 8.0 mysql> alter table cloud_data_json add key published_at_data_name (published_at desc, data_name asc); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0 

27 .mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: index possible_keys: NULL key: published_at_data_name key_len: 267 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 

28 .JSON field type was introduced in 5.7, improved in 8.0 ● Partial updates ○ in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column ○ Only functions are supported: JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() Use JSON_STORAGE_FREE(json_val) to see how much storage space was freed in its binary representation after it was updated in place 

29 .mysql> select json_pretty(data) from cloud_data_json where data->>'$.data' like '%beer%' limit 1\G ... json_pretty(data): { "ttl": 60, "data": "FvGav,tagkey=beer-store spFridge=7.00,pvFridge=7.44", "name": "LOG_DATA_DEBUG", "coreid": "3600....", "published_at": "2017-09-28T18:21:16.517Z" }