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 present two case of data manipulation on the PostgreSQL side.

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

Prerequisites

This section is an addition to the How to use IoT API with curl tutorial, make sure you follow the section Getting started with IoT Hub first!

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

# Database settings
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.

First, we need a database table to store the temperature measurements. Here is how to create a table for this purpose:

# 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
  )
'

Second, we need to 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
curl -sS -H "X-Auth-Token: $SCW_TOKEN" -d '{
    "name": "temperature",
    "hub_id": "'$(jq -r '.id' hub.json)'",
    "topic": "temperature/+",
    "query": "INSERT INTO temperatures VALUES (NOW(), split_part($TOPIC, '"'/'"', 2), cast(encode($PAYLOAD, '"'escape'"') as double precision))",
    "database": {
      "host": "'$DBHOST'",
      "port": '$DBPORT',
      "dbname": "'$DBNAME'",
      "username": "'$DBUSER'",
      "password": "'$DBPASS'"
    }
  }' $IOT_API/routes/database | jq

Finally, we can test our setup. We’re going to publish a message and then check that we received it in the database:

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'

Let’s 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/’

# 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
curl -sS -H "X-Auth-Token: $SCW_TOKEN" -d '{
    "name": "non-working hours office access",
    "hub_id": "'$(jq -r '.id' hub.json)'",
    "topic": "access-control/office/#",
    "query": "SELECT nonworkinghours((encode($PAYLOAD, '"'escape'"')::json->>'"'timestamp'"')::timestamp, (encode($PAYLOAD, '"'escape'"')::json->>'"'user'"')::text)",
    "database": {
      "host": "'$DBHOST'",
      "port": '$DBPORT',
      "dbname": "'$DBNAME'",
      "username": "'$DBUSER'",
      "password": "'$DBPASS'"
    }
  }' $IOT_API/routes/database | jq

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)

Discover the Cloud That Makes Sense