MySQL flexible schema and JSON for Internet of Things

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

…
$ 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"}')
...

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'
});

...
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);
});
});

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>

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 ...;

CREATE TABLE `cloud_data` (
`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

Everyone knows what JSON is, right?

CREATE TABLE `cloud_data_json` (
`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

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

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;

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

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;

mysql> alter table cloud_data_json add key (published_at);
mysql> explain select data_name, published_at, data->>'$.data' as data from
cloud_data_json order by published_at desc limit 10G
partitions: NULL
type: index
possible_keys: NULL
key: published_at
key_len: 9
ref: NULL
rows: 10
filtered: 100.00
Extra: Backward index scan

cloud_data_json order by published_at desc, data_name asc limit 10G
select_type: SIMPLE
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 101589
filtered: 100.00
Extra: Using filesort

mysql> alter table cloud_data_json
add key published_at_data_name
(published_at desc, data_name asc);
New in MySQL 8.0

cloud_data_json order by published_at desc limit 10G
select_type: SIMPLE
partitions: NULL
type: index
possible_keys: NULL
key: published_at_data_name
key_len: 267
ref: NULL
rows: 10
filtered: 100.00
Extra: NULL

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

mysql> select json_pretty(data) from cloud_data_json
where data->>'$.data' like '%beer%' limit 1G
...
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"
}

mysql> alter table cloud_data_json_indexes add fulltext key (data);
ERROR 3152 (42000): JSON column 'data' supports indexing only via generated columns on
a specified JSON path.
mysql> ALTER TABLE cloud_data_json_indexes
-> ADD data_data VARCHAR(255)
-> GENERATED ALWAYS AS (data->>'$.data') VIRTUAL;
mysql> alter table cloud_data_json_indexes add fulltext key ft_json(data_name,
data_data);
ERROR 3106 (HY000): 'Fulltext index on virtual generated column' is not supported for
generated columns.

-> ADD data_name VARCHAR(255) CHARACTER SET UTF8MB4
-> GENERATED ALWAYS AS (data->>'$.name') STORED;
mysql> alter table cloud_data_json_indexes add fulltext key ft_json(data_name);
Query OK, 0 rows affected, 1 warning (3.78 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+

-> ADD data_data TEXT CHARACTER SET UTF8MB4
-> GENERATED ALWAYS AS ( CONVERT(data->>'$.data' USING UTF8MB4) ) STORED;
mysql> alter table cloud_data_json_indexes drop key ft_json,
add fulltext key ft_json(data_name, data_data);

● JSON aggregation functions
○ JSON_ARRAYAGG()
○ JSON_OBJECTAGG()
mysql> SELECT JSON_ARRAYAGG(`key`) AS `keys` FROM t1;
+--------------------------+
| keys |
+--------------------------+
| [ "key1",
"key2",
"key3" ]
|
+--------------------------+
1 row in set (0,00 sec)

Other uses for JSON and flexible storage
● Custom fields (CMS)
● Complex structures
● Etc

MySQL flexible schema and JSON for Internet of Things

MySQL flexible schema and JSON for Internet of Things

More Related Content

What's hot

Similar to MySQL flexible schema and JSON for Internet of Things

Recently uploaded

MySQL flexible schema and JSON for Internet of Things