Serial a SQL

En un articulo anterior había comentado como logré tomar los datos que transmite un Arduino (provenientes de un sensor) y guardarlos en una hoja de calculo. En esta oportunidad quiero comentarles algo que esta en la misma dirección, en lugar de guardar en una hoja de calculo, llevarlos a una base de datos.

Para cumplir esto desarrolle una sencilla herramienta de terminal, que la pueden encontrar:

https://github.com/gsampallo/serial2sql

Luego de descargar la herramienta, el primer punto es tener acceso a un motor de base de datos; en mi caso utilice MySQL, puesto que es la más común y fácil de implementar. Debemos tener disponible los datos de las credenciales y permisos sobre una base de datos, luego el script se ocupara de crear una tabla e insertar en ella los datos. También existe la opción de no guardar los datos en la base al momento de recibirlos sino guardarlos en un archivo sql para luego insertarlos donde necesitemos. Está en las necesidades puntuales de cada uno ver cual es el uso optimo para cada aplicación.

Sera necesario proveerle un mínimo de información al script para que este realizar las acciones, para ello se utiliza una archivo config.json con la siguiente estructura:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
    "port":"/dev/ttyUSB0",
    "baudrate": 115200,
    "credentials" : {
    "host":"host",
    "database":"databaseName",
    "user":"userName",
    "password":"password",
    "raise_on_warnings": "True"
    },
    "tableName": "tableName",
    "fields": [
        { "name":"indice","type":"INT(10)" },
        { "name":"valor","type":"INT(10)" }
    ]
}

Se debe editar el archivo para que sea congruente con los datos de nuestra instalación.

Basicamente contiene la información de los datos de conexion del puerto serie: puerto y velocidad que se van a emplear para conectarse. Asi como tambien el nombre de la tabla (tableName) que utilizara para almacenar los datos, a continuación tenemos fields, que es una lista que contiene el nombre de cada campo junto con el tipo de dato que almacenara.

Un punto a tener en cuenta, es que el tipo de datos empleado es SQL, por lo que es sencillo definirlo; en este caso utilizamos los tipos de MySQL. En el json de ejemplo solo empleamos dos campos indice y valor, ambos enteros; pero esto podemos establecerlo según las necesidades puntuales. Siempre sabiendo de antemano cuales serán los datos que Arduino transmitirá por el puerto serie.

Si por ejemplo Arduino transmitiera cuatro campos: A,B,C,D donde A,B y C son enteros y D es una cadena de texto, deberíamos especificar el segmento fields de la siguiente manera:


1
2
3
4
5
6
    "fields": [
        { "name":"A","type":"INT(10)" },
        { "name":"B","type":"INT(10)" },
        { "name":"C","type":"INT(10)" },
        { "name":"D","type":"VARCHAR(80)" }
    ]

La longitud dependerá de lo que almacenemos.

Luego de establecido el archivo config.json simplemente basta ejecutarlo de la siguiente manera:

python serial2sql.py

Si estuviéramos en linux debemos anteponer el comando sudo para tener permisos sobre el puerto.

Adicionalmente también existe la posibilidad de guardar los datos a un archivo sql, de forma de poder insertarlos luego, para hacerlo basta especificar el parametro -o y el nombre del archivo:

python serial2sql.py -o output.sql

Otra variante interesante es utilizar la extensión csv en lugar sql, los datos de serán almacenados en formato csv:

python serial2sql.py -o output.csv

Especificando estas dos opciones no se guardara en la base de datos, ni se creara la tabla, solo se guardara el archivo.

Raspberry Pi: MQTT Bridge

La historia es la siguiente: tengo varios dispositivos en casa que están conectados a un servidor broker en la nube, cuando por algún motivo no hay conexión a internet quedan totalmente offline y no se los puede controlar.

Una posible solución a esto, es utilizar de manera local una raspberry pi como servidor broker, que los dispositivos se conecten a ella y de esa forma controlar los eventos y las acciones que se produzcan en ellos. La ventaja es que eliminamos la necesidad de la conexión a un servidor externo, la contra es que perdemos la posibilidad de controlarlos de manera remota. Si no estoy conectado al WiFi de mi casa, no los puedo activar o recibir datos de los mismos.

