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):

1. Create a table

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

2. 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.

Answer 1

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()