Note: I wanted you to know that this post is written by one of our contributors, Shan Desai. Shan is a research scientist working for the Bremen Institute for Production and Logistics (BIBA). His work involves the use of IoT devices in order to improve product tracking and transparency in a B2B marketplace. You can find more details on Shan's personnal website.
Thanks a lot for your contribution Shan!
QuestDB is the fastest open-source Time-Series Database out there in terms of performance.
The developers were kind enough to welcome me into their community and I wanted to make things easier for people trying things out with QuestDB.
Lo! and behold Questitto an out-of-the-box repository for your initial IoT Applications. The repository is an altered version for my repository tiguitto which helps users deploy the highly used TIG+Mosquitto (Telegraf, InfluxDB, Grafana) + Mosquitto MQTT Broker stack in no time.
I am really looking forward to use some
SQL queries with Time-Series Databases
QuestDB provides such functionalities as well as some cool new features of
Not to mention, my staple InfluxDB's line Protocol is supported via sockets too!
questitto currently comes with basic user authentication support for Mosquitto
MQTT broker. The Broker allows only specific users to publish / subscribe data
hence reducing misuse. Telegraf writes the incoming data via subscribing to the
MQTT Broker and pushes the data to QuestDB.
In order to make it easy to deploy, the stack is deployable via
configuration is made simple via usage of text files (MQTT broker's users) and
an Environment File (for Telegraf)
Clone the repository:
Your Directory structure should look like:
Some brief information on the files:
mosquitto/config/passwd: file that has the usernames and passwords necessary for publishing/subscribing to the MQTT broker
questitto.env: environment variable file used by
telegrafcontainer to subscribe to the MQTT Broker for data ingestion
telegraf/telegraf.conf: TOML Configuration file for letting
telegrafdo the heavy lifting and inserting the data into QuestDB
User Management for Mosquitto MQTT Broker
In the repository there are two users added by default (see
You can use the
pubclient credential on your IoT Devices / MQTT Client to
publish information to the Broker. Similarly,
subclient credential will be
telegraf or any other user of the stack in order to subscribe to the
incoming data. Feel free to change the passwords for the usernames or add more
credentials according to your needs. The format for the credential entries is as
follows (in plain text):
Mosquitto Broker requires the the credentials to be encrypted and hence you bring the stack up with encrypting the passwords, the broker container will fail to start
Let's encrypt the passwords using the following command:
The command does not return anything hence, after executing the command check
mosquitto/config/passwd file using:
Input Data Format + MQTT Topic Design
For IoT Applications, let the higher components in the stack do the heavy-lifting i.e.
mosquittoand keep the payload and topics very simple
As an example the MQTT Topics are selected as follows:
if your IoT sensor publishes temperature data then you can publish it to a topic:
with the payload in InfluxDB line protocol string:
We then let
telegraf translate the location of
sensor1 for us using the
processors plugin and the MQTT topic itself.
telegraf subscribes to the MQTT Broker using the
If you change the user credentials, make sure to encrypt the password and change
questitto.env file with the actual credentials for
Let's look at how
telegraf can add our sensor's location for us.
We use the
inputs.mqtt_consumer plugin to connect to our broker and subscribe
to it via the credentials in the
we store the MQTT topic as a
topic and now leverage it for some
Regular Expression and Enumeration Magic as follows:
Based on our MQTT Topic design we know that the
SensorID will be on the second
We perform the Regular Expression to extract the sensor's ID and use
map it to its dedicated location:
The location will be stored as a
Data Insertion to QuestDB
will send the line protocol String to port 9009 of the
questdb container and
you don't even need to define a schema beforehand!
QuestDB comes with its own cool UI available on
Get the Stack up:
As a simple Example I used MQTT.fx as a client to publish information in line Protocol to the following Topic:
pubclient:questitto credentials and on the QuestDB UI you can see:
location and other
tags from the line protocol inserted:
A simple query where I would like to know the acceleration value in the
kitchen for the X-axis is as simple as:
If you need to add/remove or adapt the Users or the
bringing down the stack or the services within
questitto simply use the
SIGHUP signal for the containers.
See my blog post for a detailed write up.