IoT database route tips & tricks

Database Routes tips and tricks

Routes are integrations with the Scaleway ecosystem: they can forward MQTT messages to Scaleway services.

You can find the documentation here: IoT Hub Routes.

This page shows advanced usages of PostgreSQL and MySQL.

PostgreSQL Database advanced usage

This section shows two cases of data manipulation on the PostgreSQL side.

  1. The first one converts payloads to a float data type.
  2. The second one uses both JSON manipulation and stored plsql functions.

Prerequisites

This section is an addition to the How to use IoT Hub with scw cli tutorial. Make sure you follow the section Getting started with IoT Hub first!

Also, you must have a working PostgreSQL database, with valid credentials (username and password).

1 . For the rest of this tutorial, please setup the following environment variables:

# Database settings
DBENGINE=postgresql
DBHOST=<your db host>
DBPORT=<your db port>
DBNAME=<your db name>
DBUSER=<your db user>
DBPASS=<your db password>

Non-binary payloads example

As we are not limited to binary payloads, in this example we will use PostgreSQL functions to manipulate $TOPIC and $PAYLOAD placeholders.

Let’s say we have a temperature sensor publishing on the temperature/living-room topic. We also know that this sensor will only publish float values.

1 . Create a database table to store the temperature measurements:

# Create the target database table
psql -h $DBHOST --port $DBPORT -U $DBUSER -d $DBNAME -c '
  CREATE TABLE temperatures (
    time timestamp,
    room text,
    value double precision
  )
'

2 . Setup a database route to listen to the incoming messages, and persist them in the database:

# Create the IoT Hub Database Route
# The query
#   1. extract topic 2nd part (temperature/living-room -> living-room) stored in _room_ field,
#   2. converts the binary payload into a float stored in _value_ field
scw iot route create \
  hub-id=$(jq -r '.id' hub.json) \
  name=temperature \
  topic="temperature/+" \
  db-config.engine="$DBENGINE" \
  db-config.host="$DBHOST" \
  db-config.port=$DBPORT \
  db-config.dbname="$DBNAME" \
  db-config.username="$DBUSER" \
  db-config.password="$DBPASS" \
  db-config.query='INSERT INTO temperatures VALUES (NOW(), split_part($TOPIC, '"'/'"', 2), cast(encode($PAYLOAD, '"'escape'"') as double precision))'

We may now test our setup. We’re going to publish a message and then check that it was stored in the database.

3 . Publish a message:

sleep 5 # wait a little for the route to start
mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t 'temperature/bedroom' \
  -m '19.5'

4 . Check the result in the database:

psql -h $DBHOST --port $DBPORT -U $DBUSER -d $DBNAME -c "SELECT * FROM temperatures"

Advanced example with JSON and plsql functions

In the same manner, we can also manipulate JSON payload, call plsql functions, and take advantage of all the expressiveness that PosgreSQL offers.

The following example is logging building access attempts in non-working hours. Those are notified by badge readers, that publish a JSON payload on ‘access-control/office/’

1 . Run the following commands in a terminal on your computer:

# Create the target database table
psql -h $DBHOST --port $DBPORT -U $DBUSER -d $DBNAME -c '
  CREATE TABLE access_logs (
    time timestamp PRIMARY KEY,
    login varchar(128)
  );

  CREATE OR REPLACE FUNCTION nonworkinghours(at timestamp, login text) RETURNS void
  AS $$
  DECLARE
    hour INTEGER;
  BEGIN
    hour := EXTRACT(hour FROM at);
    IF hour <= 8 OR hour >= 22 THEN
      INSERT INTO access_logs VALUES (at, login);
    END IF;
  END;
  $$ LANGUAGE plpgsql;
'

# Create the IoT Hub Database Route
# NOTE: the query decodes the payload as json, then extract both 'timestamp' and 'user' values,
# and finally executes `nonworkinghours` function with timestamp and user values as parameters
scw iot route create \
  hub-id=$(jq -r '.id' hub.json) \
  name="non-working hours office access" \
  topic="access-control/office/#" \
  db-config.engine="$DBENGINE" \
  db-config.host="$DBHOST" \
  db-config.port=$DBPORT \
  db-config.dbname="$DBNAME" \
  db-config.username="$DBUSER" \
  db-config.password="$DBPASS" \
  db-config.query='SELECT nonworkinghours((encode($PAYLOAD, '"'escape'"')::json->>'"'timestamp'"')::timestamp, (encode($PAYLOAD, '"'escape'"')::json->>'"'user'"')::text)'

