IOT reading and storing problems - what is the most efficient method to update MySqli table from a JSON Structure

[John_Betong]

I will be using an Apache2 Cron job to obtain the following JSON Data:

[ {
    "battery":3200,
    "device_id":"ambadevice2",
    "humidity":55,
    "raw":"+BgANwyA",
    "temperature":45.12,
    "time":"2018-10-03T13:16:49.433592417Z"
},
   
{
    "battery":3200,
    "device_id":"ambadevice2",
    "humidity":55,
    "raw":"+A4ANwyA",
    "temperature":45.02,
    "time":"2018-10-03T13:19:17.666560507Z"
}  ]

I was wondering the best table structure to store the JSON data and how to UPDATE the table.

Proposed Table Structure:

CREATE TABLE `tn_001` (
  `id` int(8) NOT NULL,
  `battery` int(8) NOT NULL,
  `device_id` varchar(12) NOT NULL,
  `humidity` int(4) NOT NULL,
  `raw` varchar(12) NOT NULL,
  `temperature` decimal(10,0) NOT NULL,
  `time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[John_Betong]

…the continuing saga of an IOT Device

I had problems with the above table structure and used this one instead:

<?php
  declare(strict_types=1); // Fails Fast
  error_reporting(-1);
  ini_set('display_errors', 'TRUE');

  $sql = <<< ______TMP
    CREATE TABLE 
      `ttn_001`
      (
        `battery`     int(8)        NOT NULL,
        `device_id`   varchar(12)   NOT NULL,
        `humidity`    int(4)        NOT NULL,
        `raw`         varchar(12)   NOT NULL,
        `temperature` decimal(10,0) NOT NULL,
        `time`        varchar(42)   NOT NULL
      )
      ENGINE=InnoDB DEFAULT CHARSET=utf8;
______TMP;

  $mysqli = new mysqli("myServer", 'myUserName', "myPassWord", "myDataBase");

# check connection 
  if ($qry = $mysqli->query($sql)):
    echo sprintf("<br>Connect failed: %s\n", mysqli_connect_error() );
  endif;

Also created the primary index on the time field.

MySqli Update Query:

<?php
  declare(strict_types=1); // Fails Fast
  error_reporting(-1);
  ini_set('display_errors', 'TRUE');

$rows = json_decode($json, true);
foreach($rows as $key => $row):
  if($row):
    $sql = <<< ______TMP
      INSERT INTO 
        `ttn_001`
      (
        `battery`,
        `device_id`,
        `humidity`,
        `raw`,
        `temperature`,
        `time`
      )
      VALUES 
      (
        {$row['battery']},
        "{$row['device_id']}",
        {$row['humidity']},
        "{$row['raw']}",
        {$row['temperature']},
        "{$row['time']}"
      )
      ON DUPLICATE KEY UPDATE
        `time` = "{$row['time']}"
______TMP;
    $mysqli->query($sql);      
  endif;
endforeach;  

Observations

1. JSON data parameters are all strings
2. Table fields are integers, floats and strings (more efficient?)
3. PHP update query has to convert the JSON string parameters to relevant field types
4. PHP table data is later be queried to produce SVG Graphs

Ponderism for today

Would it be better to change all table field types to strings and the update query to store the JSON string parameters .

[m_hutley]

Well i’m gonna start with an obvious one: Actually specify a key for your table. Time is not strictly unique.

The tuple of device_id and time should be.

As far as efficiency of table… are we talking about table size? speed? information value?

is there some place that stores the device data? Is that being joined to this table to make queries?

[John_Betong]

@m_hutley
Well i’m gonna start with an obvious one: Actually specify a key for your table. Time is not strictly unique.

The tuple of device_id and time should be.

The IOT LoraWan Device transmits a scheduled (minutes, hours or days) JSON packet to a LoraWan Gateway…

1. The LoraWan Gateway relays the JSON received LoraWan device data to https://TheThinksNetwork.org Server.
2. The JSON data is then transmitted to a designated server.
3. The time will be unique and also used as a SVG Chart coordinate.

@m_hutley
As far as efficiency of table… are we talking about table size? speed? information value?

The JSON parameters are strings and need to be converted at some stage to integers or floats for the SVG Chart. I was hoping to make the resultant SVG Chart render as soon as possible by minimising conversion bottlenecks.

@m_hutley
is there some place that stores the device data? Is that being joined to this table to make queries?

As far as I understand the LoraWan IOT Device only measures and transmits data to a LoraWan Gateway… where it is passed to a designated client server. I am in the process of gathering the data into a database table where it will be queried to produce SVG Charts. Also later to detect temperatures, etc outside a specified range and notify clients by email or SMS.

Further details:

The Things Network

I am having trouble with the jargon for this relatively new concept and learning slowly the LoraWan system’s benefits and pitfalls… it is not easy