La solución que quiero probar es realizar un punto intermedio, en donde los dispositivos se conecten a la raspberry pi dentro de la red local; la raspberry controle todos los eventos relacionados con los mismos; pero a la vez está se encuentre conectado (suscripta si utilizamos el termino correcto) a un servidor broker en la nube; de esta forma mantenemos el funcionamiento de los equipos en caso que se corte internet (las ordenes las tenemos que dar a la raspberry y ya no al servidor en la nube) y podemos darle instrucciones a los dispositivos desde cualquier lugar.

¿Como arrancamos?

Lo primero va a ser instalando Raspbian en la Raspberry Pi,, no voy a entrar en detalles puesto que existen numerosos tutoriales online donde se explican paso por paso.

Como segundo paso es instalar Mosquitto:


1
sudo apt install -y mosquitto mosquitto-clients

Luego para que se ejecute como servicio:


1
sudo systemctl enable mosquitto.service

No voy a entrar en el detalle de como securizar Mosquitto, en este punto solo necesitamos que se ejecute en la Raspberry Pi.

MQTT Bridge

MQTT Bridge es el termino utilizado para esta metodología que les comentaba; de esta forma se logra que por medio de la configuración de uno de los brokers (el que esta dentro de nuestra red lan) lograr nuestro objetivo.

Debemos modificar el archivo de configuración de Mosquitto en la raspberry pi, ubicado en /etc/mosquitto/mosquitto.conf


1
2
3
4
5
#Connection BrokerLan
connection bridge-01
address 198.41.30.241:1883

topic DEMO/LUCES both 0 "" ""

La primer linea indica el nombre de la conexion, luego especificamos cual es el servidor broker al que desemos conectarnos y que puerto.

En el ejemplo utilizamos la dirección IP del broker publico de Eclipse: iot.eclipse.org

Luego debemos indicar cuales son los tópicos sobre los cuales deseamos realizar el puente; en nuestro caso utilizaremos uno llamado DEMO/LUCES; indicamos el sentido: both, es decir que seria bidireccional tanto si lo cambiamos de manera local como remota, se publicara el mensaje en ambos brokers. También podemos indicar si es in o out. Luego indicamos el valor de QOS. Finalmente podemos remapear el topico a otro dependiendo de lo que busquemos; en nuestro caso dejamos «» para mantener el mismo.

Guardamos el archivo, y luego debemos reiniciar Mosquitto para que tome los cambios. Aquí podríamos darnos por hecho, configurando los dispositivos en nuestro hogar para que se conecten a la Raspberry Pi en lugar del servidor en la nube, podríamos tener todo funcionando.

¿Como lo probamos?

Quienes tengan experiencia utilizando node-red o configurando dispositivos con servicios broker podrian omitir esta prueba y simplemente configurarlos. Vemos un sencillo ejemplo, aunque algo extenso, de como trabaja Mosquitto en modo Bridge.

Utilizaremos Node-RED para publicar los mensajes en el servidor broker de la nube, los cuales serán retransmitidos por el servidor Mosquitto de la RPI a un nodemcu conectado en nuestra lan.

Para verificar el funcionamiento utilizaremos Node-Red en la misma Raspberry, para iniciarlo tenemos que ir al menu Inicio – Programming – Node-RED

Se abrira una terminal, y luego de que cargue los componentes podremos conectarnos a Node-Red; hay que tener en cuenta que el servicio solo esta disponible mientras que tengamos prendida la Raspberry Pi, si la apagamos debemos volver a iniciarlo. Para iniciarlo como servicio debemos ejecutar desde la terminal:


1
sudo systemctl enable nodered.service

Una vez que esta corriendo, abrimos un navegador y nos conectamos a la ip de la raspberry mas el puerto por defecto de Node-Red 1880.

Desde el menú de la izquierda, dentro del apartador Input, elegimos el item inject y lo arrastramos al área de trabajo:

Lo configuramos de la siguiente manera:


De la misma forma, agregamos un segundo nodo Inject pero lo configuramos con el valor de false y el nombre off.

Luego buscamos en el menu de la izquierda nuevamente, dentro del apartado output, el node llamada mqtt, lo conectamos a ambos nodos y lo configuramos para que se conecte al servidor publico de eclipse:


En las propiedades del nodo, indicamos que el topico que vamos a utilizar es DEMO/LUCES

Por ultimo desde el apartado de input buscamos el nodo mqtt, lo agregamos al area de trabajo, luego buscamos en el apartado de output el nodo debug y lo conectamos al mismo. Al nodo de mqtt lo configuramos para que se conecte al servidor broker local (la propia raspberry) y utilice le tópico DEMO/LUCES (de la misma manera que lo hicimos en el ejemplo anterior).

Va a quedar armado algo asi:

Para quienes quieran avanzar mas rapido y no tener que agregar nodo por nodo, puede importar todo el flujo de datos desde le Menu Import – Clipboard:


1
[{"id":"2f15244e.d6d11c","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"c199590a.8f9278","type":"inject","z":"2f15244e.d6d11c","name":"on","topic":"DEMO/LUCES","payload":"true","payloadType":"bool","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":80,"wires":[["6cb4d45a.3bc25c"]]},{"id":"b19ada37.dccef8","type":"inject","z":"2f15244e.d6d11c","name":"off","topic":"DEMO/LUCES","payload":"false","payloadType":"bool","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":140,"wires":[["6cb4d45a.3bc25c"]]},{"id":"6cb4d45a.3bc25c","type":"mqtt out","z":"2f15244e.d6d11c","name":"","topic":"DEMO/LUCES","qos":"","retain":"","broker":"bb0be034.08a1b","x":410,"y":120,"wires":[]},{"id":"17be2189.41824e","type":"mqtt in","z":"2f15244e.d6d11c","name":"","topic":"DEMO/LUCES","qos":"2","datatype":"auto","broker":"7e629599.3fb36c","x":140,"y":240,"wires":[["b96b0d7a.27869"]]},{"id":"b96b0d7a.27869","type":"debug","z":"2f15244e.d6d11c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":420,"y":240,"wires":[]},{"id":"bb0be034.08a1b","type":"mqtt-broker","z":"","name":"eclipse","broker":"iot.eclipse.org","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"7e629599.3fb36c","type":"mqtt-broker","z":"","name":"localhost","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

En este punto, si tocamos cualquiera de los nodos para inyectar datos (on/off) veremos en el panel de debug los datos recibidos en nuestro broker local, como se refleja los datos que publicamos en el servidor publico.

Utilizaremos un nodemcu con MicroPython, que solo tendrá conectado un led:

y el siguiente programa corriendo:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import wlan
from machine import Pin
import time
from umqtt.simple import MQTTClient

wlan.do_connect()

led = Pin(4,Pin.OUT,0)

def encender(topico,mensaje):
    print(mensaje)
    if(mensaje == b'true'):
        led.on()
    if(mensaje == b'false'):
        led.off()

cliente = MQTTClient('micropython/cliente1','RASPBERRY_IP')
cliente.set_callback(encender)
cliente.connect()
cliente.subscribe(b"DEMO/LUCES")

while True:
    cliente.check_msg()
    time.sleep_ms(100)

Hay que recordar cambiar ‘RASPBERRY_IP’ por la dirección IP de la raspberry. También debemos agregar el archivo wlan para conectarse a nuestra red wifi:


1
2
3
4
5
6
7
8
9
10
def do_connect():
    import network
    wlan = network.WLAN(network.STA_IF)
    wlan.active(True)
    if not wlan.isconnected():
        print('connecting to network...')
        wlan.connect('essid', 'password')
        while not wlan.isconnected():
            pass
    print('network config:', wlan.ifconfig())

Reemplazando essid y password por los correctos.

Luego que tengamos corriendo el programa en nuestro nodemcu, al inyectar los datos en el servidor broker publico veremos como el led se enciende o apaga dependiendo lo que ingresemos.

De esta forma podemos utilizar la Raspberry Pi como Bridge entre los dispositivos locales y un servidor en la nube.