What's the best way to upload an ILP file into QuestDB?

Community
1

There was a question with the same title 1 year and 7 months ago on stack overflow, and this question is closed.

I used the script from Javier:

import socket
import sys

sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)

def send_utf8(msg):
    print(msg)
    sock.sendall(msg.encode())

if __name__ == '__main__':
    try:
        sock.connect(('localhost', 9009))
        with open("YOUR_FILE") as infile:
            for line in infile:
                # print(line)
                send_utf8(line)
    except socket.error as e:
        sys.stderr.write(f'Got error: {e}')
    sock.close()

it works but after some seconds I got an error:

Got error: [Errno 32] Broken pipe

I tried to slow down the script with a sleep(0.01), so I got a little more data into questDB but after some minutes there comes also the error again. And I also tried something with “signal” I found on stack overflow, but I din’t get it work for my whole file with 18GB. Can somebody help me to extend the script for continuous work.

2

Hi @bob3150 ,

One way to achieve this is using the Time-Series Benchmark Suite (TSBS). GitHub - questdb/tsbs: Time Series Benchmark Suite, a tool for comparing and evaluating databases for time series data

This is a CLI tool which allows you to benchmark ingestion and querying performance for QuestDB, and some other databases.

You can use the tsbs_load_questdb tool to load an ILP file into QuestDB. See instructions here: tsbs/docs/questdb.md at a090657be3c89811c968bb3ef6fa17f2d3489eb3 · questdb/tsbs · GitHub

You should be able to skip the ‘generate data’ steps, as you already have a file to send. Also, you can skip the gzip steps.

Alternatively, you can modify your original approach and instead send the lines in POST requests to /write HTTP endpoint i.e at localhost:9000/write. This endpoint accepts ILP text and inserts it into your table.

3

Hi,

I tried the TSBS-Tool with my data

./tsbs_load_questdb --file ~/Downloads/test.lp --workers 4
time,per. metric/s,metric total,overall metric/s,per. row/s,row total,overall row/s
2024/09/05 01:04:19 parse error: line does not have 3 tuples, has 4

and get a parse error, do you know what I can do?

The lines of the .lp looks like the following

Füllstände Endlager=3.0931365489959717 1642082238000000000
4

I tried a little bit more and found out that there is a problem with number of lines:

60.000 lines work

head -n 60000 ~/Downloads/biogas.lp >> ~/Downloads/test.lp./tsbs_load_questdb --file ~/Downloads/test.lp --workers 4
time,per. metric/s,metric total,overall metric/s,per. row/s,row total,overall row/s

Summary:
loaded 60000 metrics in 0.007sec with 4 workers (mean rate 9171857.68 metrics/sec)
loaded 60000 rows in 0.007sec with 4 workers (mean rate 9171857.68 rows/sec)

60.001 do not work, and there is no problem in line 60.001 → I also tried do delete line 1 from 60.001 and the file work’s.

head -n 60001 ~/Downloads/biogas.lp >> ~/Downloads/test.lp
./tsbs_load_questdb --file ~/Downloads/test.lp --workers 4
time,per. metric/s,metric total,overall metric/s,per. row/s,row total,overall row/s
2024/09/06 18:08:35 parse error: line does not have 3 tuples, has 4

Who knows what can be the problem?