Consuming Payload Data with Python

Home Labs Stories Consuming Payload Data with Python

by Shane Hull

ABOUT THIS STORY

Posted on Oct. 11, 2021

#Community #MQTT #Grafana #Python #Database #Visualization #Sensor #Container #PostgreSQL #Docker #Microservice

Intermediate

1 Hour

1 person likes this

Contact author Discuss on forum

Introduction

In this lab, we'll use Python to subscribe to The Things Stack v3 (via MQTT) and write the payload values to a PostgreSQL database.

It is assume that you have a working application and devices in your console.

The lab will revolve around a configurable script that I've written, available on GitLab here. It uses Paho MQTT client for Python to subscribe to MQTT and SQLAlchemy to write to the DB.
You should clone this to your machine and follow the configuration instructions in the next sections to get started.

The script handles multiple devices and is configured via a YAML file, passed into it via a CLI flag.

It subscribes to TTSv3 via MQTT and creates a table per device, then writes the payload data as it comes in.

It provides support for the TimescaleDB, an extension built to optimise PostgreSQL for time-series data.

The data flow will look like the diagram below.

Data Flow

And the tables for your devices will look a little something like:

ERD Representation

Run with Docker

See the example Dockerfile, which uses the official Python3 image to dockerize the script.

Here is what it looks like:

FROM python:3.9

ARG TZ=Australia/Melbourne

WORKDIR /app

COPY ttn2postgresql.py .

COPY config.yaml .

COPY requirements.txt .

RUN pip3 install -r requirements.txt

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

CMD ["ttn2postgresql.py", "-l", "INFO"]

ENTRYPOINT ["python"]

A docker-compose example of running ttn2postgresql with a TimescaleDB container is also available in the docker-compose.yaml file.

version: "3.9"

services:
  ttn2postgresql:
    build:
      context: .
      args:
        TZ: Australia/Melbourne
    networks:
      ttn2postgresql_network:
    depends_on:
        timescale:
          condition: service_healthy
    deploy:
      restart_policy:
        condition: on-failure

  timescale:
    image: timescale/timescaledb:latest-pg13
    volumes:
      - type: volume
        source: ttn2postgresql_volume
        target: /var/lib/postgresql/data
        read_only: false
    environment:
      POSTGRES_HOST: timescale
      POSTGRES_DB: ttn
      POSTGRES_USER: ttn
      POSTGRES_PASSWORD: secure-password!
      TZ: Australia/Melbourne
    ports:
      - 5432:5432
    networks:
      ttn2postgresql_network:
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U ttn']
      interval: 10s
      timeout: 10s
      retries: 3
    deploy:
      restart_policy:
        condition: on-failure

volumes:
  ttn2postgresql_volume:
networks:
  ttn2postgresql_network:

To easily visualise the data, just add Grafana to the mix.

version: "3.9"

services:
  ttn2postgresql:
    build:
      context: .
      args:
        TZ: Australia/Melbourne
    networks:
      ttn2postgresql_network:
    depends_on:
        timescale:
          condition: service_healthy
    deploy:
      restart_policy:
        condition: on-failure

  timescale:
    image: timescale/timescaledb:latest-pg13
    volumes:
      - type: volume
        source: ttn2postgresql_volume
        target: /var/lib/postgresql/data
        read_only: false
    environment:
      POSTGRES_HOST: timescale
      POSTGRES_DB: ttn
      POSTGRES_USER: ttn
      POSTGRES_PASSWORD: secure-password!
      TZ: Australia/Melbourne
    ports:
      - 5432:5432
    networks:
      ttn2postgresql_network:
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U ttn']
      interval: 10s
      timeout: 10s
      retries: 3
    deploy:
      restart_policy:
        condition: on-failure

  grafana:
    image: grafana/grafana:8.2.0
    ports:
      - 3000:3000
    networks:
      ttn2postgresql_network:
      
volumes:
  ttn2postgresql_volume:
networks:
  ttn2postgresql_network: