
note
The UDP receiver is deprecated since QuestDB version 6.5.2. We recommend the TCP receiver introduced by this article.
We've had a UDP version of the InfluxDB Line Protocol (ILP) reader in QuestDB for quite some time, but we've had customers ask for a TCP version of it, so we delivered!
Using and configuring it are relatively simple, so this tutorial will walk you through the basics of setting it up and using it. For an added bonus, we'll see how to migrate from using InfluxDB to using QuestDB with a single-line configuration change.
#
Configuring TCP InfluxDB line protocol listenerHere's the best part, at least for a basic implementation that you don't need to performance tune at all: It's already set up.
That's right, as soon as you start QuestDB, both the UDP and TCP ILP listeners
start automatically on port 9009
. Yes, TCP and UDP both use the same port. No,
that's not a problem since one is UDP and one is TCP.
There are many configuration options you can tune in your conf/server.conf
file if you're interested. I won't go through them here, but you can read all
about them in our docs. I hope they are
relatively self-explanatory.
#
InfluxDB line protocol refresherIf you have used ILP before, this should all be review. If you're new to ILP, this will tell you how you should write your data to QuestDB.
#
Basic structurePretty basic. So let's dive into what each element actually is and how to structure a line of ILP for writing.
The first element is the table_name
portion, which tells the ILP writer which
database table to write values into.
Next comes the set of tags you want to use. These are standard key=value
pairs, and you can add as many of them as you want or need. Just separate them
with commas.
There should only ever be 2 spaces in your line protocol. No more. The first
space separates your tags
from the values
you want to associate with those
tags
s. The second space separates the values from the timestamp for those tags
and values.
The values are also key=value
pairs, and again you can send as many as you
want in a line.
Finally comes your timestamp
value, typically in µSeconds.
#
Example ILPLet's use an example of writing some environmental data to QuestDB. I have a sensor that reads the temperature, atmospheric pressure, humidity, and altitude.
Reading | Value |
---|---|
Temperature | 23.180000 |
Humidity | 51.982422 |
Pressure | 1002.112061 |
Altitude | 93.146370 |
And I want to use the following tag
s:
Tag Name | Tag Value |
---|---|
dev_id | THP002 |
dev_loc | Apex |
dev_name | BME280 |
And my table_name
is iot
Now I have all the basic elements I need to construct my ILP, which will look like this:
iot,dev_id=THP002,dev_loc=Apex,dev_name=BME280 temp_c=23.18,altitude=93.10,humidity=52.16,pressure=1002.12
And yes, I rounded those values. But you'll notice that I did not add a
timestamp
value. In this case, it's because I am sending the values from a
small, embedded sensor device that really doesn't have a great sense of time. By
sending the ILP without a timestamp
I'm telling the database itself to add one
for me, using the timestamp
s arrival time.
#
Database structureOne of the cool features of using the ILP reader (well, QuestDB in general, really) is the ability to do 'Schema on Write'.
What that means is that if an ILP message arrives, QuestDB will automatically
create tables and columns to fit the incoming ILP. So if you need to add a tag
later, you can add it to the new device's tagset and start writing. The new tag
will get added to the schema.
If you leave a tag value off, and it exists in the database, it will fill a
null
value.
When I start writing the above ILP to QuestDB, I'll get a table that looks like this:
dev_id | dev_name | temp_c | humidity | timestamp | dev_loc | altitude | pressure |
---|---|---|---|---|---|---|---|
THP002 | BME280 | 26.52 | 51.94 | 2020-07-21T14:54:59.156202Z | Apex | 76.27 | 1004.12 |
THP002 | BME280 | 26.54 | 51.85 | 2020-07-21T14:54:59.157358Z | Apex | 75.97 | 1004.16 |
THP002 | BME280 | 26.56 | 51.83 | 2020-07-21T14:54:59.157389Z | Apex | 75.84 | 1004.17 |
THP002 | BME280 | 26.58 | 51.79 | 2020-07-21T14:54:59.287416Z | Apex | 75.93 | 1004.16 |
This is what that table looks like in the QuestDB Web Console:
#
But how did you write that?Oh, so how did I write that ILP to QuestDB? Well, my sensor is an Arduino, with
a Bosch BME280 sensor attached. It is WiFi connected, so a WiFiClient
can do
the TCP write for me:
Will connect the client to the QuestDB Server defined by Quest_Server
on port
9009
.
If I then have a line of ILP like this:
iot,dev_id=THPL002,dev_loc=Demo,dev_name=BME280 temp_c=23.18,altitude=93.10,humidity=52.16,pressure=1002.12\n
in a buffer
I can call espClient.printf(buffer);
and that line of data will
be written to QuestDB.
#
Can I do batch writes?Of course, you can! Put each line of ILP on a separate 'line', separated by a
newline \n
, and then when you have all your batch built up, write the whole
thing to QuestDB.
Of course, if you're relying on QuestDB to add timestamps
for you, then be
aware that QuestDB will give the entire batch sequential timestamps based on
when they are read/written to the database.
#
ConclusionsInfluxDB Line Protocol (ILP) is a simple, well-known, and relatively compact data format for sending Time Series data to a database. That's why we decided to support it.
I'm now going to give you a simple configuration change to migrate from using
InfluxDB to using QuestDB. If you're using Telegraf as a data collector, we have
documentation for integrating with QuestDB
for more details, but to summarize the migration, edit telegraf.conf
(it may
be in different places, depending on your operating system) and change the line:
to be:
That's it. That's the migration. All data that was previously being written to InfluxDB will now be written to QuestDB.