Guardar valores recibidos por mqtt en una base de datos sqlite (Linux)

Objetivo: Vamos a realizar un script sencillo para almacenar los datos que se van recibiendo cuando estamos conectados a un broker mqtt en una base de datos sqlite.

La ventaja de almacenarlo en una base de datos es que podremos realizar consultas sql del tipo:

  • Dame los valores de las últimas 24 horas.
  • Dame el valor medio de todo un día.
  • Dame el valor máximo y mínimo de los valores almacenados.

Otra ventaja es que los datos están un un solo fichero que podremos mover de un lado a otro fácilmente.

mqtt-sqlite-1

Requisitos:

Tener instalado mosquitto en Linux:

En caso de Ubuntu, linux Mint y Raspberry Pi:

sudo apt-get update ; sudo apt-get install mosquitto mosquitto-clients

Para Openwrt:

opkg-update ; opkg install mosquitto mosquitto-client libmosquitto

Instalación de Sqlite en ubuntu, Mint:

sudo apt-get update; sudo apt-get install sqlite3 libsqlite3-dev

Instalación en Openwrt:

opkg update; opkg install libsqlite3 sqlite3-cli

Creación de la base de datos

Lo primero que haremos es crear la base de datos en nuestro home de Linux y dentro una tabla que almacene los valores:

/usr/bin/sqlite3 $HOME/BASEDATOS.DB "CREATE TABLE valores (fechahora text,valor1 real);";

Hemos creado una tabla llamada valores, en la que cada campo almacenará una fecha y hora y un valor1 que hemos decidido que sea de tipo real.

Debes adecuar tu tabla al tipo de datos que quieres almacenar. Consulta la siguiente página para ver los diferentes tipos de datos en sqlite.

https://www.sqlite.org/datatype3.html

Vamos con el Script

El script será el siguiente y lo llamaremos guarda-sqlite.sh:

!/bin/bash
# Con openwrt debemos cambiar lo anterior por #!/bin/sh
# Configuración del broker y topic a escuchar.
# Cambiar por valores propios las siguientes variables:
broker="test.mosquitto.org" 
port="1883"
topic="temperature/floor1"
basedatos="$HOME/BASEDATOS.DB"
#------------------------------------
#Ponemos el cliente de mosquitto escuchando
mosquitto_sub -t $topic -h $broker -p $port | while read value; do
   # Guardamos la fecha y hora actual en una variable.
   hora=$( date "+%Y-%m-%d %H:%M:%S" );
   # Insertamos el valor leido en la base de datos
   /usr/bin/sqlite3 $basedatos "INSERT INTO valores values ('$hora',$value)";
done

Guardamos y le damos permisos de ejecución:

chmod +x guarda-sqlite.sh

Ejecutamos el script:

./guarda-sqlite.sh

Testear el script

Para probar este script vamos a utilizar un broker público para realizar pruebas que es test.mosquitto.org. En este broker hay muchas personas realizando pruebas por lo que podemos utilizarlo para guardar lo que otros están enviando.

Vamos a suscribirnos a dicho broker por línea de comandos con la orden:

mosquitto_sub -h test.mosquitto.org -t "#" -v

Nos devolverá un montón de valores ya que estamos viendo en tiempo real todo lo que la gente está publicando. Pulsaremos CTRL+C para pararlo.

A continuación vemos una porción de lo que me ha devuelto a mi en color rojo vemos el topic utilizado por otros dispositivos:

...
ssjh/sensor/4/device/11/reading 289
ssjh/sensor/4/reading 289
ssjh/readings/device/11/sensor/4/reading 289
ssjh/readings/sensor/4/device/11/reading 289
mqtt/feedback/esp/builtin/led 0
temperature/board 96.44
Light/Level 765
temperature/soil 75.56
revspace/sensors/temperature/2/2 22.25 °C
smartflat/arduino/1/room/1/temperature/status 22

...

A continuación probamos a ver sólo lo que se envía en un topic. En nuestro caso vamos a hacerlo con uno de ellos que es temperature/board (Atención los topics distinguen entre minúsculas y mayúsculas).

mosquitto_sub -h test.mosquitto.org -t "temperature/board" -v

me devuelve:

temperature/board 96.98
temperature/board 97.7
temperature/board 96.98
temperature/board 96.98
temperature/board 96.98
temperature/board 96.98
...

Con esto he comprobado que se actualiza regularmente. En mi caso aproximadamente cada segundo.

Edito el script y cambio el topic por el que me interesa y lo pruebo.

Lo ejecuto y lo dejo un rato funcionando.

Consultas en la base de datos

Para ver todos los registros almacenados en la base de datos ejecuto los siguiente:

/usr/bin/sqlite3 BASEDATOS.DB "select * from valores"

Si quiero ver la media de todos los valores:

/usr/bin/sqlite3 BASEDATOS.DB "select avg(valor1) from valores"

Si quiero ver el valor máximo y el mínimo de todos los valores:

/usr/bin/sqlite3 BASEDATOS.DB "select max(valor1),min(valor1) from valores"

Si quiero ver los valores almacenados entre dos fechas-horas:

/usr/bin/sqlite3 BASEDATOS.DB "select * from valores where fechahora between '2016-07-06 00:46:00' and '2016-07-06 00:47:00'"

Si quiero ver el valor máximo y el mínimo de todos los valores entre dos horas:

/usr/bin/sqlite3 BASEDATOS.DB "select max(valor1),min(valor1) from valores where fechahora between '2016-07-06 13:00:00' and '2016-07-06 14:00:00'"

Podemos hacer las consultas SQL que deseemos.

Volcar los datos a un fichero de texto CSV

Puede interesarnos volcar los datos a un fichero separado por comas para trabajarlo con excel. Vamos pues a generar un archivo .CSV con todos los valores almacenados.

Ejecutamos lo siguiente:

/usr/bin/sqlite3 BASEDATOS.DB
...
Activaremos las cabeceras para que nos las almacene dentro del CSV
sqlite> .header on
sqlite> .mode csv
A continuación introduciremos la ruta donde queremos almacenar los valores
sqlite> .output /home/miusuario/valores.csv
Realizamos la consulta con todo lo que tiene
sqlite> select * from valores;
Salimos de sqlite.
sqlite> .exit

Para más información visitar el siguiente enlace:

https://www.sqlite.org/cli.html

 

3 comentarios en “Guardar valores recibidos por mqtt en una base de datos sqlite (Linux)

  1. Hola qué tal! Primero que todo muy buen post!! Me ha servido muchísimo! Gracias.
    Una pregunta, si requiero de guardar varios datos enviados por varios sensores, cómo sería el script? se pueden concatenar los valores en la parte del while así? :
    mosquitto_sub -t $topic -h $broker -p $port | while read gardenLight, backLight, backTemp, laundryTemp, garageTemp, gardenTemp, gardenHumidity do
    # Guardamos la fecha y hora actual en una variable.
    [….]

    Gracias nuevamente! (:

    1. Sí se puede. Pero no de esa forma que tú comentas.
      Lo ideal es que todos los sensores envíen hacia el mismo servidor en diferentes topics pero que cuelguen de una rama.
      Podría ser de la siguiente forma:
      Cada sensor publicará en
      /mysensors/gardenLight
      /mysensors/backLight
      /mysensors/backTemp
      /mysensors/laundryTemp
      /mysensors/garageTemp
      /mysensors/gardenTemp
      /mysensors/gardenHumidity

      mosquitto_sub te permite ver todo lo que llega de la rama /mysensors

      Te pongo un ejemplo del servidor mosquitto.org

      mosquitto_sub -h test.mosquitto.org -t "bbc/subtitles/#" -v

      Verás los subtítulos de la bbc en tiempo real pero ves lo que cambia lo que hay a partir de bbc/subtitles/ .

      Para recibir todo con una línea harías lo siguiente:

      mosquitto_sub -h test.mosquitto.org -t "/mysensors/#" -v

      Irás recibiendo valores tales como:

      /mysensors/gardenLight ON
      /mysensors/backLight OFF
      /mysensors/backTemp 26,4
      /mysensors/laundryTemp 28,6

      Entonces habría que cambiar lo siguiente:

      Paso 1 – La base de datos debe estar preparada para distinguir entre varios sensores
      La creas con este comando:

      /usr/bin/sqlite3 $HOME/BASEDATOS.DB "CREATE TABLE valores (sensor text,fechahora text,valor1 text);";

      (nota) Al recibir diferentes tipos de datos , es mejor almacenarlo tal como vienen, o sea, tipo texto.

      Paso 2 – Modificamos el script que quedará así:

      #!/bin/bash
      # Con openwrt debemos cambiar lo anterior por #!/bin/sh
      # Configuración del broker y topic a escuchar.
      # Cambiar por valores propios las siguientes variables:
      broker="test.mosquitto.org"
      port="1883"
      topic="/mysensors/#"
      basedatos="$HOME/BASEDATOS.DB"
      #------------------------------------
      #Ponemos el cliente de mosquitto escuchando
      mosquitto_sub -t $topic -h $broker -p $port -v | while read value; do
      senval=$(echo "$value" | awk -F '/' {'print $3'})
      sensor=$(echo "$senval" | awk {'print $1'})
      valor=$(echo "$senval" | awk {'print $2'})
      # La siguiente línea muestra por pantalla los valores recibidos.
      echo "$sensor-->$valor"

      # Guardamos la fecha y hora actual en una variable.
      hora=$( date "+%Y-%m-%d %H:%M:%S" );
      # Insertamos el valor leido en la base de datos
      /usr/bin/sqlite3 $basedatos "INSERT INTO valores values ('$sensor','$hora','$valor')";
      done

      Con este script, para enviar los valores tienes que hacerlo siempre a /mysensors/tipo_sensor. Si necesitaras aumentar un nivel en el topic, como por ejemplo: /mysensors/floor1/backTemp debes variar el valor $3 por $4.

      Para ver lo que se ha ido guardando haces:

      sqlite3 $HOME/BASEDATOS.DB "select * from valores;";

      Para borrar los valores:

      sqlite3 $HOME/BASEDATOS.DB "delete from valores;";

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *