Migrate from InfluxDB

QuestDB supports the InfluxDB Line Protocol (ILP).

Update existing InfluxDB clients with a QuestDB address for a drop-in replacement.

ILP over HTTP requires WAL-enabled tables.

A chart showing high-cardinality ingestion performance of InfluxDB, TimescaleDB, and QuestDB
Benchmark results for QuestDB 7.1.3, InfluxDB 2.7.1

Overview#

As of QuestDB 7.3.9, QuestDB supports the InfluxDB Line Protocol over HTTP. Previously, ILP ran over TCP. To workaround limitations in using TCP for streaming data, ILP over HTTP offers benefits like feedback and chunking with no performance overhead.

Tables in InfluxDB are created if they do not exist. Therefore, porting existing usage of an ILP client library into QuestDB will typically setup as needed to then flow data from an InfluxDB instance into a QuestDB instance.

ILP over HTTP also allows teams to use InfluxDB clients with only a change in connecting address. Thus this guide focuses on the usage of existing InfluxDB clients. The QuestDB clients will soon be updated to support InfluxDB Line Protocol over HTTP. We recommend using the QuestDB clients at that time.

Open source or Enterprise#

If you are running ILP on QuestDB Enterprise:

  1. Basic authentication credentials are required, default u: admin p: quest
  2. ILP over HTTP must be enabled in server.conf via line.http.enabled=true

Remember to change default credentials!

For open source, ILP over HTTP is enabled by default & arbitrary or blank credentials are accepted.

Example with Python#

If QuestDB is not up and running, see the quick start.

Our example is adapted from the InfluxDB Python client repo.

There are many ways that the client can be utilized.

The InfluxDB clients have many examples, and you may already be deep into a usage pattern.

Therefore the example below provides trivialized write examples.

Switch client URLs#

By default, the InfluxDB connection string of http://localhost:8086 is included in client examples.

With ILP on QuestDB, first change url in the client method to an active QuestDB instance.

The default QuestDB endpoint is: http://localhost:9000.

If you would prefer to use 8086 or any port, alter the HTTP port via http.bind.to in server.conf:

http.bind.to=0.0.0.0:8086

Now apply the client & write as per usual:

from datetime import datetime
from influxdb_client import InfluxDBClient, Point, WritePrecision
from influxdb_client.client.write_api import SYNCHRONOUS
# Create a new client - note the QuestDB HTTP port (9000)
# Pass the username and password for basic auth
# If Enterprise, swap clients to use below username & password string:
# client = InfluxDBClient(url="http://localhost:9000", username="admin", password="questdb")
client = InfluxDBClient(url="http://localhost:9000", token="")
# Use blocking write client for writes to desired bucket
write_api = client.write_api(write_options=SYNCHRONOUS)
# Create point using full params constructor
p = Point("stat").tag("unit", "temperature").field("avg", 24.5).field("max", 45.0).time(datetime.utcnow(), WritePrecision.NS)
# Write point immediately
write_api.write(bucket="qdb", record=p, write_precision=WritePrecision.NS)
# Create point using fluent style
p = Point("stat").tag("unit", "temperature").field("avg", 23.2).field("max", 45.0).time(datetime.utcnow(), WritePrecision.NS)
# Write point
write_api.write(bucket="qdb", record=p, write_precision=WritePrecision.NS)
# Or write directly via line protocol
line = "stat,unit=temperature avg=23.5,max=45.0"
write_api.write(bucket="qdb", record=line)

Authentication is not yet supported unless on QuestDB Enterprise - as such, pass empty or arbitrary values for token, org, and bucket. Also, db parameters are not yet supported and thus a user cannot alter the underying QuestDB instance.

Features#

Error handling#

Our example table above created avg & max columns which were given inferred types of double:

p = Point("stat").tag("unit", "temperature").field("avg", 24.5).field("max", 45.0).time(datetime.utcnow(), WritePrecision.NS)

If a request were then to be made that, say, passed strings instead:

p = Point("stat").tag("unit", "temperature").field("avg", "24.5").field("max", "45.0").time(datetime.utcnow(), WritePrecision.NS)

A helpful error will be presented:

panic: invalid: failed to parse line protocol:errors encountered on line(s):
error in line 1: table: stat, column: avg; cast error from protocol type: STRING to column type: DOUBLE

Precision#

Pass precision parameters to ensure correct timestamp increment:

...
p = Point("stat").tag("unit", "temperature").field("avg", 24.5).field("max", 45.0).time(datetime.utcnow(), WritePrecision.NS)
...