Try by publishing some access events

sleep 5 # wait a little for the route to start
mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t access-control/office/reader1 \
  -m '{
      "timestamp": "2019-10-11T11:21:12",
      "user": "jdoe",
      "door": "front",
      "access": "True"
    }'

mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t access-control/office/reader2 \
  -m '{
      "timestamp": "2019-10-11T04:10:44",
      "user": "thief",
      "door": "rear",
      "access": "False"
    }'

The first access occur during working hours so it should not be recorded, however the second one occur during the night and will be recorded.

psql -h $DBHOST --port $DBPORT -U $DBUSER -d $DBNAME -c "SELECT * FROM access_logs"

Should output:

        time         | login 
---------------------+-------
 2019-10-11 04:10:44 | thief
(1 row)

MySQL Database usage

This section presents similar examples using MySQL:

  1. The first one is a basic example of an IoT route with MySQL
  2. The second one converts payloads to a float data type.
  3. The third one uses both JSON manipulation and MySQL stored functions.

Requirements

This section is an addition to the How to use IoT Hub with scw cli tutorial. Make sure you follow the section Getting started with IoT Hub first!

Also, you must have a working MySQL database, with valid credentials (username and password). You can use a Scaleway Database instance, or any other MySQL instance publicly accessible.

1 . For the rest of this tutorial, please setup the following environment variables:

# Database settings
DBENGINE=mysql
DBHOST=<your db host>
DBPORT=<your db port>
DBNAME=<your db name>
DBUSER=<your db user>
DBPASS=<your db password>

Basic MySQL example

This is a port of the Database Route section of the cli tutorial to MySQL.

NOTE: The topic database field must be a of text type, and payload can be of text or blob type depending on your usage.

1 . Run the following commands in a terminal on your computer:

# Create the target database table
mysql -h $DBHOST --port $DBPORT -p -u $DBUSER -D $DBNAME <<EOF
  CREATE TABLE messages (
    time timestamp,
    topic text,
    payload blob
  ) CHARACTER SET utf8 COLLATE utf8_bin;
EOF

# Create the IoT Hub Database Route
# The query will insert message topic and payload with current timestamp
scw iot route create \
  hub-id=$(jq -r '.id' hub.json) \
  topic="hello/world" \
  db-config.engine="$DBENGINE" \
  db-config.host="$DBHOST" \
  db-config.port=$DBPORT \
  db-config.dbname="$DBNAME" \
  db-config.username="$DBUSER" \
  db-config.password="$DBPASS" \
  db-config.query='INSERT INTO messages VALUES (NOW(), $TOPIC, $PAYLOAD)'

The output will contain something like

ID                 2251e2b1-c616-4a7e-9e72-b658da656424
Name               cli-route-db-tutorial
HubID              b20c3639-9030-496c-a1b2-6feb15846726
Topic              hello/world
Type               database
CreatedAt          now
DbConfig.Engine    mysql
DbConfig.Host      127.0.0.1
DbConfig.Port      5432
DbConfig.Dbname    route_tests
DbConfig.Username  jdoe
DbConfig.Password  <your_pass>
DbConfig.Query     INSERT INTO messages VALUES (NOW(), $TOPIC, $PAYLOAD)
UpdatedAt          now

2 . Publish a message and check whether it is inserted into the message table.

sleep 5 # wait a little for the route to start
mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t hello/world \
  -m 'Hello, world!'
mysql -h $DBHOST --port $DBPORT -p -u $DBUSER -D $DBNAME <<EOF
SELECT * FROM messages;
EOF

Non-binary payloads example

As we are not limited to binary payloads, in this example we will use MySQL functions to manipulate $TOPIC and $PAYLOAD placeholders.

Let’s say we have a temperature sensor publishing on the temperature/living-room topic. We also know that this sensor will only publish float values.

1 . Create a database table to store the temperature measurements:

# Create the target database table
mysql -h $DBHOST --port $DBPORT -p -u $DBUSER -D $DBNAME <<EOF
  CREATE TABLE temperatures (
    time timestamp,
    room text,
    value double precision
  );
EOF

2 . Setup a database route to listen to the incoming messages, and persist them in the database:

# Create the IoT Hub Database Route
# The query
#   1. extract topic 2nd part (temperature/living-room -> living-room) stored in _room_ field,
#   2. implicit converts the binary payload into a double stored in _value_ field
scw iot route create \
  hub-id=$(jq -r '.id' hub.json) \
  topic="temperature/+" \
  db-config.engine="$DBENGINE" \
  db-config.host="$DBHOST" \
  db-config.port=$DBPORT \
  db-config.dbname="$DBNAME" \
  db-config.username="$DBUSER" \
  db-config.password="$DBPASS" \
  db-config.query='INSERT INTO temperatures VALUES (NOW(), SUBSTRING_INDEX($TOPIC, '"'/'"', -1), $PAYLOAD)'

We may now test our setup. We’re going to publish a message and then check that it was stored in the database.

3 . Publish a message:

sleep 5 # wait a little for the route to start
mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t 'temperature/bedroom' \
  -m '19.5'

4 . Check the result in the database:

mysql -h $DBHOST --port $DBPORT -p -u $DBUSER -D $DBNAME <<EOF
SELECT * FROM temperatures;
EOF

Advanced example with JSON and stored functions

In the same manner, we can also manipulate JSON payload, call stored functions, and take advantage of all the expressiveness that MySQL offers.

The following example is logging building access attempts in non-working hours. Those are notified by badge readers, that publish a JSON payload on ‘access-control/office/’

1 . Run the following commands in a terminal on your computer:

# Create the target database table
mysql -h $DBHOST --port $DBPORT -p -u $DBUSER -D $DBNAME <<EOF
  CREATE TABLE access_logs (
    time timestamp PRIMARY KEY,
    login varchar(128)
  );

  DELIMITER $$
  CREATE PROCEDURE nonworkinghours(at timestamp, login text)
  BEGIN
    DECLARE hour INTEGER;
    SET hour = HOUR(at);
    IF hour <= 8 OR hour >= 22 THEN
      INSERT INTO access_logs VALUES (at, login);
    END IF;
  END$$
  DELIMITER ;
EOF


# Create the IoT Hub Database Route
# NOTE: the query decodes the payload as json, then extract both 'timestamp' and 'user' values,
# and finally executes `nonworkinghours` procedure with timestamp and user values as parameters
scw iot route create \
  hub-id=$(jq -r '.id' hub.json) \
  topic="access-control/office/#" \
  db-config.engine="$DBENGINE" \
  db-config.host="$DBHOST" \
  db-config.port=$DBPORT \
  db-config.dbname="$DBNAME" \
  db-config.username="$DBUSER" \
  db-config.password="$DBPASS" \
  db-config.query='CALL nonworkinghours(JSON_EXTRACT($PAYLOAD, '"'$.timestamp'"'), JSON_EXTRACT($PAYLOAD, '"'$.user'"'))'

Try by publishing some access events

sleep 5 # wait a little for the route to start
mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t access-control/office/reader1 \
  -m '{
      "timestamp": "2021-10-11T11:21:12",
      "user": "jdoe",
      "door": "front",
      "access": "True"
    }'

mosquitto_pub \
  -h $(jq -r '.endpoint' hub.json) \
  -i $(jq -r '.device.id' dev2.json) \
  -t access-control/office/reader2 \
  -m '{
      "timestamp": "2021-10-11T04:10:44",
      "user": "thief",
      "door": "rear",
      "access": "False"
    }'

The first access occur during working hours so it should not be recorded, however the second one occur during the night and will be recorded.

mysql -h $DBHOST --port $DBPORT -p -u $DBUSER -D $DBNAME <<EOF
SELECT * FROM access_logs;
EOF

Should output:

time	login
2021-10-11 04:10:44	"thief"

Learn more about Scaleway IoT Hub, discover how to add Devices to the hub, check the IoT Hub metrics or get started in a few clicks with the IoT Hub Kickstarts.

Discover the Cloud That Makes Sense