Issue writing from Influx Line Protocol to QuestDB

Question

My problem is that I cannot wrote data to Quest DB with Influx Line Protocol (ILP) is I have an already created table with a schema. If I have an empty table (i.e. with no schema) then I can do the schema and the schema is auto-created.

My questions are:

Why does this happen?
How can I make it work (if possible)?

Below I describe what I do (on QuestDB server 5.0.6 started with a docker container):

Create a table

CREATE TABLE my_table(
    location SYMBOL,
    car_brand SYMBOL,
    ts TIMESTAMP,
    kmph FLOAT,
    age INT
) timestamp(ts)  PARTITION BY MONTH;

In Python then I try and write to the table

import time
import socket
HOST = 'localhost'
PORT = 9009
# For UDP, change socket.SOCK_STREAM to socket.SOCK_DGRAM
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
    sock.connect((HOST, PORT))
    sock.send('my_table,location=london,car_brand=vw kmph=281.14000000,age=2 1420701827750051000\n'.encode())
except socket.error as e:
    print("Got error: %s" % (e))
sock.close()

This creates two problems:

It does not write anything to the table (the table is with no rows after this) and it does not raise any error
if it did not manage to write, I’d expect an error.

Then I thought maybe it has to do with the designated timestamp / partitioning, so I created a new table:

CREATE TABLE my_table_v2(
    location SYMBOL,
    car_brand SYMBOL,
    ts TIMESTAMP,
    kmph FLOAT,
    age INT
);

Writing to it yield the same result - nothing was written.

Finally I created an empty table (no schema) I tried like this (but it did not work)

CREATE TABLE my_empty_table();

so I did a workaround like so;

CREATE TABLE my_empty_table(smth INT);

and then removed the column:

ALTER TABLE my_empty_table
DROP COLUMN smth;

Then when I wrote the data to the empty table - all worked as expected…

Ideally I would like to define the schema and then write to the table and ideally if it fails to write I’d like to capture this somehow.

Brian Smith · Accepted Answer · 2021-04-29T15:39:11.300

The first python example throws an error in the QuestDB logs:

E i.q.c.l.t.LineTcpMeasurementScheduler mismatched column and value types [table=so_table, column=kmph, columnType=FLOAT, valueType=DOUBLE]

If the table has DOUBLE column type for kmph. age will also throw a similar parsing error. The schema that you want to create is

CREATE TABLE new_ilp_table(
    location SYMBOL,
    timestamp TIMESTAMP,
    car_brand SYMBOL,
    kmph double,
    age long
) timestamp(timestamp)  PARTITION BY MONTH;

So the following types need to be changed:

FLOAT -> DOUBLE
INT -> LONG

For more information on the data types, see ILP data types documentation

And per the error above, you can have a look out for LineTcpMeasurementScheduler in QuestDB logs

Edit:

int and float types can be stored as expected in version 6 of QuestDB. The default types for numerics are long and double but if a table is manually created with any equivalent type of lower resolution, this is handled as expected.

This is working with the beta version of 6.0:

docker pull questdb/questdb:6.0.0-beta-linux-amd64
docker run -p 9000:9000 -p 8812:8812 -p 9009:9009 \ 
questdb/questdb:6.0.0-beta-linux-amd64

SQL to create table

CREATE TABLE ilp_table(
    location SYMBOL,
    car_brand SYMBOL,
    kmph FLOAT,
    age INT,
    ts TIMESTAMP
) timestamp(ts)  PARTITION BY MONTH;

Python example to write to this table

import time
import socket
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
  sock.connect(('localhost', 9009))
  sock.send(('master_ilpf_table,location=london,car_brand=vw kmph=1.1,age=2i %d\n' %(time.time_ns())).encode())
except socket.error as e:
  print("Got error: %s" % (e))
sock.close()

Issue writing from Influx Line Protocol to QuestDB

1 Answers1