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.

The documentation is there: 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 the example, we will use PostgreSQL functions to manipulate $TOPIC and $PAYLOAD placeholders.

Let’s say we have a temperature sensor publishing on ‘temperature/living-room’ topic. We also this sensor will publish payloads that are always float values.

We can setup the following configuration to store it into your database:

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

# 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

Try by publishing a sample temperature notification

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 base

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, …

The following example is logging building access attemps 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 a New Cloud Experience

Deploy SSD Cloud Servers in seconds.