Precision parameters include n or ns for nanoseconds, u or us for microseconds, ms for milliseconds, s for seconds, m for minutes and h for hours. Otherwise, it will default to nanoseconds.

Ping! Health check#

Is your server running? Well then you'd better... catch it!

But in all seriousness, a health check endpoint is always of high usefulness.

curl -I http://localhost:9000/ping

Returns (pong!):

HTTP/1.1 204 OK
Server: questDB/1.0
Date: Fri, 2 Feb 2024 17:09:38 GMT
Transfer-Encoding: chunked
Content-Type: text/plain; charset=utf-8
X-Influxdb-Version: v2.7.4

Determine whether an instance is active and confirm the version of InfluxDB Line Protocol with which you are interacting.

Transactionality caveat#

As of writing, the HTTP endpoint does not provide full transactionality in all cases.

Specifically:

  • If an HTTP request contains data for two tables and the final commit fails for the second table, the data for the first table will still be committed. This is a deviation from full transactionality, where a failure in any part of the transaction would result in the entire transaction being rolled back.

  • When adding new columns to a table, an implicit commit occurs each time a new column is added. If the request is aborted or has parse errors, this commit cannot be rolled back.

Transform data in QuestDB#

InfluxDB exports only one metric for each line. If you are storing more than one metric for the same series, one row will create multiple ILP lines with one valid metric value. The other metrics then show as NULL. Therefore, once your data arrives we recommend transforming it in QuestDB.

For example, if you query a table with several metrics:

SELECT * FROM diagnostics WHERE timestamp = '2016-01-01T00:00:00.000000Z' AND driver='Andy' AND name='truck_150')

Your result may be something like this:

device_versiondriverfleetmodelcurrent_loadtimestampfuel_capacityfuel_stateload_capacitynominal_fuel_consumptionstatusname
v1.0AndyWestH-2null2016-01-01T00:00:00.000000Znullnull150nullnulltruck_150
v1.0AndyWestH-2null2016-01-01T00:00:00.000000Znullnull15012nulltruck_150
v1.0AndyWestH-2null2016-01-01T00:00:00.000000Znullnull150nullnulltruck_150
v1.0AndyWestH-202016-01-01T00:00:00.000000Znullnull150nullnulltruck_150
v1.0AndyWestH-2null2016-01-01T00:00:00.000000Z150null150nullnulltruck_150
v1.0AndyWestH-2null2016-01-01T00:00:00.000000Znull1150nullnulltruck_150

To solve this, execute a SQL query grouping data by all dimensions and then select the maximum values for all the metrics:

SELECT
timestamp,
device_version,
driver,
fleet,
model,
name,
max(current_load) AS current_load,
max(fuel_capacity) AS fuel_capacity,
max(fuel_state) AS fuel_state,
max(load_capacity) AS load_capacity,
max(nominal_fuel_consumption) AS nominal_fuel_consumption,
max(status) AS status
FROM
diagnostics;

This produces aggregated rows containing all the metrics for each dimension group:

timestampdevice_versiondriverfleetmodelnamecurrent_loadfuel_capacityfuel_stateload_capacitynominal_fuel_consumptionstatus
2016-01-01T00:00:00.000000Zv1.0DerekEastG-2000truck_324803001nullnullnull
2016-01-01T00:00:00.000000Zv1.0DerekEastG-2000truck_2222030015000190
2016-01-01T00:00:00.000000Zv1.0DerekEastG-2000truck_1886030015000190
2016-01-01T00:00:00.000000Zv1.0DerekEastG-2000truck_1540030015000190
... (rows continue) ....................................
2016-01-01T00:00:00.000000Zv1.0AndyWestH-2truck_583015011500120
2016-01-01T00:00:00.000000Zv1.0AndyWestH-2truck_3546015011500120
2016-01-01T00:00:00.000000Zv1.0AndyWestH-2truck_3247015011500120

You can use the INSERT keyword to output the processed result into a new table.

Also, if you leverage Telegraf, you can do this with a small configuration change:

# -- AGGREGATOR PLUGINS ------------------------------------------------- #
# Merge metrics into multifield metrics by series key
[[aggregators.merge]]
## If true, the original metric will be dropped by the
## aggregator and will not get sent to the output plugins.
drop_original = true

Dig deeper#

Now that you know the basics of InfluxDB Line Protocol, you may want to dig deeper into customization options for ILP or HTTP, learn more about the protocol, or compare QuestDB with Influx:


โญ Something missing? Page not helpful? Please suggest an edit on GitHub.